Нарастающим итогом с начала года

01 Декабрь 2015

Стандартная ситуация – вы ведете в табличке продажи по дням. Примерно так:

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

Суть ее – берем предыдущую сумму и добавляет продажи за текущий день.
Но есть более грамотный и красивый способ.
В колонке «Сумма нарастающим итогом» пишем формулу =СУММ($D$4:D4)
Важно чтобы верхняя граница суммируемого диапазона не изменялась, именно для этого перед координатами ячейки ставится знак «$», а нижняя наоборот при копировании изменялась так, чтобы при копировании вниз изменялся нужный нам диапазон

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

Вы спросите — чем этот способ лучше?
Допустим вам нужна сумма нарастающим итогом не на каждый день а через день или раз в неделю или в пять дней. При первом варианте, если вы удалите сумму нарастающим в каком-то из дней, все ваши вычисления «поплывут» и будут показывать неправильные значения.
Во втором варианте, вы можете оставлять значения нарастающим итогом в тех ячейках, которые вам нужны, например каждую пятницу или каждый четный день. Данные всегда будут отражаться корректно.
Да, насчет пятницы, интересная мысль. Что теперь сидеть с календарем и выбирать эти самые пятницы?
Совершенно не обязательно, вы уже догадались, что и эту задачу легко можно решить с помощью формулы в Excel
Для этого существует функция =ДЕНЬНЕД(). Она как раз подходит для нашего случая.
Создаем такую вот формулу:
=ЕСЛИ(ДЕНЬНЕД(A11)=5;СУММ($D$4:D11);»»)
В переводе на обычный язык она говорит следующее: Если день недели в ячейке с датой имеет номер 5 (а это как известно пятница), то тогда нужно показать сумму нарастающим итогом, если же нет, то ничего не показывать. «Ничего» и 0 это разные понятия. Поэтому «Ничего» мы обозначаем двумя кавычками ( «» ). В результате таблица будет выглядеть примерно так:

Как видите все получилось довольно аккуратно и информативно.
Надеюсь работа в Excel будет доставлять вам только удовольствие.

Расчет и удержание НДФЛ нарастающим итогом с начала года.

