Статьи

Northwind: поиск прямых / транзитивных отчетов в SQL и Cypher Neo4j

Декабрь 14, 2018

Каждые несколько месяцев мы проводим встречу по реляционному графику в офисе в Нео-Лондоне, где рассказываем, как перенести ваши данные из реляционной базы данных в граф.

Мы используем набор данных Northwind, который часто поставляется как демонстрационный набор данных в реляционных базах данных, и придумываем некоторые запросы, которые кажутся графическими по своей природе.

Мой любимый запрос — это вопрос о том, как организованы сотрудники и кто перед кем отчитывается. Я подумал, что было бы довольно интересно посмотреть, как это будет выглядеть в Postgres SQL, просто для удовольствия.

Мы начнем с получения списка сотрудников и лица, которому они сообщают:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
SELECT e."EmployeeID", e."ReportsTo"
FROM employees AS e
WHERE e."ReportsTo" IS NOT NULL;
  
 EmployeeID | ReportsTo
------------+-----------
          1 |         2
          3 |         2
          4 |         2
          5 |         2
          6 |         5
          7 |         5
          8 |         2
          9 |         5
(8 ROWS)

В Cypher мы бы сделали это:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
MATCH (e:Employee)<-[:REPORTS_TO]-(sub)
RETURN sub.EmployeeID, e.EmployeeID
  
+-------------------------------+
| sub.EmployeeID | e.EmployeeID |
+-------------------------------+
| "4"            | "2"          |
| "5"            | "2"          |
| "1"            | "2"          |
| "3"            | "2"          |
| "8"            | "2"          |
| "9"            | "5"          |
| "6"            | "5"          |
| "7"            | "5"          |
+-------------------------------+
8 rows

Теперь давайте найдем большого босса, который никому не подчиняется. Сначала в SQL:

1
2
3
4
5
6
7
8
SELECT e."EmployeeID" AS bigBoss
FROM employees AS e
WHERE e."ReportsTo" IS NULL
  
 bigboss
---------
       2
(1 ROW)

А теперь шифр

01
02
03
04
05
06
07
08
09
10
MATCH (e:Employee)
WHERE NOT (e)-[:REPORTS_TO]->()
RETURN e.EmployeeID AS bigBoss
  
+---------+
| bigBoss |
+---------+
| "2"     |
+---------+
1 row

Нам по-прежнему не нужно ничего объединять, поэтому запрос пока не так интересен. Давайте добавим еще несколько свойств из записи менеджера, поэтому нам нужно самостоятельно присоединиться к таблице сотрудников:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
SELECT e."FirstName", e."LastName", e."Title", manager."FirstName", manager."LastName", manager."Title"
FROM employees AS e
JOIN employees AS manager ON e."ReportsTo" = manager."EmployeeID"
WHERE e."ReportsTo" IS NOT NULL
  
 FirstName | LastName  |          Title           | FirstName | LastName |         Title
-----------+-----------+--------------------------+----------+-----+
 Nancy     | Davolio   | Sales Representative     | Andrew    | Fuller   | Vice President, Sales
 Janet     | Leverling | Sales Representative     | Andrew    | Fuller   | Vice President, Sales
 Margaret  | Peacock   | Sales Representative     | Andrew    | Fuller   | Vice President, Sales
 Steven    | Buchanan  | Sales Manager            | Andrew    | Fuller   | Vice President, Sales
 Michael   | Suyama    | Sales Representative     | Steven    | Buchanan | Sales Manager
 Robert    | King      | Sales Representative     | Steven    | Buchanan | Sales Manager
 Laura     | Callahan  | Inside Sales Coordinator | Andrew    | Fuller   | Vice President, Sales
 Anne      | Dodsworth | Sales Representative     | Steven    | Buchanan | Sales Manager
(8 ROWS)
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
MATCH (e:Employee)<-[:REPORTS_TO]-(sub)
RETURN sub.FirstName, sub.LastName, sub.Title, e.FirstName, e.LastName, e.Title
  
