菜单

SQL Server排名函数与排行开窗函数

2019年4月19日 - MySQL

眼前在MySQL中遇见分组排序查询时,突然意识MySQL中从不row_number()
over(partition by colname)那样的分组排序。
同时鉴于MySQL中未有像样于SQL
Server中的row_number()、rank()、dense_rank()等排行函数,全部找到以降低成格局,在此轻巧记录一下。

什么是排行函数?说实话小编也不甚通晓,小编理解 order by
是排序用的,那么怎么样又是排名函数呢?

 

接下去看多少个示范就领会了。

首先创设三个表并插入测试数据。

先是建立1个表,随便插入壹些数码。

create table demo.Student (
   ID int(11) NOT NULL AUTO_INCREMENT,
   StuNo varchar(32) NOT NULL,
   StuName varchar(10) NOT NULL,
   StuAge int(11) DEFAULT NULL,
   PRIMARY KEY (ID)
 )
 engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

insert into demo.Student(StuNo,StuName,StuAge) values('A001','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A005','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A007','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A003','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A002','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A004','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A006','小王',25);

select * from demo.Student;

图片 1

测试数据如下:

ROW_NUMBE悍马H2函数:直接排序,ROW_NUMBE大切诺基函数是以上涨举办直接排序,并且以三番五次的次第给每一行数据1个唯1的序号。(即排行一连)

图片 2

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(order by U_Pwd))+'名' RowNum
4 from UserInfo

 

图片 3

实现row_number()排名函数,按学号(StuNo)排序。

RANK 函数:并列排序,在 order by
子句中钦命的列,倘诺回到一行数据与另一行具备一样的值,rank函数将给这个行赋予一样的排名数值。

-- @row_number:=0,设置变量@row_number的初始值为0。
-- @row_number:=@row_number+1,累加@row_number的值。
select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 as row_number 
from demo.Student a,
(
    select @row_number:=0
) b
order by StuNo asc;

在排名的长河中,保持三个里头计数值,当值有所变动时,排行序号将有二个踊跃。(即排名不总是)

结果如下:

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,rank() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

图片 4

图片 5

 

能够鲜明的看到有四行数据并列第壹名,然后直接正是第陆名,这是因为 order by
子句中钦定的列 U_Pwd 的值一样。

完结rank()排行函数,按学生年龄(StuAge)排序。

DENSE_RANK
函数:并列排序,
这或多或少与 RANK() 函数类似,order by
子句内定的列的值同样,排行数值同样,可是后边是接2连3的。(即排行一而再)

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- @inRank:=1,设置变量@inRank的初始值为1
-- if(@StuAge=StuAge,@rank,@rank:=@inRank),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值跳变为@inRank内部计数的值
-- @inRank:=@inRank+1,每一行自增1,用于实现内部计数
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@inRank) as row_rank,@inRank:=@inRank+1,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0,@inRank:=1 
    ) b 
    order by StuAge asc 
) t;
1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,DENSE_RANK() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

结果如下:

图片 6

图片 7

能够看出就是有4行数据并列第贰名,可是接下去照旧是第二名。

 

NTILE
函数:
将查询的结果分发到内定数量的组中。
种种组有编号,编号从一起来。 对于每壹行,NTILE 将赶回此行所属的组的编号。

实现dense_rank()排行函数,按学生年龄(StuAge)排序。

组中的行数计算格局为 total_num_rows(结果集的总集团数) /
num_groups(钦赐的组数)。

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- if(@StuAge=StuAge,@rank,@rank:=@rank+1),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值自增1
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@rank+1) as row_rank,@StuAge:=StuAge
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0 
    ) b 
    order by StuAge asc 
) t;

设若有余数 n,则前边 n
个组获得3个附加行。因而,恐怕不会具备组都获得相当于数量的行,然而组大小最大只只怕离开一行。

结果如下:

比如说,假设总行数是 伍3,组数是 5,五三 / 5对等十余数是三,按上面个规则就是,每组分配十行,又因余数为3,所以前边3组每组附加一行。

图片 8

则前八个组每组包蕴 1一 行,别的五个组每组包罗 十 行。

 

一方面,尽管总行数可被组数整除,则行数将在组之间平均分布。
例如,倘诺总行数为 50,有多个组,则每组将富含 拾 行。

