Creación de una base de datos de grafos y ejecución de algunas consultas de coincidencia de patrones mediante T-SQL
Se aplica a:
SQL Server 2017 (14.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance
En este ejemplo se proporciona un script transact-SQL para crear una base de datos de grafos con nodos y bordes y, a continuación, usar la nueva cláusula MATCH para que coincida con algunos patrones y recorrer el gráfico. Este script de ejemplo funciona tanto en Azure SQL Database como en SQL Server 2017 (14.x) y versiones posteriores.
Esquema de ejemplo
En este ejemplo se crea un esquema de gráfico para una red social hipotética que tiene Peoplenodos y RestaurantCity . Estos nodos se conectan entre sí mediante Friendsbordes , LivesInLikesy LocatedIn . En el diagrama siguiente se muestra un esquema de ejemplo con restaurantbordes , city, person nodos y LivesIn, LocatedIn. Likes
Script de ejemplo
El siguiente script de ejemplo usa la nueva sintaxis de T-SQL para crear tablas perimetrales y de nodo. Obtenga información sobre cómo insertar datos en tablas perimetrales y de nodo mediante INSERT la instrucción y también muestra cómo usar MATCH la cláusula para la coincidencia de patrones y la navegación.
Este script realiza los pasos siguientes:
- Cree una base de datos denominada
GraphDemo. - Crear tablas de nodo.
- Crear tablas perimetrales.
-- 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;
Ahora, insertaremos datos para representar las relaciones.
- Insertar datos en tablas de nodo.
- La inserción en una tabla de nodos es la misma que la inserción en una tabla normal.
- Inserte datos en tablas perimetrales, en este caso, para los que cada persona quiera en el
likesperímetro.- Al insertar en una tabla perimetral, proporcione las
$node_idcolumnas from$from_idy$to_id.
- Al insertar en una tabla perimetral, proporcione las
- Inserte datos en el
livesInperímetro para asociar personas a la ciudad donde viven. - Inserte datos en el
locatedInperímetro para asociar restaurantes a la ciudad donde se encuentran. - Inserte datos en el
friendOfborde a los amigos asociados.
-- 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));
A continuación, consultaremos los datos para buscar información de los datos.
- Use la función MATCH del grafo para buscar qué restaurantes le gusta John.
- Encuentra los restaurantes a los que le gustan los amigos de John.
- Encuentra gente que le gusta un restaurante en la misma ciudad en la que viven.
-- 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);
Por último, una consulta más avanzada busca a los amigos de amigos. Esta consulta excluye los casos en los que la relación "vuelve a bucles". Por ejemplo, Alice es amiga de John; Juan es amigo de María; y Mary a su vez es amiga de Alice. Esto hace que un "bucle" vuelva a Alice. En muchos casos, es necesario comprobar explícitamente estos bucles y excluir los resultados.
-- 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;
Limpieza
Limpie el esquema y la base de datos creados para el ejemplo en 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
Limpie el esquema y la base de datos creados para el ejemplo en 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