统计函数应用之SQL 分析函数

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                       Copyright (c) Microsoft Corporation

                       Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

1.[AVG(字段) over(PARTITION BY 分组字段 order by 逐层字段)]

2.NTILE函数  分组评优

3.语法：CUME_DIST( )   OVER ( [ partition_by_clause] order_by_clause )    

目录:

• 一、问题描述
• 二、实现思路
• 三、示例

---------------------------------------------------------------------------

一、问题描述编辑

对于一些用户而言，有时候需要对原始数据分组求每层(包含)所处前N层的平均值来与当月数据做对比分析，称求【逐层平均值】。遇到类似需求时候，该如何处理呢？如图所示：

 

二、实现思路编辑

在创建数据集时用sql的开窗排名函数[AVG(字段) over(PARTITION BY 分组字段 order by 逐层字段)]处理，然后进行直接调用。

 

1. SELECT t.*,AVG(t.销量) over(PARTITION BY t.年份 order by t.月份) as '前N月平均销量'  FROM 大陆车辆销售 t;      

三、示例编辑

3.1 初始数据

 

打开软件自带的FRDemo库中的【大陆汽车销售】表：

 

3.2 逐层平均值sql脚本

1. SELECT t.*,AVG(t.销量) over(PARTITION BY t.年份 order by t.月份) as '前N月平均销量'  FROM 大陆车辆销售 t;      

3.3 效果预览

执行SQL脚本，效果如下：

————————————————————————————————————————————————————

2.有时我们在设计报表的过程中，可能会遇到这样的需求：将数据按照某一字段平均分组，比如一张成绩表（100人）按某一科目成绩（高低）顺序分为4组，将第一组-优，第二组-良，第三组-较差，第四组-不及格 的情况。像这样需要将一个有序整体按照一定的比例划分数据的情况。

一般思路：先将成绩按照一定顺序排名后按名次平均分为4组，1-25名为第一组，26-50为第二组，51-75为第三组，76-100为第四组，最后给对应所在组评级。这种实现思路太过于繁琐，实现起来也较难！今天分享一个快速实现的函数【NTILE函数】，使用它可以起到事半功倍的效果。

2. 思路编辑

在数据集中用sql的【NTILE (N) OVER(ORDER BY 字段)】函数处理，然后进行直接调用。

注：NTILE(Num)，Num为要等分的组数（量）。

语句结构：

1. CASE NTILE (N) OVER (ORDER BY GRADE DESC)  
2. WHEN 1 THEN  
3.     'A'  
4. WHEN 2 THEN  
5.     'B'  
6. WHEN 3 THEN   
7.         'C'  
8. WHEN 4 THEN  
9.     'D'  
10.    . . .  
11. WHEN N THEN  
12.     'X'  
13. END  

 

3. 示例编辑

3.1 准备数据

打开SQL Server库中的【STSCORE】表：

 

3.2 NTILE (N)实现的Sql

 

 

 

1. SELECT  
2.     STUDENTNO AS 学号,  
3.     GRADE AS 科目,  
4.     CASE NTILE (4) OVER (ORDER BY GRADE DESC)  
5. WHEN 1 THEN  
6.     '优'  
7. WHEN 2 THEN  
8.     '良'  
9. WHEN 3 THEN '较差'  
10. WHEN 4 THEN  
11.     '不及格'  
12. END AS '级别'  
13. FROM  
14.     "STSCORE"  
15. WHERE  
16.     COURSE = 'Chemistry'  
17. ORDER BY  
18.     STUDENTNO;  

 

注：case when then else end 用法请参考：CASE用法。

 

3.3 效果预览

 

 

执行SQL脚本，效果如下：

——————————————————————————————————————————————————————

3. 函数介绍编辑

 

分析函数CUME_DIST()：–CUME_DIST 小于等于当前值的行数/分组内总行数  

语法：CUME_DIST( )   OVER ( [ partition_by_clause] order_by_clause )    

解释：通过 partition_by_clause 将划分为分区函数应用到的 FROM 子句生成的结果集。 如果未指定，则此函数将查询结果集的所有行视为单个组。 order_by_clause 确定在其中执行该操作的逻辑顺序。 order_by_clause 是必需的。  

返回类型：CUME_DIST 返回的值范围大于 0 并小于或等于 1的数值。 

 

4. 示例编辑

 

4.1 准备数据

 

使用以下SQL构建表：

 

1. -- ----------------------------  
2. -- Table structure for ZSH_170222  
3. -- ----------------------------  
4. DROP TABLE [dbo].[ZSH_170222]  
5. GO  
6. CREATE TABLE [dbo].[ZSH_170222] (  
7. [PART] varchar(255) NULL ,  
8. [NAME_C] varchar(255) NULL ,  
9. [PAY] int NULL   
10. )  
11. GO  
12. -- ----------------------------  
13. -- Records of ZSH_170222  
14. -- ----------------------------  
15. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'小明', N'9741')  
16. GO  
17. GO  
18. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'小兰', N'6908')  
19. GO  
20. GO  
21. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'李东', N'6336')  
22. GO  
23. GO  
24. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'杨澜', N'9089')  
25. GO  
26. GO  
27. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'黄伟', N'1646')  
28. GO  
29. GO  
30. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'赵丽', N'4486')  
31. GO  
32. GO  
33. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'张军', N'3538')  
34. GO  
35. GO  
36. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘伟', N'2143')  
37. GO  
38. GO  
39. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'张强', N'6522')  
40. GO  
41. GO  
42. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄渤', N'1247')  
43. GO  
44. GO  
45. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'赵丽', N'7975')  
46. GO  
47. GO  
48. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘东', N'2990')  
49. GO  
50. GO  
51. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'张伟', N'4266')  
52. GO  
53. GO  
54. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄俊', N'4815')  
55. GO  
56. GO  
57. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄伟', N'7788')  
58. GO  
59. GO  
60. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘康', N'4605')  
61. GO  
62. GO  
63. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘冰', N'6184')  
64. GO  
65. GO  

 

4.2 使用分析函数进行分析

 

执行sql：

 

1. SELECT  
2.     part,  
3.     name_c,  
4.     pay,  
5.   
6.   CUME_DIST () OVER (ORDER BY pay) AS cat_1,  
7.     CUME_DIST () OVER (PARTITION BY part ORDER BY pay) AS cat_2  
8. FROM  
9.     ZSH_170222  
10. ORDER BY  
11.     part,  
12.     pay    

结果预览与分析：

Cat_1: 没有PARTITION BY ，在整个公司里分析 

所有数据均为1组，总行数为17  

第一行（黄伟）：小于等于1646的行数为2，因此，2/17= 0.117647058823529  

第二行（张军）：小于等于3538的行数为5，因此，5/17= 0.294117647058824  

第三行（赵丽）：小于等于3538的行数为7，因此，7/17= 0.411764705882353  

…  

第十七行（赵丽君）：小于等于7975的行数为15，因此，15/17= 0.882352941176471  

  

Cat_2: 按照部门（技术部/综合部）分析

技术组的行数为7,  

第一行（黄伟）：小于等于1646的行数为1，因此，1/7= 0.142857142857143  

第二行（张军）：小于等于3538的行数为2，因此，2/7= 0.285714285714286  

…  

第七行（小明）：小于等于9741的行数为7，因此，7/7= 1  

综合部的行数为10,  

第一行（黄渤）：小于等于1247的行数为1，因此，1/10= 0.1  

第二行（刘伟）：小于等于2142的行数为2，因此，2/10= 0.2  

…  

 

第十行（赵丽君）：小于等于7975的行数为10，因此，10/10= 1