Оконные функции SQL: как получить верхнюю строку для каждой группы

1. Что такое оконная функция в одном запросе

Перед пояснением: вы пишете запрос на 175 строк и добавляете COUNT(*) OVER () в список выбора. Сколько строк возвращается?

Все 175, каждый показывает число 175. В этом вся идея оконной функции в одном результате. Он что-то вычислял по строкам, не удаляя ни одну из них.

SELECT Name,
       PctPositive,
       COUNT(*) OVER () AS total_gems
FROM gems;

OVER это слово, которое делает его оконной функцией. В нем говорится: «Проведите этот расчет по набору строк и поместите ответ в каждую строку». Скобки после OVER опишите какие строки. Пустые скобки означают все из них.

Сравните это с версией без OVER. SELECT COUNT(*) FROM gems возвращает одну строку, содержащую 175, а имена исчезают. Слово «окно» — это картинка, за которую нужно держаться. Каждая строка может просматривать набор других строк и сообщать что-то о них, при этом просмотр ничего не требует.

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

OVER (
    PARTITION BY PrimaryGenre   -- which rows are in this row's window
    ORDER BY PctPositive DESC   -- in what order to consider them
)                               -- (a frame can narrow it further, section 6)

PARTITION BY разбивает строки на отдельные окна, по одному на каждое значение. Это та же идея, что и GROUP BY, с одним отличием, которое имеет большее значение, чем любое другое: оно ничего не разрушает.

2. Верхний ряд в каждой группе

Перед объяснением: SELECT PrimaryGenre, MAX(PctPositive) FROM gems GROUP BY PrimaryGenre дает вам лучший рейтинг в каждом жанре. Что мешает вам добавить Name в этот список выбора, чтобы узнать, какая это была игра?

Ссора закончилась. MAX вернул число, а не строку, и на его создание пошло 75 приключенческих игр. Нет ни одного Name осталось для базы данных, чтобы передать вам. Большинство движков полностью отклоняют запрос, а те, которые его допускают, возвращают произвольное имя, что еще хуже, поскольку оно выглядит как ответ.

Оконные функции решают эту проблему в два хода. Сначала пронумеруйте ряды, а затем оставьте те, которые имеют номер 1.

WITH ranked AS (
    SELECT Name,
           PrimaryGenre,
           PctPositive,
           TotalReviews,
           ROW_NUMBER() OVER (
               PARTITION BY PrimaryGenre
               ORDER BY PctPositive DESC, TotalReviews DESC
           ) AS rn
    FROM gems
)
SELECT PrimaryGenre, Name, PctPositive, TotalReviews
FROM ranked
WHERE rn = 1
ORDER BY PctPositive DESC;

Прочтите OVER скобки вслух как предложение: в каждом жанре расставьте игры по наивысшему рейтингу и пронумеруйте их, начиная с единицы. Каждой из 175 строк присвоен номер. Лучшая приключенческая игра — 1, вторая лучшая — 2, а лучшая экшн-игра — тоже 1, потому что PARTITION BY возобновляет подсчет для каждого жанра.

Вот реальный результат.

тот WHERE rn = 1 должен сидеть снаружи, на втором этапе, и стоит знать причину, потому что это самая распространенная ошибка, с которой здесь сталкиваются. Оконные функции выполняются после WHERE, поэтому столбец rn еще не существует, когда WHERE оценивается. Письмо WHERE ROW_NUMBER() OVER (...) = 1 это ошибка в каждом двигателе. Оберните запрос в CTE, а затем отфильтруйте CTE. именованные шаги вот как вы получаете второе место для фильтрации.

Скажи вслух, почему ORDER BY PctPositive DESC LIMIT 1 не смог бы создать эту таблицу, пока вы не прочитали дальше. Ответ в том, что LIMIT сокращает весь результат и дает вам одну игру в целом, а не по одной игре на каждый жанр. PARTITION BY это LIMIT который перезапускается для каждого значения, и это то, что обычный SQL не имеет другого способа выразить.

Измените одно слово, и тот же запрос ответит на другой вопрос. WHERE rn <= 3 дает тройку лучших по жанру. Эта форма, число внутри и фильтрация снаружи, охватывает большую часть того, для чего людям действительно нужны оконные функции.

Представьте, что вы запускаете это на столе, с которым работаете. Разделите по клиентам, упорядочите по дате и сохраните rn = 1. Что вы только что построили и сколько строк вернулось?

