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

Articolo
11/19/2024

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

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 in 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 , Restaurante City . Questi nodi sono connessi tra loro usando Friendsi bordi , Likes, LivesIne LocatedIn . Il diagramma seguente illustra uno schema di esempio con restaurantarchi , person city, nodi e LivesIn, . Likes LocatedIn

Diagramma che mostra uno schema di esempio con i bordi ristorante, città, persona e LivesIn, LocatedIn, Likes.

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.

Lo script esegue quindi i passaggi seguenti:

Creare un database denominato GraphDemo.
Creare tabelle dei nodi.
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;

Ora vengono inseriti dati per rappresentare le relazioni.

Inserire i dati nelle tabelle dei nodi.
1. L'inserimento in una tabella dei nodi equivale all'inserimento in una tabella normale.
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 .
Inserire i dati nel livesIn bordo per associare le persone alla città in cui vivono.
Inserire i dati nel locatedIn bordo per associare ristoranti alla città in cui si trovano.
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 eseguono query sui dati per trovare informazioni dettagliate dai dati.

Usare la funzione MATCH del grafo per trovare i ristoranti a cui piace John.
Trova i ristoranti come amici di John.
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 in 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

Condividi tramite

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

Schema di esempio

Script di esempio

Eseguire la pulizia

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive