Чем ROWS отличается от RANGE в рамке оконной функции и какая рамка стоит по умолчанию?

ЗнаниеSeniorИногда

Проверила команда СопроводОбновлено

Короткий ответ

ROWS отсчитывает рамку в строках: 2 PRECEDING — две строки перед текущей. RANGE отсчитывает по значению столбца сортировки и включает все строки, равные текущей по ORDER BY. Если в OVER есть ORDER BY, по умолчанию стоит RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, без ORDER BY рамка — весь раздел.

Почему этот вопрос задают

Ошибка в рамке дает правдоподобные, но неверные скользящие средние и накопительные итоги. От опытного аналитика ждут, что он знает, по каким строкам считается окно, и выбирает рамку осознанно.

Как рассуждать

  1. Скажите, что рамка выбирает строки окна для расчета по текущей строке
  2. Объясните ROWS через число строк, RANGE через разницу значений сортировки
  3. Назовите рамку по умолчанию и отдельно случай без 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 минут, то есть один замер.

SQL
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: в чем разница

СвойствоROWSRANGE
Как считает отступчисло строкразница значений сортировки
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 минут, если замеры иногда пропадают?

Похожие вопросы

ЗнаниеJuniorЧасто

Чем оконная функция отличается от GROUP BY?

GROUP BY схлопывает строки: на выходе одна строка на группу, и в SELECT остаются только столбцы группировки и агрегаты. Оконная функция с OVER считает агрегат по группе строк, но каждая строка остается в результате со всеми столбцами. Поэтому окно берут, когда рядом с каждой строкой нужно число по ее группе: среднее, ранг, итог.

  • оконные функции
  • GROUP BY
274
ЗадачаMiddleЧасто

Посчитайте накопительную выручку по дням месяца, включая дни без заказов

Соберите календарь дней через generate_series и присоедините к нему заказы через LEFT JOIN, чтобы пустые дни не пропали. Сверните заказы до одной строки на день через GROUP BY и coalesce(sum(o.amount), 0.00): без coalesce у пустого дня вместо нуля будет NULL. Поверх дневной выручки посчитайте sum(revenue) OVER (ORDER BY day): окно сложит все дни от начала периода до текущего.

  • оконные функции
  • generate_series
210
ЗадачаSeniorИногда

Найдите для каждого пользователя самую длинную серию дней подряд, когда он заходил в приложение

Сначала оставьте по одной строке на пользователя и день через DISTINCT. Затем вычтите из даты номер строки ROW_NUMBER() внутри пользователя: у дней подряд разность одинаковая, это и есть номер серии. Сгруппируйте по пользователю и этой разности, посчитайте дни в каждой серии и возьмите MAX.

  • оконные функции
  • ROW_NUMBER
181
ЗнаниеJuniorЧасто

Чем ROW_NUMBER отличается от RANK и DENSE_RANK?

Все три функции нумеруют строки окна в порядке из ORDER BY, а различаются на равных значениях. ROW_NUMBER дает каждой строке свой номер, даже при равенстве. RANK дает равным строкам одинаковый номер и пропускает следующие: 1, 2, 2, 4. DENSE_RANK тоже дает одинаковый номер, но без пропусков: 1, 2, 2, 3.

  • оконные функции
239

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Чаще на middle и senior, особенно у тех, кто считает скользящие средние и сравнивает периоды. На junior обычно хватает накопительного итога и ранжирования без явной рамки.

С PostgreSQL 11: в нем появились RANGE с отступом, режим GROUPS и исключение строк из рамки. В старых версиях RANGE понимал только UNBOUNDED и CURRENT ROW, и скользящее окно по времени собирали самосоединением.

Добавьте в запрос count(*) OVER (...) с той же рамкой: он покажет, сколько строк попало в расчет для каждой строки. На пяти-шести строках с пропуском и повтором разница между ROWS и RANGE видна сразу.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

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

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

Назовите свое правило: что вы отдаете команде и что оставляете себе, и покажите его на одной задаче. Расскажите, кому и почему передали, какие полномочия и рамки дали, как проверяли без мелкого контроля. Закончите честным итогом: что получилось хуже или медленнее, чем у вас, и что вы выиграли.

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

Поговорите сначала с каждым по отдельности: что случилось, с чего началось и что мешает работать. Потом соберите обоих вместе и обсуждайте работу: кто за какие данные и шаги отвечает. За такой ссорой часто стоит размытая ответственность. Запишите договоренности, назовите новый срок для сорванного отчета и через пару недель проверьте, что все работает.

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227