Киревнина Юлия
Порядок исчисления и удержания НДФЛ кажется довольно простым и понятным, в первую очередь ввиду того, что данная процедура производится по несколько раз в месяц. И многие опытные бухгалтеры, расчетчики и другие специалисты зачастую могут сделать это, что называется, с закрытыми глазами. Однако и в таком, на первый взгляд, элементарном вопросе имеются свои тонкости, которые нужно учитывать. Иначе есть риск неверно произвести расчет НДФЛ и исказить данные в отчетности.
В соответствии с п.1 ст.24 НК РФ налоговые агенты по НДФЛ обязаны исчислять и удерживать налог с физических лиц — получателей доходов. При этом исчисление сумм налога производится на дату фактического получения дохода (которая определяется в соответствии со статьей 223 НК РФ), нарастающим итогом с начала налогового периода применительно ко всем доходам (за исключением доходов от долевого участия в организации), начисленным налогоплательщику за данный период, с зачетом удержанной в предыдущие месяцы текущего налогового периода суммы налога.
Давайте попробуем перевести, что называется, с русского на русский.
Сотрудник ежемесячно получает доход. Чтобы определить сумму НДФЛ, подлежащую удержанию необходимо сложить весь его доход за год («нарастающим итогом с начала налогового периода»), уменьшить на сумму вычетов с начала года (если есть на них право), умножить на 13% и вычесть тот НДФЛ, который мы уже удержали в предыдущих месяцах этого года («с зачетом удержанной в предыдущие месяцы текущего налогового периода суммы налога»).
На примере это будет выглядеть следующим образом:
Пример 1: Сотрудник получил доход (предположим, права на вычеты у него нет):
Январь 10000 руб. НДФЛ составит 10000 x 13% = 1300 руб.
Февраль 10000 руб. НДФЛ составит 20000 x 13% – 1300=1300 руб.
И вроде бы не совсем понятно, к чему такие сложности с пересчетом сумм с начала года, когда можно просто взять 10000 за Февраль умножить на ставку налога и получить ту же цифру? (10000 x 0,13 = 1300). Конкретно в данном случае — да, результат получился одинаковым.
Но, во–первых, в реальной жизни редко приходится сталкиваться с такими удобными цифрами. Во-вторых, надо помнить, что НДФЛ исчисляется с точностью до рублей, а значит, мы можем получить несколько иные цифры за счет округления. И в-третьих, следует учитывать, что программы 1С разработаны в соответствии с законодательством, и в механизмы расчета и работы программы заложен именно алгоритм, указанный в Налоговом Кодексе.
Что это значит для пользователей программы? В первую очередь то, что при создании в программе расчетных документов (будь то зарплата, отпуск и т.д.) расчет НДФЛ будет произведен по следующей формуле1*:
НДФЛ13% = (НБ – В) x 13% – НДФЛпред
Где:
НДФЛ13% — налог, исчисленный по ставке 13 процентов;
НБ — налоговая база, т.е. сумма облагаемых по ставке 13 процентов доходов, полученных налогоплательщиком с начала года;
В — сумма налоговых вычетов, на которые налогоплательщик имеет право;
НДФЛпред — сумма налога, удержанная налоговым агентом с начала календарного года по предыдущий месяц.
И уже после этого результат округляется до рублей.
Именно такой порядок указан и на сайте ИТС (https://its.1c.ru/db/taxndfl#content:591:hdoc).
Рассмотрим на примере ситуацию, которая наглядно покажет, что расчет с начала года действительно необходим:
Пример 2: Сотрудник получил доход (предположим, права на вычеты у него нет):
Январь 12549 руб. НДФЛ составит: 12549 x 13% = 1631 руб.
(если быть точным, то до округления сумма составила 1631,37)
Февраль 8980 руб. НДФЛ составит (12549 + 8980) x 13% – 1631 = 1168 руб. (до округления 1167,77)
Если бы мы рассчитали НДФЛ только исходя из суммы начисления за февраль, мы бы получили другую сумму: 8980 x 13% = 1167 руб. (до округления 1167,4)
Уже образовалась разница между расчетами в 1 рубль.
Этот рубль часто исправляется пользователями вручную и тогда он начинает «гулять», появляясь в каждом последующем расчете НДФЛ по данному сотруднику, пока его не зачтут.
Но иногда разница, обусловленная исчислением налога нарастающим итогом с начала года может быть гораздо большей, чем 1 рубль. Например, интересные ситуации возникают, когда имеем дело с вычетами по НДФЛ.
Рассмотрим пример, и проанализируем результаты.
Пример 3: Сотрудник имеет право на «детский» вычет в размере 1400 руб.
Доходы сотрудника составляют:
Январь 10000 руб.
Февраль 1000 руб.
При таких условиях исчисленный налог составит:
Январь:
(10000 – 1400) х 13% = 1118 руб.
Где:
10000 руб. — доход;
1400 руб. — вычет.
Февраль:
Доходы сотрудника в феврале меньше, чем сумма вычета (например, у него был переходящий отпуск с января, и рабочих дней в феврале осталось совсем мало). При этом важно понимать, что налоговая база в данном случае не приравнивается к нулю, и сумма налога также будет не равна нулю, а будет рассчитываться по формуле, описанной выше. И тогда исчисленный налог составит:
(10000 + 1000 – 2800) х 13% – 1118 = –52 руб.
Где:
10000 руб. — доход с начала года;
2800 руб. — вычеты с начала года;
1118 руб. — удержанный ранее налог.
Сумма налога в данном случае получается отрицательной за счет того, что сотрудник имел доход в предыдущем месяце, с которого мы можем удержать налог. Сумма налога нарастающим итогом с начала года составит 1066 руб. (1118 – 52) Таким образом, для определения налогооблагаемой базы по НДФЛ, учитывается не отдельно взятый документ или конкретный месяц расчета, а общая сумма дохода, полученная нарастающим итогом с начала года.
Также будут складываться и суммы применяемых вычетов: нарастающим итогом с начала года.
Следующий пример наглядно отразит это утверждение:
Пример 4: Сотрудник имеет право на «детский» вычет в размере 1400 руб.
Доходы сотрудника составляют:
Январь 1000 руб.
Февраль 10000 руб.
Исчисленный налог составит:
Январь:
(1000 – 1400) х 13% = 0.
В данном случае сумма вычета превышает доход нарастающим итогом с начала года и налог составит 0 руб.
В феврале расчет налога произойдет по следующей формуле:
Февраль:
(1000 + 10000 – 2800) х 13% – 0 = 1066 руб.
Где:
1000 руб. — доход за январь;
10000 руб. — доход за февраль;
2800 руб. — вычеты за два месяца.
Образно говоря, остаток вычета, который не закрылся доходом в январе (1000 (доход) – 1400 (вычет) = 400 руб. это «остаток» вычета) перешел на следующий месяц и учёлся в процессе расчета налога.
Если бы мы ошибочно полагали, что расчет НДФЛ происходит в каждом конкретном документе, или даже месяце, вне зависимости от того, что происходило в течение года в предыдущих месяцах, мы бы получили совсем другие результаты в каждом из рассмотренных примеров, а, следовательно, допустили бы ошибки при составлении отчетности.
Например, в последнем примере сумма НДФЛ составила бы:
(10000 – 1400) х 13% = 1118 руб.
Очевидно, что расхождение составляет уже 52 рубля.
Рассмотренные примеры наглядно отражают, как должен рассчитываться НДФЛ в соответствии с законодательством — нарастающим итогом с начала года. Именно такой порядок определен Налоговым кодексом РФ и именно он реализован в программах «1С».
В заключении хотелось бы отметить, что для проведения анализа данных, составления полной картины по расчету НДФЛ а также для поиска возможных учетных ошибок по НДФЛ в программах «1С» можно использовать отчет «Регистр налогового учета по НДФЛ». Он содержит полную информацию в отношении НДФЛ по конкретному сотруднику в рамках налогового периода и зачастую дает ответы на большинство вопросов, связанных с НДФЛ по сотруднику.
*https://its.1c.ru/db/taxndfl#content:591:hdoc

Нарастающий итог в 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 год).
Тестовые данные — создание таблиц и наполнение их данными — — простейший случай create table test_simple (dt date null, val int null ); — используем формат дат своей СУБД (или меняем настройки, напр. через NLS_DATE_FORMAT в Oracle) insert into test_simple (dt, val) values (‘2019-11-01’, 6); insert into test_simple (dt, val) values (‘2019-11-02’, 3); insert into test_simple (dt, val) values (‘2019-11-03’, 3); insert into test_simple (dt, val) values (‘2019-11-04’, 4); insert into test_simple (dt, val) values (‘2019-11-05’, 2); insert into test_simple (dt, val) values (‘2019-11-06’, 4); insert into test_simple (dt, val) values (‘2019-11-07’, 8); insert into test_simple (dt, val) values (‘2019-11-08’, 0); insert into test_simple (dt, val) values (‘2019-11-09’, 6); insert into test_simple (dt, val) values (‘2019-11-10’, 0); insert into test_simple (dt, val) values (‘2019-11-11’, 8); insert into test_simple (dt, val) values (‘2019-11-12’, 8); insert into test_simple (dt, val) values (‘2019-11-13’, 0); insert into test_simple (dt, val) values (‘2019-11-14’, 2); insert into test_simple (dt, val) values (‘2019-11-15’, 8); insert into test_simple (dt, val) values (‘2019-11-16’, 7); — случай с группами create table test_groups (grp varchar null, — varchar2(1) in Oracle dt date null, val int null ); — используем формат дат своей СУБД (или меняем настройки, напр. через NLS_DATE_FORMAT в Oracle) insert into test_groups (grp, dt, val) values (‘a’, ‘2019-11-06’, 1); insert into test_groups (grp, dt, val) values (‘a’, ‘2019-11-07’, 3); insert into test_groups (grp, dt, val) values (‘a’, ‘2019-11-08’, 4); insert into test_groups (grp, dt, val) values (‘a’, ‘2019-11-09’, 1); insert into test_groups (grp, dt, val) values (‘a’, ‘2019-11-10’, 7); insert into test_groups (grp, dt, val) values (‘b’, ‘2019-11-06’, 9); insert into test_groups (grp, dt, val) values (‘b’, ‘2019-11-07’, 10); insert into test_groups (grp, dt, val) values (‘b’, ‘2019-11-08’, 9); insert into test_groups (grp, dt, val) values (‘b’, ‘2019-11-09’, 1); insert into test_groups (grp, dt, val) values (‘b’, ‘2019-11-10’, 10); insert into test_groups (grp, dt, val) values (‘c’, ‘2019-11-06’, 4); insert into test_groups (grp, dt, val) values (‘c’, ‘2019-11-07’, 10); insert into test_groups (grp, dt, val) values (‘c’, ‘2019-11-08’, 9); insert into test_groups (grp, dt, val) values (‘c’, ‘2019-11-09’, 4); insert into test_groups (grp, dt, val) values (‘c’, ‘2019-11-10’, 4); — проверяем данные — select * from test_simple order by dt; select * from test_groups order by grp, dt;

1. Оконные функции

Оконные функции – вероятно, самый простой способ. В базовом случае (таблица без групп) мы рассматриваем данные, отсортированные по дате:
order by dt
… но нас интересуют только строки до текущей:
rows between unbounded preceding and current row
В конечном итоге, нам нужна сумма с этими параметрами:
sum(val) over (order by dt rows between unbounded preceding and current row)
А полный запрос будет выглядеть так:
select s.*, coalesce(sum(s.val) over (order by s.dt rows between unbounded preceding and current row), 0) as total from test_simple s order by s.dt;
В случае нарастающего итога по группам (поле grp) нам требуется только одна небольшая правка. Теперь мы рассматриваем данные как разделённые на «окна» по признаку группы:

Чтобы учесть это разделение необходимо использовать ключевое слово partition by :
partition by grp
И, соответственно, считать сумму по этим окнам:
sum(val) over (partition by grp order by dt rows between unbounded preceding and current row)
Тогда весь запрос преобразуется таким образом:
select tg.*, coalesce(sum(tg.val) over (partition by tg.grp order by tg.dt rows between unbounded preceding and current row), 0) as total from test_groups tg order by tg.grp, tg.dt;
Производительность оконных функций будет зависеть от специфики вашей СУБД (и её версии!), размеров таблицы, и наличия индексов. Но в большинстве случаев этот метод будет самым эффективным. Тем не менее, оконные функции недоступны в старых версиях СУБД (которые ещё в ходу). Кроме того, их нет в таких СУБД как Microsoft Access и SAP/Sybase ASE. Если необходимо вендоро-независимое решение, следует обратить внимание на альтернативы.

