Условный подсчёт: агрегаты с CASE

Аналитики и бэкендеры постоянно получают задачи вида «сколько заказов было в январе, а сколько в феврале, по каждому клиенту». Новичок пишет три запроса и склеивает их в коде. На собеседовании ждут один запрос с условными агрегатами. Запросы урока работают на учебной базе блока.

Как спрашивают

Одним запросом посчитай, сколько сотрудников получают от 200 000 и сколько меньше. А теперь выведи суммы заказов каждого клиента по месяцам в отдельных колонках.

Короткий ответ

Внутрь агрегата ставят условие. Классический переносимый способ: sum(CASE WHEN условие THEN 1 ELSE 0 END) или count(CASE WHEN условие THEN 1 END). В PostgreSQL есть запись короче и читаемее: count(*) FILTER (WHERE условие). Так за один проход по таблице получается несколько счётчиков, а колонки по месяцам превращают строки в сводную таблицу.

Разбор: счётчики на кассе

Представь кассира с тремя счётчиками-кликерами. Каждый покупатель проходит мимо один раз, а кассир щёлкает нужным счётчиком: «крупная покупка», «мелкая покупка». В конце смены у него готовы все числа, и для этого не пришлось пропускать очередь три раза. Условный агрегат работает так же: одна строка, одно решение, в какой счётчик она попадёт.

SELECT count(*) FILTER (WHERE salary >= 200000)            AS high,
       count(*) FILTER (WHERE salary <  200000)            AS low,
       sum(CASE WHEN salary >= 200000 THEN 1 ELSE 0 END)   AS high_case
FROM employees;

high | low | high_case
-----+-----+----------
   4 |   3 |         4

FILTER и CASE дают одно число. FILTER описан в стандарте SQL и работает в PostgreSQL и SQLite, но поддерживают его не все СУБД, например MySQL. CASE работает почти везде, поэтому его полезно уметь писать на память.

Условия по группам

Условные агрегаты складываются с GROUP BY. Посчитаем по отделам: всего сотрудников, нанятых до 2021 года, и руководителей (у них manager_id IS NULL):

SELECT d.name,
       count(e.id)                                         AS total,
       count(e.id) FILTER (WHERE e.hired_at < '2021-01-01') AS before_2021,
       count(e.id) FILTER (WHERE e.manager_id IS NULL)      AS heads
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name;

name       | total | before_2021 | heads
-----------+-------+-------------+------
Маркетинг  |     0 |           0 |     0
Поддержка  |     1 |           0 |     1
Продажи    |     2 |           1 |     1
Разработка |     3 |           2 |     1

Считаем count(e.id), а не count(*), по той же причине, что в уроке про варианты COUNT: у «Маркетинга» строка из LEFT JOIN с NULL. С count(*) FILTER (WHERE e.manager_id IS NULL) «Маркетинг» получил бы одного несуществующего руководителя.