时间:2020-06-09来源:www.pcxitongcheng.com作者:电脑系统城
个人示例例子
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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 |
执行结果
2023-02-28
解决linux不识别nfst格式u盘问题技巧 linux无法识别ntfs格式U盘怎么办?2023-02-28
linux扩容swap分区的技巧 linux怎么调整swap大小?2023-02-26
微软Linux资源库首次加入Edge浏览器稳定版当linux在运作时不能直接关闭电源容易将档案系统损毁,因此需要用shutdown以安全的方式关闭,那么这个操作该怎么实现呢?下面就为大家带来了详细教程。...
2022-11-07
由于linux系统和我们常用的windows系统是不一样的,所以如果是初学者,可能会不知道linux怎么强制删除文件,其实我们只要打开终端,使用命令就可以删除了。...
2022-11-03