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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 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)

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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
30天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
1月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
|
1月前
|
SQL 数据库 索引
SQL中COUNT函数结合条件使用的技巧与方法
在SQL查询中,COUNT函数是一个非常常用的聚合函数,用于计算表中满足特定条件的记录数
|
1月前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
|
1月前
|
SQL 数据库
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
216 0
|
2月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
3月前
|
SQL 数据处理 数据库
|
3月前
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
91 0
|
3月前
|
SQL Oracle 关系型数据库
SQL 中的大小写处理函数详解
【8月更文挑战第31天】
132 0
|
3月前
|
SQL 数据采集 数据挖掘
为什么要使用 SQL 函数?详尽分析
【8月更文挑战第31天】
48 0

相关产品

  • 云数据库 RDS SQL Server 版