Чем ROWS отличается от RANGE в рамке оконной функции и какая рамка стоит по умолчанию?
Проверила команда Сопровод · Обновлено
Короткий ответ
ROWS отсчитывает рамку в строках: 2 PRECEDING — две строки перед текущей. RANGE отсчитывает по значению столбца сортировки и включает все строки, равные текущей по ORDER BY. Если в OVER есть ORDER BY, по умолчанию стоит RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, без ORDER BY рамка — весь раздел.
Почему этот вопрос задают
Ошибка в рамке дает правдоподобные, но неверные скользящие средние и накопительные итоги. От опытного аналитика ждут, что он знает, по каким строкам считается окно, и выбирает рамку осознанно.
Как рассуждать
- Скажите, что рамка выбирает строки окна для расчета по текущей строке
- Объясните
ROWSчерез число строк,RANGEчерез разницу значений сортировки - Назовите рамку по умолчанию и отдельно случай без
ORDER BY
Ответ с примером
Рамка задает, какие строки окна попадут в расчет для текущей строки. ROWS считает физические строки: 2 PRECEDING возьмет две предыдущие, как бы далеко по времени они ни были. RANGE считает по значению столбца сортировки: INTERVAL '20 minutes' PRECEDING возьмет строки не старше 20 минут. С отступом RANGE требует ровно один столбец в ORDER BY. Третий режим, GROUPS, считает группы равных по сортировке строк.
По умолчанию при ORDER BY в окне рамка идет от начала раздела до текущей строки в режиме RANGE. Конец такой рамки — последняя строка, равная текущей по сортировке, поэтому у двух продаж одного дня накопительный итог одинаковый.
Датчик складского холодильника в запросе ниже пропустил замеры в 08:30 и 08:40. В 08:50 ROWS усреднил показания за 40 минут, включая 08:10. RANGE взял только последние 20 минут, то есть один замер.
SELECT to_char(measured_at, 'HH24:MI') AS t, temperature,
round(avg(temperature) OVER (ORDER BY measured_at
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_rows,
round(avg(temperature) OVER (ORDER BY measured_at
RANGE BETWEEN INTERVAL '20 minutes' PRECEDING AND CURRENT ROW), 2) AS avg_range
FROM sensor_readings
WHERE sensor_id = 1
ORDER BY measured_at;t | temperature | avg_rows | avg_range -------+-------------+----------+----------- 08:00 | -18.0 | -18.00 | -18.00 08:10 | -17.6 | -17.80 | -17.80 08:20 | -17.8 | -17.80 | -17.80 08:50 | -15.2 | -16.87 | -15.20 09:00 | -16.4 | -16.47 | -15.80 09:10 | -17.0 | -16.20 | -16.20 (6 rows)
ROWS и RANGE: в чем разница
| Свойство | ROWS | RANGE |
|---|---|---|
| Как считает отступ | число строк | разница значений сортировки |
| CURRENT ROW | только текущая строка | текущая и равные ей по сортировке |
| Пропуски во времени | не замечает | учитывает |
| ORDER BY для отступа | любой | ровно один столбец |
| Рамка по умолчанию | нет | да, если в окне есть ORDER BY |
Как ответить сильнее
Что добавит опытный кандидат
Что спросят следом
Доступно по подписке
Приемы опытных и готовые ответы
Частые ошибки
«Рамка по умолчанию — все строки раздела»
Уточните: так только без
ORDER BYв окне. С ним рамка идет от начала раздела до текущей строки и равных ей по сортировке«
ROWSиRANGEдают одинаковый результат»Покажите, где они расходятся: на повторах в столбце сортировки и на пропусках во времени
«Среднее за час — это
ROWS BETWEEN 5 PRECEDING AND CURRENT ROWпри замерах раз в 10 минут»Предупредите, что при пропуске замеров такая рамка захватит старые данные. Для периода пишите
RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW«
last_value()вернет последнюю строку раздела»Скажите, что по умолчанию рамка кончается на текущей строке и равных ей. Задайте
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Сигналы для интервьюера
Сильный ответ
Называет рамку по умолчанию, объясняет равные по сортировке строки в RANGE и сам выбирает рамку под неравномерные данные
Тревожный сигнал
Знает синтаксис ROWS BETWEEN, но не может объяснить одинаковый итог у строк с одной датой
Красный флаг
Не знает, что у окна есть рамка, и считает, что ORDER BY в OVER только сортирует строки
Как еще могут спросить
- Какая рамка окна используется по умолчанию в PostgreSQL?
- В чем разница между
ROWS BETWEENиRANGE BETWEEN? - Почему накопительный итог дает одинаковые значения для строк с одной датой?
- Как посчитать скользящее среднее за последние 30 минут, если замеры иногда пропадают?











