Як будувати звіти в PostgreSQL за допомогою FILTER і generate_series?

FILTER - умова для окремого агрегату. Кілька різних підрахунків за один прохід таблиці:

SELECT
    count(*)                                      AS total,
    count(*) FILTER (WHERE status = 'paid')       AS paid,
    count(*) FILTER (WHERE status = 'refunded')   AS refunded,
    sum(total) FILTER (WHERE status = 'paid')     AS revenue
FROM orders
WHERE created_at >= date_trunc('month', now());

Те саме в стандартному SQL пишуть через sum(CASE WHEN ... THEN 1 ELSE 0 END) - FILTER коротший і читабельніший. Він працює з будь-якими агрегатами: sum, avg, array_agg, jsonb_agg.

generate_series - згенерувати послідовність чисел чи дат:

SELECT generate_series(1, 5);
SELECT generate_series('2026-10-01'::date, '2026-10-07', interval '1 day');

Звіт по днях без пропусків. Якщо згрупувати замовлення за днем, дні без замовлень просто зникнуть з результату - а на графіку потрібен нуль:

SELECT d::date AS day,
       count(o.id) AS orders,
       coalesce(sum(o.total), 0) AS revenue
FROM generate_series(date_trunc('day', now()) - interval '29 days',
                     date_trunc('day', now()),
                     interval '1 day') AS d
LEFT JOIN orders o
       ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

• серія дає всі дні періоду;
• LEFT JOIN зберігає дні без замовлень;
• count(o.id), а не count(*) - інакше порожній день порахується як 1.

Зведена таблиця (pivot) через FILTER:

SELECT date_trunc('week', created_at) AS week,
       count(*) FILTER (WHERE channel = 'web')    AS web,
       count(*) FILTER (WHERE channel = 'mobile') AS mobile
FROM orders
GROUP BY 1
ORDER BY 1;

Часові пояси: date_trunc('day', created_at) обрізає за поясом сесії бази. Для звіту за київським часом - date_trunc('day', created_at, 'Europe/Kyiv') (для timestamptz), інакше «день» починатиметься о 02:00 чи 03:00 за Києвом.

Продуктивність: умова за діапазоном created_at >= ... AND created_at < ... використовує індекс, а WHERE date(created_at) = ... - ні (функція над колонкою).

У Laravel такі запити зручно писати через selectRaw і DB::select, а для дашбордів - кешувати: звіт за місяць не має перераховуватися на кожне відкриття сторінки.

Докладніше в документації: Агрегатні функції