Что значит нарастающий итог
Нарастающий итог в SQL
Нарастающий (накопительный) итог долго считался одним из вызовов SQL. Что удивительно, даже после появления оконных функций он продолжает быть пугалом (во всяком случае, для новичков). Сегодня мы рассмотрим механику 10 самых интересных решений этой задачи – от оконных функций до весьма специфических хаков.
В электронных таблицах вроде Excel нарастающий итог вычисляется очень просто: результат в первой записи совпадает с её значением:
… а затем мы суммируем текущее значение и предыдущий итог.
Появление в таблице двух и более групп несколько усложняет задачу: теперь мы считаем несколько итогов (для каждой группы отдельно). Впрочем, и здесь решение лежит на поверхности: необходимо каждый раз проверять, к какой группе принадлежит текущая запись. Click and drag, и работа выполнена:
Как можно заметить, подсчёт нарастающего итога связан с двумя неизменными составляющими:
(а) сортировкой данных по дате и
(б) обращением к предыдущей строке.
Но что SQL? Очень долго в нём не было нужного функционала. Необходимый инструмент – оконные функции – впервые появился только стандарте SQL:2003. К этому моменту они уже были в Oracle (версия 8i). А вот реализация в других СУБД задержалась на 5-10 лет: SQL Server 2012, MySQL 8.0.2 (2018 год), MariaDB 10.2.0 (2017 год), PostgreSQL 8.4 (2009 год), DB2 9 для z/OS (2007 год), и даже SQLite 3.25 (2018 год).
1. Оконные функции
Оконные функции – вероятно, самый простой способ. В базовом случае (таблица без групп) мы рассматриваем данные, отсортированные по дате:
… но нас интересуют только строки до текущей:
В конечном итоге, нам нужна сумма с этими параметрами:
А полный запрос будет выглядеть так:
В случае нарастающего итога по группам (поле grp ) нам требуется только одна небольшая правка. Теперь мы рассматриваем данные как разделённые на «окна» по признаку группы:
Чтобы учесть это разделение необходимо использовать ключевое слово partition by :
И, соответственно, считать сумму по этим окнам:
Тогда весь запрос преобразуется таким образом:
Производительность оконных функций будет зависеть от специфики вашей СУБД (и её версии!), размеров таблицы, и наличия индексов. Но в большинстве случаев этот метод будет самым эффективным. Тем не менее, оконные функции недоступны в старых версиях СУБД (которые ещё в ходу). Кроме того, их нет в таких СУБД как Microsoft Access и SAP/Sybase ASE. Если необходимо вендоро-независимое решение, следует обратить внимание на альтернативы.
2. Подзапрос
Как было сказано выше, оконные функции были очень поздно введены в основных СУБД. Эта задержка не должна удивлять: в реляционной теории данные не упорядочены. Куда больше духу реляционной теории соответствует решение через подзапрос.
Такой подзапрос должен считать сумму значений с датой до текущей (и включая текущую): 
.
Что в коде выглядит так:
Чуть более эффективным будет решение, в котором подзапрос считает итог до текущей даты (но не включая её), а затем суммирует его со значением в строке:
В случае нарастающего итога по нескольким группам нам необходимо использовать коррелированный подзапрос:
Условие g.grp = t2.grp проверяет строки на вхождение в группу (что, в принципе, сходно с работой partition by grp в оконных функциях).
3. Внутреннее соединение
Поскольку подзапросы и джойны взаимозаменяемы, мы легко можем заменить одно на другое. Для этого необходимо использовать Self Join, соединив два экземпляра одной и той же таблицы:
Точно также можно сделать для случая с разными группами grp :
4. Декартово произведение
Раз уж мы заменили подзапрос на join, то почему бы не попробовать декартово произведение? Это решение потребует только минимальных правок:
Или для случая с группами:
Перечисленные решения (подзапрос, inner join, cartesian join) соответсвуют SQL-92 и SQL:1999, а потому будут доступны практически в любой СУБД. Основная проблема всех этих решений в низкой производительности. Это не велика беда, если мы материализуем таблицу с результатом (но ведь всё равно хочется большей скорости!). Дальнейшие методы куда более эффективны (с поправкой на уже указанные специфику конкретных СУБД и их версий, размер таблицы, индексы).
5. Рекурсивный запрос
Один из более специфических подходов – это рекурсивный запрос в common table expression. Для этого нам необходим «якорь» – запрос, возвращающий самую первую строку:
Часть кода, добавляющая один день, не универсальна. Например, это r.dt = dateadd(day, 1, cte.dt) для SQL Server, r.dt = cte.dt + 1 для Oracle, и т.д.
Совместив «якорь» и основной запрос, мы получим окончательный результат:
Решение для случая с группами будет ненамного сложнее:
6. Рекурсивный запрос с функцией row_number()
Итак, для рекурсивного запроса с row_number() нам понадобится два СТЕ. В первом мы только нумеруем строки:
… и если номер строки уже есть в таблице, то можно без него обойтись. В следующем запросе обращаемся уже к cte1 :
А целиком запрос выглядит так:
… или для случая с группами:
7. Оператор CROSS APPLY / LATERAL
Один из самых экзотических способов расчёта нарастающего итога – это использование оператора CROSS APPLY (SQL Server, Oracle) или эквивалентного ему LATERAL (MySQL, PostgreSQL). Эти операторы появились довольно поздно (например, в Oracle только с версии 12c). А в некоторых СУБД (например, MariaDB) их и вовсе нет. Поэтому это решение представляет чисто эстетический интерес.
Функционально использование CROSS APPLY или LATERAL идентично подзапросу: мы присоединяем к основному запросу результат вычисления:
… что целиком выглядит так:
Похожим будет и решение для случая с группами:
Итого: мы рассмотрели основные платформо-независимые решения. Но остаются решения, специфичные для конкретных СУБД! Поскольку здесь возможно очень много вариантов, остановимся на нескольких наиболее интересных.
8. Оператор MODEL (Oracle)
Оператор MODEL в Oracle даёт одно из самых элегантных решений. В начале статьи мы рассмотрели общую формулу нарастающего итога:
MODEL позволяет реализовать эту формулу буквально один к одному! Для этого мы сначала заполняем поле total значениями текущей строки
Функция cv() здесь отвечает за значение текущей строки. А весь запрос будет выглядеть так:
9. Курсор (SQL Server)
Нарастающий итог – один из немногих случаев, когда курсор в SQL Server не только полезен, но и предпочтителен другим решениям (как минимум до версии 2012, где появились оконные функции).
Реализация через курсор довольно тривиальна. Сначала необходимо создать временную таблицу и заполнить её датами и значениями из основной:
Затем задаём локальные переменные, через которые будет происходить обновление:
После этого обновляем временную таблицу через курсор:
И, наконец, получем нужный результат:
10. Обновление через локальную переменную (SQL Server)
Обновление через локальную переменную в SQL Server основано на недокументированном поведении, поэтому его нельзя считать надёжным. Тем не менее, это едва ли не самое быстрое решение, и этим оно интересно.
Создадим две переменные: одну для нарастающих итогов и табличную переменную:
Сначала заполним @tv данным из основной таблицы
Затем табличную переменную @tv обновим, используя @VarTotal :
… после чего получим окончательный результат:
Резюме: мы рассмотрели топ 10 способов расчёта нарастающего итога в SQL. Как можно заметить, даже без оконных функций эта задача вполне решаема, причём механику решения нельзя назвать сложной.
Ваш браузер устарел, пожалуйста обновите ваш браузер пройдя по ссылке www.microsoft.com/download
Известно, что запросы, простым способом не позволяют нумеровать строки и создавать нарастающие итоги. В запросах это возможно сделать только при соединении таблицы с самой собой достаточно хитрым способом, определенным образом упорядочивая, группируя и в соединения указывать неравенство.
Будет полезна изучающим запросы.
Ссылки при написании:
Данная обработка показывает по шагам как нужно создавать в запросе нарастающий итог. В обработке реализовано два примера.
Нумерация строк в запросе как яркий пример нарастающего итога. В качестве колонки_выбора выбран «код».
Реализован нарастающий итог по колонке «сумма документа». В качестве колонки_выбора выбран «номер».
Примечание1. Во всех примерах в условиях отбора в запросе используется «пометка на удаление».
Последовательность действия при получении в запросе «НарастающихИтогов».
Перед написание запроса необходимо определить колонку по которой хотим получить нарастающий итог.
Если мы хотим получить нумерацию строк в результате запроса то выибирать можно любую колонку.
По выбранной_колонке будем сортировать, соединять и использовать при группировки в нашем итоговом запросе.
Шаг1. Составляем запрос в который должны попасть то что мы хотим видеть.
Шаг2. Дополнем наш запрос из Шаг1. его точной копией.
Шаг3. Соединяем таблицу и таблицу_копию по выбранной_колонке и по ней же упорядочиваем.
Шаг4. Группируем наш запрос так чтобы в суммируемые колонки попала выбранная_колонка
Шаг5. Меняем условие соединения таблиц на больше_равно
Готово. Смотрим результат. Вот.
Примечание2. Последовательность «шагов» может быть любая. Так данное описание составление запросов с нарастающим итогом условное.
Примечание3. Быстродействие данных запросов ресурсоемко. Для большинства повседневных задач их использование вполне приемлемо.
Скачать файлы
Специальные предложения
гыы. все ссылки уже помечены как прочтенные (:
Шепот, на самом деле показан очень конкретный пример. Вместо больше равно может быть и меньше равно. в зависимости от целей.
Но для тех кто изучает сложное на примерах (как я например (% ) зер гуд.
З.Ы. Предлагаю название изменить. Двухпальцевые нарастающие итоги и нумерация строк в запросе. Всё-таки если быть последовательным и логичным, то «понимаешь» ты в статьях, а вот обработки у тебя «двухпальцевые». Это без иронии.
. БОЛЬШОЕ видится на расстоянии, а начинается с МАЛОГО …
Метод построения нарастающих итогов в таблице и диаграмме в СКД 8.2
1.Типичным решением этой проблемы является левое соединение таблицы с самой собой. В группировки включается период исходной таблицы по которому строится итог с накоплением. В условие соединения входит выражение вида Исх.ПериодДень >= Соед.ПериодДень. Собственно итоговое поле в запросе вычисляется агрегатной функцией СУММА(Соед.Ресурс) в присоединенной таблице по полю ресурса.
Этот подход хорош для специализиированных отчетов, в которых заранее известно, какое поле периода будет использовано в группировке.
2. Другим широко применяемым подходом является использование функции СКД “ВычислитьВыражение” в вычислимых полях.
Вычислимое поле настраивается ресурсом. Для правильного расчета в группировках и итогах в выражении ресурса должна быть использована функция Максимум()
Этот подход прекрасно работает в таблицах по любым вертикально расположенным группировкам, но совершенно не работает в диаграммах. Диаграммы почему то совершенно не хотят вычислять функцию “ВычислитьВыражение”.Вместо значений с накоплением в диаграмме отображаются значения без накопления.
В горизонтально расположенных группировках таблиц – нарастающий итог тоже не рассчитывается… но это еще предмет будущего изучения.
Решении которое предлагаю ниже – отталкивается от второго подхода. Нарастающие итоги создаются созданием ресурса на основе вычисляемого поля с функцие СКД “ВыполнитьВыражение”. Единственное дополнительное условие которое вводится – ограничение на имя “накопительного” ресурса – в нем должно быть в любом регистре подстрока “нараст”. Зачем это нужно – увидите далее.
Теперь надо чтобы при выборе такого ресурса в диагрумму значения считались с накоплением. Идея проста – накапливать значение для каждого ресурса в момент когда выводится точка диаграммы и подменять значение в диаграмме на “накопленное”.Для этого используется обработчик события “ПередВыводомЭлементРезультата” в модуле объекта отчета.
В обработчике “ПередВыводомОтчета” создаем массив макетов.
Переменная МассивМакетов должна быть переменной модуля.
Обратите внимание- по названию ресурса программа сама теперь знает надо ли в диаграмме выводить нарастающий итог.
Нарастающие итоги в Power Pivot и Power BI
У нас накопились ответы на вопросы о накопительных итогах (даже ответы про накопительные итоги – накапливаются =) Такое впечатление, что с задачей рассчитать нарастающие или накопительные итоги сталкивается практически каждый слушатель наших курсов. И вопрос стоит даже не в том, какую формулу использовать.
Обычно всех интересуют нюансы. Например, как «остановить» нарастающий итог, чтобы он не отображался в периодах, где данных еще нет. Или как считать такой итог не в рамках года, а за все время.
Немного о нарастающих итогах
Нарастающий итог – это сумма показателей, где к данным текущего периода добавляются суммы предыдущих периодов. Вычисления нарастающих итогов обычно просят руководители, чтобы увидеть показатели с начала месяца, квартала или года, например, продажи или прибыль. Или посмотреть, сколько денег принес проект за все время работы. Совсем специфический случай – моделирование расчетных остатков, переходящих из года в год.
Отсюда, вычисления можно разделить на два вида:
а) внутри периода (с начала месяца, квартала, года);
б) без привязки к периодам.
В Power Pivot и Power BI для расчета нарастающих итогов есть специальные формулы.
DAX-формулы для расчета нарастающих итогов
4. Нарастающий итог без привязки к периодам.
При расчете нарастающего итога без привязки к периодам показатели будут суммироваться с самого начала проекта – с его первой даты, а в начале нового периода не «сбросятся».
Вычисление нарастающих итогов в T-SQL
Это еще одна часто встречающаяся задача. Основной принцип заключается в накоплении значений одного атрибута (агрегируемого элемента) на основе упорядочения по другому атрибуту или атрибутам (элемент упорядочения), возможно при наличии секций строк, определенных на основе еще одного атрибута или атрибутов (элемент секционирования). В жизни существует много примеров вычисления нарастающих итогов, например вычисление остатков на банковских счетах, отслеживание наличия товаров на складе или текущих цифр продаж и т.п.
Для демонстрации разных решений я воспользуюсь остатками на счетах. Вот код, который создает и наполняет таблицу Transactions небольшим объемом тестовых данных:
Каждая строка таблицы представляет банковскую операцию на счете. Депозиты отмечаются как транзакции с положительным значением в столбце val, а снятие средств — как отрицательное значение транзакции. Наша задача — вычислить остаток на счете в каждый момент времени путем аккумулирования сумм операций в строке val при упорядочении по столбцу tranid, причем это нужно сделать для каждого счета отдельно. Желаемый результат должен выглядеть так:
Для тестирования обоих решений нужен больший объем данных. Это можно сделать с помощью такого запроса:
Можете задать свои входные данные, чтобы изменить число секций (счетов) и строк (транзакций) в секции.
Основанное на наборах решение с использованием оконных функций
Я начну рассказ с решения на основе наборов, в котором используется оконная функция агрегирования SUM. Определение окна здесь довольно наглядно: нужно секционировать окно по actid, упорядочить по tranid и фильтром отобрать строки в кадре с крайней нижней (UNBOUNDED PRECEDING) до текущей. Вот соответствующий запрос:
Этот код не только простой и прямолинейный — он и выполняется быстро. План этого запроса показан на рисунке:
До SQL Server 2012 использовались либо вложенные запросы, либо соединения. При использовании вложенного запроса нарастающие итоги вычисляются путем фильтрации всех строк с тем же значением actid, что и во внешней строке, и значением tranid, которое меньше или равно значения во внешней строке. Затем к отфильтрованным строкам применяется агрегирование. Вот соответствующий запрос:
Аналогичный подход можно реализовать с применением соединений. Используется тот же предикат, что и в предложении WHERE вложенного запроса в предложении ON соединения. В этом случае для N-ой транзакции одного и того же счета A в экземпляре, обозначенном как T1, вы будете находить N соответствий в экземпляре T2, при этом номера транзакций пробегают от 1 до N. В результате сопоставления строки в T1 повторяются, поэтому нужно сгруппировать строки по всем элементам с T1, чтобы получить информацию о текущей транзакции и применить агрегирование к атрибуту val из T2 для вычисления нарастающего итога. Готовый запрос выглядит примерно так:
На рисунке ниже приведены планы обоих решений:
Заметьте, что в обоих случаях в экземпляре T1 выполняется полный просмотр кластеризованного индекса. Затем для каждой строки в плане предусмотрена операция поиска в индексе начала раздела текущего счета на конечной странице индекса, при этом считываются все транзакции, в которых T2.tranid меньше или равно T1.tranid. Точка, где происходит агрегирование строк, немного отличается в планах, но число считанных строк одинаково.
Чтобы понять, сколько строк просматривается, надо учесть число элементов данных. Пусть p — число секций (счетов), а r — число строк в секции (транзакции). Тогда число строк в таблице примерно равно p*r, если считать, что транзакции распределены по счетам равномерно. Таким образом, приведенный в верхней части просмотр охватывает p*r строк. Но больше всего нас интересует происходящее в итераторе Nested Loops.
Решения с использованием курсора
Решения на основе курсора реализуются «в лоб». Объявляется курсор на основе запроса, упорядочивающего данные по actid и tranid. После этого выполняется итеративный проход записей курсора. При обнаружении нового счета сбрасывается переменная, содержащая агрегат. В каждой итерации в переменную добавляется сумма новой транзакции, после этого строка сохраняется в табличной переменной с информацией о текущей транзакции плюс текущее значение нарастающего итога. После итеративного прохода возвращается результат из табличной переменной. Вот код законченного решения:
План запроса с использованием курсора показан на рисунке:
Этот план масштабируется линейно, потому что данные из индекса просматриваются только раз в определенном порядке. Также у каждой операции получения строки из курсора примерно одинаковая стоимость в расчете на каждую строку. Если принять нагрузку, создаваемую при обработке одной строки курсора, равной g, стоимость этого решения можно оценить как p*r + p*r*g (как вы помните, p — это число секций, а r — число строк в секции). Так что, если увеличить число строк на секцию в f раз, нагрузка на систему составит p*r*f + p*r*f*g, то есть будет расти линейно. Стоимость обработки в расчете на строку высока, но из-за линейного характера масштабирования, с определенного размера секции это решение будет демонстрировать лучшую масштабируемость, чем решения на основе вложенных запросов и соединений из-за квадратичного масштабирования этих решений. Проведенное мной измерение производительности показало, что число, когда решение с курсором работает быстрее, равно нескольким сотням строк на секцию.
Несмотря на выигрыш в производительности, обеспечиваемый решениями на основе курсора, в общем случае их надо избегать, потому что они не являются реляционными.
Решения на основе CLR
Чтобы иметь возможность выполнить эту хранимую процедуру в SQL Server, сначала надо на основе этого кода построить сборку по имени AccountBalances и развернуть в базе данных TSQL2012. Если вы не знакомы с развертыванием сборок в SQL Server, можете почитать раздел «Хранимые процедуры и среда CLR» в статье «Хранимые процедуры».
Если вы назвали сборку AccountBalances, а путь к файлу сборки — «C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll», загрузить сборку в базу данных и зарегистрировать хранимую процедуру можно следующим кодом:
После развертывания сборки и регистрации процедуры можно ее выполнить следующим кодом:
По завершении надо выполнить следующий код очистки:
Вложенные итерации
До этого момента я показывал итеративные решения и решения на основе наборов. Следующее решение основано на вложенных итерациях, которые являются гибридом итеративного и основанного на наборах подходов. Идея заключается в том, чтобы предварительно скопировать строки из таблицы-источника (в нашем случае это банковские счета) во временную таблицу вместе с новым атрибутом по имени rownum, который вычисляется с использованием функции ROW_NUMBER. Номера строк секционируются по actid и упорядочиваются по tranid, поэтому первой транзакции в каждом банковском счете назначается номер 1, второй транзакции — 2 и т.д. Затем во временной таблице создается кластеризованный индекс со списком ключей (rownum, actid). Затем используется рекурсивное выражение CTE или специально созданный цикл для обработки по одной строке за итерацию во всех счетах. Затем нарастающий итог вычисляется путем суммирования значения, соответствующего текущей строке, со значением, связанным с предыдущей строкой. Вот реализация этой логики с использованием рекурсивного CTE:
А это реализация с использованием явного цикла:
Это решение обеспечивает хорошую производительность, когда есть большое число секций с небольшим числом строк в секциях. Тогда число итераций небольшое, а основная работа выполняется основанной на наборах частью решения, которая соединяет строки, связанные с одним номером строки, со строками, связанными с предыдущим номером строки.
Многострочное обновление с переменными
Показанные до этого момента приемы вычисления нарастающих итогов гарантированно дают правильный результат. Описываемая в этом разделе методика неоднозначна, потому что основана на наблюдаемом, а не задокументированном поведении системы, кроме того она противоречит принципам релятивности. Высокая ее привлекательность обусловлена большой скоростью работы.
В этом способе используется инструкция UPDATE с переменными. Инструкция UPDATE может присваивать переменным выражения на основе значения столбца, а также присваивать значениям в столбцах выражение с переменной. Решение начинается с создания временной таблицы по имени Transactions с атрибутами actid, tranid, val и balance и кластеризованного индекса со списком ключей (actid, tranid). Затем временная таблица наполняется всеми строками из исходной БД Transactions, причем в столбец balance всех строк вводится значение 0,00. Затем вызывается инструкция UPDATE с переменными, связанными с временной таблицей, для вычисления нарастающих итогов и вставки вычисленного значения в столбец balance.
Используются переменные @prevaccount и @prevbalance, а значение в столбце balance вычисляется с применением следующего выражения:
Выражение CASE проверяет, не совпадают ли идентификаторы текущего и предыдущего счетов, и, если они равны, возвращает сумму предыдущего и текущего значений в столбце balance. Если идентификаторы счетов разные, возвращается сумма текущей транзакции. Далее результат выражения CASE вставляется в столбец balance и присваивается переменной @prevbalance. В отдельном выражении переменной ©prevaccount присваивается идентификатор текущего счета.
После выражения UPDATE решение представляет строки из временной таблицы и удаляет последнюю. Вот код законченного решения:
В этом решении предполагается, что при оптимизации выполнения UPDATE всегда будет выполняться упорядоченный просмотр кластеризованного индекса, и в решении предусмотрен ряд подсказок, чтобы предотвратить обстоятельства, которые могут помешать этому, например параллелизм. Проблема в том, что нет никакой официальной гарантии, что оптимизатор всегда будет посматривать в порядке кластеризованного индекса. Нельзя полагаться на особенности физических вычислений, когда нужно обеспечить логическую корректность кода, если только в коде нет логических элементов, которые по определению могут гарантировать такое поведение. В данном коде нет никаких логических особенностей, которые могли бы гарантировать именно такое поведение. Естественно выбор, использовать или нет этот способ, лежит целиком на вашей совести. Я считаю, что безответственно использовать его, даже если вы тысячи раз проверяли и «вроде бы все работает, как надо».
К счастью, в SQL Server 2012 этот выбор становится практически ненужным. При наличии исключительно эффективного решения с использованием оконных функций агрегирования не приходится задумываться о других решениях.
Измерение производительности
Я провел измерение и сравнение производительности различных методик. Результаты приведены на рисунках ниже:
