반응형
/*******************************************************************************************************************
-- Title : [SQL2017] Graph Table에서의 MATCH 연산자
-- Reference : www.sqlshack.com/introduction-sql-server-2017-graph-database/
-- Key word : 그래프 테이블 그래프테이블 그래프 데이터베이스 graph table graph database graph db
match 연산자 node edge 노드 엣지 그래프db 그래프 db graph db
*******************************************************************************************************************/
■ Why Graph DB?
■ Sample Tree
■ Script
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
use graphdb;
go
drop table emp, empnode, empReportsTo;
-- Create table
create table emp
(empno int not null,
ename varchar(20),
job varchar(10),
mgr int,
joindate datetime,
salary decimal(7, 2),
commisiion decimal(7, 2),
dno int
);
-- Insert recurvie data
Insert INTO dbo.EMP values
(7369, 'SMITH', 'CLERK', 7902, '1970-03-02', 8000, NULL, 2),
(7499, 'ALLEN', 'SALESMAN', 7698, '1971-03-20', 1600, 3000, 3),
(7521, 'WARD', 'SALESMAN', 7698, '1983-02-07', 1250, 5000, 3),
(7566, 'JONES', 'MANAGER', 7839, '1961-07-02', 2975, 50000, 2),
(7654, 'MARTIN', 'SALESMAN', 7698, '1971-02-28', 1250, 14000, 3),
(7698, 'BLAKE', 'MANAGER', 7839, '1988-01-01', 2850, 12000, 3),
(7782, 'CLARK', 'MANAGER', 7839, '1971-09-09', 2450, 13000, 1),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-10-09', 3000, 1200, 2),
(7839, 'KING', 'PRESIDENT', NULL, '1971-06-17', 5000, 1456, 1),
(7844, 'TURNER', 'SALESMAN', 7698, '1971-08-08', 1500, 0, 3),
(7876, 'ADAMS', 'CLERK', 7788, '1973-04-12', 1100, 0, 2),
(7900, 'JAMES', 'CLERK', 7698, '1971-10-03', 950, 0, 3),
(7902, 'FORD', 'ANALYST', 7566, '1961-04-04', 3000, 0, 2),
(7934, 'MILLER', 'CLERK', 7782, '1972-01-21', 1300, 0, 1);
-- Select data
select * from emp;
-- Create node table
CREATE TABLE dbo.EmpNode(
ID Int Identity(1,1),
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
MGR NUMERIC(4)
) AS NODE;
-- Insert node table
INSERT INTO EmpNode(EMPNO,ENAME,MGR)
select empno,ename,MGR from emp
-- Select node table
select * from EmpNode;
-- Create edge table
CREATE TABLE empReportsTo(Deptno int) AS EDGE
-- Insert edge table
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 1),
(SELECT $node_id FROM EmpNode WHERE id = 13),20);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 2),
(SELECT $node_id FROM EmpNode WHERE id = 6),10);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 3),
(SELECT $node_id FROM EmpNode WHERE id = 6),10)
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 4),
(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 5),
(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 6),
(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 7),
(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 8),
(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 9),
(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 10),
(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 11),
(SELECT $node_id FROM EmpNode WHERE id = 8),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 12),
(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 13),
(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 14),
(SELECT $node_id FROM EmpNode WHERE id = 7),30);
-- Select edge table
select * from empReportsTo
-- Select Meta
SELECT t.is_edge, t.name, *
FROM sys.tables t
WHERE name like 'emp%'
-- Select data w/ MATCH
SELECT E.EMPNO, E.ENAME, E.MGR
, E1.EMPNO, E1.ENAME, E1.MGR
FROM empnode e, empnode e1, empReportsTo m
WHERE MATCH(e-(m)->e1)
and e.ENAME = 'SMITH';
SELECT E.EMPNO, E.ENAME, E.MGR
, E1.EMPNO, E1.ENAME, E1.MGR
, E2.EMPNO, E2.ENAME, E2.MGR
FROM empnode e, empnode e1, empReportsTo m
, empReportsTo m1, empnode e2
WHERE MATCH(e-(m)->e1-(m1)->e2)
and e.ENAME='SMITH'
SELECT E.EMPNO, E.ENAME, E.MGR
, E1.EMPNO, E1.ENAME, E1.MGR
, E2.EMPNO, E2.ENAME, E2.MGR
, E3.EMPNO, E3.ENAME, E3.MGR
FROM empnode e, empnode e1, empReportsTo m
, empReportsTo m1, empnode e2
, empReportsTo m2, empnode e3
WHERE MATCH(e<-(m)-e1<-(m1)-e2<-(m2)-e3)
|
cs |
반응형