Каждые несколько месяцев мы проводим встречу по реляционному графику в офисе в Нео-Лондоне, где рассказываем, как перенести ваши данные из реляционной базы данных в граф.
Мы используем набор данных Northwind, который часто поставляется как демонстрационный набор данных в реляционных базах данных, и придумываем некоторые запросы, которые кажутся графическими по своей природе.
Мой любимый запрос — это вопрос о том, как организованы сотрудники и кто перед кем отчитывается. Я подумал, что было бы довольно интересно посмотреть, как это будет выглядеть в PostgreSQL, просто для удовольствия.
Мы начнем с получения списка сотрудников и лица, которому они сообщают:
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 мы бы сделали это:
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:
SELECT e."EmployeeID" AS bigBoss
FROM employees AS e
WHERE e."ReportsTo" IS NULL
bigboss
---------
2
(1 ROW)А теперь шифр
MATCH (e:Employee)
WHERE NOT (e)-[:REPORTS_TO]->()
RETURN e.EmployeeID AS bigBoss
+---------+
| bigBoss |
+---------+
| "2" |
+---------+
1 rowНам по-прежнему не нужно ничего объединять, поэтому запрос пока не так интересен. Давайте добавим еще несколько свойств из записи менеджера, поэтому нам нужно самостоятельно присоединиться к таблице сотрудников:
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)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Теперь посмотрим, сколько прямых отчетов имеет каждый менеджер:
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)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 rowsThings start to get more interesting if we find the transitive reporting relationships that exist. I’m not an expert at Postgres but one way to achieve this is by writing a recursive WITH query like so:
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)If there’s a simpler way let me know in the comments.
In cypher we only need to add one character, ‘*’, after the ‘REPORTS_TO’ relationship to get it to recurse as far as it can. We’ll also remove the ‘OPTIONAL MATCH’ so that we only get back people who have people reporting to them:
MATCH (e:Employee)<-[rel:REPORTS_TO*]-(report)
RETURN e.EmployeeID AS employee, COUNT(rel) AS reports
+--------------------+
| employee | reports |
+--------------------+
| "2" | 8 |
| "5" | 3 |
+--------------------+
2 rowsNow I need to find some relational datasets with more complicated queries to play around with. If you have any ideas do let me know.