Оконные рамки ROWS

Синтаксис границ оконной рамки ROWS

В базовых запросах с накопительным итогом конструкция ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW включала в расчёт все строки от начала раздела до текущей позиции. Если аналитическая задача требует ограничить расчёт только соседними строками, в секции OVER() задают явные смещения.

Подготовим таблицу замеров температуры на серверах:

CREATE TABLE server_readings (
  reading_id INTEGER PRIMARY KEY,
  server_id INTEGER NOT NULL,
  measured_at TEXT NOT NULL,
  temp_c REAL
);

INSERT INTO server_readings (reading_id, server_id, measured_at, temp_c) VALUES
  (1, 101, '2024-03-01 08:00:00', 40.0),
  (2, 101, '2024-03-01 12:00:00', NULL),
  (3, 101, '2024-03-05 09:00:00', 50.0),
  (4, 101, '2024-03-06 10:00:00', 60.0),
  (5, 101, '2024-03-07 11:00:00', 70.0),
  (6, 202, '2024-03-01 08:00:00', 25.0),
  (7, 202, '2024-03-02 08:00:00', 35.0);

Базовый состав строк при сортировке выглядит так:

reading_id	server_id	measured_at	temp_c
1	101	2024-03-01 08:00:00	40.0
2	101	2024-03-01 12:00:00	NULL
3	101	2024-03-05 09:00:00	50.0
4	101	2024-03-06 10:00:00	60.0
5	101	2024-03-07 11:00:00	70.0
6	202	2024-03-01 08:00:00	25.0
7	202	2024-03-02 08:00:00	35.0

Конструкция оконной рамки имеет вид: ROWS BETWEEN <начало> AND <конец>

Для границ начала и конца допустимы пять выражений:

• UNBOUNDED PRECEDING — от самой первой строки раздела;
• N PRECEDING — на N физических строк назад от текущей;
• CURRENT ROW — текущая строка;
• N FOLLOWING — на N физических строк вперёд от текущей;
• UNBOUNDED FOLLOWING — до самой последней строки раздела.

SQLite строго регламентирует допустимый порядок категорий границ: тип границы FOLLOWING не может стоять в позиции начала рамки, если в конце указан PRECEDING или CURRENT ROW. При попытке нарушить порядок типов границ:

SELECT
  reading_id,
  SUM(temp_c) OVER (
    ORDER BY measured_at
    ROWS BETWEEN 1 FOLLOWING AND 1 PRECEDING
  ) AS invalid_sum
FROM server_readings;

СУБД вернёт синтаксическую ошибку (unsupported frame specification).

При этом числовая инверсия внутри одного типа границы синтаксической ошибкой в SQLite не является: выражения вида ROWS BETWEEN 2 PRECEDING AND 5 PRECEDING или ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING успешно выполняются, но формируют пустую рамку, для которой функции вроде SUM возвращают NULL.

Порядок строк в окне ORDER BY должен быть строго детерминированным. Если поле времени содержит одинаковые значения, к нему добавляют уникальный идентификатор строки (например, ORDER BY measured_at, reading_id), иначе при пересчёте одинаковые строки могут попадать в рамку в случайном порядке.

Физические строки и календарные интервалы

Рамка ROWS оперирует исключительно количеством физических строк в упорядоченном наборе данных. Она не анализирует календарные даты, метки времени и интервалы между ними.

Рассчитаем сумму температур по серверу 101 с рамкой из двух предшествующих физических строк:

SELECT
  reading_id,
  measured_at,
  temp_c,
  SUM(temp_c) OVER (
    PARTITION BY server_id
    ORDER BY measured_at, reading_id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS sum_last_3_physical_rows
FROM server_readings
WHERE server_id = 101
ORDER BY measured_at, reading_id;

Результат выполнения:

reading_id	measured_at	temp_c	sum_last_3_physical_rows
1	2024-03-01 08:00:00	40.0	40.0
2	2024-03-01 12:00:00	NULL	40.0
3	2024-03-05 09:00:00	50.0	90.0
4	2024-03-06 10:00:00	60.0	110.0
5	2024-03-07 11:00:00	70.0	180.0

Обратите внимание на строку 3 (2024-03-05 09:00:00). Между ней и предыдущим измерением от 2024-03-01 12:00:00 прошло почти четыре календарных дня. Однако рамка ROWS BETWEEN 2 PRECEDING AND CURRENT ROW требует взять ровно две предшествующие записи из таблицы: строку 1 и строку 2. В сумму 90.0 вошли значения строк 1 (40.0), 2 (NULL) и 3 (50.0).

Отсюда вытекает типичная ошибка: попытка посчитать «недельное скользящее среднее» через ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Если в таблице зафиксированы не все календарные дни (например, пропущены выходные или были сбои сбора данных), такое окно объединит 7 физических строк. По календарю эти 7 строк могут растянуться на 10, 14 или 30 дней. Рамка ROWS гарантирует число строк в расчёте, но никогда не гарантирует интервал времени.

Усечение рамки на краях раздела и поведение NULL

Когда текущая строка находится близко к началу или концу раздела (PARTITION BY), запрошенное количество предшествующих или последующих строк может физически отсутствовать. В этом случае СУБД не выдаёт ошибку и не заполняет недостающие строки нулями, а автоматически усекает рамку до фактически существующих записей внутри текущего раздела.

Проверим работу симметричного окна ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, а также поведение функций подсчёта и усреднения:

SELECT
  server_id,
  reading_id,
  measured_at,
  temp_c,
  COUNT(*) OVER (
    PARTITION BY server_id
    ORDER BY measured_at, reading_id
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS frame_row_count,
  COUNT(temp_c) OVER (
    PARTITION BY server_id
    ORDER BY measured_at, reading_id
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS frame_val_count,
  AVG(temp_c) OVER (
    PARTITION BY server_id
    ORDER BY measured_at, reading_id
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg_3
FROM server_readings
ORDER BY server_id, measured_at, reading_id;

Результат выполнения:

server_id	reading_id	measured_at	temp_c	frame_row_count	frame_val_count	moving_avg_3
101	1	2024-03-01 08:00:00	40.0	2	1	40.0
101	2	2024-03-01 12:00:00	NULL	3	2	45.0
101	3	2024-03-05 09:00:00	50.0	3	2	55.0
101	4	2024-03-06 10:00:00	60.0	3	3	60.0
101	5	2024-03-07 11:00:00	70.0	2	2	65.0
202	6	2024-03-01 08:00:00	25.0	2	2	30.0
202	7	2024-03-02 08:00:00	35.0	2	2	30.0

Механизм усечения проявляется на границах разделов:

• Для строки 1 предшествующей строки в разделе server_id = 101 нет. Рамка усекается слева и содержит только строки 1 и 2 (frame_row_count = 2).
• Для строки 5 последующей строки в разделе нет. Рамка усекается справа и содержит только строки 4 и 5 (frame_row_count = 2).
• Переход к серверу 202 изолирует данные: строки сервера 101 не проникают в окно сервера 202. Для строки 6 рамка усечена слева (строки 6 и 7), для строки 7 — усечена справа (строки 6 и 7).

Наличие NULL в столбце temp_c разделяет поведение COUNT(*) и агрегатных функций по столбцу:

• COUNT(*) считает все физические строки рамки. Для строки 2 в рамку попали строки 1, 2 и 3, поэтому frame_row_count равен 3.
• COUNT(temp_c) считает только строки с непустыми значениями. Во второй строке значение NULL, поэтому frame_val_count равен 2.
• AVG(temp_c) игнорирует NULL. Для строки 2 среднее вычисляется по значениям строк 1 (40.0) и 3 (50.0): (40.0 + 50.0) / 2 = 45.0. Значение делится именно на число непустых элементов (2), а не на общее количество строк в рамке (3). Если бы все значения в рамке оказались NULL, функции SUM и AVG вернули бы NULL, а COUNT(temp_c) вернул бы 0.

Шаблоны скользящих показателей и остатка до конца раздела

На практике смещения рамки ROWS используют для трёх основных задач: расчёта предшествующего скользящего окна, сглаживания по окрестности и вычисления остатка до конца партиции.

Шаблон скользящего среднего по текущей и двум предшествующим физическим строкам:

SELECT
  server_id,
  measured_at,
  temp_c,
  ROUND(AVG(temp_c) OVER (
    PARTITION BY server_id
    ORDER BY measured_at ASC, reading_id ASC
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_temp
FROM server_readings;

Добавление reading_id ASC после даты гарантирует детерминированность: если за одну секунду поступят два замера, они выстроятся в строгом порядке первичного ключа.

Второй шаблон — симметричное скользящее окно для сглаживания метрики по соседям слева и справа:

AVG(temp_c) OVER (
  PARTITION BY server_id
  ORDER BY measured_at ASC, reading_id ASC
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

Третий шаблон — агрегация от текущей строки до конца раздела с помощью границы UNBOUNDED FOLLOWING. Она применяется для расчёта остатка метрики:

SELECT
  server_id,
  reading_id,
  measured_at,
  temp_c,
  SUM(temp_c) OVER (
    PARTITION BY server_id
    ORDER BY measured_at, reading_id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS remaining_sum
FROM server_readings
ORDER BY server_id, measured_at, reading_id;

Результат запроса остатка:

server_id	reading_id	measured_at	temp_c	remaining_sum
101	1	2024-03-01 08:00:00	40.0	220.0
101	2	2024-03-01 12:00:00	NULL	180.0
101	3	2024-03-05 09:00:00	50.0	180.0
101	4	2024-03-06 10:00:00	60.0	130.0
101	5	2024-03-07 11:00:00	70.0	70.0
202	6	2024-03-01 08:00:00	25.0	60.0
202	7	2024-03-02 08:00:00	35.0	35.0

В первой строке раздела server_id = 101 рамка охватывает строки с 1 по 5. Значения 40.0 + 50.0 + 60.0 + 70.0 дают общую сумму раздела 220.0. По мере движения вниз предшествующие строки исключаются из рамки, пока на последней строке раздела (строка 5) в окне не останется только текущее значение 70.0.