Подсчет итогов в SQL: оконный фрейм, который определяет ответ

1. Однострочный промежуточный итог

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

2026-01-01 | 2630
2026-02-01 | 2245
2026-03-01 | 2585
2026-05-01 | 2430

Теперь накопительный столбец.

SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM by_month
ORDER BY month;

2026-01-01 | 2630 | 2630
2026-02-01 | 2245 | 4875
2026-03-01 | 2585 | 7460
2026-05-01 | 2430 | 9890

Проверьте вручную: 2630 плюс 2245 — это 4875, плюс 2585 — это 7460, плюс 2430 — это 9890, что составляет весь доход таблицы. Это последнее равенство не является украшением; это испытание, и седьмой раздел делает его привычкой.

Прочтите этот пункт как предложение: суммируйте доход по строкам, упорядоченным по месяцам, с самого начала до этой строки включительно. Вот что UNBOUNDED PRECEDING и CURRENT ROW имею в виду, и как только вы сможете произнести это вслух, синтаксис перестанет нуждаться в поиске.

2. Что на самом деле представляет собой фрейм

До пояснения: нормальный SUM с GROUP BY сворачивает строки. Этот сохраняет все четыре. Скажите, откуда взялось дополнительное число.

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

Три части по порядку, и все три необязательны, вот отсюда и беда.

Остальная часть страницы — это те три, по одному. Если вы хотите более широкий тур о том, что еще OVER можно сделать, ранжирование и LAG и остальное, руководство по функциям окна это место.

3. ORDER BY — вот что заставляет его работать

Перед объяснением: удалите ORDER BY изнутри OVER. Предскажите четыре числа.

SELECT month, revenue, SUM(revenue) OVER () AS whole_thing
FROM by_month ORDER BY month;

2026-01-01 | 2630 | 9890
2026-02-01 | 2245 | 9890
2026-03-01 | 2585 | 9890
2026-05-01 | 2430 | 9890

Общий итог, повторенный четыре раза. Без ORDER BY здесь нет последовательности, поэтому нет «пока», и кадр каждой строки представляет собой весь раздел. Это действительно полезная вещь: именно так вы ставите знаменатель рядом с каждой строкой, и это не промежуточный итог.

Обратите внимание также на ORDER BY в конце утверждения совсем другое. Он сортирует вывод. Тот, что внутри OVER определяет порядок накопления. Часто это один и тот же столбец, и это не обязательно, и запрос, который сортирует выходные данные по одному столбцу и накапливает данные по другому, создаст накопительный столбец, который кажется скачущим.

4. Связи и фрейм по умолчанию, объединяющий их

Это раздел, который оправдывает страницу. Упорядочивайте по доходам, а не по месяцам, чтобы существовали связи: два заказа стоят 440, два — 660 и два — 880. Вычислите промежуточную сумму дважды: один раз с фреймом по умолчанию и один раз с ROWS.

order_id | revenue | default | ROWS
    1004 |     240 |     240 |  240
    1009 |     280 |     520 |  520
    1007 |     425 |     945 |  945
    1011 |     440 |    1825 | 1385
    1016 |     440 |    1825 | 1825
    1008 |     480 |    2305 | 2305
    1014 |     510 |    2815 | 2815
    1013 |     600 |    3415 | 3415
    1003 |     660 |    4735 | 4075
    1005 |     660 |    4735 | 4735
    1006 |     680 |    5415 | 5415
    1012 |     765 |    6180 | 6180
    1002 |     850 |    7030 | 7030
    1001 |     880 |    8790 | 7910
    1015 |     880 |    8790 | 8790
    1010 |    1100 |    9890 | 9890

Посмотрите на две строки 440. По умолчанию для обоих указано 1825. ROWS говорит 1385, а затем 1825. То же самое касается двух 660-х и двух 880-х: шесть из шестнадцати строк не совпадают.

Это тоже не ошибка. Когда ты пишешь ORDER BY в окне и оставьте рамку вне поля, стандартное значение по умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, и RANGE Работает над Значения а не позиции: текущая строка означает каждую строку с тем же значением порядка, что и эта. Таким образом, оба числа 440 находятся внутри кадра друг друга, и оба получают сумму, включая оба. ROWS работает с позициями, поэтому текущая строка означает буквально эту строку и не далее.

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

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

5. Отдельный промежуточный итог на группу.

PARTITION BY перезапускает накопление при каждом изменении столбца раздела.

SELECT region, order_date, units*unit_price AS revenue,
       SUM(units*unit_price) OVER (PARTITION BY region ORDER BY order_date
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders ORDER BY region, order_date;

East  | 2026-01-19 |  660 |  660
East  | 2026-02-09 |  680 | 1340
East  | 2026-03-09 | 1100 | 2440
East  | 2026-05-04 |  600 | 3040
North | 2026-01-05 |  880 |  880
North | 2026-01-26 |  240 | 1120
North | 2026-02-16 |  425 | 1545
North | 2026-03-16 |  440 | 1985
North | 2026-05-11 |  510 | 2495
...

Каждый регион начинает заново со своего первого порядка, и последнее текущее значение каждого региона равно общему значению этого региона: Восток 3040, Север 2495, Юг 2670, Запад 1685. Эти четыре в сумме дают 9890, поэтому разделы между ними составляют каждую строку.

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

6. Скользящая рамка: скользящие средние

Измените место начала кадра, и он перестанет накапливаться и начнет скользить. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW окно состоит из трех строк: этой строки и двух перед ней.

Выполните ежемесячный ряд с календарем, чтобы пустой апрель был настоящим нулем, а не отсутствующей строкой:

month      | revenue |    ma3 | running
2026-01-01 |    2630 | 2630.00 |    2630
2026-02-01 |    2245 | 2437.50 |    4875
2026-03-01 |    2585 | 2486.67 |    7460
2026-04-01 |       0 | 1610.00 |    7460
2026-05-01 |    2430 | 1671.67 |    9890

Проверьте первый: три месяца, заканчивающиеся в марте, составляют 2630, 2245 и 2585, что в среднем составляет 2486,67. Обратите внимание на первые две строки: рамка не может выйти за пределы начала, поэтому среднее значение января превышает одну строку, а среднее значение февраля — более двух. Обычно это нормально, но иногда вводит в заблуждение, и если это имеет значение, вы подавляете первые строки, а не позволяете среднему значению одной строки выглядеть как трехмесячное.

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

Еще два кадра, которые стоит знать поименно. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING накапливается отсюда до конца, что дает столбец остатка. И ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING центрирует окно по текущей строке, что, честно говоря, более плавно, когда вы не делаете прогнозы.

7. Проверка, которая ловит всё

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

last running value | grand total
              9890 | 9890

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

Запишите это как запрос, а не делайте это на глаз, потому что «последнее число выглядит правильно» не является проверкой:

SELECT (SELECT MAX(running) FROM r) AS last_running,
       (SELECT SUM(units*unit_price) FROM orders) AS grand_total;

Представьте себе сводный отчет, который вы отправляете чаще всего. Есть ли в нем что-нибудь, что могло бы подсказать вам, если бы его последняя строка и его итоговая сумма перестали совпадать?

8. Текущий подсчет и текущая доля

Одна и та же форма работает со всем, что можно агрегировать, и два варианта занимают свое место практически в любом сводном отчете.

month      | orders | running_orders | pct_of_year
2026-01-01 |      4 |              4 |        26.6
2026-02-01 |      4 |              8 |        49.3
2026-03-01 |      4 |             12 |        75.4
2026-05-01 |      4 |             16 |       100.0

Текущий счетчик отвечает на вопрос «сколько уже» и заканчивается на 16, что соответствует количеству строк в таблице. Столбец «Доля» делит промежуточную сумму на общую сумму, и люди на самом деле его читают, потому что 75,4% годового дохода к марту — это предложение, а 7460 — число.

Обратите внимание, как была построена доля: оконная промежуточная сумма, разделенная на окно без ORDER BY, что из третьего раздела представляет собой сумму всего раздела. Два окна в одном выражении, одно накапливающее, другое нет.

ROUND(100.0 * SUM(revenue) OVER (ORDER BY month
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
      / SUM(revenue) OVER (), 1)

И последняя строка должна быть 100,0. Если это не так, то проверка седьмого раздела не удалась под другим видом.

Пограничные случаи, которые нарушают промежуточный итог

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

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

NULL в столбце заказа. Они сортируются в один конец, и какой конец зависит от двигателя. Строка с отсутствующей датой может оказаться в начале накопительного столбца, не неся ничего, или в конце, не содержащая лота.

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

Отсутствующий период. Рамка окна считает ряды, поэтому месяц без рядов — это не ровный шаг, его просто нет, и 2 PRECEDING возвращается на три месяца вместо двух. Сначала создайте календарь.

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

Фильтрация по столбцу окна. WHERE running > 5000 не работает, поскольку оконные функции вычисляются после WHERE. Оберните запрос в CTE и отфильтруйте его вне него, что и является шаблоном. именованные шаги существовать для.