Server排名函数,四大排名函数

图片 20
数据库

多年来在MySQL中相遇分组排序查询时,突然意识MySQL中平昔不row_number()
over(partition by colname)那样的分组排序。
何况鉴于MySQL中尚无看似于SQL
Server中的row_number()、rank()、dense_rank()等排名函数,全数找到以下达成形式,在那轻松记录一下。

over()深入分析函数用于计算基于组的某种聚合值,它和聚合函数的差异之处是:对于种种组重返多行,而聚合函数对于种种组只重临大器晚成行。
例子:

  排行函数是Sql
Server2007新添的功效,上面简要介绍一下他们分其他用法和界别。大家新建一张Order表并累计一些从头数据实惠我们查阅效果。

 

select *from (select name,class,score,rank() over(partition by class order by score desc) mm from t2 ) where mm=1

 

先是创立二个表并插入测验数据。

通过class班级进行分组,并基于score分数进行排序,用rank(卡塔 尔(英语:State of Qatar)函数排序方法为mm列给予序号,然后mm=1就能够找到每组的率先名,当然可以依靠score就能够倒序能够找到最终一名。

图片 1图片 2

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;

row_number() over(partition by ... order by ...)

CREATE TABLE [dbo].[Order](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [TotalPrice] [int] NOT NULL,
    [SubTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Order] ON 

GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (1, 1, 100, CAST(0x0000A419011D32AF AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (2, 2, 500, CAST(0x0000A419011D40BA AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (3, 3, 300, CAST(0x0000A419011D4641 AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (4, 2, 1000, CAST(0x0000A419011D4B72 AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (5, 1, 520, CAST(0x0000A419011D50F3 AS DateTime))
GO
INSERT [dbo].[Order] ([ID], [UserId], [TotalPrice], [SubTime]) VALUES (6, 2, 2000, CAST(0x0000A419011E50C9 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Order] OFF
GO
ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_SubTime]  DEFAULT (getdate()) FOR [SubTime]
GO

测验数据如下:

轻松易行的说row_number()从1起来,为每一条分组记录重回一个数字,
row_number() over(order by score desc)是先把score
列降序,再为降序现在的没条xlh记录再次回到一个序号。(若无分组能够领悟成将全体结果作为一个分组卡塔尔

表结构和起来数据Sql

图片 3

row_number() over(partition by class order by score
desc)表示遵照class分组,在分组内部依照 score
排序,而此函数总结的值就意味着每组内部排序后的逐个编号(组内一连的唯大器晚成的)

 

 

rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)

 

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

作为分数函数中有关排序的rank(),dense_rank(),row_number()。

  附上表结议和始发数据图:

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

rank()
over是的效用是意识到钦赐条件后开展多少个排行的榜单,但是有叁个性子。要是是对学员排行,那么实用那几个函数,战绩相符的两名是比量齐观(排名为1,2,2,4卡塔尔

  图片 4

结果如下:

dense_rank()的效应和rank()很像,唯意气风发不一样就是,相通战表并列未来,下一位同学并不空出并列所占的排行(排行为1,2,2,3卡塔 尔(阿拉伯语:قطر‎

 

图片 5

row_number()就不均等了,它和方面二种的区分就很显明了,这么些函数无需思虑是或不是并列,哪怕依据规范查询出来的数值相通也会实行连接排名。

一、ROW_NUMBER

  row_number的用场的不行广阔,排序最佳用她,日常能够用来促成web程序的分页,他会为查询出来的每大器晚成行记录生成二个序号,依次排序且不会再度,注意利用row_number函数时务必要用over子句选择对某一列实行排序才具生成序号。row_number用法实例:

 

select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order]

 

  查询结果如下图所示:

  图片 6

  图中的row_num列就是row_number函数生成的序号列,其基本原理是先使用over子句中的排序语句对记录进行排序,然后根据这一个顺序生成序号。over子句中的order by子句与SQL语句中的order
by子句未有其余关联,这两处的order by
能够完全不相同,如以下sql,over子句中根据SubTime降序排列,Sql语句中则按TotalPrice降序排列。

select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order] order by [TotalPrice] desc

  查询结果如下图所示:

  图片 7

  利用row_number能够兑现web程序的分页,大家来询问内定范围的表数据。例:根据订单提交时间倒序排列获取第三至第五条数据。

with orderSection as
(
    select ROW_NUMBER() OVER(order by [SubTime] desc) rownum,* from [Order]
)
select * from [orderSection] where rownum between 3 and 5 order by [SubTime] desc

  查询结果如下图所示:

  图片 8

  注意:在使用row_number完毕分页时索要特别注意一点,over子句中的order
by 要与Sql排序记录中的order by
保持后生可畏致,不然获得的序号大概不是接连的。
下边大家写叁个例子来表明那一点,将地方Sql语句中的排序字段由SubTime改为TotalPrice。其它提一下,对于带有子查询和CTE的询问,子查询和CTE查询有序并不代表全部查询有序,除非展现内定了order
by。

with orderSection as
(
    select ROW_NUMBER() OVER(order by [SubTime] desc) rownum,* from [Order]
)
select * from [orderSection] where rownum between 3 and 5 order by [TotalPrice] desc

  查询结果如下图所示:

  图片 9

  

 

对此多表查询,可感觉空置加上三个料定来突显查询数据为空的数码。

二、RANK

  rank函数用于重返结果集的分区内每行的排行, 行的排行是相关行此前的排名数加生机勃勃。简单的话rank函数正是对查询出来的笔录进行排行,与row_number函数差异的是,rank函数思谋到了over子句中排序字段值相仿的事态,要是利用rank函数来生成序号,over子句中排序字段值相像的序号是相似的,前边字段值不相符的序号将跳过相似的排行号排下八个,也正是相关行在此以前的排名数加大器晚成,能够明白为根据当前的笔录数生成序号,前面包车型地铁笔录由此及彼。恐怕作者陈述的相比苍白,精晓起来也正如棘手,大家一贯上代码,rank函数的行使办法与row_number函数完全相似。

select RANK() OVER(order by [UserId]) as rank,* from [Order] 

  查询结果如下图所示:

  图片 10

  由上海教室能够看出,rank函数在举行排名时,同生龙活虎组的序号是均等的,而背后的则是基于当下的记录数依次类推,图中首先、二条记下的客商Id相似,所以她们的序号是相近的,第三条记下的序号则是3。  

 

落到实处rank()排名函数,按学子年龄(StuAge)排序。

case when score is null then 1 else rank() over (partition by class order by score desc ) end as mm

三、DENSE_RANK

  dense_rank函数的意义与rank函数相仿,dense_rank函数在生成序号时是三番三次的,而rank函数生成的序号有望不总是。dense_rank函数现身同等排行时,将不跳过相近排行号,rank值紧接上贰遍的rank值。在风流罗曼蒂克一分组内,rank()是跳跃排序,有五个率先名时接下去正是第四名,dense_rank()是连接排序,有两个头名时照旧跟着第二名。将地点的Sql语句改由dense_rank函数来完成。

select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

  查询结果如下图所示:

  图片 11

  图中率先、二条记下的客商Id相近,所以她们的序号是均等的,第三条记下的序号紧接上三个的序号,所感到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;

其他常用的深入分析函数:

四、NTILE

  ntile函数能够对序号实行分组管理,将长期以来分区中的行分发到钦赐数量的组中。 各类组有编号,编号从生机勃勃初始。 对于每一个行,ntile
将回到此行所属的组的编号。这就约等于将查询出来的记录集放到钦点长度的数组中,每四个数组成分存放一定数量的记录。ntile函数为每条记下生成的序号正是那条记下全体的数组成分的目录(从1发端卡塔 尔(英语:State of Qatar)。也足以将每二个分配记录的数组元素称为“桶”。ntile函数有叁个参数,用来钦命桶数。上边包车型地铁SQL语句使用ntile函数对Order表进行了装桶管理:

select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]

  查询结果如下图所示:

  图片 12

  Order表的总记录数是6条,而地点的Sql语句ntile函数内定的组数是4,那么Sql
Server二零零五是怎么来决定每风姿罗曼蒂克组应该分多少条记下呢?这里大家就供给驾驭ntile函数的分组依附(约定卡塔尔国。

  ntile函数的分组依靠(约定卡塔 尔(英语:State of Qatar):

  1、每组的记录数不可能超越它上大器晚成组的记录数,即编号小的桶放的笔录数不能够小于编号大的桶。**也正是说,第1组中的记录数只可以大于等于第2组及现在各组中的记录数。**

  2、全体组中的记录数要么都后生可畏律,要么从某三个记录少之又少的组(命名称叫X卡塔尔初叶前面全体组的记录数都与该组(X组卡塔 尔(阿拉伯语:قطر‎的记录数雷同。也便是说,倘诺有个组,前三组的记录数都以9,而第四组的记录数是8,那么第五组和第六组的记录数也亟须是8。

  这里对约定2张开详尽说贝拉米下,以便于更加好的明亮。

  首先系统会去反省能或无法对具备满意条件的记录进行平均分组,若能则平素平均分配就成功分组了;若不能够,则会先分出四个组,那几个组分多少条记下呢?正是(总记录数/总组数卡塔 尔(英语:State of Qatar)+1 条,之所以分配 (总记录数/总组数卡塔尔国+1
条是因为当不能扩充平均分组时,总记录数%总组数显著是有钱的,又因为分组约定1,所以先分出去的组要求+1条。

  分完以往系统会继续去相比较余下的记录数和未分配的组数能还是无法开展平均分配,若能,则平均分配余下的记录;若无法,则再分出去意气风发组,那个组的记录数也是(总记录数/总组数卡塔 尔(英语:State of Qatar)+1条。

  然后系统持续去相比较余下的记录数和未分配的组数能还是不能够张开平均分配,若能,则平均分配余下的笔录;若依旧不可能,则再分配出去生龙活虎组,继续相比较余下的……这样直白开展下去,直至分组完毕。

  举个例子,将51条记下分配成5组,1/35==1不能够平均分配,则先分出来大器晚成组(51/5卡塔 尔(英语:State of Qatar)+1=11条记下,然后比较余下的
51-11=40
条记下是或不是平均分配给未分配的4组,能平均分配,则剩下的4组,每组各40/4=10
条记下,分配完了,分配结果为:11,10,10,10,10,晓菜鸟小编最初就不当的以为她会分配成
11,11,11,11,7。

  依据上面包车型地铁八个约定,能够吸收如下的算法:

 

//mod表示取余,div表示取整.
if(记录总数 mod 桶数==0)
{
  recordCount=记录总数 div 桶数;
  //将每桶的记录数都设为recordCount.
}
else
{
  recordCount1=记录总数 div 桶数+1;
  int n=1;//n表示桶中记录数为recordCount1的最大桶数.
  m=recordCount1*n;
  while(((记录总数-m) mod (桶数- n)) !=0)
  {
    n++;
    m=recordCount1*n;
  }
  recordCount2=(记录总数-m) div (桶数-n);
  //将前n个桶的记录数设为recordCount1.
  //将n+1个至后面所有桶的记录数设为recordCount2.
}

 

图片 13图片 14

int recordTotal = 51;//记录总数.
int tcount = 5;//总组数.
string groupResult = "将" + recordTotal + "条记录分成" + tcount + "组,";
int recordCount = 0;//平均分配时每组的记录数.
//不能平均分配
int recordCount1 = 0;//前n个组每组的记录数.
int recordCount2 = 0;//第n+1组至后面所有组每个组的记录数.
int n = 1;//组中记录数为recordCount1的最大组数(前n组).
if (recordTotal % tcount == 0)//能平分.
{
    recordCount = recordTotal / tcount;//每组的记录数.
}
else//不能平分.
{
    recordCount1 = recordTotal / tcount + 1;//不能平分则先分出一组-前n组每组的记录数.
    int m = recordCount1 * n;//已分配的记录数.
    while ((recordTotal - m) % (tcount - n) != 0)//余下的记录数和未分配的组不能进行平分.
    {
        //还是不能平分,继续分出一组.
        n++;
        m = recordCount1 * n;
    }
    recordCount2 = (recordTotal - m) / (tcount - n);//余下的记录数和未分配的组能进行平分或者只剩下最后一组了-第n+1组至后面所有组每个组的记录数.
}
//输出.
if (recordCount != 0)
{
    groupResult += "能平均分配,每组" + recordCount + "个.";
}
else
{
    groupResult += "不能平均分配,前" + n + "组,每组" + recordCount1 + "个,";
    if (n < tcount - 1)
    {
        //groupResult += "第" + (groupNumber + 1) + "组至后面所有组,每组" + recordCount2 + "个.";
        groupResult += "第" + (n + 1) + "组至第" + tcount + "组,每组" + recordCount2 + "个.";
    }
    else
    {
        groupResult += "第" + (n + 1) + "组" + recordCount2 + "个.";
    }
}
ViewData["result"] = groupResult;

NTILE()函数算法完成代码

  

  分部方的算法,假若总记录数为59,总组数为5,则
n=4 , recordCount1=12 , recordCount2=11,分组结果为
:12,12,12,12,11。

  假设总记录数为53,总组数为5,则
n=3 , recordCount1=11 ,
recordCount2=10,分组结果为:11,11,11,10,10。

  就拿地方的例证来讲,总记录数为6,总组数为4,通过算法拿到 n=2
, recordCount1=2 , recordCount2=1,分组结果为:2,2,1,1。

 

select ntile,COUNT([ID]) recordCount from 
(
    select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]
) as t
group by t.ntile

 

  运维Sql,分组结果如图:

  图片 15

  比对算法与Sql
Server的分组结果是后生可畏律的,表达算法没有错。:)

 

总结:

在采取排名函数的时候必要注意以下三点:

  1、排名函数必得有 OVETiggo 子句。

  2、排行函数必得有隐含
O奥迪Q7DEKuga BY 的 OVE奥迪Q3 子句。

  3、分组内从1起始排序。

 

感谢:

  在博文的末段自个儿要谢谢园友
海岸线,他写的 SQL2006四个排行函数(row_number、rank、dense_rank和ntile)的比较 对自己扶助超级大,极其感激!

结果如下:

count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value() over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...)

图片 16

 

实现dense_rank()排行函数,按学子岁数(StuAge)排序。

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

结果如下:

图片 17

 

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

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

结果如下:

图片 18

 

金玉满堂分组聚合字符串,即把钦赐列的值拼成字符串。
在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 

结果如下:

图片 19

 

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 

结果如下:

图片 20

 

发表评论

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

网站地图xml地图