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

Агрегаты с CASE: как в одном запросе посчитать сразу несколько условных метрик.

Связка SUM(CASE WHEN условие THEN 1 ELSE 0 END) считает, сколько строк удовлетворяют условию — это условный подсчёт.

Идея

Часто нужно в одной строке отчёта показать несколько срезов: сколько заказов оплачено, сколько отменено, сколько в работе. Вместо трёх запросов используют CASE внутри агрегата — он превращает условие в 1/0, а SUM их складывает.

CREATE TABLE orders (
    id     INTEGER PRIMARY KEY,
    status TEXT,
    amount INTEGER
);
INSERT INTO orders (status, amount) VALUES
    ('paid',     500),
    ('paid',     300),
    ('cancelled',200),
    ('pending',  150),
    ('paid',     400);

SELECT
    COUNT(*) AS vsego,
    SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS oplacheno,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS otmeneno,
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS vyruchka
FROM orders;

Вывод:

5|3|1|1200

Одним проходом получили: всего 5 заказов, оплачено 3, отменено 1, выручка только по оплаченным — 1200. Каждый CASE — отдельная условная метрика.

Pivot: разворот строк в колонки

Тот же приём в связке с GROUP BY делает «поворот» (pivot): категории-строки превращаются в колонки. Посчитаем выручку по месяцам, разложив статусы по колонкам:

CREATE TABLE orders (
    id     INTEGER PRIMARY KEY,
    month  TEXT,
    status TEXT
);
INSERT INTO orders (month, status) VALUES
    ('Янв','paid'), ('Янв','paid'), ('Янв','cancelled'),
    ('Фев','paid'), ('Фев','pending');

SELECT month,
       SUM(CASE WHEN status='paid'      THEN 1 ELSE 0 END) AS oplacheno,
       SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END) AS otmeneno,
       SUM(CASE WHEN status='pending'   THEN 1 ELSE 0 END) AS v_rabote
FROM orders
GROUP BY month
ORDER BY month;

Вывод:

Фев|1|0|1
Янв|2|1|0

Получилась мини-таблица «месяц × статус». Янв: 2 оплачено, 1 отменён. Фев: 1 оплачен, 1 в работе. Так строят сводные отчёты без отдельного pivot-синтаксиса.

AVG с CASE и NULL

Хитрость: если в CASE для несовпадения вернуть не 0, а NULL, то AVG и COUNT их проигнорируют — это считает среднее только по подходящим строкам:

CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT, amount INTEGER);
INSERT INTO orders (status, amount) VALUES
    ('paid', 500), ('paid', 300), ('cancelled', 9999), ('paid', 400);

-- Средний чек только по оплаченным: для прочих CASE даёт NULL, AVG их пропустит
SELECT CAST(AVG(CASE WHEN status='paid' THEN amount END) AS INTEGER)
       AS sredniy_chek_oplachennyh
FROM orders;

Вывод:

400

Средний чек оплаченных = (500+300+400)/3 = 400. Отменённый заказ на 9999 не исказил среднее, потому что CASE вернул для него NULL, а AVG NULL не учитывает.

Итог

• SUM(CASE WHEN … THEN 1 ELSE 0 END) — условный подсчёт строк за один проход.
• С GROUP BY это даёт pivot: категории-строки превращаются в колонки.
• Возврат NULL из CASE исключает строку из AVG/COUNT — удобно для условных средних.