T-SQL을 사용하여 그래프 데이터베이스를 만들고 몇 가지 패턴 일치 쿼리 실행

아티클
08/21/2023

적용 대상: SQL Server 2017(14.x) 이상 Azure SQL Database Azure SQL Managed Instance

이 샘플에서는 Transact-SQL 스크립트를 제공하여 노드와 가장자리가 있는 그래프 데이터베이스를 만든 다음 새 MATCH 절을 사용하여 일부 패턴을 일치시키고 그래프를 트래버스합니다. 이 샘플 스크립트는 Azure SQL Database 및 SQL Server 2017(14.x) 이상 버전 모두에서 작동합니다.

샘플 스키마

이 샘플에서는 노드가 있는 가상 소셜 네트워크에 PeopleRestaurantCity 대한 그래프 스키마를 만듭니다. 이러한 노드는 , LikesLivesIn 및 LocatedIn 에지를 사용하여 Friends서로 연결됩니다. 다음 다이어그램에서는 , cityperson 노드 및 LocatedInLikesLivesIn에지가 있는 restaurant샘플 스키마를 보여 줍니다.

Diagram showing a sample schema with restaurant, city, person nodes and LivesIn, LocatedIn, Likes edges.

샘플 스크립트

다음 샘플 스크립트는 새 T-SQL 구문을 사용하여 노드 및 에지 테이블을 만듭니다. 문을 사용하여 INSERT 노드 및 에지 테이블에 데이터를 삽입하는 방법을 알아보고 패턴 일치 및 탐색에 절을 사용하는 MATCH 방법도 보여 줍니다.

이 스크립트는 다음 단계를 수행합니다.

라는 데이터베이스를 만듭니다 GraphDemo.
노드 테이블을 만듭니다.
에지 테이블을 만듭니다.

-- 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. 노드 테이블에 삽입하는 것은 일반 테이블에 삽입하는 것과 같습니다.
에지 테이블에 데이터를 삽입합니다. 이 경우 각 사용자가 좋아하는 레스토랑이 likes 가장자리에 있습니다.
1. 에지 테이블에 삽입하는 동안 원본 $from_id 및 $to_id 열을 제공합니다$node_id.
에지에 livesIn 데이터를 삽입하여 사람이 거주하는 도시와 연결합니다.
에지에 locatedIn 데이터를 삽입하여 레스토랑이 위치한 도시와 연결합니다.
연결된 친구에게 에지에 데이터를 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));

다음으로 데이터를 쿼리하여 데이터에서 인사이트를 찾습니다.

그래프 MATCH 함수를 사용하여 John이 좋아하는 레스토랑을 찾습니다.
존의 친구들이 좋아하는 레스토랑을 찾습니다.
그들이 살고있는 같은 도시에서 레스토랑을 좋아하는 사람들을 찾을 수 있습니다.

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

마지막으로, 고급 쿼리는 친구의 친구를 찾습니다. 이 쿼리는 관계가 "루프백"되는 경우를 제외합니다. 예를 들어 Alice는 John의 친구입니다. 요한은 마리아의 친구입니다. 그리고 메리는 차례로 앨리스의 친구입니다. 그러면 "루프"가 Alice로 돌아갑니다. 대부분의 경우 이러한 루프를 명시적으로 확인하고 결과를 제외해야 합니다.

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

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

T-SQL을 사용하여 그래프 데이터베이스를 만들고 몇 가지 패턴 일치 쿼리 실행

샘플 스키마

샘플 스크립트

정리

다음 단계

피드백

피드백

추가 리소스