Tworzenie grafowej bazy danych i uruchamianie niektórych zapytań pasujących do wzorca przy użyciu języka T-SQL

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje bazy danychSQL Sql Database azure SQLDatabasew usłudze Microsoft Fabric

W tym przykładzie przedstawiono skrypt Transact-SQL umożliwiający utworzenie grafowej bazy danych z węzłami i krawędziami, a następnie użycie nowej klauzuli MATCH w celu dopasowania niektórych wzorców i przechodzenia przez graf. Ten przykładowy skrypt działa zarówno w usługach Azure SQL Database, jak i SQL Server 2017 (14.x) i nowszych wersjach.

Przykładowy schemat

Ten przykład tworzy schemat grafu dla hipotetycznej sieci społecznościowej, która ma węzły People, Restauranti City. Te węzły są połączone ze sobą przy użyciu krawędzi Friends, Likes, LivesIni LocatedIn. Na poniższym diagramie przedstawiono przykładowy schemat z krawędziami restaurant, city, person i LivesIn, LocatedIn, Likes krawędziami.

Przykładowy scenariusz

Poniższy przykładowy skrypt używa nowej składni języka T-SQL do tworzenia tabel węzłów i krawędzi. Dowiedz się, jak wstawiać dane do tabel węzłów i krawędzi przy użyciu instrukcji INSERT, a także pokazuje, jak używać klauzuli MATCH do dopasowywania wzorców i nawigacji.

Ten skrypt wykonuje następujące kroki:

1. Utwórz bazę danych o nazwie GraphDemo.
2. Tworzenie tabel węzłów.
3. Tworzenie tabel krawędzi.

-- Create a GraphDemo database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'graphdemo')
    CREATE DATABASE GraphDemo;
GO

USE GraphDemo;
GO

-- Create NODE tables
CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE Restaurant (
  ID INTEGER NOT NULL,
  name VARCHAR(100),
  city VARCHAR(100)
) AS NODE;

CREATE TABLE City (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100),
  stateName VARCHAR(100)
) AS NODE;

-- Create EDGE tables.
CREATE TABLE likes (rating INTEGER) AS EDGE;
CREATE TABLE friendOf AS EDGE;
CREATE TABLE livesIn AS EDGE;
CREATE TABLE locatedIn AS EDGE;

Teraz wstawimy dane reprezentujące relacje.

1. Wstaw dane do tabel węzłów.
   1. Wstawianie do tabeli węzłów jest takie samo jak wstawianie do regularnej tabeli.
2. W tym przypadku wstaw dane do tabel brzegowych, dla których każda osoba lubi się do krawędzi likes.
   1. Podczas wstawiania do tabeli krawędzi podaj $node_id z kolumn $from_id i $to_id.
3. Wstaw dane do krawędzi livesIn, aby skojarzyć osoby z miastem, w którym mieszkają.
4. Wstaw dane do krawędzi locatedIn, aby skojarzyć restauracje z miastem, w którym się znajdują.
5. Wstaw dane do krawędzi friendOf do skojarzonych znajomych.

-- Insert data into node tables. Inserting into a node table is same as inserting into a regular table
INSERT INTO Person (ID, name)
    VALUES (1, 'John')
         , (2, 'Mary')
         , (3, 'Alice')
         , (4, 'Jacob')
         , (5, 'Julie');

INSERT INTO Restaurant (ID, name, city)
    VALUES (1, 'Taco Dell','Bellevue')
         , (2, 'Ginger and Spice','Seattle')
         , (3, 'Noodle Land', 'Redmond');

INSERT INTO City (ID, name, stateName)
    VALUES (1,'Bellevue','WA')
         , (2,'Seattle','WA')
         , (3,'Redmond','WA');

-- Insert into edge table. While inserting into an edge table,
-- you need to provide the $node_id from $from_id and $to_id columns.
/* Insert which restaurants each person likes */
INSERT INTO likes
    VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
         , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9);

/* Associate in which city live each person*/
INSERT INTO livesIn
    VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1))
         , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2))
         , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3))
         , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
         , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1));

/* Insert data where the restaurants are located */
INSERT INTO locatedIn
    VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1))
         , ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2))
         , ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3));

/* Insert data into the friendOf edge */
INSERT INTO friendOf
    VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
         , ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));

Następnie odpytujemy dane, aby znaleźć szczegółowe informacje na podstawie danych.

1. Użyj funkcji MATCH grafu, aby znaleźć restauracje, które lubi John.
2. Znajduje restauracje, które przyjaciele Johna lubią.
3. Znajdź ludzi, którzy lubią restaurację w tym samym mieście, w którym mieszkają.

-- Find Restaurants that John likes
SELECT Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)->Restaurant)
AND Person.name = 'John';

-- Find Restaurants that John's friends like
SELECT Restaurant.name
FROM Person person1, Person person2, likes, friendOf, Restaurant
WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant)
AND person1.name='John';

-- Find people who like a restaurant in the same city they live in
SELECT Person.name
FROM Person, likes, Restaurant, livesIn, City, locatedIn
WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);

Wreszcie, bardziej zaawansowane zapytanie znajduje przyjaciół przyjaciół przyjaciół przyjaciół. To zapytanie wyklucza te przypadki, w których relacja "powraca". Na przykład Alicja jest przyjacielem Johna; Jan jest przyjacielem Mary; i Mary z kolei jest przyjacielem Alicji. Powoduje to powrót "pętli" do Alicji. W wielu przypadkach konieczne jest jawne sprawdzenie takich pętli i wykluczenie wyników.

-- Find friends-of-friends-of-friends, excluding those cases where the relationship "loops back".
-- For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice.
-- This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
SELECT CONCAT(Person.name, '->', Person2.name, '->', Person3.name, '->', Person4.name)
FROM Person, friendOf, Person as Person2, friendOf as friendOffriend, Person as Person3, friendOf as friendOffriendOfFriend, Person as Person4
WHERE MATCH (Person-(friendOf)->Person2-(friendOffriend)->Person3-(friendOffriendOfFriend)->Person4)
AND Person2.name != Person.name
AND Person3.name != Person2.name
AND Person4.name != Person3.name
AND Person.name != Person4.name;

Sprzątanie

Wyczyść schemat i bazę danych utworzoną dla przykładu w programie SQL Server.

USE graphdemo;
go

DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Restaurant;
DROP TABLE IF EXISTS City;
DROP TABLE IF EXISTS friendOf;
DROP TABLE IF EXISTS livesIn;
DROP TABLE IF EXISTS locatedIn;

USE master;
go
DROP DATABASE graphdemo;
go

Wyczyść schemat i bazę danych utworzoną dla przykładu w usłudze Azure SQL Database.

--Connect to the graphdemo database
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Restaurant;
DROP TABLE IF EXISTS City;
DROP TABLE IF EXISTS friendOf;
DROP TABLE IF EXISTS livesIn;
DROP TABLE IF EXISTS locatedIn;

--Connect to the master database
DROP DATABASE graphdemo;
go

Dalsze kroki

• Przetwarzanie grafów
• CREATE TABLE (SQL Graph)
• INSERT (graf SQL)
• MATCH (Transact-SQL)