sql - 每月重置的运行总计

Question

我的表结构如下:

declare  @TestTable as table
(
    id int,
    somedate date,
    somevalue int
)

insert into @TestTable values
(1,     '01/Jan/09',   10000),
(2,     '08/Jan/09',   -100),
(3,     '02/Feb/09',   -200),
(4,     '14/Feb/09',   -200),
(5,     '20/Feb/09',   -400),
(6,     '02/Mar/09',   -300),
(7,     '03/Feb/10',   -400),
(8,     '04/Feb/10',   -300),
(9,     '03/Mar/10',   -400),
(10,    '04/Mar/10',   -300)

我的代码:

select id,
      FORMAT(somedate,'dd.MM.yyyy') as   somedate ,
       somevalue,
       sum(somevalue) over(order by somedate) as run_tot ,     
       sum(somevalue) over( partition by MONTH(somedate) order by somedate) as m_run_tot

from @TestTable

所需输出:

somedate    somevalue   run_tot   m_run_tot   Required_output
01.01.2009  10000        10000       10000     10000
08.01.2009  -100          9900        9900     10000
02.02.2009  -200          9700       -200      9900     ----Prev month Running total
14.02.2009  -200          9500       -400      9900
20.02.2009  -400          9100       -800      9900     
02.03.2009  -300          8800       -300      9100    ---Prev month Running total

我需要一个每月重置来计算运行总数(以后我可以使用年度重置)？

Answer 1

出于性能原因，我强烈建议使用窗口函数执行此操作:

select id, somedate, somevalue, run_tot, m_run_tot,
       coalesce(max(prev_m_run_tot) over (partition by year(somedate), month(somedate)),
                first_value(m_run_tot) over (order by somedate)
               ) as required_output
from (select id, somedate, somevalue,
             sum(somevalue) over (order by somedate) as run_tot ,     
             sum(somevalue) over (partition by year(somedate), month(somedate) order by somedate) as m_run_tot,
             (case when row_number() over (partition by year(somedate), month(somedate) order by somedate) = 1
                   then sum(somevalue) over (order by somedate rows between unbounded preceding and 1 preceding)
              end) as prev_m_run_tot
      from TestTable
     ) t;

Here是一个db<> fiddle 。

逻辑是计算每个月第一个日期的运行总计，然后将其分摊到整个月。

请注意，这也修复了 partition by s 所以它们包括年和月。

您实际上可以通过减去两个累积和来在没有子查询的情况下执行此操作:

select id, somedate, somevalue,
       sum(somevalue) over (order by somedate) as run_tot ,     
       sum(somevalue) over (partition by year(somedate), month(somedate) order by somedate) as m_run_tot,
       (case when rank() over (order by year(somedate), month(somedate)) = 1
             then first_value(somevalue) over (order by somedate)
             else sum(somevalue) over (order by somedate) - sum(somevalue) over (partition by year(somedate), month(somedate) order by somedate)
        end) as required_output
from TestTable;

Here是这个版本的 db<>fiddle。