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