Graph durchsuchen

CREATE SCHEMA IF NOT EXISTS graph_search;

CREATE OR REPLACE TABLE graph_search.Person (
  id               INT64,
  name             STRING,
  PRIMARY KEY (id) NOT ENFORCED
);

CREATE OR REPLACE TABLE graph_search.Account (
  id                    INT64,
  create_time           TIMESTAMP,
  is_blocked            BOOL,
  description           STRING,
  description_embedding STRUCT<result ARRAY<FLOAT64>, status STRING>
                          GENERATED ALWAYS AS (
                          AI.EMBED(description, model => 'embeddinggemma-300m')
                          ) STORED OPTIONS( asynchronous = TRUE ),
  PRIMARY KEY (id) NOT ENFORCED
);

CREATE OR REPLACE TABLE graph_search.PersonOwnAccount (
  id               INT64 NOT NULL,
  account_id       INT64 NOT NULL,
  create_time      TIMESTAMP,
  PRIMARY KEY (id, account_id) NOT ENFORCED,
  FOREIGN KEY (id) REFERENCES graph_search.Person(id) NOT ENFORCED,
  FOREIGN KEY (account_id) REFERENCES graph_search.Account(id) NOT ENFORCED
);

CREATE OR REPLACE TABLE graph_search.AccountTransferAccount (
  id               INT64 NOT NULL,
  to_id            INT64 NOT NULL,
  amount           FLOAT64,
  create_time      TIMESTAMP NOT NULL,
  order_number     STRING,
  notes            STRING,
  notes_embedding  STRUCT<result ARRAY<FLOAT64>, status STRING>
                     GENERATED ALWAYS AS (
                     AI.EMBED(notes, model => 'embeddinggemma-300m')
                     ) STORED OPTIONS( asynchronous = TRUE ),
  PRIMARY KEY (id, to_id, create_time) NOT ENFORCED,
  FOREIGN KEY (id) REFERENCES graph_search.Account(id) NOT ENFORCED,
  FOREIGN KEY (to_id) REFERENCES graph_search.Account(id) NOT ENFORCED
);

INSERT INTO graph_search.Account
  (id, create_time, is_blocked, description)
VALUES
  (7,"2020-01-10 06:22:20.222",false,"Fund for a refreshing tropical vacation"),
  (16,"2020-01-27 17:55:09.206",true,"Fund for a rainy day!"),
  (20,"2020-02-18 05:44:20.655",false,"Saving up for travel");

INSERT INTO graph_search.Person
  (id, name)
VALUES
  (1,"Alex"),
  (2,"Dana"),
  (3,"Lee");

INSERT INTO graph_search.AccountTransferAccount
  (id, to_id, amount, create_time, order_number, notes)
VALUES
  (7,16,300,"2020-08-29 15:28:58.647","304330008004315", "wedding present"),
  (7,16,100,"2020-10-04 16:55:05.342","304120005529714", "birthday gift"),
  (16,20,300,"2020-09-25 02:36:14.926","103650009791820", "for shared cost of dinner"),
  (20,7,500,"2020-10-04 16:55:05.342","304120005529714", "fees for tuition"),
  (20,16,200,"2020-10-17 03:59:40.247","302290001484851", "loved the lunch");

INSERT INTO graph_search.PersonOwnAccount
  (id, account_id, create_time)
VALUES
  (1,7,"2020-01-10 06:22:20.222"),
  (2,20,"2020-01-27 17:55:09.206"),
  (3,16,"2020-02-18 05:44:20.655");

CREATE OR REPLACE PROPERTY GRAPH graph_search.FinGraph
NODE TABLES (graph_search.Account, graph_search.Person)
EDGE TABLES (
  graph_search.PersonOwnAccount
    SOURCE KEY (id) REFERENCES Person (id)
    DESTINATION KEY (account_id) REFERENCES Account (id)
    LABEL Owns,
  graph_search.AccountTransferAccount
    SOURCE KEY (id) REFERENCES Account (id)
    DESTINATION KEY (to_id) REFERENCES Account (id)
    LABEL Transfers
);

DECLARE similar_account DEFAULT ((
SELECT ARRAY_AGG(base.id)
FROM
  AI.SEARCH(
    (SELECT * FROM graph_search.Account WHERE description_embedding IS NOT NULL),
    'description',
    'accounts for leisure travel and vacation',
    top_k => 2)
));

GRAPH graph_search.FinGraph
MATCH (p:Person)-[:Owns]->(a:Account)
WHERE a.id IN UNNEST(similar_account)
RETURN p.name, a.description;

+------+-----------------------------------------+
| name | description                             |
+------+-----------------------------------------+
| Dana | Saving up for travel                    |
| Alex | Fund for a refreshing tropical vacation |
+------+-----------------------------------------+

DECLARE food_transfers DEFAULT ((
SELECT ARRAY_AGG(base.order_number)
FROM
  AI.SEARCH(
    (SELECT * FROM graph_search.AccountTransferAccount WHERE notes_embedding IS NOT NULL),
    'notes',
    'food',
    top_k => 2)
));

GRAPH graph_search.FinGraph
MATCH (p:Person)-[:Owns]->(:Account)-[t:Transfers]->(:Account)
WHERE t.order_number IN UNNEST(food_transfers)
RETURN p.name, t.notes;

+------+---------------------------+
| name | notes                     |
+------+---------------------------+
| Dana | loved the lunch           |
| Lee  | for shared cost of dinner |
+------+---------------------------+