[SQL SERVER][TSQL]如何計算累加值
今天同事詢問如何使用TSQL計算累加值，不多說直接看圖說故事比較快…XD
>>>>> 
原始資料 想要結果
為了達到右圖結果並維持一定的效能，我大概測試比較四種作法在效能上的差異(沒有相關索引情況下)
1.inner join
declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE();
;with cteA(rownumber,c1,c2)
as
(
select row_number() over(order by c2,c1 desc) as 'row',c1,c2
from ##mytable
)
--inner join
SELECT a.rownumber,a.c1,a.c2,sum(b.c2) as '累加量'
from cteA a
inner join cteA b
on b.rownumber <=a.rownumber
group by a.rownumber,a.c1,a.c2
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
花費時間(ms)= 63
執行計畫
整體估計成本=0.051021
ps:自己 join 自己 處理
2.Sub Query
先清除Buffers
dbcc dropcleanbuffers
declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE();
;with cteA(rownumber,c1,c2)
as
(
select row_number() over(order by c2,c1 desc) as 'row',c1,c2
from ##mytable
)
--sub query
select a.rownumber,a.c1,a.c2, a.c2 +
isnull ( (select sum(b.c2) from cteA b where b.rownumber < a.rownumber) ,0) as '累加量'
from cteA a
order by a.rownumber
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
花費時間(ms)= 76
整體估計成本=0.0522315
ps:利用子查詢處理不如使用 join
3.Loop Without Cursor(我不考慮使用Cursor)
先清除Buffers
dbcc dropcleanbuffers
-暫存表
declare @tmp table
(
rownumber int,
c1 varchar(10),
c2 int
)
--結果表
declare @result table
(
rownumber int,
c1 varchar(10),
c2 int,
total int default(0)
)
declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE()
;with cteA(rownumber,c1,c2)
as
(
select row_number() over(order by c2,c1 desc) as 'rownumber',c1,c2 from ##mytable
)
--將來源資料塞入暫存表
insert into @tmp
select * from cteA t1
declare @maxcount int,@step int,@current int,@total int,@c1 varchar(10)
select @maxcount=count(1) from @tmp
set @step=1;set @total=0;
while(@step <= @maxcount)
begin
select @c1=c1,@current=c2 from @tmp
where rownumber=@step
--取得累加值
set @total=@total+@current
--新增結果表
insert into @result
select @step,@c1,@current,@total
set @step=@step+1
end
--結果
select t1.rownumber,t1.c1,t1.c2,t1.total as '累加量'
from @result t1
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
花費時間(ms)= 204
整體估計成本=0.0032831+..(僅列出部分)
ps:該方法會重複 select , insert 邏輯和實體作業，直到離開迴圈為止，
如果資料量很大可想而知對效能上的衝擊。
4.在 Update 中直接使用計算值
先清除Buffers
dbcc dropcleanbuffers
--結果表
declare @result table
(
rownumber int,
c1 varchar(10),
c2 int,
total int default(0)
)
declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE()
;with cteA(rownumber,c1,c2)
as(
select row_number() over(order by c2,c1 desc) as 'rownumber',c1,c2 from ##mytable
)
--將來源資料塞入結果表
insert into @result
select cteA.*,0 from cteA
declare @total int
set @total=0
update @result
--設定變數與資料行相同的值
set @total = total = @total + c2
--結果
select t1.rownumber,t1.c1,t1.c2,t1.total as '累加量'
from @result t1
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
花費時間(ms)= 64
整體估計成本=0.0132843+..(僅列出部分)
ps:該方法類似整批處理
測試結果總表
可以看到 Loop Without Cursor 這方法的效能最差，其餘方法所花費時間大同小異，
個人認為 inner join 和 在 Update 中直接使用計算值 這兩個方法效能較佳(依資料量大小選擇使用)，
當然如果你有更好的方法還請告訴我，謝謝。