2. Подзапрос

Как было сказано выше, оконные функции были очень поздно введены в основных СУБД. Эта задержка не должна удивлять: в реляционной теории данные не упорядочены. Куда больше духу реляционной теории соответствует решение через подзапрос.
Такой подзапрос должен считать сумму значений с датой до текущей (и включая текущую): .
Что в коде выглядит так:
select s.*, (select coalesce(sum(t2.val), 0) from test_simple t2 where t2.dt <= s.dt) as total from test_simple s order by s.dt;
Чуть более эффективным будет решение, в котором подзапрос считает итог до текущей даты (но не включая её), а затем суммирует его со значением в строке:
select s.*, s.val + (select coalesce(sum(t2.val), 0) from test_simple t2 where t2.dt < s.dt) as total from test_simple s order by s.dt;
В случае нарастающего итога по нескольким группам нам необходимо использовать коррелированный подзапрос:
select g.*, (select coalesce(sum(t2.val), 0) as total from test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt) as total from test_groups g order by g.grp, g.dt;
Условие g.grp = t2.grp проверяет строки на вхождение в группу (что, в принципе, сходно с работой partition by grp в оконных функциях).

3. Внутреннее соединение

Поскольку подзапросы и джойны взаимозаменяемы, мы легко можем заменить одно на другое. Для этого необходимо использовать Self Join, соединив два экземпляра одной и той же таблицы:

select s.*, coalesce(sum(t2.val), 0) as total from test_simple s inner join test_simple t2 on t2.dt <= s.dt group by s.dt, s.val order by s.dt;
Как можно заметить, условие фильтрации в подзапросе t2.dt <= s.dt стало условием соединения. Кроме того, чтобы использовать агрегирующую функцию sum() нам необходима группировка по дате и значению group by s.dt, s.val.
Точно также можно сделать для случая с разными группами grp:
select g.*, coalesce(sum(t2.val), 0) as total from test_groups g inner join test_groups t2 on g.grp = t2.grp and t2.dt <= g.dt group by g.grp, g.dt, g.val order by g.grp, g.dt;

