sql - 改进 SQL 查询 : Cumulative amounts over time

Question

假设我有一个奖项的 SQL 表，其中包含日期和金额字段。我需要生成一个表格，其中包含一系列连续日期、每天奖励的金额以及运行(累计)总数。

Date         Amount_Total   Amount_RunningTotal
----------   ------------   -------------------
1/1/2010              100                   100
1/2/2010              300                   400
1/3/2010                0                   400
1/4/2010                0                   400
1/5/2010              400                   800
1/6/2010              100                   900
1/7/2010              500                  1400
1/8/2010              300                  1700

这个 SQL 有效，但没有我想要的那么快:

Declare @StartDate datetime, @EndDate datetime 
Select @StartDate=Min(Date), @EndDate=Max(Date) from Awards 

; With 

/* Returns consecutive from numbers 1 through the 
number of days for which we have data */
Nbrs(n) as (
   Select 1 Union All 
   Select 1+n 
   From Nbrs 
   Where n<=DateDiff(d,@StartDate,@EndDate)),

/* Returns all dates @StartDate to @EndDate */
AllDays as (
   Select Date=DateAdd(d, n, @StartDate) 
   From Nbrs ) 

/* Returns totals for each day */
Select 
 d.Date,
 Amount_Total = (
        Select Sum(a.Amount) 
        From Awards a 
        Where a.Date=d.Date),
 Amount_RunningTotal = (
        Select Sum(a.Amount) 
        From Awards a 
        Where a.Date<=d.Date)
From AllDays d
Order by d.Date 
Option(MAXRECURSION 1000)

我尝试向Awards.Date 添加一个索引，但它产生的差异很小。

在我求助于缓存等其他策略之前，是否有更有效的方法来编写运行总计算？

Answer 1

我通常为此使用临时表:

DECLARE @Temp TABLE
(
    [Date] date PRIMARY KEY,
    Amount int NOT NULL,
    RunningTotal int NULL
)

INSERT @Temp ([Date], Amount)
    SELECT [Date], Amount
    FROM ...

DECLARE @RunningTotal int

UPDATE @Temp
SET @RunningTotal = RunningTotal = @RunningTotal + Amount

SELECT * FROM @Temp

如果您不能将日期列设为主键，那么您需要包含一个 ORDER BY [Date]在 INSERT陈述。

另外，这个问题之前已经被问过几次了。见 here或搜索“sql running total”。我发布的解决方案，据我所知，仍然是性能最好的一个，也很容易编写。