Data Science. SQL hub: “SQL-приём: FILTER в агрегатах вместо пачки CASE WHEN. Обычно пишут так…”

SQL-приём: FILTER в агрегатах вместо пачки CASE WHEN.

Обычно пишут так:


SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_orders,
    SUM(CASE WHEN amount > 1000 THEN amount ELSE 0 END) AS big_order_revenue
FROM orders;

Но в PostgreSQL это можно сделать чище:


SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
    COUNT(*) FILTER (WHERE status = 'failed') AS failed_orders,
    SUM(amount) FILTER (WHERE amount > 1000) AS big_order_revenue
FROM orders;

Чем полезно:

• запрос читается как аналитическая таблица
• меньше шума от CASE WHEN
• проще добавлять новые метрики
• условия не смешиваются с логикой агрегации
• особенно удобно для дашбордов и отчётов

А ещё можно делать несколько метрик по разным условиям за один проход по данным:


SELECT
    date_trunc('day', created_at) AS day,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid,
    COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
    AVG(amount) FILTER (WHERE status = 'paid') AS avg_paid_amount
FROM orders
GROUP BY 1
ORDER BY 1;

Это маленькая фича, но в реальной аналитике она сильно повышает читаемость SQL.