在SQL查询中积累列
我有一个相当复杂的查询(大约需要30秒执行)返回我下面的数据集:在SQL查询中积累列
Month Buy Sell
2010/10 1 2
2010/11 1 3
2010/12 2 5
而这里的查询:
select month, avg(buy) [buy], avg(sell) [sell] from products group by month order by month
现在我想添加两个累积列,预期结果集如下:
Month Ac. Buy Ac. Sell
2010/10 1 2
2010/11 2 5
2010/12 4 10
我正在尝试使用此查询
select
distinct x.month
,(select SUM(buy) from products where month <= x.month) [Ac Buy]
,(select SUM(sell) from products where month <= x.month) [Ac Sell]
from products X
order by x.month
但是,这太长了!
有什么办法可以更快地做到这一点?
我正在使用MS SQL 2008服务器,但我的compability级别设置为80(如MSSQL 2000,我无法改变这一点)。所以我觉得我只驾驶一档法拉利。 );
对于13行,我只是将中间结果物化为表变量,然后做一个三角形连接。
DECLARE @Results TABLE
(
Month char(7) PRIMARY KEY,
Buy int,
Sell int
)
INSERT INTO @Results /*Your select query goes here*/
SELECT '2010/10',1,2 UNION ALL
SELECT '2010/11',1,3 UNION ALL
SELECT '2010/12',2,5
SELECT R1.Month,
R1.Buy,
R1.Sell,
SUM (R2.Sell)AS AcSell,
SUM (R2.Buy) AS AcBuy
FROM @Results R1
JOIN @Results R2 ON R2.Month <= R1.Month
GROUP BY R1.Month,
R1.Buy,
R1.Sell
谢谢马丁! 使用您的解决方案,基本查询需要00:27,最终结果为1:29。大部分时间是将数据插入@Results。 – vbandrade
说完看了这个,我想你可能从CTE中受益(假设你可以使用那些与COM级别设置为80 ....)
开始用一种CTE您收集的原始数据,然后将cte结果加入其自身以便能够求和平均值:
;with productsCTE
as
(
-- Original query!
select month, AVG(buy) buy, AVG(sell) sell
from products
group by mnth
)
select
p1.month,
p1.buy,
SUM(p2.buy) sumavgbuy,
p1.sell,
SUM(p2.sell) sumavgsell
from productsCTE p1
inner join productsCTE p2 on p2.month <= p1.month
group by p1.month,p1.buy,p1.sell
可以使用SQL Server 2000中的视图或派生表来模拟CTE,但是它们不会**先物化,所以自加入很可能最终会花费两次昂贵的聚合。 –
谢谢Jamien!使用您的解决方案,基本查询需要00:27,最终结果为1:47。非常接近马丁的解决方案。我接受他的回答,因为他第一回答。 – vbandrade
@vbandrade - 这很有趣,与我之前的评论完全矛盾!在这种情况下,SQL Server是否为计划添加了一个假脱机程序? –
从第一个查询返回的数据集有多大,但没有运行总数?另外什么是“月”列的数据类型? –
尝试使用谷歌搜索“sqlserver running sum” – Andomar
Monht是一个varchar列,但如果datetime使它更快,我会为它付出。 第一次查询只返回13行,但原始表有近2MM记录。我正在使用这个小组来进行一些现金流动。 – vbandrade