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

    xiaoxiao2025-09-25  86


    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 --bd ('andy01','bd',15000), ('andy02','bd',12000), ('andy03','bd',12000), ('andy04','bd',10000), ('andy05','bd',8000), --ca ('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 --bd ('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'), --ca ('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 --bd ('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'), --ca ('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 --bd ('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'), --ca ('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

    相关资源:python入门教程(PDF版)
    最新回复(0)