Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як будувати звіти в 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, а для дашбордів - кешувати: звіт за місяць не має перераховуватися на кожне відкриття сторінки.

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

Схожі питання