ROWS BETWEEN
Задаёт рамку окна — строки раздела, по которым оконная функция считает значение для текущей строки.
{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end [frame_exclusion]- [ ]необязательная часть
- { a | b }один из вариантов
- ROWS | RANGE | GROUPS
- Единица границ: строки, значения ORDER BY или группы равных строкКлючевое словоROWSRANGEGROUPS
- frame_start
- Начало рамкиКлючевое словоUNBOUNDED PRECEDINGoffset PRECEDINGCURRENT ROWoffset FOLLOWING
- frame_end
- Конец рамки, не раньше её началаКлючевое словоoffset PRECEDINGCURRENT ROWoffset FOLLOWINGUNBOUNDED FOLLOWING
- frame_exclusion
- Какие строки исключить из рамки, по умолчанию никакиеКлючевое словоНеобязательныйEXCLUDE CURRENT ROWEXCLUDE GROUPEXCLUDE TIESEXCLUDE NO OTHERS
Возвращаемое значение
Не возвращает значение: задаёт строки для оконной функции внутри 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 — её и равные ей по ORDER BY, а EXCLUDE TIES — только равные. На ранжирующие функции и на LAG и LEAD рамка не влияет.