Оконные рамки RANGE и равные значения

Понятие peers и рамка по умолчанию при равных значениях

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

Создадим таблицы замеров давления и результатов тестирования, на которых проверим поведение оконных агрегатов:

CREATE TABLE sensor_readings (
    reading_id INTEGER PRIMARY KEY,
    sensor_id INTEGER NOT NULL,
    elapsed_sec INTEGER NOT NULL,
    pressure REAL NOT NULL
);

INSERT INTO sensor_readings (reading_id, sensor_id, elapsed_sec, pressure) VALUES
(1, 101, 10, 2.0),
(2, 101, 12, 3.5),
(3, 101, 12, 1.5),
(4, 101, 15, 4.0),
(5, 101, 25, 5.0),
(6, 101, 26, 2.0),
(7, 102, 10, 8.0);

CREATE TABLE exam_scores (
    student_id INTEGER PRIMARY KEY,
    score INTEGER NOT NULL
);

INSERT INTO exam_scores (student_id, score) VALUES
(1, 70),
(2, 75),
(3, 75),
(4, 80),
(5, 95);

Выполним расчет нарастающего итога для датчика 101 без явного указания границ рамки:

SELECT reading_id, elapsed_sec, pressure,
       SUM(pressure) OVER (
           PARTITION BY sensor_id
           ORDER BY elapsed_sec
       ) AS running_pressure_default
FROM sensor_readings
WHERE sensor_id = 101
ORDER BY elapsed_sec, reading_id;

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

reading_id	elapsed_sec	pressure	running_pressure_default
1	10	2.0	2.0
2	12	3.5	7.0
3	12	1.5	7.0
4	15	4.0	11.0
5	25	5.0	16.0
6	26	2.0	18.0

Для строк 2 и 3 значение elapsed_sec одинаково и равно 12. В столбце running_pressure_default обе строки получили результат 7.0. В сумму вошли замер первой секунды (2.0) и оба замера на двенадцатой секунде: 2.0 + 3.5 + 1.5 = 7.0.

Строки раздела, которые имеют одинаковые значения во всех столбцах предложения ORDER BY, называются сверстниками (peers). По стандарту SQL и в SQLite, если в окне указан ORDER BY, но рамка не задана явно, СУБД применяет определение по умолчанию:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

В рамке RANGE граница CURRENT ROW работает иначе, чем в ROWS. Она обозначает не одну текущую физическую запись, а группу всех её сверстников. При расчете агрегата для строки 2 рамка расширяется до конца группы со значением 12 и сразу захватывает строку 3. Поэтому обе строки получают идентичный накопленный итог.

Ограниченная рамка RANGE по числовой шкале

Рамка RANGE умеет ограничивать окно не количеством физических строк, а математическим смещением по числовой шкале ORDER BY. Конструкция RANGE BETWEEN N PRECEDING AND CURRENT ROW включает в расчёт только те строки, чьё значение ключа сортировки попадает в числовой интервал [val - N, val], где val — значение текущей строки, а N — неотрицательная числовая константа.

Посчитаем скользящую сумму давления за текущую секунду и три предшествующие секунды (интервал [elapsed_sec - 3, elapsed_sec]):

SELECT reading_id, elapsed_sec, pressure,
       SUM(pressure) OVER (
           PARTITION BY sensor_id
           ORDER BY elapsed_sec
           RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
       ) AS sum_last_3_sec
FROM sensor_readings
WHERE sensor_id = 101
ORDER BY elapsed_sec, reading_id;

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

reading_id	elapsed_sec	pressure	sum_last_3_sec
1	10	2.0	2.0
2	12	3.5	7.0
3	12	1.5	7.0
4	15	4.0	9.0
5	25	5.0	5.0
6	26	2.0	7.0

На строке с секундой 15 окно отбирает замеры из диапазона от 12 до 15 секунд (15 - 3 = 12). В этот интервал попадают строки со значениями 12 (давление 3.5 и 1.5) и 15 (давление 4.0). Их сумма равна 3.5 + 1.5 + 4.0 = 9.0. Замер с десятой секунды в диапазон не вошел, так как 10 < 12.

На строке с секундой 25 окно ищет замеры в диапазоне от 22 до 25. Предыдущий замер был сделан на 15 секунде, поэтому в рамку вошла только сама 25-я секунда со значением 5.0.

Рамку можно расширять и вперёд с помощью FOLLOWING. Рассчитаем количество студентов из таблицы exam_scores, чьи баллы находятся в коридоре [score - 5, score + 5]:

SELECT student_id, score,
       COUNT(*) OVER (
           ORDER BY score
           RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
       ) AS students_in_corridor
FROM exam_scores
ORDER BY score, student_id;

Результат вычисления:

student_id	score	students_in_corridor
1	70	3
2	75	4
3	75	4
4	80	3
5	95	1

Для студента 1 с баллом 70 коридор составляет [65, 75]. В него попадают студент 1 (70) и два студента с баллом 75, всего 3 человека. Для студентов с баллом 75 диапазон равен [70, 80]: в него входят строки со значениями 70, 75, 75 и 80, то есть 4 человека. Для балла 95 коридор [90, 100] содержит лишь одну строку.

