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() = CEILING(小于自己的记录数/(总的记录数-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,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) row_number_ ,
    PERCENTILE_CONT(0.5) WITHIN GROUP( ORDER BY salary) 
    OVER(PARTITION BY dept) AS percentile_cont_
FROM @analytic

--n=5
--x=0.5
--rn=(1+x*(n-1))=(1+0.5*(5-1))=3
--crn=ceiling(rn)=3
--frn=floor(rn)=3
--percentile_cont= 12000

--n=10
--x=0.5
--rn=(1+x*(n-1))=(1+0.5*(10-1))=5.5
--crn=ceiling(rn)=6
--frn=floor(rn)=5
--
/*percentile_cont=   
        (crn - rn) * (value of expression for row at frn) 
        +(rn - frn) * (value of expression for row at crn)
--select (6-5.5)*10000.00+(5.5-5)*11000    

*/

请看看下图:
8

这个算法比较复杂,我推测了一下,算法如下:
n为每个分组的记录数
x为percentile_cont的百分位参数值
rn是计算出来的row number,但这个是逻辑计算出来的,不是对应物理的数据行
rn=(p+x*(n-1))
crn是ceiling(rn)
frn是floor(rn)

所以算法就很清楚了:

  1. 如果(crn = frn = rn) 那么percentile_cont= (value of expression from row at rn)
  2. 如果(crn = frn= rn) 不满足,percentile_cont=(crn- rn) (value of expression for row at frn) +(rn- frn) (value of expression for row at crn)

有兴趣的同学可以试试看这个算法。

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

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

帮用户承担一切数据库风险,给您何止是安心!

官方博客
最新文章
相关文章
链接