Раздел 39 · Neo4j и графовые базы

Агрегации: count, collect и неявная группировка

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

До сих пор каждый ответ был списком найденных совпадений: строка на каждую цепочку. Но часто нужен итог: сколько книг прочитал каждый, какая средняя оценка, кто живёт в каждом городе. Такие функции называют агрегатными. В Cypher они работают почти как в SQL, только без GROUP BY. Если ты проходил урок про GROUP BY в PostgreSQL, следи за отличиями.

Сколько всего

count считает строки. count(*) считает все строки, count(x) только те, где x не null:

MATCH (p:Person)
RETURN count(p) AS people, count(p.born) AS withBorn, count(*) AS rows;
+--------------------------+
| people | withBorn | rows |
+--------------------------+
| 10     | 9        | 10   |
+--------------------------+

1 row

У Егора года нет, поэтому count(p.born) на единицу меньше.

Неявная группировка

Теперь сколько книг у каждого читателя:

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

GROUP BY здесь нет, а группы есть. Правило Cypher простое: все колонки RETURN без агрегатов становятся ключом группы. Здесь ключ p.name, и count(b) считается для каждого имени отдельно. Это и есть неявная группировка.

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

Несколько агрегатов сразу. По каждой книге: читатели, средняя, низшая и высшая оценки:

MATCH (p:Person)-[r:READ]->(b:Book)
RETURN b.title, count(p) AS readers, avg(r.rating) AS rating, min(r.rating) AS low, max(r.rating) AS high
ORDER BY readers DESC, b.title;
+-------------------------------------------------------------------------------+
| b.title                            | readers | rating            | low | high |
+-------------------------------------------------------------------------------+
| "Мастер и Маргарита"               | 4       | 4.0               | 3   | 5    |
| "Пикник на обочине"                | 4       | 4.0               | 3   | 5    |
| "Понедельник начинается в субботу" | 3       | 4.666666666666667 | 4   | 5    |
| "Двенадцать стульев"               | 2       | 4.5               | 4   | 5    |
| "Обломов"                          | 2       | 3.5               | 2   | 5    |
+-------------------------------------------------------------------------------+

5 rows

У «Пикника на обочине» четыре читателя, а средняя посчитана по трём оценкам: отзыв Ильи без оценки, и avg пропустил null. Все агрегаты, кроме count(*), так делают.

collect: свернуть в список

collect собирает значения группы в список. Кто живёт в каждом городе:

MATCH (p:Person)-[:LIVES_IN]->(c:City)
WITH c, p
ORDER BY p.name
RETURN c.name AS city, collect(p.name) AS people, count(*) AS total
ORDER BY total DESC, city;
+---------------------------------------------+
| city     | people                   | total |
+---------------------------------------------+
| "Москва" | ["Аня", "Борис", "Глеб"] | 3     |
| "Пермь"  | ["Егор", "Жанна", "Зоя"] | 3     |
| "Казань" | ["Вера", "Дина"]         | 2     |
+---------------------------------------------+

3 rows

Порядок элементов в collect совпадает с порядком строк, которые в него пришли. Поэтому сначала WITH ... ORDER BY p.name сортирует строки, и только потом они сворачиваются. Без этого шага порядок имён зависел бы от того, как база обошла граф.

Внутри агрегата можно писать DISTINCT. Сколько разных авторов и какие:

MATCH (b:Book)
RETURN count(DISTINCT b.author) AS authors, collect(DISTINCT b.author) AS names;
+-------------------------------------------------------------------+
| authors | names                                                   |
+-------------------------------------------------------------------+
| 4       | ["Булгаков", "Стругацкие", "Ильф и Петров", "Гончаров"] |
+-------------------------------------------------------------------+

1 row

WITH: фильтр по агрегату

В SQL условие на агрегат пишут в HAVING. В Cypher отдельного слова нет: агрегат считают в WITH, а следом пишут WHERE. WITH работает как промежуточный RETURN: передаёт дальше только то, что перечислено.

MATCH (p:Person)-[:READ]->(b)
WITH p, count(b) AS books
WHERE books > 1
RETURN p.name, books
ORDER BY p.name;
+-----------------+
| p.name  | books |
+-----------------+
| "Аня"   | 2     |
| "Борис" | 2     |
| "Вера"  | 2     |
| "Глеб"  | 2     |
| "Жанна" | 2     |
| "Илья"  | 2     |
+-----------------+

6 rows

Так же через WITH берут «топ». Три участника с наибольшим числом знакомств:

MATCH (p:Person)-[:KNOWS]-(f)
WITH p, count(f) AS friends
ORDER BY friends DESC, p.name
LIMIT 3
RETURN p.name, friends;
+-------------------+
| p.name  | friends |
+-------------------+
| "Аня"   | 3       |
| "Борис" | 3       |
| "Глеб"  | 3       |
+-------------------+

3 rows

Пустая группа

Что вернут агрегаты, если строк нет вовсе?

MATCH (p:Person)
WHERE p.name = 'Никто'
RETURN count(p) AS c, collect(p.name) AS l, sum(p.born) AS s, avg(p.born) AS a, max(p.born) AS m;
+--------------------------+
| c | l  | s | a    | m    |
+--------------------------+
| 0 | [] | 0 | NULL | NULL |
+--------------------------+

1 row

Одна строка: count даёт ноль, collect пустой список, sum ноль, а avg и max null, им не из чего считать. Но если в RETURN есть ключ группы, строк не будет совсем: нет ни одной группы.

MATCH (p:Person)
WHERE p.name = 'Никто'
RETURN p.name, count(*) AS c;
+------------+
| p.name | c |
+------------+
+------------+

0 rows

UNWIND: обратно из списка в строки

UNWIND делает противоположное collect: разворачивает список в строки. С ним удобно проверять агрегаты на руках:

UNWIND [3, 1, 2, 1] AS x
RETURN collect(x) AS all, collect(DISTINCT x) AS uniq, sum(x) AS s, avg(x) AS a;
+-------------------------------------+
| all          | uniq      | s | a    |
+-------------------------------------+
| [3, 1, 2, 1] | [3, 1, 2] | 7 | 1.75 |
+-------------------------------------+

1 row

avg всегда возвращает дробное число, даже для целых. В загрузке данных UNWIND разворачивает список параметров в строки для MERGE, как в уроке про MERGE.

Попробуй сам

Запрос считает оценки по авторам и возвращает их таблицей. Картинки не будет: в ответе только числа и строки.

Добавь колонку со списком книг автора через collect(DISTINCT b.title). Потом оставь только авторов, у которых больше двух отзывов, через WITH и WHERE.

Проверь себя

Задание

Дальше

Кластер про обходы и итоги закончен. В следующем уроке возвращаемся к записи: как поменять свойство или метку у существующего узла через SET и как убрать их через REMOVE.

домашка

Домашка