4. Декартово произведение

Раз уж мы заменили подзапрос на join, то почему бы не попробовать декартово произведение? Это решение потребует только минимальных правок:
select s.*, coalesce(sum(t2.val), 0) as total from test_simple s, test_simple t2 where t2.dt <= s.dt group by s.dt, s.val order by s.dt;
Или для случая с группами:
select g.*, coalesce(sum(t2.val), 0) as total from test_groups g, test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt group by g.grp, g.dt, g.val order by g.grp, g.dt;
Перечисленные решения (подзапрос, inner join, cartesian join) соответсвуют SQL-92 и SQL:1999, а потому будут доступны практически в любой СУБД. Основная проблема всех этих решений в низкой производительности. Это не велика беда, если мы материализуем таблицу с результатом (но ведь всё равно хочется большей скорости!). Дальнейшие методы куда более эффективны (с поправкой на уже указанные специфику конкретных СУБД и их версий, размер таблицы, индексы).

5. Рекурсивный запрос

Один из более специфических подходов – это рекурсивный запрос в common table expression. Для этого нам необходим «якорь» – запрос, возвращающий самую первую строку:
select dt, val, val as total from test_simple where dt = (select min(dt) from test_simple)
Затем к «якорю» с помощью union all присоединяются результаты рекурсивного запроса. Для этого можно опереться на поле даты dt, прибавляя у нему по одному дню:
select r.dt, r.val, cte.total + r.val from cte inner join test_simple r on r.dt = dateadd(day, 1, cte.dt) — + 1 день в SQL Server
Часть кода, добавляющая один день, не универсальна. Например, это r.dt = dateadd(day, 1, cte.dt) для SQL Server, r.dt = cte.dt + 1 для Oracle, и т.д.
Совместив «якорь» и основной запрос, мы получим окончательный результат:
with cte (dt, val, total) as (select dt, val, val as total from test_simple where dt = (select min(dt) from test_simple) union all select r.dt, r.val, cte.total + r.val from cte inner join test_simple r on r.dt = dateadd(day, 1, cte.dt) — r.dt = cte.dt + 1 в Oracle, и т.п. ) select dt, val, total from cte order by dt;
Решение для случая с группами будет ненамного сложнее:
with cte (dt, grp, val, total) as (select g.dt, g.grp, g.val, g.val as total from test_groups g where g.dt = (select min(dt) from test_groups where grp = g.grp) union all select r.dt, r.grp, r.val, cte.total + r.val from cte inner join test_groups r on r.dt = dateadd(day, 1, cte.dt) — r.dt = cte.dt + 1 в Oracle, и т.п. and cte.grp = r.grp ) select dt, grp, val, total from cte order by grp, dt;

6. Рекурсивный запрос с функцией row_number()

Предыдущее решение опиралось на непрерывность поля даты dt с последовательным приростом на 1 день. Мы избежать этого, используя оконную функцию row_number(), которая нумерует строки. Конечно, это нечестно – ведь мы собрались рассматривать альтернативы оконным функциям. Тем не менее, это решение может быть своего рода proof of concept: ведь на практике может быть поле, заменяющее номера строк (id записи). Кроме того, в SQL Server функция row_number() появилась раньше, чем была введена полноценная поддержка оконных функций (включая sum()).
Итак, для рекурсивного запроса с row_number() нам понадобится два СТЕ. В первом мы только нумеруем строки:
with cte1 (dt, val, rn) as (select dt, val, row_number() over (order by dt) as rn from test_simple)
… и если номер строки уже есть в таблице, то можно без него обойтись. В следующем запросе обращаемся уже к cte1:
cte2 (dt, val, rn, total) as (select dt, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.rn = cte2.rn + 1 )
А целиком запрос выглядит так:
with cte1 (dt, val, rn) as (select dt, val, row_number() over (order by dt) as rn from test_simple), cte2 (dt, val, rn, total) as (select dt, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.rn = cte2.rn + 1 ) select dt, val, total from cte2 order by dt;
… или для случая с группами:
with cte1 (dt, grp, val, rn) as (select dt, grp, val, row_number() over (partition by grp order by dt) as rn from test_groups), cte2 (dt, grp, val, rn, total) as (select dt, grp, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.grp, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.grp = cte2.grp and cte1.rn = cte2.rn + 1 ) select dt, grp, val, total from cte2 order by grp, dt;

7. Оператор CROSS APPLY / LATERAL

Один из самых экзотических способов расчёта нарастающего итога – это использование оператора CROSS APPLY (SQL Server, Oracle) или эквивалентного ему LATERAL (MySQL, PostgreSQL). Эти операторы появились довольно поздно (например, в Oracle только с версии 12c). А в некоторых СУБД (например, MariaDB) их и вовсе нет. Поэтому это решение представляет чисто эстетический интерес.
Функционально использование CROSS APPLY или LATERAL идентично подзапросу: мы присоединяем к основному запросу результат вычисления:
cross apply (select coalesce(sum(t2.val), 0) as total from test_simple t2 where t2.dt <= s.dt ) t2
… что целиком выглядит так:
select s.*, t2.total from test_simple s cross apply (select coalesce(sum(t2.val), 0) as total from test_simple t2 where t2.dt <= s.dt ) t2 order by s.dt;
Похожим будет и решение для случая с группами:
select g.*, t2.total from test_groups g cross apply (select coalesce(sum(t2.val), 0) as total from test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt ) t2 order by g.grp, g.dt;
Итого: мы рассмотрели основные платформо-независимые решения. Но остаются решения, специфичные для конкретных СУБД! Поскольку здесь возможно очень много вариантов, остановимся на нескольких наиболее интересных.

8. Оператор MODEL (Oracle)

Оператор MODEL в Oracle даёт одно из самых элегантных решений. В начале статьи мы рассмотрели общую формулу нарастающего итога:

MODEL позволяет реализовать эту формулу буквально один к одному! Для этого мы сначала заполняем поле total значениями текущей строки
select dt, val, val as total from test_simple
… затем рассчитываем номер строки как row_number() over (order by dt) as rn (или используем готовое поле с номером, если оно есть). И, наконец, вводим правило для всех строк, кроме первой: total = total + val.
Функция cv() здесь отвечает за значение текущей строки. А весь запрос будет выглядеть так:
select dt, val, total from (select dt, val, val as total from test_simple) t model dimension by (row_number() over (order by dt) as rn) measures (dt, val, total) rules (total = total + val) order by dt;

9. Курсор (SQL Server)

Нарастающий итог – один из немногих случаев, когда курсор в SQL Server не только полезен, но и предпочтителен другим решениям (как минимум до версии 2012, где появились оконные функции).
Реализация через курсор довольно тривиальна. Сначала необходимо создать временную таблицу и заполнить её датами и значениями из основной:
create table #temp (dt date primary key, val int null, total int null ); insert #temp (dt, val) select dt, val from test_simple order by dt;
Затем задаём локальные переменные, через которые будет происходить обновление:
declare @VarTotal int, @VarDT date, @VarVal int; set @VarTotal = 0;
После этого обновляем временную таблицу через курсор:
declare cur cursor local static read_only forward_only for select dt, val from #temp order by dt; open cur; fetch cur into @VarDT, @VarVal; while @@fetch_status = 0 begin set @VarTotal = @VarTotal + @VarVal; update #temp set total = @VarTotal where dt = @VarDT; fetch cur into @VarDT, @VarVal; end; close cur; deallocate cur;
И, наконец, получем нужный результат:
select dt, val, total from #temp order by dt; drop table #temp;

10. Обновление через локальную переменную (SQL Server)

Обновление через локальную переменную в SQL Server основано на недокументированном поведении, поэтому его нельзя считать надёжным. Тем не менее, это едва ли не самое быстрое решение, и этим оно интересно.
Создадим две переменные: одну для нарастающих итогов и табличную переменную:
declare @VarTotal int = 0; declare @tv table (dt date null, val int null, total int null );
Сначала заполним @tv данным из основной таблицы
insert @tv (dt, val, total) select dt, val, 0 as total from test_simple order by dt;
Затем табличную переменную @tv обновим, используя @VarTotal:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *