文档中心 > 云数据库 RDS 版

云数据库 RDS 版

解析SQL Server 2012常用的分析函数

更新时间： 访问次数：1061

分析函数CUME_DIST

微软的定义：

计算某个值在SQL Server 2012中的一组值内的累积分布。CUME_DIST计算某指定值在一组值中的相对位置。对于行r，假定采用升序，r的CUME_DIST是值低于或等于r的值的行数除以在分区或查询结果集中求出的行数。

函数解析：

执行如下代码，构造一组数据。

 
 
    
  
  
  1. DECLARE
    2. 
  
  
  2.     @analytic TABLE( 
    3. 
  
  
  3.         name varchar(35) ,
    4. 
  
  
  4.         dept varchar(35),
    5. 
  
  
  5.         salary money
    6. 
  
  
  6.     )
    7. 
  
  
  7. INSERT INTO @analytic 
    8. 
  
  
  8.         VALUES
    9. 
  
  
  9. --bd
    10. 
  
  
  10. ('andy01','bd',15000),
    11. 
  
  
  11. ('andy02','bd',12000),
    12. 
  
  
  12. ('andy03','bd',12000),
    13. 
  
  
  13. ('andy04','bd',10000),
    14. 
  
  
  14. ('andy05','bd',8000),
    15. 
  
  
  15. --ca
    16. 
  
  
  16. ('andy06','ca',20000),
    17. 
  
  
  17. ('andy07','ca',18000),
    18. 
  
  
  18. ('andy08','ca',18000),
    19. 
  
  
  19. ('andy09','ca',15000),
    20. 
  
  
  20. ('andy10','ca',12000),
    21. 
  
  
  21. ('andy11','ca',12000),
    22. 
  
  
  22. ('andy12','ca',10000),
    23. 
  
  
  23. ('andy13','ca',8000),
    24. 
  
  
  24. ('andy14','ca',8000),
    25. 
  
  
  25. ('andy15','ca',8000)
    26. 
  
  
  26. 
  
  
  27. SELECT 
    28. 
  
  
  28.     dept,name ,salary,
    29. 
  
  
  29.     CUME_DIST() OVER(PARTITION BY dept ORDER BY salary)  AS cume_dist_ 
    30. 
  
  
  30. FROM @analytic
    31. 
  
  
  31. ORDER BY dept,salary DESC
    32.

返回结果如下：

示例解析：

按照dept分组，根据salary逻辑排序，针对每一个分组里的每一个值，计算在该分组下等于或者小于自己的salary的分布的百分比。举个例子，bd部门的andy02，salary为12000，那么等于或者小于这个12000的有4条，总共5条记录，因此那么CUME_DIST()=4/5=0.8。 同理，其它也是这样计算。

分析函数LAST_VALUE

微软的定义：

返回SQL Server 2012中有序值集中的最后一个值。

函数解析：

执行如下代码，构造一组数据。

 
 
    
  
  
  1. DECLARE
    2. 
  
  
  2.     @analytic TABLE( 
    3. 
  
  
  3.         name varchar(35) ,
    4. 
  
  
  4.         dept varchar(35),
    5. 
  
  
  5.         salary money ,
    6. 
  
  
  6.         hiredate date
    7. 
  
  
  7.     )
    8. 
  
  
  8. INSERT INTO @analytic 
    9. 
  
  
  9.         VALUES
    10. 
  
  
  10. --bd
    11. 
  
  
  11. ('andy01','bd',15000,'2002-01-09'),
    12. 
  
  
  12. ('andy02','bd',12000,'2003-01-09'),
    13. 
  
  
  13. ('andy03','bd',12000,'2003-02-09'),
    14. 
  
  
  14. ('andy04','bd',10000,'2005-05-09'),
    15. 
  
  
  15. ('andy05','bd',8000,'2003-06-09'),
    16. 
  
  
  16. --ca
    17. 
  
  
  17. ('andy06','ca',20000,'2003-01-09'),
    18. 
  
  
  18. ('andy07','ca',18000,'2005-02-09'),
    19. 
  
  
  19. ('andy08','ca',18000,'2005-03-09'),
    20. 
  
  
  20. ('andy09','ca',15000,'2004-01-09'),
    21. 
  
  
  21. ('andy10','ca',12000,'2003-06-09'),
    22. 
  
  
  22. ('andy11','ca',12000,'2002-09-09'),
    23. 
  
  
  23. ('andy12','ca',10000,'2003-07-09'),
    24. 
  
  
  24. ('andy13','ca',8000,'2003-08-09'),
    25. 
  
  
  25. ('andy14','ca',8000,'2003-11-09'),
    26. 
  
  
  26. ('andy15','ca',8000,'2003-01-09')
    27. 
  
  
  27. 
  
  
  28. SELECT 
    29. 
  
  
  29.     dept,name ,salary,hiredate,
    30. 
  
  
  30.     LAST_VALUE(hiredate) OVER(PARTITION BY dept ORDER BY salary)  AS last_value_ 
    31. 
  
  
  31. FROM @analytic
    32.

返回结果如下：

示例解析：

按照OVER子句中ORDER BY根据salary排序，取salary最后行的hiredate值作为最后的LAST VALUE，重点在于当salary有相同的值时，需要取根据salary排序后的最后一条记录作为其他的LAST VALUE。

分析函数FIRST_VALUE

微软的定义：

返回SQL Server 2012中有序值集中的第一个值。

函数解析：

从微软的定义来看，FIRST_VALUE似乎跟LAST_VALUE是相反的含义，但实际并非如此。

执行如下代码，构造一组数据。

 
 
    
  
  
  1. DECLARE
    2. 
  
  
  2.     @analytic TABLE( 
    3. 
  
  
  3.         name varchar(35) ,
    4. 
  
  
  4.         dept varchar(35),
    5. 
  
  
  5.         salary money ,
    6. 
  
  
  6.         hiredate date
    7. 
  
  
  7.     )
    8. 
  
  
  8. INSERT INTO @analytic 
    9. 
  
  
  9.         VALUES
    10. 
  
  
  10. --bd
    11. 
  
  
  11. ('andy01','bd',15000,'2002-01-09'),
    12. 
  
  
  12. ('andy02','bd',12000,'2003-01-09'),
    13. 
  
  
  13. ('andy03','bd',12000,'2003-02-09'),
    14. 
  
  
  14. ('andy04','bd',10000,'2005-05-09'),
    15. 
  
  
  15. ('andy05','bd',8000,'2003-06-09'),
    16. 
  
  
  16. --ca
    17. 
  
  
  17. ('andy06','ca',20000,'2003-01-09'),
    18. 
  
  
  18. ('andy07','ca',18000,'2005-02-09'),
    19. 
  
  
  19. ('andy08','ca',18000,'2005-03-09'),
    20. 
  
  
  20. ('andy09','ca',15000,'2004-01-09'),
    21. 
  
  
  21. ('andy10','ca',12000,'2003-06-09'),
    22. 
  
  
  22. ('andy11','ca',12000,'2002-09-09'),
    23. 
  
  
  23. ('andy12','ca',10000,'2003-07-09'),
    24. 
  
  
  24. ('andy13','ca',8000,'2003-08-09'),
    25. 
  
  
  25. ('andy14','ca',8000,'2003-11-09'),
    26. 
  
  
  26. ('andy15','ca',8000,'2003-01-09')
    27. 
  
  
  27. 
  
  
  28. SELECT 
    29. 
  
  
  29.     dept,name ,salary,hiredate,
    30. 
  
  
  30.     FIRST_VALUE(name) OVER(PARTITION BY dept ORDER BY salary)  AS first_value_ 
    31. 
  
  
  31. FROM @analytic
    32.

返回结果如下：

示例分析：

