Оконные функции PostgreSQL
Группировка полезна для сводки темы, но после неё отдельные курсы исчезают из результата. Иногда редактору нужна каждая карточка вместе с суммарным планом её темы или местом в упорядоченном списке. Оконная функция вычисляет показатель над связанным набором строк, сохраняя строки исходного уровня.
Рассмотрим PostgreSQL 17.11 и самостоятельный снимок catalog-db/lesson-12 из архива серии. SQL не запускался. Работаем с пятью опубликованными курсами исходного набора, поэтому черновая производительность не участвует в окнах этих запросов. Если изменить условие отбора, изменится и доступный функциям набор.
Показатель рядом с каждой строкой
Добавим сумму плана внутри темы и порядковое место курса:
SELECT id, topic_id, slug, planned_lessons,
sum(planned_lessons) OVER (PARTITION BY topic_id) AS topic_plan,
row_number() OVER (
PARTITION BY topic_id ORDER BY planned_lessons DESC, id
) AS position
FROM catalog.courses
WHERE status = 'published'
ORDER BY topic_id, position;Ожидаются пять строк. Во фронтенде JavaScript имеет план двадцать и место один, HTML — шестнадцать и место два. Рядом с обоими будет сумма тридцать шесть. Публикация получит общий план двадцать две главы, бэкенд — шестнадцать. Сумма повторяется в ответе намеренно: это характеристика группы, прикреплённая к каждой её строке.
PARTITION BY делит доступный набор на части для конкретного окна. Оно не сворачивает строки, как GROUP BY. Две функции в одном запросе могут иметь разные окна. Здесь сумма использует всю тему без порядка, а порядковый номер использует упорядочение внутри темы. Так одно представление содержит и общий размер, и относительное место.
row_number() выдаёт отдельный номер каждой строке. Второй ключ id делает выбор порядка определённым при одинаковом плане. Без него две равные по плану строки могли бы поменяться местами, сохраняя корректность первого критерия. Финальный ORDER BY задаёт порядок ответа; порядок внутри окна описывает вычисление и не заменяет автоматически порядок показа. Основу механизма объясняет введение PostgreSQL в оконные функции.
Равенство и ранги
Для сравнения объёма по всей опубликованной библиотеке рассмотрим rank() и dense_rank():
SELECT slug, planned_lessons,
rank() OVER (ORDER BY planned_lessons DESC) AS rank,
dense_rank() OVER (ORDER BY planned_lessons DESC) AS dense_rank
FROM catalog.courses
WHERE status = 'published'
ORDER BY planned_lessons DESC, id;JavaScript с двадцатью главами получает ранг один. HTML и PostgreSQL с шестнадцатью главами делят ранг два. Следующий Markdown с двенадцатью получает rank = 4, но dense_rank = 3. SEO с десятью получает пять и четыре. Пропуск в обычном ранге отражает два объекта на предыдущем месте; плотный ранг считает разные группы значений.
Здесь специально не добавлен id внутрь определения ранга. Если добавить его, две строки с одинаковым планом перестанут быть равными по всему набору критериев. Финальная сортировка по ключу по-прежнему помогает удобно показывать равные строки, не меняя смысл ранга. Определения этих функций приведены в справочнике PostgreSQL.
Не называйте ранг планируемого числа глав оценкой качества курса. Более длинная серия не обязательно лучше короткой. SQL рассчитывает выбранный показатель точно, однако смысл подписи задаётся предметной задачей. Для редактора такой порядок может помогать видеть объём работ, а для читателя потребуется другая логика рекомендаций.
Кадр накопительного вычисления
Окно имеет не только разделение и порядок, но и кадр: часть упорядоченной секции, которую использует конкретное вычисление. Для накопленного числа опубликованных глав зададим строки явно:
SELECT id, slug, published_at, planned_lessons,
sum(planned_lessons) OVER (
ORDER BY published_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS accumulated
FROM catalog.courses
WHERE status = 'published'
ORDER BY published_at, id;Ожидается последовательность сумм: шестнадцать, тридцать шесть, сорок восемь, шестьдесят четыре, семьдесят четыре. Она следует публикациям HTML, JavaScript, Markdown, PostgreSQL и SEO. У Markdown и PostgreSQL одинаковый момент, но ключ три располагается раньше ключа пять. Явный ROWS включает уже пройденные строки до текущей по выбранному полному порядку.
Если написать только ORDER BY published_at внутри агрегатного окна и оставить кадр по умолчанию, одинаковые моменты станут важны. Типичный кадр по умолчанию включает равные по этому порядку строки до конца текущей группы равенства. У Markdown и PostgreSQL тогда накопленная сумма станет шестьдесят четыре у обоих. Это не ошибка арифметики: изменился договор кадра.
Поэтому перед выбором кадра сформулируйте желаемое наблюдение. «После каждого курса в устойчивом порядке» подходит явному ROWS и ключу. «После всех публикаций в данный момент» может соответствовать обработке равных моментов вместе. Разница отражает предметный вопрос, а не универсальное превосходство одного ключевого слова.
Окно и отбор результата
Оконные выражения применяются к набору после обычного отбора строк. В наших примерах WHERE status = 'published' убирает черновик до вычисления суммы. Если сначала посчитать общий план темы над всеми курсами во вложенном запросе, а потом отобрать опубликованные, у фронтенда получится сорок восемь рядом с двумя публичными курсами. Такой запрос отвечает другому вопросу.
По результату row_number() нельзя фильтровать через WHERE на том же уровне так, будто номер уже существует до вычисления окна. Для выбора первых курсов понадобится внешний запрос или CTE. Этот переход будет раскрыт в следующем уроке; сейчас важно видеть границу уровней, а не пытаться повторить имя псевдонима в любом месте SQL.
Старые агрегатные окна T-SQL учат похожему способу рассуждения о секции, порядке и кадре. Но конкретные возможности и ограничения старого SQL Server 2012 нельзя считать ограничениями PostgreSQL. Например, поддержка FILTER в нашем диалекте реальна, что уже использовалось в групповой сводке.
Повторённый показатель нельзя суммировать снова
В первом оконном результате сумма темы прикреплена к каждой карточке. Если приложение сложит topic_plan по всем возвращённым строкам, оно посчитает одну тему несколько раз. Например, тридцать шесть фронтенда присутствует рядом с двумя опубликованными курсами. Окно сохранило детализацию намеренно; потребитель должен понимать, что показатель повторён для контекста.
Для общего плана всей библиотеки достаточно суммы планов отдельных курсов или отдельного группового результата. DISTINCT по числовому значению темы опять не решит задачу надёжно: две разные темы могут иметь одинаковую сумму. Сначала сохраняется ключ группы, а уже потом её единственный показатель используется на другом уровне.
Оконный номер также не стоит превращать в постоянный ключ объекта. Добавление курса с большим планом изменит относительные места прежних карточек. Их собственные id останутся прежними, что и требуется ссылкам приложения. Номер отражает текущий набор и критерий сравнения, а не историю создания.
Уменьшение ответа до нескольких строк после расчёта окна и уменьшение входа перед ним дадут разные сравнительные показатели. Если интерфейс показывает место курса во всей теме, окно должно видеть нужную тему до окончательного ограничения карточек. Если нужен порядок только выбранного поднабора, это другой явно сформулированный сценарий. Разница похожа на WHERE и HAVING из групповой главы, но уровень вычисления теперь оконный.
При будущем ручном чтении сначала предскажите числа на двух одинаковых моментах публикации, затем сравните два определения окна в снимке. Если вы способны объяснить сорок восемь и шестьдесят четыре без ссылки на случайный вывод клиента, механизм понятен. Окна позволяют добавить сравнительный контекст к каждой карточке, сохранив уровень курса и явно определив, какие строки участвуют в расчёте.