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

2022-01-27 23:28:09

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

我们再看看结果：

从这个结果看，确实如此，与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

从这个图可以清楚的：
最小的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

看看结果，我们来分析分析：

其实这个算法很简单：

首先要计算出CUME_DIST() 的值
其次要比较CUME_DIST() 的值和PERCENTILE_DISC (x)给定的x
如果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    

*/

请看看下图：

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

所以算法就很清楚了：

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

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

码农公寓