改进 SQL 查询:随时间的累积量
假设我有一个奖项 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 添加索引,但它产生的差异非常小。
在我采用缓存等其他策略之前,是否有更有效的方法来编写运行总计计算的代码?
Suppose I have a SQL table of Awards, with fields for Date and Amount. I need to generate a table with a sequence of consecutive dates, the amount awarded in each day, and the running (cumulative) total.
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
This SQL works, but isn't as quick as I'd like:
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)
I tried adding an index to Awards.Date, but it made a very minimal difference.
Before I resort to other strategies like caching, is there a more efficient way to code the running total calculation?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我通常为此使用临时表:
如果您无法将日期列作为主键,那么您需要在
INSERT
语句中包含ORDER BY [Date]
。另外,这个问题之前已经被问过几次了。请参阅此处或搜索“sql 运行总计”。据我所知,我发布的解决方案仍然是性能最好的解决方案,而且也很容易编写。
I generally use a temporary table for this:
If you can't make the date column a primary key then you need to include an
ORDER BY [Date]
in theINSERT
statement.Also, this question's been asked a few times before. See here or search for "sql running total". The solution I posted is, as far as I know, still the one with the best performance, and also easy to write.
我面前没有数据库设置,所以我希望下面的第一个镜头可以工作。像这样的模式应该会导致更快的查询...您只需加入两次,类似的聚合量:
注意:我在顶部更改了您的表表达式,它遗漏了前一天...如果这是故意只是在其上加上一个 where 子句来排除它。如果这不起作用或不适合,请在评论中告诉我,我会做出任何调整。
I don't have a database setup in front of me so I hope the below works first shot. A pattern like this should result in a much speedier query...you're just joining twice, similar amount of aggregation:
Note: I changed your table expression up top, it was leaving out the first day before...if this is intentional just slap a where clause on this to exclude it. Let me know in the comments if this doesn't work or doesn't fit and I'll make whatever adjustments.
这是一个基于@Aaronaught 答案的可行解决方案。我在 T-SQL 中必须克服的唯一问题是 @RunningTotal 等不能为 null(需要转换为零)。
Here's a working solution based on @Aaronaught's answer. The only gotcha I had to overcome in T-SQL was that
@RunningTotal
etc. can't be null (need to be converted to zero).