Информатика · объяснение и практика
Почему оконная функция сохраняет строки, а агрегат собирает их
Сохраняем каждую работу ученика рядом с итогом его группы, сравниваем агрегат и оконную сумму и явно задаём рамку накопления.

В теме Таблицы, базы данных и SQL · Учебная карта
Главная мысль
Оконная функция вычисляет показатель для связанных строк, сохраняя отдельные строки результата. Обычная группировка сворачивает группу в итоговую строку. Ключевые вопросы окна — состав раздела, порядок внутри него и рамка строк, участвующих в вычислении.
Итог рядом с каждой работой
В журнале три работы Лены с баллами 4,6,5 и две работы Максима с баллами 8,2. Обычная сумма с GROUP BY по ученику выдаст две строки: Лена — 15, Максим — 10. Идентичность отдельных работ при таком выводе не сохраняется, если она не входит в другую детализацию.
Оконная сумма SUM(score) OVER (PARTITION BY pupil_id) добавит к каждой работе сумму её ученика. В строках Лены рядом с 4,6,5 будет стоять 15, в обеих строках Максима — 10. Без дополнительных отборов или DISTINCT останутся пять строк, а не две.
Пример
Работы Лены: (4,15), (6,15), (5,15). Первый элемент — балл работы, второй — итог ученика.
Суммировать второй столбец по всем строкам нельзя как общий результат учеников: сумма каждого ученика уже повторена на каждой его работе.
Порядок и рамка меняют смысл суммы
Для накопительного итога нужен порядок работ, например по времени сдачи и уникальному идентификатору. С рамкой ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW баллы Лены 4,6,5 дадут последовательность итогов 4,10,15. Каждая строка включает все предыдущие строки раздела и саму себя.
Если время сдачи совпало у двух работ, один только порядок по времени не определит, какая из них раньше. Дополнительный уникальный ключ убирает неоднозначность. Кроме того, рамка по умолчанию в присутствии ORDER BY может учитывать равных соседей иначе, чем явная ROWS. Поэтому рамку и порядок лучше задавать в соответствии с тем результатом, который обещан пользователю.
Номер строки не заменяет устойчивый порядок
ROW_NUMBER присваивает номера по порядку внутри окна. Но сам оконный ORDER BY не обещает такой же порядок всего вывода: итоговый SELECT сортируют отдельно. Для выбора последней работы удобно сначала посчитать номер в обратном порядке в подзапросе, а затем во внешнем запросе оставить номер один.
После JOIN оконная функция видит уже соединённые строки. Если соединение размножило одну работу, оконная сумма тоже посчитает её несколько раз. Окно не исправляет ошибки исходной детализации. На маленьком наборе проверьте число строк до и после расчёта, одинаковые даты, несколько учеников и ученика с одной работой. Это различает ошибку раздела, неоднозначный порядок и неправильную рамку.
От чтения к действию
А теперь — вы
Частые вопросы
Можно ли вывести процент балла работы от суммы ученика?
Да: оконная сумма даёт знаменатель для каждой строки. Нужно отдельно обработать нулевую сумму и выбрать вещественное деление вместо случайного целочисленного.
PARTITION BY сортирует строки по ученикам?
Оно определяет независимые разделы вычисления, но не гарантирует порядок вывода. Для отображения нужен отдельный ORDER BY результата.
Источники и работа с материалом
Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.
Продолжить разбираться
Как WHERE и HAVING фильтруют разные этапы группировки
Отбираем завершённые работы и кружки с достаточной суммой баллов, разделяя фильтр отдельных записей WHERE и фильтр готовых групп HAVING.
Открыть темуКак соединение таблиц размножает строки и меняет сумму
Соединяем школьные заказы с позициями и метками, считаем получившиеся строки вручную и устраняем повтор суммы без опасного SUM DISTINCT.
Открыть тему