Условная агрегация: CASE внутри агрегата и FILTER

Бран · продюсер «Хроник Пограничья»

В чате гильдий ноют, что в Огненном Хребте квесты не пройти. Правда или ноют как всегда? И заодно: сколько покупок нам приносят айфоны и сколько андроиды, по месяцам, одной табличкой, чтобы я вставил её в презентацию.

В уроке про CASE ты раскладывал строки по категориям: «новичок», «середняк», «ветеран». А GROUP BY умеет считать по группам, но каждую группу кладёт в отдельную строку. Брану нужно другое: провалы и успехи рядом, айфоны и андроиды рядом, в одной строке. Сегодня учимся считать несколько метрик за один проход, каждая со своим условием.

Три копилки вместо одной

Представь кассира на ярмарке, у которого не одна копилка, а три: для монет, для купюр и для фальшивок. Он берёт из кучи деньги по одной и кладёт каждую в свою копилку. Куча одна, проход по ней один, а итогов на выходе три.

Это и есть условная агрегация. Обычный count(*) кидает в копилку каждую строку. Условный кидает только ту, что прошла проверку, а остальные пропускает. Таких счётчиков в одном SELECT может быть сколько угодно.

CASE внутри SUM

Первый способ ты уже знаешь по частям. CASE превращает строку в число: 1, если прохождение провалено, 0, если нет. А sum складывает эти единицы, то есть считает провалы.

SELECT count(*) AS runs,
       sum(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)    AS failed,
       sum(CASE WHEN status = 'abandoned' THEN 1 ELSE 0 END) AS abandoned
FROM quest_runs;

 runs  | failed | abandoned
-------+--------+-----------
 36348 |   2708 |      1991

Одна строка, три метрики. Без условной агрегации пришлось бы писать три запроса с разными WHERE и склеивать ответы руками.

Этот приём работает в любой базе: в MySQL, ClickHouse, BigQuery. Поэтому его стоит узнавать в чужих запросах, даже если сам пишешь иначе.

FILTER: то же самое, но короче

В PostgreSQL есть запись прямее. После агрегата пишешь FILTER (WHERE условие), и агрегат видит только подходящие строки:

SELECT count(*)                                   AS runs,
       count(*) FILTER (WHERE status = 'failed')    AS failed,
       count(*) FILTER (WHERE status = 'abandoned') AS abandoned
FROM quest_runs;

Ответ тот же: 36348, 2708, 1991. Читается почти как фраза: «посчитай строки, отфильтровав провалы». FILTER работает с любым агрегатом, не только с count: sum(amount_rub) FILTER (WHERE status = 'paid') сложит только оплаченные суммы.

Так покупки раскладываются по платформам в одну строку, как просил Бран:

SELECT count(*) FILTER (WHERE platform = 'ios')     AS ios,
       count(*) FILTER (WHERE platform = 'android') AS android,
       count(*) FILTER (WHERE platform = 'pc')      AS pc
FROM purchases;

 ios | android | pc
-----+---------+-----
 388 |     586 | 276

Такой поворот, когда значения из столбца становятся отдельными столбцами, называют pivot, как сводную таблицу в Excel.