"Нарастающий итог" как элемент общей лексики
После вопроса о пространственных отношениях, в котором никто ничего не понял, включая меня, автора вопроса, я решила спросить что-нибудь полегче и выудила из глубин сознания такую злобу дня: в каких случаях выражение «нарастающий итог» как элемент общей лексики, определяющий в предложении предмет или характер совершения действия, употребляется с предлогом «с», а в каких — без него? Например: «трагизм (с) нарастающим итогом», «препарат действует (с) нарастающим итогом». Я сформулировала вопрос с помощью примеров, но мне хотелось бы обсудить общие случаи употребления этого сочетания.
Нарастающий итог — экономический фразеологизм, термин, он не может употребляться так же, как свободное выражение.
Трагизм не может быть с нарастающим итогом, его никто не подсчитывал.Синоним слова итог в этом значении -сумма, вывод всякого сложенья, количество. Просто вывод, следствие, плод, последствие, продукт, развязка, исход, эффект — это частичные синонимы, к переносному значению. Нарастающим итогом можно подсчитать прибыль, проценты выплат, а по отношению к трагизму можно сказать «трагизм усиливается с нарастающей силой». Препарат действует с нарастающим не итогом, а эффектом.
Нарастающий — многозначное слово.
Нарастать — 1. Вырастать на поверхности чего-л. в каком-либо количестве.
Накапливаться в каком-либо количестве (о недоимке, процентах и т.п.).
Увеличиваться в размерах.
Итог(нарастающий) накапливается, трагизм (нарастающий)усиливается, морская волна(нарастающая) увеличивается в размерах.
Трагизм МОЖЕТ БЫТЬ с нарастающим итогом — другое дело, существует ли такой трагизм. Я могу себе представить пьесу, в которой развязка наступает в конце каждого действия, но при этом общая напряженность сюжетной линии от действия к действию не ослабевает, а еще более усиливается. Но для этого автор должен написать такую пьесу, а театральный критик определить этот прием как художественную находку.
В этом случае «трагизм с нарастающим итогом» будет метафорой, а метафора — это «троп, слово или выражение, употребляемое в переносном значении, в основе которого лежит неназванное сравнение предмета с каким-либо другим на основании их общего признака. Термин принадлежит Аристотелю и связан с его пониманием искусства как подражания жизни».
Однажды кто-то сказал, что дождь «идет», и можно представить, как оценили окружающие его метафору. Не исключено, что они долго объясняли, что дождь ходить не может и высказывание это неожиданно, странно и реальности не соответствует.
Нарастающий итог в 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 квартал.
2) Сумма за 1 квартал + сумма за 2 квартал = Сумма за полугодие.
3) Сумма за полугодие + сумма за 3 квартал = Сумма за 9 месяцев.
4) Сумма за 9 месяцев + сумма за 4 квартал = Сумма за год.
Нужно последовательно складывать сумму за текущий период с итоговой суммой, полученной за прошлые периоды.
При исчислении налогов нарастающим итогом и заполнении налоговой декларации отражаются доходы и расходы, накопленные с начала года до окончания текущего отчётного периода.
Ранее исчисленные суммы авансовых платежей по налогу учитываются при определении суммы налога к уплате за отчетный период.
Теперь рассмотрим, как выполнить суммирование нарастающим итогом в программе Эксель (Excel).
Вот исходные данные:
В ячейку C2 нужно внести формулу:
Теперь с помощью маркера заполнения копируем эту формулу в нижние ячейки (C3, C4 и C5).
В результате мы получим итоги за год и промежуточные итоги:
Такое понятие как нарастающий итог используется в бухгалтерии.
Нагляднее и быстрее всего объяснять что такое нарастающий итог на примере. Допустим у нашей организации показатели продаж за 7 месяцев отражены вот в такой табличке:
Во второй графе таблицы у нас просто показатель объема продаж за каждый месяц, а в третьей графе как раз та самая сумма нарастающим итогом, и за февраль она равна продажам за январь плюс продажи за февраль.
За март продажи нарастающим итогом будут уже равны сумме продаж за три месяца: за январь, февраль и март, и так далее.
Нарастающим итогом называют промежуточный итог за какой-то промежуток времени, например, данные нарастающим итогом за второй квартал равны сумме данных за апрель, май и июнь.
Во многих отчётностях бухгалтерии суммы требуется указывать нарастающим итогом. Обычно это делается с начала года, то есть с первого его месяца, коим является январь. Допустим, выплаты за первый зимний месяц составили 200 руб., за февраль-они увеличились — 250 руб., в марте опять был прирост — 300 руб. По итогам месяца январь единый соц. налог следует определять с суммы 200 руб., февраля — с 450 руб. (200 + 250), а уже с марта месяца — с 750 рублей (то есть в данном случае получается — 200 + 250 + 350). Таким образом нарастающий итог — это общая сумма показателей за определённый период времени. Это может быть месяц или полугодие и даже год. В результате в бюджет будет перечисляться разница между суммой, которую рассчитали и той, что уже на момент расчёта уже была уплачена.
На самом деле в понятии «нарастающий итог» нет ничего сложного. Пожалуй, чаще всего с этим термином сталкиваются бухгалтера, экономисты, люди, чьи профессии связаны со статистикой и составлением различных отчетов с цифрами.
Многие документы в бухгалтерской и финансовой отчетности составляются нарастающим итогом. Это означает, что данные по какому-то показателю как бы накапливаются от периода ( например, отчетного, налогового ) к периоду: к сумме текущего периода добавляются суммы предшествующих периодов.
Например, у нас есть данные, что за первый квартал фонд заработной платы ( ФОТ ) составил 100000 рублей, за второй — 110000 рублей, за третий — 115000 рублей и за четвертый — 100000 рублей.
Нарастающим итогом это будет выглядеть следующим образом:
1 квартал — 100000 рублей,
2 квартал ( полугодие )- 210000 рублей ( ФОТ 1 квартала + ФОТ 2 квартала ),
3 квартал ( 9 месяцев ) — 325000 рублей ( ФОТ за полугодие + ФОТ 3 кв ),
4 квартал ( год ) — 425000 рублей ( ФОТ за 9 месяцев + ФОТ 4 кв ).
Попробую по-простому рассказать, что такое нарастающий итог. Нарастающим итогом можно назвать показатель, который представляет сумму текущего периода с предыдущими периодами.
Этот термин часто употребляется экономистами, бухгалтерами.
Считать этот показатель лучше в Экселе, ниже в ответе приведу пример:
Теперь рассмотрим как считать нарастающий итог, например в 4 квартале:
То есть нужно будет нарастающий итог за третий квартал просуммировать со значением четвертого квартала. В итоге получается нарастающий итог 4 квартала, который должен будет равен сумме продаж за год.
Думаю, что после этого примера понятно, как считать нарастающий итог.
Сам тоже недавно познакомился с понятием «нарастающий итог»
Когда пришлось сдавать отчет по объемам потребленной воды из недр.
Это не слишком мудреное дело, главное понять. В случае с продажами, это будет выглядеть так —
Делаете таблицу, разбиваете на 12 строк (ну или другое количество — например 30 дней в месяце) ставите месяца с января по декабрь и пишете выручку в каждом. В низу выводите общую сумму периода — например за квартал, за полугодие, за год (смотря на каком этапе таблицы вы сейчас находитесь — если вы делаете отчет по итогам 2 квартала, то делаете суммарный за квартал и суммарный за полугодие. В конце года за все четыре кварта в отдельности, за два полугодия, и за год в целом.
Рассмотрим нарастающий итог на примере объема продаж за определённый срок.
Для наглядности можно сделать таблицу, в которой нужно сделать столько строк, сколько месяцев в нашем периоде.
Если наш период равен году, делаем 12 строк и каждую из них про именуем названиями месяцев: январь, февраль. декабрь. Затем нужно написать сумму прибыли в каждом из месяцев. Внизу таблицы необходимо сделать общую сумму за нужный вам период, к примеру за год.
В этом случае нарастающий итог это финансовый прирост в каждом периоде, месяце. Нарастающий итог может быть взят за разные промежутки времени, к примеру за месяц, квартал, полугодие или год.
Нарастающий итог — это когда в последующем месяце учитывается и итог предыдущего. Промежуточный итог — это и есть нарастающий за промежуток времени. Например — нарастающий итог за 1-й квартал равен итогам за январь, февраль, март. Если сложить нарастающие итоги за первый и второй квартал, то это значение будет равно нарастающему итогу за первое полугодие.
Нарастающий итог получается при последовательном сложении итогов за предыдущие месяца.
Нарастающий итог — это классический прием в отчетах. Он наглядно показывает прибыль или другие параметры.
При расчете налогов нарастающим итогом учитываются все расходы и доходы.
Нарастающий итог — это ничто иное, как суммарный объем определенных показателей за какой-то промежуток времени.
К примеру, надо подсчитать зарплату за 12 месяцев, значит нарастающим итогом будет сумма зарплаты за 12 месяцев.
Предположим, что ежемесячная зарплата составляет 30 000 рублей. Считаем нарастающий итог за год. Сумма нарастающего итога каждый месяц будет увеличиваться на 300 тысяч.
Вот еще один расчет:
Если бы суммы были разные, нарастающий итог увеличивался бы на ту сумму, которая представлена в доходе.
Но стоит указать, если считается доход, то может быть и минус, т.е. тогда надо вычесть из получаемой суммы ту цифру, сколько получается минус.
Чтобы понять наглядно, что такое нарастающий итог, приведем пример, допустим, давайте посчитаем объем продаж нарастающим итогом за 7 месяцев с января по июль:
В январе объем продаж 100, в феврале — 200, а в феврале нарастающим итогом — 300 (100 за январь плюс 200 за февраль), дальше так и прибавляем, нарастающим итогом объем продаж в марте будет уже 600 (100 (январь) плюс 200 (февраль) плюс 300 (март), и так далее.
Нарастающий итог это промежуточный итог за определенный промежуток времени, например, данные нарастающим итогом за первое полугодие 2019 года равны сумме данных за первый и второй кварталы 2019 года.
Нарастающий итог в Excel
Начнём, для разогрева, с наиболее простого варианта — формул. Если мы имеем на входе небольшую отсортированную по дате таблицу, то для расчёта нарастающего итога в отдельном столбце нам потребуется элементарная формула:
Основная фишка тут в хитром закреплении диапазона внутри функции СУММ — ссылка на начало диапазона делается абсолютной (со знаками доллара), а на конец — относительной (без долларов). Соответственно, при копировании формулы вниз на весь столбец мы получаем растягивающийся диапазон, сумму которого и считаем.
Минусы такого подхода очевидны:
- Таблица обязательно должна быть отсортирована по дате.
- При добавлении новых строк с данными формулу придётся допротягивать вручную.
Способ 2. Сводная таблица
Этот способ чуть сложнее, но гораздо приятнее. Причём для усугубления давайте рассмотрим задачку посерьезнее — таблицу из 2000 строк с данными, где по столбцу даты нет сортировки, зато есть повторы (т.е. мы можем несколько раз продать в один и тот же день):
Конвертируем нашу исходную таблицу в «умную» (динамическую) сочетанием клавиш Ctrl + T или командой Главная — Форматировать как таблицу (Home — Format as Table) , а затем строим по ней сводную таблицу командой Вставка — Сводная таблица (Insert — Pivot Table) . В область строк в сводной закидываем дату, а в область значений — число проданных товаров:
Обратите внимание, что если у вас не совсем старая версия Excel, то даты автоматически сгруппируются по годам, кварталам и месяцам. Если вам нужна другая группировка (или она совсем не нужна), то это можно исправить, щёлкнув правой кнопкой мыши по любой дате и выбрав команды Группировать / Разгруппировать (Group / Ungroup) .
Если хочется видеть одновременно и получившиеся итоги по периодам и нарастающий итог в отдельном столбце, то имеет смысл закинуть в область значений поле Продано ещё раз, чтобы получить дубликат поля — в нём мы и включим отображение нарастающих итогов. Для этого щёлкните правой кнопкой мыши по полю и выберите команду Дополнительные вычисления — Нарастающий итог (Show Values as — Running Totals) :
Там же можно выбрать и вариант нарастающих итогов в процентах, а в следующем окне нужно выбрать поле, по которому пойдет накопление — в нашем случае это поле даты:
Плюсы такого подхода:
- Быстро считается большой объем данных.
- Не нужно вводить вручную никаких формул.
- При изменении в исходных данных достаточно обновить сводную правой кнопкой мыши или командой Данные — Обновить всё (Data — Refresh All).
Способ 3. Запрос Power Query
Загрузим нашу «умную» таблицу с исходными данными в редактор запросов Power Query, используя команду Данные — Из таблицы / диапазона (Data — From Table/Range) . В последних версиях Excel её, кстати, переименовали — теперь она называется С листа (From Sheet) :
Затем выполним следующие шаги:
1 . Отсортируем таблицу по возрастанию по столбцу даты командой Сортировать по возрастанию в выпадающем списке фильтра в шапке таблицы.
2 . Чуть позже для подсчета нарастающего итога нам потребуется вспомогательный столбец с порядковым номером строки. Добавим его командой Добавление столбца — Столбец индекса — От 1 (Add column — Index column — From 1) .
3 . Также для подсчёта нарастающего итога нам потребуется ссылка на столбец Продано, где лежат наши суммируемые данные. В Power Query столбцы ещё называются списками (list) и чтобы получить ссылку на него, щёлкнем правой кнопкой мыши по заголовку столбца и выберем команду Детализация (Show detail) . В строке формул появится нужное нам выражение, состоящее из имени предыдущего шага #»Добавлен индекс», откуда мы берём таблицу и имени столбца [Продано] из этой таблицы в квадратных скобках:
Копируем это выражение в буфер для дальнейшего использования.
4 . Удаляем ненужный более последний шаг Продано и добавляем вместо него вычисляемый столбец для подсчета нарастающего итога командой Добавление столбца — Настраиваемый столбец (Add column — Custom column) . Нужная нам формула будет выглядеть так:
Здесь функция List.Range берёт исходный список (столбец [Продано]) и извлекает из него элементы, начиная с первого (в формуле это 0, т.к. нумерация в Power Query начинается с нуля). Количество извлекаемых элементов — это номер строки, который мы берём из столбца [Индекс]. Таким образом, эта функция для первой строки возвращает только одну первую ячейку столбца Продано. Для второй строки — уже первых две ячейки, для третьей — первых три и т.д.
Ну, а затем функция List.Sum суммирует извлечённые значения и мы получаем в каждой строке сумму всех предыдущих элементов, т.е. нарастающий итог:
Осталось удалить ненужный нам больше столбец Индекс и выгрузить результаты обратно в Excel командой Главная — Закрыть и загрузить (Home — Close&Load to).
Форсаж
В принципе, на этом можно было бы и остановиться, но есть небольшая ложка дёгтя — созданный нами запрос работает со скоростью черепахи. Для примера, на моем не самом слабом ПК таблица всего в 2000 строк обрабатывается 17 секунд. А если данных будет больше?
Для ускорения можно использовать буферизацию при помощи специальной функции List.Buffer, которая загружает заданный ей в качестве аргумента список (list) в оперативную память, что сильно ускоряет обращение к нему в дальнейшем. В нашем случае имеет смысл буферизовать список #»Добавлен индекс»[Продано], к которому Power Query вынужден обращаться при расчёте нарастающего итога в каждой строке нашей 2000-строчной таблицы.
Для этого в редакторе Power Query на Главной вкладке жмём кнопку Расширенный редактор (Home — Advanced Editor), чтобы открыть исходный код нашего запроса на встроенном в Power Query языке М:
А затем добавляем туда строку с переменной MyList, значение которой возвращает функция буферизации и подменяем на следующем шаге обращение к списку на эту переменную:
После внесения таких изменений наш запрос станет существенно резвее и справится с 2000-строчной таблицей всего за 0.3 секунды!