Creare un database a grafo ed eseguire alcune query di criteri di ricerca usando T-SQL

Si applica a: SQL Server 2017 (14.x) e versioni successive Istanza gestita di SQL di Azure di Azure

Questo esempio fornisce uno script Transact-SQL per creare un database a grafo con nodi e archi e quindi usare la nuova clausola MATCH per trovare una corrispondenza con alcuni modelli e attraversare il grafico. Questo script di esempio funziona sia nel database SQL di Azure che in SQL Server 2017 (14.x) e versioni successive.

Schema di esempio

Questo esempio crea uno schema del grafo per un social network ipotetico con Peoplenodi e RestaurantCity . Questi nodi sono connessi tra loro usando Friendsi bordi , LivesInLikese LocatedIn . Il diagramma seguente illustra uno schema di esempio con restaurantbordi , personcity, nodi e LivesIn, . LikesLocatedIn

Script di esempio

Lo script di esempio seguente usa la nuova sintassi T-SQL per creare tabelle del nodo e dei bordi. Informazioni su come inserire dati in tabelle node e edge usando INSERT l'istruzione e illustra anche come usare MATCH la clausola per la ricerca di criteri e la navigazione.

Questo script esegue i passaggi seguenti:

1. Creare un database denominato GraphDemo.
2. Creare tabelle dei nodi.
3. Creare tabelle perimetrali.

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

A questo punto verranno inseriti dati per rappresentare le relazioni.

1. Inserire i dati nelle tabelle dei nodi.
   1. L'inserimento in una tabella dei nodi equivale all'inserimento in una tabella normale.
2. Inserire i dati nelle tabelle dei bordi, in questo caso, per i ristoranti a cui ogni persona piace nel likes bordo.
   1. Durante l'inserimento in una tabella perimetrale, specificare le $node_id colonne da $from_id e $to_id .
3. Inserire i dati nel livesIn bordo per associare le persone alla città in cui vivono.
4. Inserire i dati nel locatedIn bordo per associare ristoranti alla città in cui si trovano.
5. Inserire i dati nel friendOf bordo per gli amici associati.

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

Successivamente, si eseguiranno query sui dati per trovare informazioni dettagliate dai dati.

1. Usare la funzione MATCH del grafo per trovare i ristoranti a cui piace John.
2. Trova i ristoranti come amici di John.
3. Trova persone che amano un ristorante nella stessa città in cui vivono.

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

Infine, una query più avanzata trova gli amici degli amici degli amici. Questa query esclude i casi in cui la relazione "esegue il loopback". Ad esempio, Alice è un amico di John; John è un amico di Maria; e Mary a sua volta è un amico di Alice. In questo modo un "ciclo" torna ad Alice. In molti casi, è necessario verificare in modo esplicito la presenza di tali cicli ed escludere i risultati.

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

Eseguire la pulizia

Pulire lo schema e il database creati per l'esempio in 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

Pulire lo schema e il database creati per l'esempio nel database SQL di 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

Passaggi successivi

• Elaborazione del grafico
• CREATE TABLE (grafo SQL)
• INSERT (grafo SQL)
• MATCH (Transact-SQL)