菜单

SQL Server排名函数和排名开窗函数

2018年11月16日 - MySQL

近日于MySQL中遇到分组排序查询时,突然意识MySQL中绝非row_number()
over(partition by colname)这样的分组排序。
并且由于MySQL中无看似于SQL
Server中之row_number()、rank()、dense_rank()等排名函数,所有找到以下实现方式,在这简单记录转。

哟是排名函数?说实话我呢无甚了解,我了解 order by
是排序用的,那么什么而是行函数呢?

 

接通下看几独示范就明白了。

先是创建一个表并插入测试数据。

率先成立一个表明,随便插入一些数目。

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_NUMBER
函数:直接排序,
ROW_NUMBER函数是以上升进行直接排序,并且因为连续的逐条为各国一行数一个唯一的序号。(即行连)

图片 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

 

好肯定的见到出4行数据并列第2名叫,然后直接就是是第6名为,这是因 order by
子句被指定的列 U_Pwd 的值相同。

贯彻rank()排名函数,按学生年龄(StuAge)排序。

DENSE_RANK
函数:并列排序,
立或多或少同 RANK() 函数类似,order by
子句指定的排的价值相同,排名数值相同,但是后面是接二连三的。(即行连)

-- @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行数据并列第2叫做,但是连下还是是第3叫。

 

NTILE
函数:
用查询的结果分发到指定数量之组中。
各个组有编号,编号从1始。 对于各一样实践,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
个组获得一个附加行。因此,可能不会见具有组都获得相当数量的尽,但是组大小最特别才恐去一行。

结果如下:

譬如,如果总行多次凡是 53,组数是 5,53 / 5
对等10余屡凡3,按点只规则就是,每组分配10履,又因为余数为3,所以前面3组各组附加一行。

图片 8

则前三只组各组包含 11 行,其余两个组各组包含 10 行。

 

一派,如果总行多次而让组数整除,则行数将在组中平均分布。
例如,如果总行多次也 50,有五个组,则每组将包含 10 行。

实现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/3等给3余频1。

图片 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属于排名函数,OVER()就是窗口函数。

结果如下:

窗口函数OVER()指定同组行,开窗函数计算起窗口函数输出的结果集中各行的值。

图片 11

开窗函数不需要动用GROUP
BY就好本着数据开展分组,还可又返回基础实施的排列和聚合列。

 

排名开窗函数可以独立使用ORDER 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 

ODER BY 指定排名开窗函数的逐一。在排名开窗函数中务必利用ORDER 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 这同排有4种不同之值,所以分为4组,然后 ROW_NUMBER
还当各国一样组被开展连接排序。

 

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 这同样排有4种不同的值,所以同样是分为4组,然后 RANK
再以各国一样组被开展排序,因为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 再当各级一样组中展开排序,因为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
这同排有4种不同之值,所以同样是分为4组。第1组有1长数据,所以尽管1独区。第2组有4漫长数,4/3等于1余数1,所以第2组分为3单区,又因为余数为1,所以第1只区附加1行。第3组来3久数,3/3抵1余数吧0,所以第3组发3单区。第4组发生2长长的数,所以分也2只区。

PS:在排序开窗函数中采用 PARTITION BY
子句需要停放在 ORDER BY子句之前。

 

参考:

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

相关文章

发表评论

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

网站地图xml地图