Пошук уроків, статей та іншого контенту
Обчислюйте ковзні суми, середні та накопичувальні показники за допомогою віконних агрегатних функцій.
Агрегатна функція у звичайному запиті стискає кілька рядків в один результат:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM payments
GROUP BY customer_id;Віконна агрегатна функція обчислює агрегат для набору рядків, але не видаляє окремі рядки з результату:
SELECT
payment_date,
amount,
SUM(amount) OVER (
ORDER BY payment_date
) AS cumulative_amount
FROM payments;Кожен платіж залишається в результаті, а додатковий стовпець містить суму в межах визначеного вікна.
Синтаксис:
aggregate_function(...) OVER (
[PARTITION BY ...]
[ORDER BY ...]
[frame_definition]
)Найчастіше у вікнах використовують:
SUM() — сума;
AVG() — середнє значення;
MIN() і MAX() — мінімум і максимум;
COUNT() — кількість рядків або ненульових значень.
У прикладах використаємо денні продажі кількох магазинів:
WITH daily_sales(store_id, sale_date, amount) AS (
VALUES
(1, DATE '2026-01-01', 100.00::numeric),
(1, DATE '2026-01-02', 140.00::numeric),
(1, DATE '2026-01-03', 80.00::numeric),
(1, DATE '2026-01-04', 120.00::numeric),
(1, DATE '2026-01-05', 160.00::numeric),
(1, DATE '2026-01-06', 90.00::numeric),
(1, DATE '2026-01-07', 200.00::numeric),
(1, DATE '2026-01-08', 110.00::numeric),
(2, DATE '2026-01-01', 70.00::numeric),
(2, DATE '2026-01-02', 95.00::numeric),
(2, DATE '2026-01-03', 130.00::numeric),
(2, DATE '2026-01-04', 85.00::numeric)
)
SELECT *
FROM daily_sales
ORDER BY store_id, sale_date;CTE daily_sales існує лише протягом одного SQL-запиту. Це зручно для демонстрацій і тестування.
Накопичувальна сума містить значення від початку послідовності до поточного рядка:
WITH daily_sales(store_id, sale_date, amount) AS (
VALUES
(1, DATE '2026-01-01', 100.00::numeric),
(1, DATE '2026-01-02', 140.00::numeric),
(1, DATE '2026-01-03', 80.00::numeric),
(1, DATE '2026-01-04', 120.00::numeric),
(1, DATE '2026-01-05', 160.00::numeric)
)
SELECT
store_id,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount
FROM daily_sales
ORDER BY store_id, sale_date;Результат для магазину 1 матиме таку логіку:
1 січня: 100;
2 січня: 100 + 140 = 240;
3 січня: 240 + 80 = 320;
4 січня: 320 + 120 = 440;
5 січня: 440 + 160 = 600.
У виразі:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWROWS визначає, що рамка рахується за фізичними рядками;
UNBOUNDED PRECEDING означає «від першого рядка розділу»;
CURRENT ROW означає «до поточного рядка включно».
Для накопичувальної суми також можна записати коротше:
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS UNBOUNDED PRECEDING
)Явне зазначення рамки часто краще за покладання на значення за замовчуванням: воно робить намір запиту зрозумілим і захищає від помилок при роботі з однаковими значеннями сортування.
Ковзна сума враховує лише обмежену кількість попередніх рядків і поточний рядок.
Наприклад, сума за останні три записи:
WITH daily_sales(store_id, sale_date, amount) AS (
VALUES
(1, DATE '2026-01-01', 100.00::numeric),
(1, DATE '2026-01-02', 140.00::numeric),
(1, DATE '2026-01-03', 80.00::numeric),
(1, DATE '2026-01-04', 120.00::numeric),
(1, DATE '2026-01-05', 160.00::numeric),
(1, DATE '2026-01-06', 90.00::numeric),
(1, DATE '2026-01-07', 200.00::numeric)
)
SELECT
store_id,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3_row_sum
FROM daily_sales
ORDER BY store_id, sale_date;Для 5 січня рамка міститиме:
3 січня;
4 січня;
5 січня.
Для перших рядків доступно менше трьох записів, тому PostgreSQL використовує всі доступні рядки:
перший рядок — лише він сам;
другий рядок — перший і другий;
третій рядок — перший, другий і третій.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означає саме три рядки, а не три календарні дні.
Для ковзного середнього замість SUM() використовують AVG():
WITH daily_sales(store_id, sale_date, amount) AS (
VALUES
(1, DATE '2026-01-01', 100.00::numeric),
(1, DATE '2026-01-02', 140.00::numeric),
(1, DATE '2026-01-03', 80.00::numeric),
(1, DATE '2026-01-04', 120.00::numeric),
(1, DATE '2026-01-05', 160.00::numeric),
(1, DATE '2026-01-06', 90.00::numeric),
(1, DATE '2026-01-07', 200.00::numeric)
)
SELECT
store_id,
sale_date,
amount,
ROUND(
AVG(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
),
2
) AS rolling_3_row_average
FROM daily_sales
ORDER BY store_id, sale_date;Для 3 січня середнє обчислюється за трьома рядками:
(100 + 140 + 80) / 3 = 106.67AVG() ігнорує NULL, тому кількість фактично врахованих значень може бути меншою за кількість рядків у рамці.
Якщо потрібно контролювати кількість значень, можна одночасно використати COUNT():
WITH daily_sales(sale_date, amount) AS (
VALUES
(DATE '2026-01-01', 100.00::numeric),
(DATE '2026-01-02', NULL::numeric),
(DATE '2026-01-03', 80.00::numeric)
)
SELECT
sale_date,
amount,
COUNT(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS non_null_values,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_average
FROM daily_sales
ORDER BY sale_date;COUNT(amount) не враховує NULL, тоді як COUNT(*) врахував би всі рядки.
ROWS рахує рядки, а не час. Якщо в таблиці є пропуски дат або кілька записів на одну дату, для календарного періоду краще використати RANGE.
Сума продажів за поточний день і шість попередніх календарних днів:
WITH daily_sales(store_id, sale_date, amount) AS (
VALUES
(1, DATE '2026-01-01', 100.00::numeric),
(1, DATE '2026-01-02', 140.00::numeric),
(1, DATE '2026-01-04', 120.00::numeric),
(1, DATE '2026-01-07', 200.00::numeric),
(1, DATE '2026-01-10', 110.00::numeric)
)
SELECT
store_id,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_7_day_sum
FROM daily_sales
ORDER BY store_id, sale_date;Для рядка з датою 2026-01-07 рамка охоплює період від 2026-01-01 до 2026-01-07, навіть якщо всередині немає записів за кожен день.
Тут:
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROWозначає часовий діапазон від поточного значення sale_date мінус шість днів до поточної дати включно.
ROWS і RANGE: різницяПрипустімо, дані містять такі дати:
2026-01-01
2026-01-01
2026-01-02ROWS працює з конкретною позицією рядка. Наприклад:
ROWS BETWEEN 1 PRECEDING AND CURRENT ROWбере поточний рядок і безпосередньо попередній фізичний рядок.
RANGE працює зі значенням у ORDER BY. Рядки з однаковою датою є рядками-«ровесниками» та можуть потрапляти у вікно разом.
Практичне правило:
використовуйте ROWS, коли потрібна кількість попередніх записів;
використовуйте RANGE, коли потрібен числовий або часовий діапазон;
для часових ковзних показників зазвичай спочатку агрегуйте дані до потрібної часової точності.
Якщо в таблиці є кілька продажів протягом одного дня, семиденне вікно за окремими транзакціями не обов’язково відповідатиме семиденному вікну за днями.
У такому разі спочатку потрібно отримати денні підсумки, а потім застосувати віконну функцію:
WITH transactions(transaction_id, store_id, occurred_at, amount) AS (
VALUES
(1, 1, TIMESTAMP '2026-01-01 09:10', 50.00::numeric),
(2, 1, TIMESTAMP '2026-01-01 15:30', 70.00::numeric),
(3, 1, TIMESTAMP '2026-01-02 10:00', 140.00::numeric),
(4, 1, TIMESTAMP '2026-01-04 12:20', 120.00::numeric),
(5, 1, TIMESTAMP '2026-01-07 11:00', 200.00::numeric)
),
daily_sales AS (
SELECT
store_id,
occurred_at::date AS sale_date,
SUM(amount) AS daily_amount
FROM transactions
GROUP BY store_id, occurred_at::date
)
SELECT
store_id,
sale_date,
daily_amount,
SUM(daily_amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_7_day_sum
FROM daily_sales
ORDER BY store_id, sale_date;Внутрішній запит створює один рядок на магазин і день. Зовнішній запит обчислює ковзну суму за календарним періодом.
Віконні функції не можна безпосередньо вкладати одна в одну в одному рівні SELECT. Якщо результат однієї агрегації потрібно використати у наступному вікні, застосовуйте CTE або підзапит.
В одному запиті можна обчислити накопичувальну суму, ковзне середнє та кількість рядків:
WITH daily_sales(store_id, sale_date, amount) AS (
VALUES
(1, DATE '2026-01-01', 100.00::numeric),
(1, DATE '2026-01-02', 140.00::numeric),
(1, DATE '2026-01-03', 80.00::numeric),
(1, DATE '2026-01-04', 120.00::numeric),
(1, DATE '2026-01-05', 160.00::numeric),
(2, DATE '2026-01-01', 70.00::numeric),
(2, DATE '2026-01-02', 95.00::numeric),
(2, DATE '2026-01-03', 130.00::numeric)
)
SELECT
store_id,
sale_date,
amount,
SUM(amount) OVER cumulative_window AS cumulative_amount,
ROUND(AVG(amount) OVER rolling_window, 2) AS rolling_average,
COUNT(*) OVER rolling_window AS rows_in_window
FROM daily_sales
WINDOW
cumulative_window AS (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
),
rolling_window AS (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
ORDER BY store_id, sale_date;Секція WINDOW дає ім’я повторно використовуваним визначенням вікон. Це зменшує дублювання та допомагає уникнути розбіжностей між формулами.
Якщо вказано ORDER BY, але не вказано рамку, PostgreSQL використовує рамку на основі RANGE, яка охоплює рядки від початку розділу до поточного рядка та його рядків-ровесників.
Наприклад:
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
)не завжди поводиться так само, як:
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)Якщо sale_date повторюється, стандартна рамка може включити всі рядки з поточною датою. Тому для построкової накопичувальної суми краще вказувати ROWS явно.
Якщо порядок рядків має бути однозначним, додайте унікальний ключ:
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)Це особливо важливо, коли кілька рядків мають однакову дату або час.
FILTERДо віконної агрегатної функції можна застосувати FILTER. Наприклад, окремо рахувати загальну суму та суму лише великих замовлень:
WITH orders(order_date, amount) AS (
VALUES
(DATE '2026-01-01', 80.00::numeric),
(DATE '2026-01-02', 150.00::numeric),
(DATE '2026-01-03', 220.00::numeric),
(DATE '2026-01-04', 90.00::numeric)
)
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount,
SUM(amount) FILTER (WHERE amount >= 100) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_large_orders
FROM orders
ORDER BY order_date;FILTER змінює набір значень, які передаються конкретній агрегатній функції, але не прибирає рядки з результату.
ROWS з календарними днямиROWS BETWEEN 6 PRECEDING AND CURRENT ROWозначає сім рядків, а не сім днів. Якщо між записами є пропуски, результат буде іншим.
Для календарного періоду використовуйте RANGE з інтервалом або попередньо створюйте часовий ряд потрібної точності.
PARTITION BYБез PARTITION BY вікно охоплює всі рядки результату:
SUM(amount) OVER (
ORDER BY sale_date
)Якщо показник потрібно рахувати окремо для кожного магазину, клієнта чи рахунку, додайте відповідне розділення:
SUM(amount) OVER (
PARTITION BY store_id
ORDER BY sale_date
ROWS UNBOUNDED PRECEDING
)Сортування лише за датою не визначає порядок двох рядків з однаковою датою. Для передбачуваного результату використовуйте додатковий унікальний стовпець.
COALESCE замість перевірки кількості значеньCOALESCE(AVG(amount), 0) замінює NULL на нуль, але може приховати відсутність даних. Нульове середнє та відсутність значень — різні ситуації.
За потреби перевіряйте кількість значень через COUNT(amount) і лише потім визначайте, як відображати результат.
Якщо потрібна сума за днями, а таблиця містить транзакції, спочатку агрегуйте транзакції до дня. Інакше вікно працюватиме з транзакційними рядками, а не з денними підсумками.
Віконні агрегатні функції обчислюють показники, не стискаючи рядки результату.
PARTITION BY створює незалежні групи для обчислення.
ORDER BY визначає послідовність рядків у кожному розділі.
ROWS задає рамку за кількістю фізичних рядків.
RANGE задає рамку за значенням сортування, зокрема за часовим інтервалом.
Накопичувальна сума задається рамкою від UNBOUNDED PRECEDING до CURRENT ROW.
Ковзна сума або середнє використовує обмежену рамку, наприклад ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
Для кількох записів на день спочатку виконайте звичайну агрегацію, а потім застосовуйте віконну.
Для однакових значень сортування явно визначайте рамку та, за потреби, додавайте унікальний ключ до ORDER BY.