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

发布时间:2018-11-15  栏目:MySQL  评论:0 Comments

 

因为 U_Pwd 这同样排有4种不同的值,所以同样是分为4组,然后
DENSE_RANK 再当各国一样组中展开排序,因为DENSE_RANK为是一视同仁排序,所以全部都是第一称呼。下面换个字段排序试试看。

图片 1

于排名的经过中,保持一个里面计数值,当值有所转时,排名序号将生一个踊跃。(即行不总是)

结果如下:

图片 2

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

图片 3

图片 4

 

 

参考:

近期以MySQL中相见分组排序查询时,突然发现MySQL中没有row_number()
over(partition by colname)这样的分组排序。
还要由于MySQL中莫接近于SQL
Server中的row_number()、rank()、dense_rank()等排名函数,所有找到以下实现方式,在此简单记录转。

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

图片 5

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,DENSE_RANK() 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;

 

 

DENSE_RANK
函数:并列排序,
当下一点暨 RANK() 函数类似,order by
子句指定的排列的值相同,排名数值相同,但是后面是连的。(即行连)

结果如下:

PARTITION BY用于将结果集进行分组,开窗函数应用为各个一样组。

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

图片 6

 

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

 

图片 7

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

而发生余数 n,则前面 n
个组获得一个附加行。因此,可能未见面有组都获得相当数量之履,但是组大小最深才恐去一行。

测试数据如下:

组中的行数计算办法呢 total_num_rows(结果集的总店多次) /
num_groups(指定的组数)。

结果如下:

排名开窗函数可以独立使用ORDER BY 语句,也可以和PARTITION BY同时使用。

结果如下:

 

-- @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;

图片 8

-- @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;

单向,如果总行多次而为组数整除,则行数将于组内平均分布。
例如,如果总行多次为 50,有五个组,则每组将包含 10 行。

 

RANK 函数:并列排序,于 order by
子句被指定的排,如果回到一行数和外一行具有相同之价,rank函数将给这些实践给相同的排名数值。

图片 9

图片 10

结果如下:

图片 11

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

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

 

 

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

图片 12

图片 13

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;

开窗函数不欲以GROUP
BY就可以对数据开展分组,还足以而且返回基础实施之排和聚合列。

图片 14

图片 15

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

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

结果如下:

以此表中来10长条数,指定分为3组,10/3相当吃3不必要反复1。

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

虽然前三独组各组包含 11 行,其余两单组各组包含 10 行。

图片 16

ODER BY 指定排名开窗函数的各个。在排名开窗函数中必利用ORDER BY语句。

贯彻分组聚合字符串,即把指定列的价拼成字符串。
在SQL Server中时用了中等变量实现,现在在MySQL中就比较简单了。
MySQL提供了一个group_concat()函数,可以把指定列的价值拼成一个字符串,并得以按指定排序方式并成字符,之间为此逗号隔开。如下示例:

ROW_NUMBER
函数:直接排序,
ROW_NUMBER函数是以上升进行直接排序,并且因为连续的依次为各国一行数一个唯一的序号。(即行连)

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

 

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

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

NTILE
函数:
将查询的结果分发到指定数量之组中。
各个组有编号,编号从1初始。 对于各级一样尽,NTILE 将回到此行所属之组的号码。

因为 U_Pwd 这同排列有4种不同之价,所以同样是分为4组,然后 RANK
再于每一样组吃开展排序,因为RANK是一视同仁排序,所以全部都是第一誉为。下面换个字段排序试试看。

PS:排名函数后面总得发 over() 子句。

排名开窗函数:

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

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

因为 U_Pwd 这无异于列有4栽不同的价值,所以分为4组,然后 ROW_NUMBER
重新于列一样组中展开连接排序。

因为 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独区。

先是成立一个发明,随便插入一些多少。

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

哟是行函数?说实话我哉无十分明了,我知道 order by
是排序用的,那么什么而是行函数呢?

得看到就是出4行数据并列第2名叫,但是连下还是第3名为。

足明显的看来有4行数据并列第2叫,然后径直就是是第6名,这是以 order by
子句被指定的列 U_Pwd 的价相同。

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

 

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数,OVER()就是窗口函数。

图片 17

对接下看几乎独示范就掌握了。

图片 18

留下评论

网站地图xml地图