Общий шаблон для агрегирования по числовому диапазону шкалы:

SELECT entity_id, val_col,
       AGG(metric_col) OVER (
           PARTITION BY entity_id
           ORDER BY val_col
           RANGE BETWEEN N PRECEDING AND CURRENT ROW
       ) AS range_agg
FROM facts;

В SQLite для явных численных границ N PRECEDING или N FOLLOWING в секции ORDER BY должно быть строго одно числовое выражение.

Различия ROWS и RANGE на дублях и разрывах шкалы

Конструкция ROWS считает физические строки, а RANGE измеряет расстояние между значениями сортировки. Сопоставим обе рамки с одинаковым числовым смещением 2 на таблице sensor_readings:

SELECT reading_id, elapsed_sec, pressure,
       SUM(pressure) OVER (
           PARTITION BY sensor_id
           ORDER BY elapsed_sec, reading_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rows_2_preceding,
       SUM(pressure) OVER (
           PARTITION BY sensor_id
           ORDER BY elapsed_sec
           RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS range_2_preceding
FROM sensor_readings
WHERE sensor_id = 101
ORDER BY elapsed_sec, reading_id;

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

reading_id	elapsed_sec	pressure	rows_2_preceding	range_2_preceding
1	10	2.0	2.0	2.0
2	12	3.5	5.5	7.0
3	12	1.5	7.0	7.0
4	15	4.0	9.0	4.0
5	25	5.0	10.5	5.0
6	26	2.0	11.0	7.0

Разница между подходами видна на двух ситуациях: дублирующихся ключах и разрывах шкалы.

На строке 2 (elapsed_sec = 12): ROWS BETWEEN 2 PRECEDING AND CURRENT ROW захватывает две физические строки: текущую и предыдущую (секунды 12 и 10), получая 3.5 + 2.0 = 5.5. Вторая запись с секундой 12 ещё не обработана физическим порядком. Рамка RANGE BETWEEN 2 PRECEDING AND CURRENT ROW оценивает диапазон [10, 12] и включает всех peers текущего значения 12. В результат сразу попадают строки 1, 2 и 3, давая сумму 7.0.

На строке 4 (elapsed_sec = 15): ROWS берёт две предшествующие физические записи — обе строки со значением 12. В сумму входят 3.5 + 1.5 + 4.0 = 9.0. Рамка RANGE отбирает замеры из отрезка [13, 15]. Замеры 12-й секунды в этот отрезок не попадают. В рамке остаётся только текущая строка со значением 4.0.

На строке 5 (elapsed_sec = 25): между 15 и 25 секундами прошло 10 секунд. ROWS игнорирует этот временной разрыв и суммирует строку 5 (5.0), строку 4 (4.0) и строку 3 (1.5), получая 10.5. RANGE проверяет диапазон [23, 25], отсекает далёкие события и оставляет только 5.0.

Выбор между ROWS и RANGE определяется смыслом задачи: если метрику нужно рассчитать строго за последние N записей независимо от времени их появления, используется ROWS; если агрегат требуется за физический интервал шкалы значений, применяется RANGE.

Ловушка добавления суррогатного ключа в ORDER BY при RANGE

Для устранения неоднозначности порядка строк в окно часто добавляют уникальный идентификатор: ORDER BY elapsed_sec, reading_id. Если применить такую сортировку к рамке RANGE с числовым смещением, запрос завершится ошибкой:

SELECT reading_id, elapsed_sec, pressure,
       SUM(pressure) OVER (
           PARTITION BY sensor_id
           ORDER BY elapsed_sec, reading_id
           RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
       ) AS err_sum
FROM sensor_readings
WHERE sensor_id = 101;

База данных возвращает диагностику:

RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression

Причина ограничения: числовое смещение 3 PRECEDING вычитается из значения ключа сортировки. Если в ORDER BY указано два столбца с разными единицами измерения (elapsed_sec и reading_id), математическая операция вычитания смещения становится неопределённой. SQLite требует строго одно выражение сортировки при использовании явных числовых границ в RANGE.

Даже в диалектах, допускающих составной ключ в неявной рамке RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, добавление уникального идентификатора в окно меняет логику расчёта. Первичный ключ делает каждую пару значений уникальной, что уничтожает группы peers: строки с одинаковым временем перестанут считаться сверстниками и начнут суммироваться раздельно.

Для сохранения логики логического диапазона и одновременного получения детерминированного вывода уникальный ключ указывается только во внешнем ORDER BY запроса, но не внутри секции OVER():

SELECT reading_id, elapsed_sec, pressure,
       SUM(pressure) OVER (
           PARTITION BY sensor_id
           ORDER BY elapsed_sec
           RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
       ) AS pressure_window_sum
FROM sensor_readings
WHERE sensor_id = 101
ORDER BY elapsed_sec, reading_id;

В таком виде оконная функция объединяет сверстников по секундной шкале, а внешний порядок гарантирует воспроизводимый список строк на выходе.