sql - 每月重置的运行总计

我的表结构如下: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_totfrom @TestTable所需输出:somedate somevalue run_tot m_run_tot Required_output01.01.2009 10000 10000 10000 1000008.01.2009 -100 9900 9900 1000002.02.2009 -200 9700 -200 9900 ----Prev month Running total14.02.2009 -200 9500 -400 990020.02.2009 -400 9100 -800 990002.03.2009 -300 8800 -300 9100 ---Prev month Running total我需要通过每月重置来计算运行总数(稍后我可以使用年度重置)？ 最佳答案 出于性能原因，我强烈建议使用窗口函数执行此操作: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_outputfrom (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 是一个 dbfiddle。逻辑是计算每个月第一个日期的运行总计，然后将其分摊到整个月。请注意，这也修复了 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_outputfrom TestTable; Here 是这个版本的 dbfiddle。关于sql - 每月重置的运行总计，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59818047/