Создание базы данных графа и выполнение некоторых запросов сопоставления шаблонов с помощью T-SQL

Применимо к: SQL Server 2017 (14.x) и более поздних версий базы данных SQLAzure Для Управляемого экземпляра SQL Azureв Microsoft Fabric

В этом примере представлен скрипт Transact-SQL для создания базы данных графа с узлами и краями, а затем использовать новое предложение MATCH для сопоставления некоторых шаблонов и обхода по графу. Этот пример скрипта работает как на База данных SQL Azure, так и на SQL Server 2017 (14.x) и более поздних версиях.

Образцовая схема

В этом примере создается схема графа для гипотетической социальной сети с PeopleRestaurantузлами и City узлами. Эти узлы подключены друг к другу с помощью Friends, LikesLivesInи LocatedIn ребер. На следующей схеме показан пример схемы с restaurantребрами, citypersonузлами и LivesInребрамиLocatedInLikes.

Пример сценария

В следующем примере скрипта для создания узлов и пограничных таблиц используется новый синтаксис T-SQL. Узнайте, как вставить данные в таблицы узлов и пограничных таблиц с помощью INSERT инструкции, а также показано, как использовать MATCH предложение для сопоставления шаблонов и навигации.

Сценарий выполнит указанные ниже действия.

1. Создайте базу данных с именем GraphDemo.
2. Создание таблиц узлов.
3. Создание пограничных таблиц.

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

Теперь мы вставим данные для представления связей.

1. Вставка данных в таблицы узлов.
   1. Вставка в таблицу узлов аналогична вставке в обычную таблицу.
2. Вставьте данные в пограничные таблицы, в этом случае, для которых каждый пользователь любит likes в краю.
   1. При вставке в граничную таблицу укажите $node_id из $from_id и $to_id столбцов.
3. Вставьте данные в livesIn край, чтобы связать людей с городом, где они живут.
4. Вставьте данные в locatedIn край, чтобы связать рестораны с городом, где они находятся.
5. Вставьте данные в friendOf край для связанных друзей.

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

Затем мы запрашиваем данные, чтобы найти аналитические сведения из данных.

1. Используйте функцию GRAPH MATCH , чтобы найти, какие рестораны любит Джон.
2. Находит рестораны, как друзья Джона.
3. Найдите людей, которые любят ресторан в том же городе, в который они живут.

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

Наконец, более сложный запрос находит друзей друзей друзей. Этот запрос исключает те случаи, когда связь "циклирует обратно". Например, Алиса является другом Джоном; Джон друг Марии; и Мэри в свою очередь является другом Алисы. Это приводит к "циклу" обратно в Алису. Во многих случаях необходимо явно проверить наличие таких циклов и исключить результаты.

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

Очистка

Очистка схемы и базы данных, созданной для примера в 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

Очистите схему и базу данных, созданную для примера в База данных SQL Azure.

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

Дальнейшие шаги

• Обработка графов
• CREATE TABLE (граф SQL)
• INSERT (граф SQL)
• МАТЧ (Transact-SQL)