+--------------------------------------------------------------+
| sub.FirstName | sub.LastName | sub.Title                  | e.FirstName | e.LastName | e.Title                 |
+--------------------------------------------------------------+
| "Margaret"    | "Peacock"    | "Sales Representative"     | "Andrew"    | "Fuller"   | "Vice President, Sales" |
| "Steven"      | "Buchanan"   | "Sales Manager"            | "Andrew"    | "Fuller"   | "Vice President, Sales" |
| "Nancy"       | "Davolio"    | "Sales Representative"     | "Andrew"    | "Fuller"   | "Vice President, Sales" |
| "Janet"       | "Leverling"  | "Sales Representative"     | "Andrew"    | "Fuller"   | "Vice President, Sales" |
| "Laura"       | "Callahan"   | "Inside Sales Coordinator" | "Andrew"    | "Fuller"   | "Vice President, Sales" |
| "Anne"        | "Dodsworth"  | "Sales Representative"     | "Steven"    | "Buchanan" | "Sales Manager"         |
| "Michael"     | "Suyama"     | "Sales Representative"     | "Steven"    | "Buchanan" | "Sales Manager"         |
| "Robert"      | "King"       | "Sales Representative"     | "Steven"    | "Buchanan" | "Sales Manager"         |
+-------------------------------------------------------------+
8 rows

Теперь посмотрим, сколько прямых отчетов имеет каждый менеджер:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
SELECT manager."EmployeeID" AS manager, COUNT(e."EmployeeID") AS reports
FROM employees AS manager
LEFT JOIN employees AS e ON e."ReportsTo" = manager."EmployeeID"
GROUP BY manager
ORDER BY reports DESC;
  
 manager | reports
---------+---------
       2 |       5
       5 |       3
       1 |       0
       3 |       0
       4 |       0
       9 |       0
       6 |       0
       7 |       0
       8 |       0
(9 ROWS)
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
MATCH (e:Employee)
OPTIONAL MATCH (e)<-[rel:REPORTS_TO]-(report)
RETURN e.EmployeeID AS employee, COUNT(rel) AS reports
  
+--------------------+
| employee | reports |
+--------------------+
| "2"      | 5       |
| "5"      | 3       |
| "8"      | 0       |
| "7"      | 0       |
| "1"      | 0       |
| "4"      | 0       |
| "6"      | 0       |
| "9"      | 0       |
| "3"      | 0       |
+--------------------+
9 rows

Вещи начинают становиться все интереснее, если мы находим существующие транзитивные отношения отчетности. Я не эксперт в Postgres, но один из способов добиться этого — написать рекурсивный запрос WITH следующим образом:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
WITH RECURSIVE recursive_employees("EmployeeID", "ReportsTo") AS (
        SELECT e."EmployeeID", e."ReportsTo"
        FROM employees e
      UNION ALL
        SELECT e."EmployeeID", e."ReportsTo"
        FROM employees e, recursive_employees re
        WHERE e."EmployeeID" = re."ReportsTo"
)
SELECT re."ReportsTo", COUNT(*) AS COUNT
FROM recursive_employees AS re
WHERE re."ReportsTo" IS NOT NULL
GROUP BY re."ReportsTo";
  
 ReportsTo | COUNT
-----------+-------
         2 |     8
         5 |     3
(2 ROWS)

Если есть более простой способ, дайте мне знать в комментариях.

В cypher нам нужно добавить только один символ, ‘*’, после отношения ‘REPORTS_TO’, чтобы заставить его повторяться как можно дальше. Мы также удалим «ФАКУЛЬТАТИВНОЕ МАТЧЕ», чтобы мы возвращали только тех людей, о которых им сообщают:

01
02
03
04
05
06
07
08
09
10
MATCH (e:Employee)<-[rel:REPORTS_TO*]-(report)
RETURN e.EmployeeID AS employee, COUNT(rel) AS reports
  
+--------------------+
| employee | reports |
+--------------------+
| "2"      | 8       |
| "5"      | 3       |
+--------------------+
2 rows

Теперь мне нужно найти несколько реляционных наборов данных с более сложными запросами, с которыми можно поиграться. Если у вас есть идеи, дайте мне знать.

Ссылка: Northwind: поиск прямых / транзитивных отчетов в SQL и Cypher Neo4j от нашего партнера по JCG Марка Нидхэма в блоге Марка Нидхэма .
42
Share: