Віконні функції SQL: ранжування, накопичувальні суми і LAG
Інструмент, який перетворює складні звіти з підзапитами на кілька рядків SQL. Розбираємо OVER, PARTITION BY, ROW_NUMBER, RANK, LAG і рамки вікна на прикладах з продажами.
Звичайна агрегація (GROUP BY) «згортає» рядки: було 10 000 замовлень — лишилось 12 рядків по місяцях. Віконні функції рахують агрегати, але зберігають кожен рядок. Це дає змогу відповісти на питання на кшталт «яка частка цього замовлення в місячному обороті?» чи «наскільки виросли продажі порівняно з минулим місяцем?» без підзапитів і самоз'єднань.
Синтаксис
функція(...) OVER (
PARTITION BY ... -- на які групи ділити (необов'язково)
ORDER BY ... -- порядок усередині групи (необов'язково)
ROWS BETWEEN ... -- рамка вікна (необов'язково)
)Дані для прикладів:
CREATE TABLE sales (
sale_date date,
region text,
manager text,
amount numeric(10,2)
);Частка від суми групи
SELECT region, manager, amount,
sum(amount) OVER (PARTITION BY region) AS region_total,
round(100.0 * amount / sum(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
WHERE sale_date >= date '2026-08-01' AND sale_date < date '2026-09-01';Кожен рядок зберігся, а поруч — сума по його регіону. З GROUP BY довелося б робити підзапит і з'єднання.
Ранжування: ROW_NUMBER, RANK, DENSE_RANK
| amount | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 900 | 1 | 1 | 1 |
| 750 | 2 | 2 | 2 |
| 750 | 3 | 2 | 2 |
| 600 | 4 | 4 | 3 |
ROW_NUMBER— унікальний номер, нічия розв'язується довільно;RANK— однакові значення отримують однаковий ранг, наступний пропускається;DENSE_RANK— без пропусків.
Топ-3 менеджери в кожному регіоні
WITH ranked AS (
SELECT region, manager, sum(amount) AS revenue,
DENSE_RANK() OVER (PARTITION BY region ORDER BY sum(amount) DESC) AS place
FROM sales
GROUP BY region, manager
)
SELECT * FROM ranked WHERE place <= 3 ORDER BY region, place;ПриміткаВіконну функцію не можна використати прямо у
WHERE— вона обчислюється пізніше. Тому фільтрацію за рангом роблять через CTE або підзапит, як вище. У деяких СКБД (Snowflake, BigQuery, DuckDB) для цього єQUALIFY.
Дедуплікація: останній запис для кожного клієнта
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
FROM customer_snapshots
) t
WHERE rn = 1;LAG і LEAD: порівняння з попереднім
WITH monthly AS (
SELECT date_trunc('month', sale_date)::date AS month, sum(amount) AS revenue
FROM sales
GROUP BY 1
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
round(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;NULLIF(..., 0) захищає від ділення на нуль. Для першого місяця LAG поверне NULL — і це чесно: порівнювати нема з чим.
ПорадаЩоб не повторювати однакове
OVER (...), оголосіть вікно один раз:WINDOW w AS (ORDER BY month)і пишітьLAG(revenue) OVER w.
Накопичувальні суми і ковзні середні
SELECT sale_date, daily,
sum(daily) OVER (ORDER BY sale_date) AS running_total,
round(avg(daily) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS avg_7d
FROM (
SELECT sale_date, sum(amount) AS daily FROM sales GROUP BY sale_date
) d
ORDER BY sale_date;Рамки вікна: ROWS проти RANGE
Коли в OVER є ORDER BY, але рамку не вказано, за замовчуванням використовується RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE включає всі рядки з тим самим значенням сортування — тож якщо в одну дату кілька продажів, накопичувальна сума «стрибне» одразу на всі.
-- рахує кожен рядок окремо, навіть якщо дати однакові
sum(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)Увага
ROWS BETWEEN 6 PRECEDING— це «6 попередніх рядків», а не «6 попередніх днів». Якщо в даних є пропущені дні, ковзне середнє вийде не за тиждень. Для календарних періодів спочатку доповніть дати черезgenerate_series.
Корисне для звітів
NTILE(4) OVER (ORDER BY revenue DESC) -- квартилі клієнтів за виручкою
FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) -- перша покупка
PERCENT_RANK() OVER (ORDER BY revenue) -- перцентиль клієнтаЧим відрізняються RANK і DENSE_RANK, якщо два менеджери ділять друге місце?
RANK дасть обом 2, а наступному — 4 (третє місце пропущено). DENSE_RANK дасть обом 2, а наступному — 3, без пропусків.
Збережіть статтю собі
Увійдіть, щоб імпортувати її у власні нотатки, відмічати вивчене, ставити лайки й питати спільноту. Реєстрація безкоштовна: вхід через Google займає кілька секунд.
Увійти через Google Інші способи входу