ROWS BETWEEN
Задаёт рамку окна — строки раздела, по которым оконная функция считает значение для текущей строки.
{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_endПараметры
- ROWS | RANGE | GROUPSключевое слово
- Как считаются границы: ROWS — в строках, RANGE — по значению ORDER BY, GROUPS — в группах строк с одинаковым значением ORDER BY
- Значения:ROWSRANGEGROUPS
- frame_startключевое слово
- Начало рамки
- Значения:UNBOUNDED PRECEDINGoffset PRECEDINGCURRENT ROWoffset FOLLOWING
- frame_endключевое слово
- Конец рамки, не раньше её начала
- Значения:offset PRECEDINGCURRENT ROWoffset FOLLOWINGUNBOUNDED FOLLOWING
Возвращаемое значение
Не возвращает значение: задаёт строки для оконной функции внутри OVER (...)
Примеры
Сумма трёх последних платежей
SELECT payment_id,
date,
unit_price,
SUM(unit_price) OVER (
ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS last_three
FROM Payments
ORDER BY date;Нарастающий итог у каждого члена семьи
SELECT family_member,
date,
unit_price,
SUM(unit_price) OVER (
PARTITION BY family_member
ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM Payments
ORDER BY family_member,
date;Сумма за последние 30 дней
SELECT date,
unit_price,
SUM(unit_price) OVER (
ORDER BY date RANGE BETWEEN INTERVAL '30 days' PRECEDING
AND CURRENT ROW
) AS last_30_days
FROM Payments
ORDER BY date;ROWS, RANGE и GROUPS при одинаковых ценах
SELECT unit_price,
COUNT(*) OVER (
ORDER BY unit_price ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rows_frame,
COUNT(*) OVER (
ORDER BY unit_price RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS range_frame,
COUNT(*) OVER (
ORDER BY unit_price GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM Payments
ORDER BY unit_price;Подробности
Границы: UNBOUNDED PRECEDING — начало раздела, offset PRECEDING — offset строк до текущей, CURRENT ROW — текущая строка, offset FOLLOWING — offset строк после текущей, UNBOUNDED FOLLOWING — конец раздела. Для RANGE offset задаётся в единицах ORDER BY, например INTERVAL '30 days' для дат.
Если рамка не указана, то с ORDER BY она идёт от начала раздела до текущей строки вместе со всеми строками с тем же значением ORDER BY (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), а без ORDER BY — весь раздел. Поэтому LAST_VALUE и NTH_VALUE без явной рамки часто возвращают не то, что ожидается.
После рамки можно исключить строки: EXCLUDE CURRENT ROW, EXCLUDE GROUP или EXCLUDE TIES. На ранжирующие функции и на LAG и LEAD рамка не влияет.