Sql Server Sum函數的特殊使用

利用Sql Server的Sum函數開窗得到累計值

具體詳解https://www.cnblogs.com/zhaoshujie/p/9594676.html

個人示例例子 

DECLARE @Sale Table
(--年份			月份			銷售總額
  [Year]  INT,  [Month] INT,  TotalSales Float		
)
insert  INTO @Sale
SELECT  2016,	1,	100 UNION ALL SELECT  2016,	2,	200 UNION ALL
SELECT  2016,	3,	300 UNION ALL SELECT  2016,	4,	400 UNION ALL
SELECT  2016,	5,	500 UNION ALL SELECT  2016,	6,	600 UNION ALL
SELECT  2016,	7,	650 UNION ALL SELECT  2016,	8,	500 UNION ALL
SELECT  2016,	9,	450 UNION ALL SELECT  2016,	10,	400 UNION ALL
SELECT  2016,	11,	300 UNION ALL SELECT  2016,	12,	400 UNION ALL
SELECT  2017,	1,	500 UNION ALL SELECT  2017,	2,	400 UNION ALL
SELECT  2017,	3,	500 UNION ALL SELECT  2017,	4,	400 UNION ALL
SELECT  2017,	5,	500 UNION ALL SELECT  2017,	6,	400 UNION ALL
SELECT  2017,	7,	500 UNION ALL SELECT  2017,	8,	400 UNION ALL
SELECT  2017,	9,	500 UNION ALL SELECT  2017,	10,	400 UNION ALL
SELECT  2017,	11,	500 UNION ALL SELECT  2017,	11,	400;	--註意2017有2條11月份重覆數據
select [YEAR], [Month], TotalSales, 
	SUM(TotalSales)over() as [16-17銷售總額],		
	--將所有的記錄的TotalSales加起來的總和
	--100+200+300+400+500+600+650+500+450+400+300+400+500+400+500+400+500+400+500+400+500+400+500+400 = 10200
	

	--【未使用 partition by 指定分區示例】-------------------------------------------------------------
	sum(TotalSales) over(order by [YEAR]) [列1],
	--按照Year排序，計算同Year的總值與前面排序的總值之和，等同於[列3]
	sum(TotalSales) over(order by [YEAR],[Month]) [列2],
	--按照Year，Month排序，計算同Year，Month的總值與前面排序的總值之和,等同於[列4]
	sum(TotalSales) over(order by [YEAR] RANGE BETWEEN unbounded preceding AND CURRENT ROW) [列3],
	--RANGE BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值範圍為 當前行與當前行前面的所有行的值
	--	Year[2016]值為4800, Year[2017]值為其5400+TValue[2016]【4800】為10200
	sum(TotalSales) over(order by [YEAR],[Month] RANGE BETWEEN unbounded preceding AND CURRENT ROW) [列4],
	--按照Year,Month排序，累加當前行和前面所有行的值寫入到同Year,Month中【註意，和列6中23，24的差異】
	sum(TotalSales) over(order by [YEAR] rows  BETWEEN unbounded preceding AND CURRENT ROW)[列5],
	--rows BETWEEN unbounded preceding AND CURRENT ROW  表示指定取值範圍為 當前行與當前行前面的所有行的值。
	--按照Year排序，把當前行的值和前面所有行的值累加
	sum(TotalSales) over(order by [YEAR],[Month] rows  BETWEEN unbounded preceding AND CURRENT ROW)[列6],
	--按照Year,Month排序，當前行的值為  當前行和排序後前面所有的行的總值，【註意，和列4中 23，24行的差異】
	sum(TotalSales) over(order by [YEAR] rows BETWEEN 1 preceding AND 2 following) [列7],
	--rows BETWEEN 1 preceding AND 2 following 表示指定取值範圍為 當前行與前一行和後兩行的值
	--即第一行的值為：600=100+200+300　　第二行的值為：1000=100+200+300+400　　第三行的值為：1400=200+300+400+500
	sum(TotalSales) over(order by [YEAR] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) [列8],
	--ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示指定取值範圍為 當前行與前一行的值
	--即第一行的值為：100=100　　第二行的值為：300=100+200　　第三行的值為：500=300+200


	--【使用 partition by 指定分區示例】--------------------------------------------------------------
	SUM(TotalSales)over(PARTITION BY [YEAR])[年銷售額],
	--按照Year進行分區，將同Year的TotalSales累積並寫入到同Year的行中
	--2016區間：100+200+300+400+500+600+650+500+450+400+300+400 = 4800
	--2017區間：500+400+500+400+500+400+500+400+500+400+500+400=5400
	SUM(TotalSales)over(PARTITION BY [YEAR],[Month])[月銷售額],
	--按照Year,Month分區， Year,Month組成的只有1條數據，值為TotalSales

	sum(TotalSales) over(partition by [YEAR] order by [YEAR]) [列09],
	--按照Year分區，2016為1個區間，2017為1個區間， 按照Year排序， 當前行值為同Year的總值	--和前面Year的總值[沒有前面Year]
	sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month]) [列10],
	--按照Year分區，2016為1個區間，2017為1個區間， 按照Year，Month排序排序，同Year,Month的值為[排序後前面同Year,Month的總值]加上[當前Year,Month的總值]

	sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] RANGE BETWEEN unbounded preceding AND CURRENT ROW) [列11],
	--按照Year分區，2016為1個區間，2017為1個區間， 以[YEAR],[Month]排序， 同Year,Month的值為[排序後前面同Year,Month的總值]加上[當前Year,Month的總值]
	sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] rows  BETWEEN unbounded preceding AND CURRENT ROW) [列12],
	--按照Year分區， 以[YEAR],[Month]排序，當前行的值為當前行和前面所有行的總值, 註意23，24行與列11的比較
	sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] rows BETWEEN 1 preceding AND 2 following) [列13],
	--按照Year分區， 以[YEAR],[Month]排序，當前行的值為當前行前面1行到當前行後面2行的總值
	sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) [列14]
	--按照Year分區， 以[YEAR],[Month]排序，當前行的值為當前行前面1行到當前行的值

	--ROWS與RANGE之間的區別看[列4]與[列5]，[列11]與[列12]中第23，24的值差異
from @Sale

執行結果