Нарастающий итог — что это значит? Как посчитать сумму нарастающим итогом?
Как считать прибыль, налоги и т.п. нарастающим итогом?
Понятие "нарастающий итог" подразумевает расчёт промежуточных итогов по истечению каждого промежуточного периода.
Например, если финансовый год разбивается на кварталы, то доходы компании нарастающим итогом будут считаться следующим образом:
1) Сумма за 1 квартал.
2) Сумма за 1 квартал + сумма за 2 квартал = Сумма за полугодие.
3) Сумма за полугодие + сумма за 3 квартал = Сумма за 9 месяцев.
4) Сумма за 9 месяцев + сумма за 4 квартал = Сумма за год.
Нужно последовательно складывать сумму за текущий период с итоговой суммой, полученной за прошлые периоды.
При исчислении налогов нарастающим итогом и заполнении налоговой декларации отражаются доходы и расходы, накопленные с начала года до окончания текущего отчётного периода.
Ранее исчисленные суммы авансовых платежей по налогу учитываются при определении суммы налога к уплате за отчетный период.
Теперь рассмотрим, как выполнить суммирование нарастающим итогом в программе Эксель (Excel).
Вот исходные данные:
В ячейку C2 нужно внести формулу:
Теперь с помощью маркера заполнения копируем эту формулу в нижние ячейки (C3, C4 и C5).
В результате мы получим итоги за год и промежуточные итоги:
Во многих отчётностях бухгалтерии суммы требуется указывать нарастающим итогом. Обычно это делается с начала года, то есть с первого его месяца, коим является январь. Допустим, выплаты за первый зимний месяц составили 200 руб., за февраль-они увеличились — 250 руб., в марте опять был прирост — 300 руб. По итогам месяца январь единый соц. налог следует определять с суммы 200 руб., февраля — с 450 руб. (200 + 250), а уже с марта месяца — с 750 рублей (то есть в данном случае получается — 200 + 250 + 350). Таким образом нарастающий итог — это общая сумма показателей за определённый период времени. Это может быть месяц или полугодие и даже год. В результате в бюджет будет перечисляться разница между суммой, которую рассчитали и той, что уже на момент расчёта уже была уплачена.
Такое понятие как нарастающий итог используется в бухгалтерии.
Нагляднее и быстрее всего объяснять что такое нарастающий итог на примере. Допустим у нашей организации показатели продаж за 7 месяцев отражены вот в такой табличке:
Во второй графе таблицы у нас просто показатель объема продаж за каждый месяц, а в третьей графе как раз та самая сумма нарастающим итогом, и за февраль она равна продажам за январь плюс продажи за февраль.
За март продажи нарастающим итогом будут уже равны сумме продаж за три месяца: за январь, февраль и март, и так далее.
Нарастающим итогом называют промежуточный итог за какой-то промежуток времени, например, данные нарастающим итогом за второй квартал равны сумме данных за апрель, май и июнь.
На самом деле в понятии "нарастающий итог" нет ничего сложного. Пожалуй, чаще всего с этим термином сталкиваются бухгалтера, экономисты, люди, чьи профессии связаны со статистикой и составлением различных отчетов с цифрами.
Многие документы в бухгалтерской и финансовой отчетности составляются нарастающим итогом. Это означает, что данные по какому-то показателю как бы накапливаются от периода ( например, отчетного, налогового ) к периоду: к сумме текущего периода добавляются суммы предшествующих периодов.
Например, у нас есть данные, что за первый квартал фонд заработной платы ( ФОТ ) составил 100000 рублей, за второй — 110000 рублей, за третий — 115000 рублей и за четвертый — 100000 рублей.
Нарастающим итогом это будет выглядеть следующим образом:
1 квартал — 100000 рублей,
2 квартал ( полугодие )- 210000 рублей ( ФОТ 1 квартала + ФОТ 2 квартала ),
Нарастающий итог в 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, соединив два экземпляра одной и той же таблицы:
Как можно заметить, условие фильтрации в подзапросе t2.dt <= s.dt стало условием соединения. Кроме того, чтобы использовать агрегирующую функцию sum() нам необходима группировка по дате и значению group by s.dt, s.val .
Точно также можно сделать для случая с разными группами grp :
4. Декартово произведение
Раз уж мы заменили подзапрос на join, то почему бы не попробовать декартово произведение? Это решение потребует только минимальных правок:
Или для случая с группами:
Перечисленные решения (подзапрос, inner join, cartesian join) соответсвуют SQL-92 и SQL:1999, а потому будут доступны практически в любой СУБД. Основная проблема всех этих решений в низкой производительности. Это не велика беда, если мы материализуем таблицу с результатом (но ведь всё равно хочется большей скорости!). Дальнейшие методы куда более эффективны (с поправкой на уже указанные специфику конкретных СУБД и их версий, размер таблицы, индексы).
5. Рекурсивный запрос
Один из более специфических подходов – это рекурсивный запрос в common table expression. Для этого нам необходим «якорь» – запрос, возвращающий самую первую строку:
Затем к «якорю» с помощью union all присоединяются результаты рекурсивного запроса. Для этого можно опереться на поле даты dt , прибавляя у нему по одному дню:
Часть кода, добавляющая один день, не универсальна. Например, это r.dt = dateadd(day, 1, cte.dt) для SQL Server, r.dt = cte.dt + 1 для Oracle, и т.д.
Совместив «якорь» и основной запрос, мы получим окончательный результат:
Решение для случая с группами будет ненамного сложнее:
6. Рекурсивный запрос с функцией row_number()
Предыдущее решение опиралось на непрерывность поля даты dt с последовательным приростом на 1 день. Мы избежать этого, используя оконную функцию row_number() , которая нумерует строки. Конечно, это нечестно – ведь мы собрались рассматривать альтернативы оконным функциям. Тем не менее, это решение может быть своего рода proof of concept: ведь на практике может быть поле, заменяющее номера строк (id записи). Кроме того, в SQL Server функция row_number() появилась раньше, чем была введена полноценная поддержка оконных функций (включая sum() ).
Итак, для рекурсивного запроса с 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 значениями текущей строки
… затем рассчитываем номер строки как row_number() over (order by dt) as rn (или используем готовое поле с номером, если оно есть). И, наконец, вводим правило для всех строк, кроме первой: total[rn >= 2] = total[cv() — 1] + val[cv()] .
Функция cv() здесь отвечает за значение текущей строки. А весь запрос будет выглядеть так:
9. Курсор (SQL Server)
Нарастающий итог – один из немногих случаев, когда курсор в SQL Server не только полезен, но и предпочтителен другим решениям (как минимум до версии 2012, где появились оконные функции).
Реализация через курсор довольно тривиальна. Сначала необходимо создать временную таблицу и заполнить её датами и значениями из основной:
Затем задаём локальные переменные, через которые будет происходить обновление:
После этого обновляем временную таблицу через курсор:
И, наконец, получем нужный результат:
10. Обновление через локальную переменную (SQL Server)
Обновление через локальную переменную в SQL Server основано на недокументированном поведении, поэтому его нельзя считать надёжным. Тем не менее, это едва ли не самое быстрое решение, и этим оно интересно.
Создадим две переменные: одну для нарастающих итогов и табличную переменную:
Сначала заполним @tv данным из основной таблицы
Затем табличную переменную @tv обновим, используя @VarTotal :
… после чего получим окончательный результат:
Резюме: мы рассмотрели топ 10 способов расчёта нарастающего итога в SQL. Как можно заметить, даже без оконных функций эта задача вполне решаема, причём механику решения нельзя назвать сложной.
Значение словосочетания «нарастающим итогом»
/>Привет! Меня зовут Лампобот, я компьютерная программа, которая помогает делать Карту слов. Я отлично умею считать, но пока плохо понимаю, как устроен ваш мир. Помоги мне разобраться!
Спасибо! Я обязательно научусь отличать широко распространённые слова от узкоспециальных.
Насколько понятно значение слова метровка (существительное):
Ассоциации к слову «итог»
Синонимы к словосочетанию «нарастающим итогом»
Предложения со словосочетанием «нарастающим итогом»
- Бухгалтерская отчётность составляется нарастающим итогом с начала года.
Сочетаемость слова «итог»
Афоризмы русских писателей со словом «итог»
- Сейчас, вот сейчас может случиться что-то совсем удивительное, настоящее диво.
Замечательное это ощущение, прекрасное состояние души. И, право, досадно, если оно глохнет у человека с возрастом, с личным жизненным опытом и усвоением науки — итога жизненного опыта, знаний всего человечества. Нет, знание никогда не должно убивать в человеке веру в то, что сегодня кажется нам тайной, дивом, а завтра будет всеми признано и объяснено!
Отправить комментарий
Дополнительно
Значение слова «итог»
ИТО́Г , -а, м. 1. Общая сумма.
Значение слова «нарастающий»
1. прич. наст. вр. от нарастать
Предложения со словосочетанием «нарастающим итогом»
Бухгалтерская отчётность составляется нарастающим итогом с начала года.
Сумма на начало каждого периода – это сумма на конец предыдущего периода, поэтому в таблице сумма на конец изменяется нарастающим итогом в течение всего года, вбирая в себя значения денежного потока в каждом из периодов.
Текущий (итоговый) анализ также является формой ретроспективного анализа и проводится по итогам работы за год: оценка результатов производится нарастающим итогом.