龙空技术网

Transact-SQL学习笔记21——排名窗口函数

包家三少 95

前言:

而今我们对“sql中排名函数”大约比较关注,看官们都需要学习一些“sql中排名函数”的相关资讯。那么小编同时在网上汇集了一些有关“sql中排名函数””的相关内容,希望我们能喜欢,你们快快来学习一下吧!

将OVER()子句和排名函数连用,就是排名窗口函数,它们只能用在SELECT子句或ORDER BY子句之后。如果放在 SELECT之后,它运行的逻顺序在 DISTINCT 之前。

逻辑处理顺序如下:

SELECT(7)<DISTINCT>(9)TOP (5)select list()<OVER0

(1)FROM...JOIN ...

(2)WHERE

(3)GROUP BY

(4)HAVING

(8)ORDER BY

它们的使用方法格式如下:

order_function OVER( [PARTITION BY expression]<ORDER BY CIase> )

order_finction 是指排名函数,包括ROW NUMBER()、RANK()、DENSE_RANK()和NTILE()与OVER()连用,OVER子句里面使用PARTITION BY关键字对输入行进行窗口划分(即分区划分),如果OVER()子句中不写PARTITION BY,则表示对所有行进行计算,这里的所有行不是 FROM 后面表的所有行,而是经过 WHERE GROUP BY和HAVING运行之后的所有行。在PARTITION BY之后还跟上ORDER BY子句对分区内的数据进行排序,它不可以省略,否则无顺序的区内数据由于不知道排名而无法使用排名函数。

实验环境如下两表:

employee表

salary 表

ROW NUMBER()进行分区编号

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary) AS ID ,* FROM salary

如果没有使用PARTITION BY,就只有一个窗口

SELECT ROW_NUMBER() OVER(ORDER BY salary) AS ID ,* FROM salary

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ) AS ID ,* FROM salary

如果不适用ORDER BY 会报错,但也许此时并不希望做排序,可以使用SELECT 0 来变通

SELECT ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY (SELECT 0)) AS ID ,* FROM salary

RANK() 和DENSE_RANK()rank()排序相同时会重复,总数不变,即会出现1、1、3这样的排序结果;dense_rank()排序相同时会重复,总数会减少,即会出现1、1、2这样的排序结果;row_number()排序相同时不会重复,会根据顺序排序。

--更新实验环境

UPDATE salary

SET salary =60

where employee_id = 1 and salary_type ='岗位工资'

SELECT

ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY salary) AS 编号,

RANK() OVER(PARTITION BY employee_id ORDER BY salary) AS 排名,

DENSE_RANK() OVER(PARTITION BY employee_id ORDER BY salary) AS 密集排名

,* FROM salary

NTIEL()进行数据分钟

NTILE()的功能是进行“均分”分组,括号内接受一个代表要分组组数量的参数,然后以组为单位进行编号,对于组内每一行数据,NTILE 都返回此行所在组的组编号。

简单地说就是NTILE 函数将每一行数据关联到组,并为每一行分配一个所属组的编号。

假设一个表的某列值为1~10 的整数,要将这 10 行分成两组,则每个组都有5 行,表示方式为NTILE(2)。如果表某列是 1~11的整数,这11行要分成3 组的表示方式为NTILE(3)但是这时候无法“均分”,它的分配方式是先分成3 组,每组3 行数据,剩下的两行数据从前向后均分,即第一组和第二组都有 4 行数据,第三组只有3 行数据。

假设1~11的整数,分为3组(1,2,3,4)(5,6,7,8)(9,10,11)

SELECT NTILE(4) OVER(ORDER BY salary) AS 分组

,* FROM salary

学习参考资料:《跟韩老师学 SQL Server 数据库设计与开发》

标签: #sql中排名函数