SQL RunningTotal

Question

我需要用Running Summary更新一个表。对于每个BusinessId，都有CalDate，并且没有标识列。此表将用于报告，并将每周截断和刷新。所以不确定创建identity col是否是一个好主意。该查询适用于第一个业务，但一旦业务发生变化，它就会变得一团糟。因此，请建议查询是不同的，还是应该有标识列。以下是示例代码：

    create table #Summary(
BusinessID int not null,
CalDate Date,
[ColA] [decimal](18, 4) NULL,
[ColB] [decimal](18, 4) NULL,
[ColC] [decimal](18, 4) NULL,
COLA_COLB_RunningSummary [decimal](18, 4) NULL,
COLA_COLC_RunningSummary [decimal](18, 4) NULL

)

INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(111, '2017-4-09', 17583.0000,  4.5301, 17717.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(111, '2017-4-10', 19979.0000,  12.3079 ,20282.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(111, '2017-4-11', 19475.0000,  4.1483  ,19726.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(222, '2017-4-09', 17583.0000,  6.9404, 17717.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(222, '2017-4-10', 14692.0000,  6.5387, 14840.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(222, '2017-4-11', 17727.0000,  10.7649 ,17977.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(333, '2017-4-09', 10731.0000,  7.3412, 10821.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(333, '2017-4-10', 7571.0000,   6.9404, 7660.0000)
INSERT INTO #Summary (BusinessID,CalDate,[ColA],[ColB] ,[ColC] ) VALUES
(333, '2017-4-11', 14692.0000,  6.5387, 14840.0000)

select * from #Summary

select BusinessID,CalDate,[ColA], [ColB], 
Sum([ColA] * [ColB]) OVER (order by BusinessID, Caldate rows unbounded preceding)  as COLA_COLB_RunningSummary
from #Summary

Answer 1

我想你是在找partition by

select BusinessID,CalDate,[ColA], [ColB], 
       Sum([ColA] * [ColB]) OVER (partition by BusinessID
                                  order by Caldate
                                 ) as COLA_COLB_RunningSummary
from #Summary;

这将重新启动每项业务的累计总和。

如果您想更新表，只需使用可更新的CTE：

with toupdate as (
      select s.*, 
             Sum([ColA] * [ColB]) OVER (partition by BusinessID
                                        order by Caldate
                                       ) as new_COLA_COLB_RunningSummary
             Sum([ColA] * [ColC]) OVER (partition by BusinessID
                                        order by Caldate
                                       ) as new_COLA_COLC_RunningSummary
      from #Summary s
     )
update toupdate
    set COLA_COLB_RunningSummary = new_COLA_COLB_RunningSummary,
        COLA_COLC_RunningSummary = new_COLA_COLC_RunningSummary;