3. Ничьи и три функции ранжирования

Перед пояснением: обе инди-игры имеют рейтинг ровно 96,6%. Если пронумеровать ряды, в каком из них получится 7, а в каком — 8?

Никто не знает, и это честный ответ, а не уловка. ROW_NUMBER должен выдать разные числа, поэтому при ничьей он выбирает одно произвольно. Запустите его дважды, и вы сможете получить другого победителя. Это первое, что нужно понимать о связях, и причина существования двух других функций ранжирования.

Все три числовых ряда. Они отличаются только тем, как обрабатывают равные значения.

SELECT Name,
       PctPositive,
       ROW_NUMBER()  OVER (ORDER BY PctPositive DESC) AS rn,
       RANK()        OVER (ORDER BY PctPositive DESC) AS rk,
       DENSE_RANK()  OVER (ORDER BY PctPositive DESC) AS dr
FROM gems
WHERE PrimaryGenre = 'Indie'
ORDER BY PctPositive DESC;

Интересная часть этого результата — четыре ряда вокруг галстука.

Три поведения, и таблица — все объяснение. ROW_NUMBER никогда не повторяется и никогда не пропускает, поэтому это разделяет ничью. RANK дает связанным строкам одинаковое количество, а затем пропускает, поэтому 8 отсутствует. DENSE_RANK дает связанным строкам одинаковое количество и не пропускает, поэтому учитываются отдельные значения, а не строки.

Выбор между ними – это не вопрос стиля, это зависит от того, для чего нужен номер.

Одна привычка убирает произвол из ROW_NUMBER навсегда. Добавьте тай-брейк в ORDER BY, поэтому порядок полностью определяется данными. Поэтому использован раздел 2. ORDER BY PctPositive DESC, TotalReviews DESC. Когда две игры имеют одинаковый рейтинг, побеждает та, у которой больше обзоров, и запрос возвращает один и тот же ответ при каждом запуске. Запрос, результат которого меняется между запусками, — это ошибка, которая пока не замечена.

4. Текущая сумма и изменение по сравнению с прошлым годом.

Перед объяснением: добавьте ORDER BY ReleaseYear внутри OVER скобки а SUM. Изменяет ли это сумму или только порядок ее отображения?

Это меняет сумму, и это самая удивительная вещь в окнах. Без ORDER BY, окно представляет собой весь раздел, и в каждой строке отображается одно и то же общее количество. Добавление ORDER BY заставляет окно расти по мере продвижения: окно каждой строки - это все, вплоть до этой строки. Сумма становится промежуточной.

SELECT ReleaseYear,
       COUNT(*) AS gems_that_year,
       SUM(COUNT(*)) OVER (ORDER BY ReleaseYear) AS running_total,
       COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY ReleaseYear) AS change_vs_prior
FROM gems
GROUP BY ReleaseYear
ORDER BY ReleaseYear;

Вот последние семь лет этого результата.

Промежуточная сумма, достигающая ровно 175 в последней строке, — это бесплатная проверка вашей работы. Если окончательное значение промежуточной суммы не соответствует простому COUNT стола, окно не закрывает то, что вы думаете.

LAG — это другая функция в этом запросе, и она делает одну небольшую вещь. Он возвращается к предыдущей строке в том же окне и возвращает из нее значение. LAG(x) на один ряд назад, LAG(x, 3) на три ряда назад, и LEAD(x) вместо этого тянется вперед. Вычтите и получите изменение. За первым рядом ничего нет, поэтому LAG возвращает NULL там, и вычитание дает NULL а не ноль. Использовать LAG(x, 1, 0) если вы хотите вместо этого использовать значение по умолчанию.

Этот запрос также показывает то, что люди с первого раза находят странным, и это намеренно законно. SUM(COUNT(*)) является агрегатом внутри оконной функции. Это работает, потому что они запускаются в разное время. COUNT(*) сначала сжимает строки в одну по году, а затем оконная функция обрабатывает эти годовые строки. Вы просматриваете сводку, а не необработанную таблицу.

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

5. Полное до и после

Тот же вопрос: какая игра имеет лучший рейтинг в каждом жанре и как каждый жанр соотносится с остальным списком?

До

SELECT PrimaryGenre,
       MAX(PctPositive) AS best_rating,
       COUNT(*) AS games
FROM gems
GROUP BY PrimaryGenre
ORDER BY best_rating DESC;

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

после

-- ============================================================
-- STEP 7: The best rated game in each genre, in context.
-- WHY: MAX() gives the best rating but destroys the row it came
--      from, so the game's name cannot come with it. Numbering
--      the rows keeps every row and marks the winner instead.
-- CHECK: rn = 1 must return one row per genre. Nine genres in,
--      nine rows out. The tiebreaker on TotalReviews makes that
--      result stable between runs.
-- ============================================================
WITH ranked AS (
    --Number the games within each genre, best rated first.
    --  TotalReviews breaks ties so the order is fully decided.
    SELECT Name,
           PrimaryGenre,
           PctPositive,
           TotalReviews,
           ROW_NUMBER() OVER (
               PARTITION BY PrimaryGenre
               ORDER BY PctPositive DESC, TotalReviews DESC
           ) AS rn,
           --How many gems the genre has, on every row of it
           COUNT(*) OVER (PARTITION BY PrimaryGenre) AS genre_size,
           --The genre's average, sitting next to the game's own
           AVG(PctPositive) OVER (PARTITION BY PrimaryGenre) AS genre_avg
    FROM gems
)
--Keep the winner of each genre. The context columns came along.
SELECT PrimaryGenre,
       Name,
       PctPositive,
       genre_size,
       ROUND(genre_avg, 1) AS genre_avg
FROM ranked
WHERE rn = 1
ORDER BY PctPositive DESC;

Три оконные функции используют один раздел, и ни одна из них не требует дополнительного прохода по таблице или дополнительного соединения. Название сохраняется, рейтинг игры сохраняется, и сравнение с ее жанром оказывается в том же ряду. В Приключение входит 75 из 175 игр, а в Моделирование — 2, что меняет ценность любого из победителей этого жанра. Формат комментариев тот же, что и у Как комментировать SQL, чтобы он обучал.

6. Крайние случаи и честные ограничения

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

Шесть вещей, которые стоит знать.

Связи в ORDER BY вытяните будущие ряды в окно. Это ответ на предварительный вопрос, и это самая тонкая ошибка на этой странице. По умолчанию окно с ORDER BY охватывает все, от начала раздела до последней строки, связанной с текущей. Две строки относятся к одной и той же дате, поэтому обе показывают итоговую сумму, включая обе. Исправить это можно одной строкой: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW подсчитывает строки, а не связанные значения. Эта линия называется рамкой и представляет собой третью часть OVER скобки.

Вы не можете фильтровать функцию окна в WHERE. Окна рассчитываются после WHERE и после GROUP BY, поэтому столбец еще не существует. Оберните его в CTE и отфильтруйте снаружи. В сообщении об ошибке обычно указывается название функции, а не поясняется порядок ее выполнения, поэтому людям приходится тратить на это час.

PARTITION BY не GROUP BY, и их смешивание показывает. Группировка меняет количество строк, которые вы получаете обратно. Разделение никогда не происходит. Если количество строк изменилось, GROUP BY сделал это.

NULL В ORDER BY сортируется по-разному в зависимости от движка. PostgreSQL помещает NULL последним в порядке возрастания, SQLite и MySQL ставят его первым. Если раздел может содержать NULL в столбце, в котором вы занимаете место, скажите, что вы хотите, с помощью NULLS FIRST или NULLS LAST там, где ваш движок это поддерживает, вместо того, чтобы принимать значение по умолчанию и удивляться следующему движку.

Окно не может видеть строки, которые WHERE уже удалил. Фильтровать по одному жанру и COUNT(*) OVER () учитывается только этот жанр. Обычно это то, чего вы хотите. Если это не так, сначала выполните оконную обработку в CTE, а затем отфильтруйте.

Сам список имеет ограничение по данным, которое вам покажет рейтинг. Портал 2 находится в этом списке с 155 440 отзывами и оценкой владельцев от 0 до 20 000, что не может быть правдой. Уровни владельцев основаны на приблизительной оценке, и она неверна для некоторых старых популярных игр. Ранжирование позволяет легко обнаружить ошибки такого рода, поскольку неправдоподобная строка поднимается наверх раздела, где ее невозможно пропустить. Сортировка ваших данных — один из самых дешевых способов обнаружить, что они грязные.