BatSchool · Cypher против SQL: те же задачи, разный путь

Мы прошли основу Cypher, а раздел начинали со сравнения с таблицами в первом уроке. Пора вернуться к нему с новыми знаниями. Возьмём книжный клуб, разложим его по таблицам и зададим одни и те же вопросы на двух языках. Цель не в том, чтобы назвать победителя, а в том, чтобы видеть, где языки совпадают, а где расходятся.

Клуб в таблицах

В реляционной базе граф клуба стал бы шестью таблицами: три для сущностей и три для связей.

CREATE TABLE people (id integer PRIMARY KEY, name text, born integer);
CREATE TABLE cities (id integer PRIMARY KEY, name text);
CREATE TABLE books  (id integer PRIMARY KEY, title text, author text, year integer);

CREATE TABLE lives_in (person_id integer REFERENCES people, city_id integer REFERENCES cities);
CREATE TABLE knows    (person_id integer REFERENCES people, friend_id integer REFERENCES people, since integer);
CREATE TABLE reads    (person_id integer REFERENCES people, book_id integer REFERENCES books, rating integer);

Каждая связь графа это строка в таблице связей, а каждый тип связи это отдельная таблица. Так обычно и раскладывают отношения «многие ко многим», как в уроке про связи в ER-модели.

Фильтр: почти одно и то же

Участники, родившиеся после 1995 года.

SELECT name, born
FROM people
WHERE born > 1995
ORDER BY born;

MATCH (p:Person)
WHERE p.born > 1995
RETURN p.name, p.born
ORDER BY p.born;

+-----------------+
| p.name | p.born |
+-----------------+
| "Вера" | 1998   |
| "Зоя"  | 1999   |
| "Дина" | 2001   |
| "Яна"  | 2003   |
+-----------------+

4 rows

Разница только в словах: FROM people становится MATCH (p:Person), а SELECT переезжает в конец под именем RETURN. Порядок клауз в Cypher ближе к порядку мысли: сначала что ищем, потом что отдаём.

Соединение: JOIN против стрелки

Кто читал книги Стругацких.

SELECT p.name, b.title
FROM people p
JOIN reads r ON r.person_id = p.id
JOIN books b ON b.id = r.book_id
WHERE b.author = 'Стругацкие'
ORDER BY p.name, b.title;

MATCH (p:Person)-[:READ]->(b:Book {author: 'Стругацкие'})
RETURN p.name, b.title
ORDER BY p.name, b.title;

+---------------------------------------------+
| p.name | b.title                            |
+---------------------------------------------+
| "Аня"  | "Пикник на обочине"                |
| "Вера" | "Пикник на обочине"                |
| "Вера" | "Понедельник начинается в субботу" |
| "Глеб" | "Понедельник начинается в субботу" |
| "Егор" | "Пикник на обочине"                |
| "Зоя"  | "Понедельник начинается в субботу" |
| "Илья" | "Пикник на обочине"                |
+---------------------------------------------+

7 rows

Два JOIN с условиями на ключи превратились в стрелку. Это главное отличие: в SQL связь между строками каждый раз собирают заново по равенству ключей, в графе она уже лежит готовой.

Группировка: GROUP BY не нужен

Сколько книг у каждого читателя.

SELECT p.name, count(*) AS books
FROM people p
JOIN reads r ON r.person_id = p.id
GROUP BY p.name
ORDER BY books DESC, p.name;

MATCH (p:Person)-[:READ]->(b:Book)
RETURN p.name, count(b) AS books
ORDER BY books DESC, p.name;

+-----------------+
| p.name  | books |
+-----------------+
| "Аня"   | 2     |
| "Борис" | 2     |
| "Вера"  | 2     |
| "Глеб"  | 2     |
| "Жанна" | 2     |
| "Илья"  | 2     |
| "Дина"  | 1     |
| "Егор"  | 1     |
| "Зоя"   | 1     |
+-----------------+

9 rows

Агрегаты те же, но ключ группы в Cypher не объявляют, его выводят из колонок без агрегатов, как в уроке про агрегации.

Анти-соединение: кого нет

Участники без города.

SELECT p.name
FROM people p
LEFT JOIN lives_in l ON l.person_id = p.id
WHERE l.person_id IS NULL
ORDER BY p.name;

MATCH (p:Person)
WHERE NOT (p)-[:LIVES_IN]->()
RETURN p.name
ORDER BY p.name;

+--------+
| p.name |
+--------+
| "Илья" |
| "Яна"  |
+--------+

2 rows

В SQL это приём с LEFT JOIN и проверкой на NULL или NOT EXISTS с подзапросом. В Cypher условие на отсутствие связи пишется прямо паттерном.

Два шага: друзья друзей

