SQLServer · 特性分析 · SQL Server 2012的分析函数未必都理解透了(1)

title: SQLServer · 特性分析 · SQL Server 2012的分析函数未必都理解透了

author: 石沫 1. 背景

最近有用户在做一些项目，使用到SQL SERVER 2012的一些新特性，比如SQL SERVER 提供的8个非常有用的分析函数，一开始我看了相关的文档，感觉内容很多，理解不清楚，不透彻。而我现在想来，其实不需要那么清楚，我觉得值要理解他的基本用法就足以应对工作，下面根据我的理解，以最简单的方式解析这些分析函数。

1. 分析函数CUME_DIST

微软的定义：
计算某个值在 SQL Server 2012 中的一组值内的累积分布。CUME_DIST 计算某指定值在一组值中的相对位置。 对于行 r，假定采用升序，r 的 CUME_DIST 是值低于或等于 r 的值的行数除以在分区或查询结果集中求出的行数。
其实，我看了也不是很懂，我们看一个实例，就很清楚了。
首先，我们构造一组数据：

DECLARE

 @analytic TABLE( 

 name varchar(35) ,

 dept varchar(35),

 salary money

INSERT INTO @analytic 

 VALUES

(andy01,bd,15000),

(andy02,bd,12000),

(andy03,bd,12000),

(andy04,bd,10000),

(andy05,bd,8000),

(andy06,ca,20000),

(andy07,ca,18000),

(andy08,ca,18000),

(andy09,ca,15000),

(andy10,ca,12000),

(andy11,ca,12000),

(andy12,ca,10000),

(andy13,ca,8000),

(andy14,ca,8000),

(andy15,ca,8000)

SELECT 

 dept,name ,salary,

 CUME_DIST() OVER(PARTITION BY dept ORDER BY salary) AS cume_dist_ 

FROM @analytic

ORDER BY dept,salary DESC

然后我们再看看结果：

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

2. 分析函数LAST_VALUE

微软的定义：
返回 SQL Server 2012 中有序值集中的最后一个值。
好像我还是不太懂。那么还是看看一个示例吧：

DECLARE

 @analytic TABLE( 

 name varchar(35) ,

 dept varchar(35),

 salary money ,

 hiredate date

INSERT INTO @analytic 

 VALUES

(andy01,bd,15000,2002-01-09),

(andy02,bd,12000,2003-01-09),

(andy03,bd,12000,2003-02-09),

(andy04,bd,10000,2005-05-09),

(andy05,bd,8000,2003-06-09),

(andy06,ca,20000,2003-01-09),

(andy07,ca,18000,2005-02-09),

(andy08,ca,18000,2005-03-09),

(andy09,ca,15000,2004-01-09),

(andy10,ca,12000,2003-06-09),

(andy11,ca,12000,2002-09-09),

(andy12,ca,10000,2003-07-09),

(andy13,ca,8000,2003-08-09),

(andy14,ca,8000,2003-11-09),

(andy15,ca,8000,2003-01-09)

SELECT 

 dept,name ,salary,hiredate,

 LAST_VALUE(hiredate) OVER(PARTITION BY dept ORDER BY salary) AS last_value_ 

FROM @analytic

我们再看看这个运行结果：

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

3. 分析函数FIRST_VALUE

微软的定义：
返回 SQL Server 2012 中有序值集中的第一个值。 从这个地方看起来，似乎跟LAST_VALUE是相反的一个意思，实际上是不是这样，我们来看看实例：

DECLARE

 @analytic TABLE( 

 name varchar(35) ,

 dept varchar(35),

 salary money ,

 hiredate date

INSERT INTO @analytic 

 VALUES

(andy01,bd,15000,2002-01-09),

(andy02,bd,12000,2003-01-09),

(andy03,bd,12000,2003-02-09),

(andy04,bd,10000,2005-05-09),

(andy05,bd,8000,2003-06-09),

(andy06,ca,20000,2003-01-09),

(andy07,ca,18000,2005-02-09),

(andy08,ca,18000,2005-03-09),

(andy09,ca,15000,2004-01-09),

(andy10,ca,12000,2003-06-09),

(andy11,ca,12000,2002-09-09),

(andy12,ca,10000,2003-07-09),

(andy13,ca,8000,2003-08-09),

(andy14,ca,8000,2003-11-09),

(andy15,ca,8000,2003-01-09)

SELECT 

 dept,name ,salary,hiredate,

 FIRST_VALUE(name) OVER(PARTITION BY dept ORDER BY salary) AS first_value_ 

FROM @analytic

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

4. 分析函数LEAD

微软的定义：
访问相同结果集的后续行中的数据，而不使用 SQL Server 2012 中的自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。
感觉这个好理解多了。不过我们还是看看一个示例来说明：

DECLARE

 @analytic TABLE( 

 name varchar(35) ,

 dept varchar(35),

 salary money ,

 hiredate date

INSERT INTO @analytic 

 VALUES

(andy01,bd,15000,2002-01-09),

(andy02,bd,12000,2003-01-09),

(andy03,bd,12000,2003-02-09),

(andy04,bd,10000,2005-05-09),

(andy05,bd,8000,2003-06-09),

(andy06,ca,20000,2003-01-09),

(andy07,ca,18000,2005-02-09),

(andy08,ca,18000,2005-03-09),

(andy09,ca,15000,2004-01-09),

(andy10,ca,12000,2003-06-09),

(andy11,ca,12000,2002-09-09),

(andy12,ca,10000,2003-07-09),

(andy13,ca,8000,2003-08-09),

(andy14,ca,8000,2003-11-09),

(andy15,ca,8000,2003-01-09)

SELECT 

 dept,name,hiredate,salary,

 LEAD(salary,1,0) OVER(PARTITION BY dept ORDER BY salary) AS lead_,

 (LEAD(salary,1,0) OVER(PARTITION BY dept ORDER BY salary)-salary) AS diff_salary 

FROM @analytic

我们再看看结果：

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

后面还有4个函数，下一篇分析：LAG，PERCENT_RANK ，PERCENTILE_DISC，PERCENTILE_CONT

石沫 长期在电子商务行业从事SQL Server的设计，开发与维护，拥有10年的相关经验，擅长数据库的架构与设计，擅长数据库的性能优化，擅长数据库的自动化和智能化运维，从2014年开始， 在云计算领域坚持奋斗, 阿里云SQL Server系列产品的设计与规划者
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载