Многие аналитики активно используют базовые оконные функции, но не всегда знают, как применять такие инструменты, как фрейм окна (window frame). А ведь это сделает запросы ещё более читабельными и эффективными. Давайте разбираться вместе.
Фрейм определяет набор строк для вычислений оконной функции относительно текущей строки.
В этой статье рассмотрим два способа определить, какие строки включать в окно для расчетов: ROWS и RANGE.
Начнем с ROWS BETWEEN. Эта конструкция работает с физическими строками и помогает анализировать конкретное количество записей до и после текущей строки. Чтобы стало понятнее, рассмотрим пример: мы хотим рассчитать скользящую сумму продаж за последние 3 дня, включая текущий день. Для этого подсчёта нам необходимо от каждой строки отсчитать две строки назад и суммировать значения продаж за эти дни.
| sales_date | sales_amount |
|------------|--------------|
| 2024-01-01 | 100 |
| 2024-01-02 | 150 |
| 2024-01-03 | 200 |
| 2024-01-04 | 250 |
SELECT
sales_date,
sales_amount,
SUM(sales_amount) OVER (
ORDER BY sales_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_sum
FROM sales_daily;
Результат:
| sales_date | sales_amount | rolling_sum |
|------------|--------------|-------------|
| 2024-01-01 | 100 | 100 |
| 2024-01-02 | 150 | 250 |
| 2024-01-03 | 200 | 450 |
| 2024-01-04 | 250 | 600 |
Выражением ROWS BETWEEN 2 PRECEDING AND CURRENT ROW мы буквально говорим, что нужно от каждой строки отсчитывать две строки назад и суммировать значения продаж за эти дни (включая значение в текущей). Таким образом, каждая строка будет содержать сумму продаж за текущий день и два предыдущих. Важно! В нашей исходной витрине данные уже сгруппированы по дням, поэтому в данном случае корректно говорить Х дней назад, но по сути мы говорим про строки.
RANGE BETWEEN работает иначе. Он группирует строки по значению, а не по их физическому положению. Это бывает полезно, когда нужно учитывать все строки с определённым диапазоном значений относительно текущей строки. Не понятно? Перейдём к примеру.
Нужно рассчитать среднюю цену за последние 3 дня от каждой даты:
| price_date | stock_price |
|-------------|-------------|
| 2024-01-01 | 100 |
| 2024-01-01 | 102 |
| 2024-01-02 | 105 |
| 2024-01-04 | 103 |
| 2024-01-04 | 106 |
| 2024-01-06 | 110 |
SELECT
price_date,
stock_price,
AVG(stock_price) OVER (
ORDER BY price_date
RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND CURRENT ROW
) AS avg_price_3days
FROM stock_prices;
Результат:
| price_date | stock_price | avg_price_3days |
|------------|-------------|-----------------|
| 2024-01-01 | 100 | 101.00 |
| 2024-01-01 | 102 | 101.00 |
| 2024-01-02 | 105 | 102.33 |
| 2024-01-04 | 103 | 103.20 |
| 2024-01-04 | 106 | 103.20 |
| 2024-01-06 | 110 | 106.33 |
При использовании RANGE все строки с одинаковым значением столбца, указанного в ORDER BY, обрабатываются вместе. А RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND CURRENT ROW говорит о том, что нужно посчитать значения в фрейме с интервалом 3 дня до текущей строки.
Обратите внимание, что даты 2024-01-03 и 2024-01-05 отсутствуют в таблице. Когда мы используем указанный RANGE BETWEEN, SQL ищет все строки, где price_date находится в диапазоне от текущей даты минус календарных 3 дня, то есть учитывает только имеющиеся даты в этом диапазоне.
Поняли разницу? RANGE учитывает все записи в указанном временном интервале, даже если их много. А ROWS всегда отсчитывает фиксированное количество строк.
Естественно обе функции можно использовать не только при расчётах связанными с датами. Давайте в одной из следующих статей рассмотрим другие примеры. Фреймы кажутся той темой, которой стоит уделить особое внимание.
#sql