Оконные функции SQL
Ранжирование, оконные рамки, LAG/LEAD, NTILE, нарастающие итоги и скользящие средние в SQL.
12 вопросов
JuniorТеорияОчень частоЧем PARTITION BY отличается от GROUP BY и почему оконная функция сохраняет каждую строку?
Чем PARTITION BY отличается от GROUP BY и почему оконная функция сохраняет каждую строку?
GROUP BY сворачивает каждую группу в одну строку, теряя детализацию. PARTITION BY лишь задаёт окно для функции, которая возвращает значение для каждой строки, поэтому строки сохраняются рядом с итогом группы.
Типичные ошибки
- ✗Считать, что оконный агрегат сворачивает строки как
GROUP BY - ✗Путать, какая клауза сохраняет детализацию по строкам
- ✗Думать, что
PARTITION BYотфильтровывает строки из результата
Уточняющие вопросы
- →Как получить итог группы И свернуть до одной строки на группу?
- →Может ли запрос использовать
GROUP BYи оконную функцию одновременно?
JuniorТеорияОчень частоROW_NUMBER vs RANK vs DENSE_RANK — как каждая обходится с равными значениями и какая пропускает номера?
ROW_NUMBER vs RANK vs DENSE_RANK — как каждая обходится с равными значениями и какая пропускает номера?
ROW_NUMBER даёт каждой строке уникальный номер, равные упорядочены произвольно. RANK даёт равным один номер, но пропускает следующие: 1,1,3. DENSE_RANK приравнивает без пропусков: 1,1,2. Пропускает только RANK.
Типичные ошибки
- ✗Думать, что
ROW_NUMBERдаёт равным строкам одинаковый номер - ✗Путать, какая из
RANK/DENSE_RANKоставляет пропуски - ✗Считать, что
DENSE_RANKиRANKдают одинаковый вывод
Уточняющие вопросы
- →Какую функцию взять, чтобы выбрать ровно одну строку на группу?
- →Как сделать
ROW_NUMBERдетерминированным, когда ключ сортировки имеет равные значения?
JuniorКодЧастоПоказать зарплату каждого сотрудника рядом со средней по его отделу в той же строке
Показать зарплату каждого сотрудника рядом со средней по его отделу в той же строке
Берут оконный агрегат, а не GROUP BY: AVG(salary) OVER (PARTITION BY department). Партиция ограничивает среднее отделом, но функция возвращает значение для каждой строки, поэтому зарплата стоит рядом со средней.
Типичные ошибки
- ✗Хвататься за
GROUP BY, что сворачивает строки сотрудников - ✗Возвращать одну строку на отдел вместо строки на сотрудника
- ✗Пропускать
PARTITION BY, получая общее среднее вместо среднего по отделу
Уточняющие вопросы
- →Как заодно отметить сотрудников с зарплатой выше средней по отделу?
- →Что вычисляет здесь
OVER ()без партиции?
MiddleКодЧастоИзменение выручки месяц к месяцу через LAG с меткой рост / падение / без изменений
Изменение выручки месяц к месяцу через LAG с меткой рост / падение / без изменений
LAG(revenue) OVER (ORDER BY month) подтягивает прошлый месяц в текущую строку; вычитание даёт изменение. CASE по знаку ставит метку рост, падение или без изменений. У первого месяца LAG равен NULL.
Типичные ошибки
- ✗Использовать
LEAD(следующая строка), когда нуженLAG(предыдущая) - ✗Помечать первый месяц вместо того, чтобы оставить тренд пустым
- ✗Добавлять
PARTITION BY month, что изолирует месяц, иLAGвсегда null
Уточняющие вопросы
- →Как посчитать процентное изменение вместо абсолютного?
- →Что происходит со строкой первого месяца и как её показать?
MiddleКодЧастоВернуть вторую по величине зарплату в каждом отделе
Вернуть вторую по величине зарплату в каждом отделе
Ранжируют по отделу, затем берут ранг 2 снаружи — окно нельзя класть в WHERE. DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) приравнивает равные, поэтому ранг 2 — второе уникальное значение.
Типичные ошибки
- ✗Использовать
ROW_NUMBER, который разделяет равные топ-зарплаты и сдвигает ранг 2 - ✗Применять
LIMIT/OFFSETглобально вместо разбивки по отделу - ✗Забывать, что результат окна фильтруют во внешнем запросе
Уточняющие вопросы
- →Как изменится ответ, если нужен второй сотрудник, включая совпадения?
- →Почему
DENSE_RANKлучшеROW_NUMBER, когда зарплаты могут совпадать?
MiddleКодЧастоВернуть топ-3 продукта по продажам в каждой категории
Вернуть топ-3 продукта по продажам в каждой категории
Агрегируют выручку по продукту, ранжируют внутри категории, снаружи берут топ: ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) с WHERE rn <= 3. GROUP BY ... LIMIT 3 режет глобально, не по категории.
Типичные ошибки
- ✗Думать, что
LIMITограничивает строки по категории, а не глобально - ✗Использовать
HAVING COUNT(*), будто он выбирает верхние строки - ✗Считать, что
RANKвсегда даёт ровно три строки до пропуска
Уточняющие вопросы
- →Как совпадения за третье место меняют число строк у
RANKпротивROW_NUMBER? - →Почему
GROUP BY ... LIMIT 3не решает ограничение по категории?
MiddleКодИногдаВычислить скользящее среднее выручки за 7 дней (по текущий день)
Вычислить скользящее среднее выручки за 7 дней (по текущий день)
Задают явную рамку: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Текущая строка и 6 предыдущих — окно из 7 строк. ROWS считает строки — нужна одна строка на день.
Типичные ошибки
- ✗Считать рамку по умолчанию при
ORDER BYокном в 7 дней, а не нарастающей - ✗Путать
PRECEDINGиFOLLOWINGдля окна по текущий день - ✗Заменять рамку фиксированной недельной корзиной
GROUP BY
Уточняющие вопросы
- →Как изменится ответ, если в таблице отсутствуют некоторые дни?
- →Почему
ROWSпредпочтительнееRANGEдля скользящего среднего с фиксированным числом?
JuniorТеорияРедкоПочему оконную функцию нельзя использовать в WHERE и как отфильтровать по её результату?
Почему оконную функцию нельзя использовать в WHERE и как отфильтровать по её результату?
Оконные функции вычисляются после WHERE, GROUP BY и HAVING, на фазе SELECT, поэтому их результата ещё нет при WHERE. Окно считают в подзапросе или CTE, а затем фильтруют внешний запрос по псевдониму.
Типичные ошибки
- ✗Думать, что
HAVINGфильтрует результат окна без обёртки - ✗Считать, что псевдоним из
SELECTвиден вWHERE - ✗Списывать ограничение на производительность, а не на порядок вычисления
Уточняющие вопросы
- →На каком месте в логическом порядке запроса работают оконные функции?
- →Почему
HAVINGтоже не может отфильтровать оконную функцию?
MiddleДебаггингРедкоLAST_VALUE возвращает зарплату самой строки вместо максимальной по отделу
LAST_VALUE возвращает зарплату самой строки вместо максимальной по отделу
При ORDER BY рамка RANGE по умолчанию кончается на текущей строке, поэтому LAST_VALUE возвращает зарплату самой строки. Расширяют её до UNBOUNDED FOLLOWING или берут MAX(salary) OVER (PARTITION BY department).
Типичные ошибки
- ✗Винить направление сортировки, а не рамку по умолчанию
- ✗Думать, что
PARTITION BYперезапускается на строке, а не на группе - ✗Ожидать, что
LAST_VALUEпо умолчанию сканирует всю партицию
Уточняющие вопросы
- →Почему
FIRST_VALUEздесь работает как ожидается, аLAST_VALUE— нет? - →Когда предпочесть
MAX() OVER (...)вместоLAST_VALUE?
MiddleДебаггингРедкоНарастающий итог по столбцу даты скачет на равных датах — ROWS против RANGE
Нарастающий итог по столбцу даты скачет на равных датах — ROWS против RANGE
При ORDER BY CURRENT ROW рамки RANGE охватывает всех соседей с тем же sale_date, поэтому равная дата даёт итог по всей дате, не по строкам. Исправляют через ROWS и тайбрейкер id.
Типичные ошибки
- ✗Винить дубли строк, а не семантику соседей у
RANGE - ✗Считать, что
ROWSиRANGEведут себя одинаково - ✗Добавлять
PARTITION BY sale_date, что сбрасывает итог по дате
Уточняющие вопросы
- →Как
RANGEопределяет соседа и почему это здесь важно? - →Что гарантирует добавление уникального
idвORDER BY?
