Рамки окна, LAG/LEAD и нарастающие итоги
Превратите окно в скользящее подокно и сравнивайте строки с соседями
Главная идея: В предыдущей теме вы добавляли
OVER (PARTITION BY ... ORDER BY ...), чтобы вычислять ранги и агрегаты по разделам. Рамка (frame) сужает это окно ещё сильнее: для каждой строки она задаёт подвижную полосу строк - "всё до этого места" или "вот эти три строки" - по которой вычисляется агрегат. Именно рамки превращают обычный SUM в нарастающий итог, а обычный AVG - в скользящее среднее. Рядом с рамками стоят функции навигации LAG и LEAD, позволяющие строке напрямую читать соседей.
К концу вы сможете:
- читать выражение рамки вроде
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, - объяснять разницу между
ROWS,RANGEи рамкой по умолчанию, - строить нарастающие итоги и скользящие средние,
- сравнивать строку с предыдущей или следующей через
LAGиLEAD, - избегать классической ловушки
LAST_VALUE, вызванной рамкой по умолчанию.
1. Таблица-пример, к которой мы будем обращаться
Каждый запрос ниже выполняется по одной небольшой таблице ежедневных продаж для двух регионов. Она намеренно крошечная, чтобы вы могли проверить каждый результат вручную.
CREATE TABLE daily_sales (
sale_date DATE,
region VARCHAR(20),
amount DECIMAL(10,2)
);
INSERT INTO daily_sales (sale_date, region, amount) VALUES
('2024-03-01', 'North', 100),
('2024-03-02', 'North', 120),
('2024-03-03', 'North', 90),
('2024-03-04', 'North', 150),
('2024-03-05', 'North', 130),
('2024-03-01', 'South', 80),
('2024-03-02', 'South', 60),
('2024-03-03', 'South', 110),
('2024-03-04', 'South', 70),
('2024-03-05', 'South', 100);Строки региона North в порядке дат выглядят так:
| sale_date | region | amount |
|---|---|---|
| 2024-03-01 | North | 100.00 |
| 2024-03-02 | North | 120.00 |
| 2024-03-03 | North | 90.00 |
| 2024-03-04 | North | 150.00 |
| 2024-03-05 | North | 130.00 |
Держите эти пять чисел в голове - 100, 120, 90, 150, 130 - потому что каждый нарастающий итог и скользящее среднее на этой странице построены из них.