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)
有兴趣的同学可以试试看这个算法。