Пошук уроків, статей та іншого контенту
Фільтруйте рядки для окремих агрегатних і віконних обчислень за допомогою конструкції FILTER.
FILTERFILTER дає змогу застосувати умову лише до рядків конкретної агрегатної функції:
aggregate_function(...) FILTER (WHERE умова)На відміну від звичайного WHERE, ця конструкція не відкидає рядки з результату запиту. Вона визначає, які рядки враховує конкретна агрегатна функція.
Наприклад, один запит може одночасно порахувати:
загальну кількість замовлень;
кількість оплачених замовлень;
кількість скасованих замовлень;
суму всіх замовлень;
суму лише оплачених замовлень.
FILTER з групуваннямРозглянемо таблицю замовлень:
DROP TABLE IF EXISTS orders;
CREATE TEMP TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL,
order_date date NOT NULL,
status text NOT NULL,
amount numeric(10, 2) NOT NULL,
product_id integer
);
INSERT INTO orders (order_id, customer_id, order_date, status, amount, product_id)
VALUES
(1, 101, DATE '2026-01-10', 'paid', 120.00, 10),
(2, 101, DATE '2026-01-12', 'pending', 80.00, 11),
(3, 101, DATE '2026-01-15', 'cancelled', 45.00, 10),
(4, 102, DATE '2026-01-11', 'paid', 200.00, 12),
(5, 102, DATE '2026-01-20', 'paid', 75.00, 13),
(6, 102, DATE '2026-01-22', 'refunded', 50.00, 12),
(7, 103, DATE '2026-01-13', 'pending', 90.00, 14),
(8, 103, DATE '2026-01-25', 'paid', 150.00, 15);Без FILTER для кожного показника довелося б писати окремі запити або використовувати складні вирази з CASE.
SELECT
customer_id,
count(*) AS total_orders,
count(*) FILTER (WHERE status = 'paid') AS paid_orders,
count(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
sum(amount) AS total_amount,
sum(amount) FILTER (WHERE status = 'paid') AS paid_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;Результат міститиме один рядок на клієнта, а кожен агрегат матиме власну умову.
Для клієнта 101:
total_orders дорівнює 3;
paid_orders дорівнює 1;
cancelled_orders дорівнює 1;
total_amount дорівнює 245.00;
paid_amount дорівнює 120.00.
У спрощеному вигляді PostgreSQL обробляє такий запит так:
FROM формує набір рядків.
WHERE відкидає рядки, які не потрібні всьому запиту.
GROUP BY формує групи.
Кожен агрегат застосовує власний FILTER.
Обчислюються агрегатні значення.
HAVING фільтрує вже сформовані групи.
Тому умова в FILTER діє лише на відповідний агрегат:
SELECT
count(*) AS all_orders,
count(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM orders;У результаті будуть враховані всі замовлення для all_orders, але лише оплачені — для paid_orders.
WHERE, HAVING і CASEWHEREWHERE видаляє рядки до виконання всіх агрегатів:
SELECT
count(*) AS paid_orders,
sum(amount) AS paid_amount
FROM orders
WHERE status = 'paid';Цей запит взагалі не бачить неоплачених замовлень. Додати до нього count(*) для всіх статусів уже неможливо.
HAVINGHAVING фільтрує групи після обчислення агрегатів:
SELECT
customer_id,
sum(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING sum(amount) > 200;Це означає: повернути лише клієнтів, загальна сума замовлень яких перевищує 200.
HAVING не замінює FILTER, коли потрібно отримати кілька різних метрик у кожній групі.
CASEТой самий підрахунок можна записати через CASE:
SELECT
customer_id,
sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount
FROM orders
GROUP BY customer_id;Але FILTER зазвичай чіткіше передає намір:
SELECT
customer_id,
sum(amount) FILTER (WHERE status = 'paid') AS paid_amount
FROM orders
GROUP BY customer_id;FILTER особливо зручний, коли в одному запиті багато умовних агрегатів.
FILTER із різними агрегатамиFILTER можна застосовувати не лише до count і sum:
SELECT
count(DISTINCT product_id) AS all_products,
count(DISTINCT product_id) FILTER (WHERE status = 'paid') AS paid_products,
min(amount) FILTER (WHERE status = 'paid') AS smallest_paid_order,
max(amount) FILTER (WHERE status = 'paid') AS largest_paid_order,
avg(amount) FILTER (WHERE status = 'paid') AS average_paid_order
FROM orders;Умова фільтра застосовується до рядків до того, як конкретний агрегат обробить їх.
Можна поєднувати FILTER з DISTINCT:
SELECT
customer_id,
count(DISTINCT product_id)
FILTER (WHERE status IN ('paid', 'refunded')) AS processed_products
FROM orders
GROUP BY customer_id;Також FILTER можна використовувати з упорядкованими агрегатами:
SELECT
customer_id,
string_agg(status, ', ' ORDER BY order_date)
FILTER (WHERE status <> 'cancelled') AS active_statuses
FROM orders
GROUP BY customer_id
ORDER BY customer_id;Тут:
FILTER виключає скасовані замовлення;
ORDER BY усередині string_agg визначає порядок значень;
результати формуються окремо для кожного клієнта.
NULL і порожні набориРезультат залежить від конкретної агрегатної функції.
count повертає 0, якщо після фільтрації не залишилося рядків:
SELECT
count(*) FILTER (WHERE status = 'unknown') AS unknown_orders
FROM orders;Результат:
0Більшість інших агрегатів повертає NULL:
SELECT
sum(amount) FILTER (WHERE status = 'unknown') AS unknown_amount,
avg(amount) FILTER (WHERE status = 'unknown') AS unknown_average
FROM orders;Результат для обох колонок буде NULL.
Якщо замість NULL потрібне нульове значення, використовуйте coalesce:
SELECT
coalesce(
sum(amount) FILTER (WHERE status = 'unknown'),
0
) AS unknown_amount
FROM orders;Важливо розрізняти:
count(*) FILTER (WHERE умова)і:
count(column_name) FILTER (WHERE умова)Перша форма рахує всі рядки, які пройшли фільтр. Друга не рахує рядки, у яких column_name дорівнює NULL.
FILTER у віконних обчисленняхАгрегатну функцію з FILTER можна використовувати як віконну:
SELECT
order_id,
customer_id,
order_date,
status,
amount,
sum(amount) FILTER (WHERE status = 'paid')
OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_paid_amount
FROM orders
ORDER BY customer_id, order_date, order_id;Для кожного рядка запит обчислює накопичувальну суму лише оплачених замовлень цього клієнта.
Сам рядок із pending або cancelled залишається в результаті, але його amount не додається до cumulative_paid_amount.
В одному запиті можна отримати кілька показників для кожного рядка:
SELECT
order_id,
customer_id,
order_date,
status,
amount,
count(*) OVER (
PARTITION BY customer_id
) AS customer_order_count,
count(*) FILTER (WHERE status = 'paid') OVER (
PARTITION BY customer_id
) AS customer_paid_order_count,
sum(amount) FILTER (WHERE status = 'paid') OVER (
PARTITION BY customer_id
) AS customer_paid_amount
FROM orders
ORDER BY customer_id, order_date, order_id;Тут:
customer_order_count рахує всі замовлення клієнта;
customer_paid_order_count рахує лише оплачені;
customer_paid_amount підсумовує лише оплачені.
На відміну від GROUP BY, віконні функції не зменшують кількість рядків у результаті.
FILTERДля накопичувальних обчислень важливо явно задавати віконну рамку:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWПовний приклад:
SELECT
order_id,
customer_id,
order_date,
status,
amount,
sum(amount) FILTER (WHERE status = 'paid')
OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS paid_amount_to_date
FROM orders
ORDER BY customer_id, order_date, order_id;Додавання order_id до ORDER BY робить порядок стабільним, навіть якщо кілька замовлень мають однакову дату.
Без ORDER BY вікно охоплює всю групу клієнта:
SELECT
order_id,
customer_id,
sum(amount) FILTER (WHERE status = 'paid')
OVER (PARTITION BY customer_id) AS total_paid_amount
FROM orders
ORDER BY customer_id, order_id;У такому випадку кожен рядок клієнта матиме однакову загальну суму його оплачених замовлень.
FILTER призначений для агрегатних функцій. Його можна використовувати з агрегатом, який працює як віконна функція, але не з довільною віконною функцією.
Правильно:
count(*) FILTER (WHERE status = 'paid')
OVER (PARTITION BY customer_id)Неправильно:
row_number() FILTER (WHERE status = 'paid')
OVER (PARTITION BY customer_id)row_number не є агрегатною функцією, тому FILTER до нього застосувати не можна.
Якщо потрібно нумерувати лише певні рядки, спочатку сформуйте потрібний набір даних у підзапиті або CTE, а потім застосуйте віконну функцію.
Неправильно, якщо потрібні і всі, і оплачені замовлення:
SELECT
count(*) AS total_orders,
count(*) AS paid_orders
FROM orders
WHERE status = 'paid';total_orders також рахуватиме лише оплачені замовлення.
Правильно:
SELECT
count(*) AS total_orders,
count(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM orders;Правильний синтаксис містить WHERE у дужках:
count(*) FILTER (WHERE status = 'paid')Конструкції на кшталт FILTER status = 'paid' або FILTER (status = 'paid') не є правильним синтаксисом PostgreSQL.
sumЯкщо жоден рядок не пройшов фільтр, sum поверне NULL, а не 0:
sum(amount) FILTER (WHERE status = 'unknown')Для нуля використовуйте:
coalesce(
sum(amount) FILTER (WHERE status = 'unknown'),
0
)FILTER із row_numberFILTER не є універсальним модифікатором для всіх функцій. Він застосовується до агрегатних функцій, зокрема коли вони використовуються в ролі віконних.
FILTER і віконною рамкоюУмова FILTER визначає, які рядки враховує агрегат. Віконна рамка визначає, які рядки входять до поточного вікна.
Наприклад:
sum(amount) FILTER (WHERE status = 'paid')
OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)FILTER залишає для суми лише оплачені рядки;
PARTITION BY розділяє дані за клієнтами;
ORDER BY задає послідовність;
ROWS визначає діапазон рядків для поточного обчислення.
FILTER (WHERE умова) застосовує умову до окремого агрегату.
На відміну від WHERE, FILTER не видаляє рядки для всього запиту.
FILTER зручно використовувати для кількох умовних метрик в одному SELECT.
Конструкція працює з агрегатами на групах і з агрегатами у віконних обчисленнях.
count для порожнього набору повертає 0, а sum, avg, min і max зазвичай повертають NULL.
Для накопичувальних віконних обчислень бажано явно задавати ROWS і стабільний порядок.
FILTER застосовується до агрегатних функцій, але не до таких функцій, як row_number або rank.