SELECT DISTINCT fof.name
FROM people a
JOIN knows k1 ON k1.person_id = a.id
JOIN knows k2 ON k2.person_id = k1.friend_id
JOIN people fof ON fof.id = k2.friend_id
WHERE a.name = 'Аня'
ORDER BY fof.name;

MATCH (:Person {name: 'Аня'})-[:KNOWS]->()-[:KNOWS]->(fof)
RETURN DISTINCT fof.name
ORDER BY fof.name;

+----------+
| fof.name |
+----------+
| "Вера"   |
| "Глеб"   |
| "Дина"   |
+----------+

3 rows

Здесь разница уже заметна. Каждый шаг в SQL это ещё одно соединение с таблицей knows, а чтобы прочитать имена, ещё и с people. В Cypher каждый шаг это ещё один кусок рисунка.

Любая глубина: рекурсия против звёздочки

Все, до кого Аня дотягивается за три шага знакомств в направлении стрелки. В SQL понадобится рекурсивный запрос:

WITH RECURSIVE reach (id, depth) AS (
  SELECT k.friend_id, 1
  FROM knows k
  JOIN people a ON a.id = k.person_id
  WHERE a.name = 'Аня'
  UNION ALL
  SELECT k.friend_id, r.depth + 1
  FROM reach r
  JOIN knows k ON k.person_id = r.id
  WHERE r.depth < 3
)
SELECT DISTINCT p.name
FROM reach r
JOIN people p ON p.id = r.id
ORDER BY p.name;

MATCH (:Person {name: 'Аня'})-[:KNOWS*1..3]->(x)
RETURN DISTINCT x.name
ORDER BY x.name;

+---------+
| x.name  |
+---------+
| "Аня"   |
| "Борис" |
| "Вера"  |
| "Глеб"  |
| "Дина"  |
| "Егор"  |
+---------+

6 rows

Ответы совпадают, но разница в объёме и в рисках. Рекурсивный запрос нужно писать аккуратно: забудешь условие на глубину, и он будет ходить по кругу Аня, Борис, Вера бесконечно. В Cypher глубину задают двумя числами, а уникальность связей не даёт пройти одну связь дважды.

Кратчайший путь

Как короче всего дойти от Ани до Ильи без учёта направления. В SQL это рекурсия, которая тащит за собой весь пройденный путь, чтобы не зациклиться, и сортировка по длине. Такой запрос занимает два десятка строк и на большой таблице знакомств быстро становится неподъёмным. В Cypher это одна функция:

MATCH p = shortestPath((:Person {name: 'Аня'})-[:KNOWS*]-(:Person {name: 'Илья'}))
RETURN [n IN nodes(p) | n.name] AS chain;

+---------------------------------------------------------+
| chain                                                   |
+---------------------------------------------------------+
| ["Аня", "Глеб", "Дина", "Егор", "Жанна", "Зоя", "Илья"] |
+---------------------------------------------------------+

1 row

Итог сравнения

Задача	SQL	Cypher
Фильтр по полю	`WHERE`	`WHERE`
Связь двух сущностей	`JOIN` по ключам	стрелка в паттерне
Группировка	`GROUP BY` явно	неявная по колонкам без агрегатов
Отсутствие связи	`LEFT JOIN ... IS NULL`	`WHERE NOT (паттерн)`
Обход на N шагов	N соединений с таблицей связей	N кусков паттерна
Глубина неизвестна	`WITH RECURSIVE`	`*1..N`
Кратчайший путь	рекурсия с массивом пути	`shortestPath`

Чем больше в вопросе связей и чем менее известна их глубина, тем заметнее выигрыш графа. А для отчётов по плоским таблицам SQL остаётся естественным языком, и Cypher там ничего не упрощает.

Попробуй сам

Запрос находит участников, которые читали ту же книгу, что и их знакомые. В SQL это было бы четыре соединения. На картинке видны треугольники «двое знакомых и общая книга».

Напиши этот же вопрос на SQL для таблиц из урока и посчитай соединения. Потом переведи на Cypher свой любимый запрос с GROUP BY.

Проверь себя

Задание

NJ-22-1★★

Возьми три своих SQL-запроса с JOIN и GROUP BY из раздела про PostgreSQL или из работы. Придумай для каждого графовую модель (какие таблицы станут узлами, какие связями) и перепиши запрос на Cypher. Отдельно переведи один запрос с WITH RECURSIVE в путь переменной длины. Для каждой пары запиши, что стало короче, а что длиннее.

Дальше

Сравнение с таблицами подводит к практическому вопросу: как перенести данные из таблиц в граф? В следующем уроке загружаем участников и знакомства из CSV-файлов через LOAD CSV.

домашка

Домашка

Перевести три своих SQL-запроса с JOIN и GROUP BY на Cypher и один запрос с WITH RECURSIVE на путь переменной длиныJ