SQLServer · 特性分析 · SQL Server 2012的分析函数未必都理解透了(2)-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: --- title: SQLServer · 特性分析 · SQL Server 2012的分析函数未必都理解透了 author: 石沫 --- # 1. 背景 最近有用户在做一些项目,使用到SQL SERVER 2012的一些新特性,比如SQL SERVER 提供的8个非常有用的分析函数,一开始我看了相关的文档,感觉内容很多,理解不清楚,不透彻。而我现在想来,其实不需要那

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

author: 石沫

1. 背景

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

5. 分析函数 LAG

微软定义:
访问相同结果集的先前行中的数据,而不使用 SQL Server 2012 中的自联接。 LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 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,
    LAG(salary,1,0) OVER(PARTITION BY dept ORDER BY salary) AS lag_,
    (LAG(salary,1,0) OVER(PARTITION BY dept ORDER BY salary)-salary) AS lag_diff_salary 
FROM @analytic

我们再看看结果:
5

从这个结果看,确实如此,与LEAD相反的行为。这里不多说了,从示例看的非常清楚。

6. 分析函数PERCENT_RANK

微软定义:
计算 SQL Server 2012 中一组行内某行的相对排名。 使用 PERCENT_RANK 计算一个值在查询结果集或分区中的相对位置。 PERCENT_RANK 类似于 CUME_DIST 函数。
只看这个,是看不出来啥意思的。我们还是从下面的实例可以看得很清楚:



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',15000,'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,
     PERCENT_RANK() OVER(PARTITION BY dept ORDER BY salary) AS percent_rank_
FROM @analytic

6

从这个图可以清楚的:
最小的SALARY的 PERCENT_RANK() 始终是0,但有唯一一个最大SALARY是1.其他值的算法是这样的:
PERCENT_RANK() = 小于自己的记录数/(总的记录数-1)。这样来看是不是要简单清晰得多了。

7. 分析函数PERCENTILE_DISC

微软定义:
计算 SQL Server 2012 中整个行集内或行集的非重复分区内已排序值的特定百分位数。 对于给定的百分位数的值 P,PERCENTILE_DISC 对 ORDER BY 子句中表达式的值进行排序,并返回具有最小 CUME_DIST 值且大于或等于 P 的值(遵照相同的排序规范)。 例如,PERCENTILE_DISC (0.5) 将计算表达式的第 50 百分位数(也即中值)。 PERCENTILE_DISC 基于列值的离散分布来计算百分位数;结果等于列中的一个特定值。
好晦涩的说法,我也没有理解,我们还是看示例:



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',15000,'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',11000,'2003-06-09'),
('andy11','ca',1100,'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,
    CUME_DIST() OVER (PARTITION BY dept ORDER BY salary) cume_dist_ ,
    PERCENTILE_DISC (0.7) WITHIN GROUP( ORDER BY salary) 
    OVER(PARTITION BY dept) AS percentile_disc_
FROM @analytic

看看结果,我们来分析分析:

7

其实这个算法很简单:

  1. 首先要计算出CUME_DIST() 的值
  2. 其次要比较CUME_DIST() 的值和PERCENTILE_DISC (x)给定的x
  3. 如果CUME_DIST() 的值等于x,在各个分组上PERCENTILE_DISC (x)对应的值( ORDER BY *)

    都会等于对应的ORDER BY的salary值

    4.若CUME_DIST() 的值不等于x,在各个分组上PERCENTILE_DISC (x)对应的值( ORDER BY *)

都会等于接近于但大于x 的CUME_DIST() 值的ORDER BY的salary值(有点拗,不过看图就明白了)

8. 分析函数PERCENTILE_CONT

微软定义:
基于 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',15000,'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',11000,'2003-06-09'),
('andy11','ca',1100,'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,
    CUME_DIST() OVER (PARTITION BY dept ORDER BY salary) cume_dist_ ,
    PERCENTILE_CONT(0.5) WITHIN GROUP( ORDER BY salary) 
    OVER(PARTITION BY dept) AS percentile_cont_
FROM @analytic

请看看下图:
8

这个函数可能不返回相同的值。 这是因为,PERCENTILE_CONT 内插适当的值,而无论它在数据集中是否存在,而 PERCENTILE_DISC 始终从数据集中返回实际值。
具体的算法,到目前,我还想得不是很清楚。读者可以自己推测一下这个算法的由来。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章