全部产品
阿里云办公

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

更新时间:2018-02-28 17:52:05

分析函数CUME_DIST

微软的定义:

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

函数解析:

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

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

返回结果如下:

示例解析:

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

分析函数LAST_VALUE

微软的定义:

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

函数解析:

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

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

返回结果如下:

示例解析:

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

分析函数FIRST_VALUE

微软的定义:

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

函数解析:

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

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

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

返回结果如下:

示例分析:

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

分析函数LEAD

微软的定义:

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

函数解析:

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

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

返回结果如下:

示例分析:

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