用 t-sql 統計資料

–一時興起純粹好玩,想用 t-sql 來計算用 MDX 常做的 累計到今、同期比較、成長率、遞移平均等運算

use tempdb
–drop table t
create table t([Year] int,[Month] int, value int)
insert t values
(99,1,1),(99,2,1),(99,3,1),(99,4,1),(99,5,1),(99,6,1),(99,7,1),(99,8,1),(99,9,1),(99,10,1),(99,11,1),(99,12,1),
(100,1,1),(100,2,2),(100,3,3),(100,4,4),(100,5,5),(100,6,6),(100,7,7),(100,8,8),(100,9,9),(100,10,10),(100,11,11),(100,12,12)
GO

WITH v
as
(select ROW_NUMBER() OVER(ORDER BY [Year],[Month]) RowNo,* FROM t)
select t1.[Year],t1.[Month],SUM(t2.value) [累計到今]
from v t1 join v t2 on t1.RowNo>=t2.RowNo
group by t1.[Year],t1.[Month]
go

select t2.[Year],t2.[Month],t2.value-t1.value [同期比較],LEFT(CONVERT(varchar(50),100.*(t2.value-t1.value)/t1.value),6)+ ‘%’ [成長率]
from t t1 join t t2 on t1.[Year]+1=t2.[Year] and t1.[Month]=t2.[Month]
GO

DECLARE @RollingAvg INT=3;
WITH v
as
(select ROW_NUMBER() OVER(ORDER BY [Year],[Month]) RowNo,* FROM t)
select t1.[Year],t1.[Month],Avg(t2.value) [遞移平均]
from v t1 join v t2 on t1.RowNo>=t2.RowNo AND t1.RowNo<t2.RowNo + @RollingAvg
group by t1.[Year],t1.[Month]

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: