Membuat database grafik dan menjalankan beberapa kueri pencocokan pola menggunakan T-SQL

Berlaku untuk: SQL Server 2017 (14.x) dan azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Sampel ini menyediakan skrip Transact-SQL untuk membuat database grafik dengan simpul dan tepi lalu menggunakan klausa MATCH baru untuk mencocokkan beberapa pola dan melintasi grafik. Contoh skrip ini berfungsi pada Azure SQL Database dan SQL Server 2017 (14.x) dan versi yang lebih baru.

Sampel Skema

Sampel ini membuat skema grafik untuk jaringan sosial hipotetis yang memiliki Peoplesimpul , dan RestaurantCity . Simpul ini terhubung satu sama lain menggunakan Friendstepi , Likes, LivesIn dan LocatedIn . Diagram berikut menunjukkan skema sampel dengan restauranttepi , , cityperson node dan LivesIn, LocatedIn, Likes .

Skrip Sampel

Contoh skrip berikut menggunakan sintaks T-SQL baru untuk membuat tabel simpul dan tepi. Pelajari cara menyisipkan data ke dalam tabel simpul dan tepi menggunakan INSERT pernyataan dan juga memperlihatkan cara menggunakan MATCH klausa untuk pencocokan pola dan navigasi.

Skrip ini melakukan langkah-langkah berikut:

1. Buat database bernama GraphDemo.
2. Membuat tabel simpul.
3. Membuat tabel edge.

-- 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;

Sekarang, kita akan menyisipkan data untuk mewakili hubungan.

1. Sisipkan data ke dalam tabel simpul.
   1. Menyisipkan ke dalam tabel simpul sama dengan menyisipkan ke dalam tabel biasa.
2. Sisipkan data ke dalam tabel edge, dalam hal ini, di mana restoran yang disukai setiap orang ke likes tepi.
   1. Saat menyisipkan ke dalam tabel edge, berikan $node_id kolom dari $from_id dan $to_id .
3. Sisipkan data ke livesIn tepi untuk mengaitkan orang dengan kota tempat mereka tinggal.
4. Sisipkan data ke locatedIn tepi untuk mengaitkan restoran dengan kota tempat mereka berada.
5. Sisipkan data ke friendOf tepi ke teman terkait.

-- 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));

Selanjutnya, kita akan mengkueri data untuk menemukan wawasan dari data.

1. Gunakan fungsi graph MATCH untuk menemukan restoran mana yang disukai John.
2. Menemukan restoran yang disukai teman John.
3. Temukan orang-orang yang menyukai restoran di kota yang sama tempat mereka tinggal.

-- 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);

Akhirnya, kueri yang lebih canggih menemukan teman-teman teman. Kueri ini mengecualikan kasus-kasus di mana hubungan "mengulang kembali". Misalnya, Alice adalah teman John; Yohanes adalah teman Maryam. Dan Maryam adalah teman Alice. Ini menyebabkan "perulangan" kembali ke Alice. Dalam banyak kasus, perlu untuk secara eksplisit memeriksa perulangan tersebut dan mengecualikan hasilnya.

-- 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;

Pembersihan

Bersihkan skema dan database yang dibuat untuk sampel di 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

Bersihkan skema dan database yang dibuat untuk sampel di 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

Langkah berikutnya

• Pemrosesan Graph
• CREATE TABLE (SQL Graph)
• INSERT (SQL Graph)
• MATCH (Transact-SQL)