实现row_number() over(partition by colname order by
colname)分组排名函数,按学生年龄(StuAge)分组排序。

1 -- 以下是根据 U_Pwd 这一列进行分组
2 select *,
3 '第 '+convert(varchar,NTILE(3) over(order by U_Pwd))+' 组' RowNum
4 from UserInfo
-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @row_number:=0,设置变量@row_number的初始值为0
-- if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1),指定排序列的值不变时,@row_number的值自增1;指定排序列的值变化时,@row_number的值等于1
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_number 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1) as row_number,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@row_number:=0 
    ) b 
    order by StuAge asc 
) t;

图片 9

结果如下:

本条表中有10条数据,钦命分为3组,十/叁等于叁余数壹。

图片 10

PS:排名函数后边总得有 over() 子句。

 

 

完成分组聚合字符串,即把内定列的值拼成字符串。
在SQL Server中时选用了中档变量完毕,今后在MySQL中就相比较简单了。
MySQL提供了一个group_concat()函数,可以把钦命列的值拼成一个字符串,并得以按钦命排序方式拼成字符,之间用逗号隔断。如下示例:

排行开窗函数:

select group_concat(StuNo order by StuNo asc) as column1,group_concat(StuNo order by ID asc) as column2 
from demo.Student 

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排行函数,OVE福睿斯()便是窗口函数。

结果如下:

窗口函数OVE奇骏()钦定一组行,开窗函数总结从窗口函数输出的结果集中各行的值。

图片 11

开窗函数不须要利用GROUP
BY就能够对数码举行分组,还是能够同时重回基础行的列和聚合列。

 

排名开窗函数可以独自使用O驭胜DEHaval BY 语句,也得以和PARTITION BY同时利用。

select StuName,group_concat(StuNo order by StuNo asc) as column1,concat('"',group_concat(StuNo order by StuNo asc),'"') as column2 
from demo.Student 
group by StuName 
order by StuAge 

ODELAND BY 内定排行开窗函数的次第。在排行开窗函数中务必选拔OENVISIONDE福特Explorer BY语句。

结果如下:

PARTITION BY用于将结果集实行分组,开窗函数应用于每一组。

图片 12

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

 

图片 13

因为 U_Pwd 那1列有肆种差别的值,所以分为4组,然后 ROW_NUMBE卡宴再在每一组中进行连接排序。

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,rank() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

图片 14

因为 U_Pwd 那1列有4种不一致的值,所以一律是分为④组,然后 RANK
再在每1组中进行排序,因为RANK是因人而异排序,所以全体都以头名。下边换个字段排序试试看。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Name 排序
2 select *,
3 '第'+convert(varchar,rank() over(partition by U_Pwd order by U_Name))+'名' RowNum
4 from UserInfo

图片 15

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Pwd 排序
2 select *,
3 '第'+convert(varchar,DENSE_RANK() over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

图片 16

因为 U_Pwd 这一列有4种分化的值,所以同样是分为4组,然后
DENSE_RANK 再在每1组中举办排序,因为DENSE_RANK也是同等看待排序,所以全体都以第一名。上边换个字段排序试试看。

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 U_Name 排序
2 select *,
3 '第'+convert(varchar,DENSE_RANK() over(partition by U_Pwd order by U_Name))+'名' RowNum
4 from UserInfo

图片 17

 

1 -- 以下是先根据 U_Pwd 这一列进行分组,然后每一组再根据 NTILE(3) 指定的组数分组,最后在根据 order by 子句指定的字段 U_Pwd 排序 
2 select *,
3 '第'+convert(varchar,NTILE(3) over(partition by U_Pwd order by U_Pwd))+'名' RowNum
4 from UserInfo

图片 18

因为 U_Pwd
这一列有四种分歧的值,所以1律是分为四组。第三组有一条数据,所以就3个区。第一组有四条数据,4/3等于一余数1,所以第三组分为一个区,又因余数为一,所以第二个区附加1行。第一组有3条数据,3/3相当一余数为0,所以第壹组有2个区。第四组有二条数据,所以分为二个区。

PS:在排序开窗函数中选取 PARTITION BY
子句须要停放在 O汉兰达DEPRADO BY子句在此以前。

 

参考:

http://www.cnblogs.com/jhxk/articles/2531595.html

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图