显然，这个与LAST_VALUE并不是相反的含义。OVER子句根据ORDER BY来排序，按dept分组来确定这个分组的第一个值，而不是根据salary的值来确定的，所以与LAST_VALUE是不一样的。将FIRST_VALUE(name)修改为FIRST_VALUE(hiredate)后，对比看得更清楚，这个很有蒙蔽性。

分析函数LEAD

微软的定义：

访问相同结果集的后续行中的数据，而不使用SQL Server 2012中的自联接。LEAD以当前行之后的给定物理偏移量来提供对行的访问。在SELECT语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。

函数解析：

执行如下代码，构造一组数据。

 
 
    
  
  
  1. DECLARE
    2. 
  
  
  2.     @analytic TABLE( 
    3. 
  
  
  3.         name varchar(35) ,
    4. 
  
  
  4.         dept varchar(35),
    5. 
  
  
  5.         salary money ,
    6. 
  
  
  6.         hiredate date
    7. 
  
  
  7.     )
    8. 
  
  
  8. INSERT INTO @analytic 
    9. 
  
  
  9.         VALUES
    10. 
  
  
  10. --bd
    11. 
  
  
  11. ('andy01','bd',15000,'2002-01-09'),
    12. 
  
  
  12. ('andy02','bd',12000,'2003-01-09'),
    13. 
  
  
  13. ('andy03','bd',12000,'2003-02-09'),
    14. 
  
  
  14. ('andy04','bd',10000,'2005-05-09'),
    15. 
  
  
  15. ('andy05','bd',8000,'2003-06-09'),
    16. 
  
  
  16. --ca
    17. 
  
  
  17. ('andy06','ca',20000,'2003-01-09'),
    18. 
  
  
  18. ('andy07','ca',18000,'2005-02-09'),
    19. 
  
  
  19. ('andy08','ca',18000,'2005-03-09'),
    20. 
  
  
  20. ('andy09','ca',15000,'2004-01-09'),
    21. 
  
  
  21. ('andy10','ca',12000,'2003-06-09'),
    22. 
  
  
  22. ('andy11','ca',12000,'2002-09-09'),
    23. 
  
  
  23. ('andy12','ca',10000,'2003-07-09'),
    24. 
  
  
  24. ('andy13','ca',8000,'2003-08-09'),
    25. 
  
  
  25. ('andy14','ca',8000,'2003-11-09'),
    26. 
  
  
  26. ('andy15','ca',8000,'2003-01-09')
    27. 
  
  
  27. 
  
  
  28. SELECT 
    29. 
  
  
  29.     dept,name,hiredate,salary,
    30. 
  
  
  30.     LEAD(salary,1,0) OVER(PARTITION BY dept ORDER BY salary) AS lead_,
    31. 
  
  
  31.     (LEAD(salary,1,0) OVER(PARTITION BY dept ORDER BY salary)-salary) AS diff_salary 
    32. 
  
  
  32. FROM @analytic
    33.

返回结果如下：

示例分析：

按照dept分区，根据salary排序，比较当前记录和后一条记录（偏移量为1）的salary值的差值，这个非常实用。

FAQ

关于此文档暂时还没有FAQ
有用(0) 我要提问
返回
顶部
阿里巴巴集团 | 淘宝网 | 天猫 | 聚划算 | 全球速卖通 | 阿里巴巴国际交易市场 | 1688 | 阿里妈妈 | 阿里旅行·去啊 | 阿里云计算 | YunOS | 阿里通信 | 万网 | 高德 | 优视 | 友盟 | 酷盘 | 虾米 | 天天动听 | 来往 | 钉钉 | 11Main | 支付宝
关于淘宝 合作伙伴 营销中心 联系客服 开放平台 诚征英才 联系我们 网站地图 法律声明 © 2016 Taobao.com 版权所有
网络文化经营许可证：文网文[2010]040号 | 增值电信业务经营许可证：浙B2-20080224-1 | 信息网络传播视听节目许可证：1109364号