最近在MySQL中遇到分组排序查询时,表结构和初始数据Sql

发布时间:2019-10-02  栏目:数据  评论:0 Comments

最近在MySQL中遇到分组排序查询时,突然发现MySQL中没有row_number()
over(partition by colname)这样的分组排序。
并且由于MySQL中没有类似于SQL
Server中的row_number()、rank()、dense_rank()等排名函数,所有找到以下实现方法,在此简单记录一下。

本文为原创,如需转载,请注明作者和出处,谢谢!
上一篇:SQL
Server2005杂谈(2):公用表表达式(CTE)的递归调用

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法

今天在使用多字段去重时,由于某些字段有多种可能性,只需根据部分字段进行去重,在网上看到了rownumber()
over(partition by col1 order by
col2)去重的方法,很不错,在此记录分享下:
  row_number() OVER ( PARTITION BY COL1 ORDER BY COL2)
表示根据COL1分组,在分组内部根据
COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
  与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.

row_number()rownum差不多,功能更强一点(可以在各个分组内从1开时排序).
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).
dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的.
lag(arg1,arg2,arg3):
  arg1是从其他行返回的表达式
  arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,是一个往回检索以前的行的数目。
  arg3是在arg2表示的数目超出了分组的范围时返回的值。

函数语法:
OPAP函数语法四部分:
1.function
本身用于对窗口中的数据进行操作;
2.partitioning clause
用于将结果集分区;
3.order by clause
用于对分区中的数据进行排序;
4.windowing clause
用于定义function在其上操作的行的集合,即function所影响的范围;

RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause
)
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】聚合函数RANK 和 dense_rank
主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当,
【区别】dence_rank在并列关系是,相关等级不会跳过。rank则跳过
rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。
【说明】Oracle分析函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组,在分组内部根据
COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)
row_number() 返回的主要是“行”的信息,并没有排名
【参数】
【说明】Oracle分析函数

主要功能:用于取前几名,或者最后几名等
sum(…) over …
【功能】连续求和分析函数
【参数】具体参示例
【说明】Oracle分析函数

lag()lead()
【语法】
lag(EXPR,<OFFSET>,<DEFAULT>) OVER (
[query_partition_clause] order_by_clause )
LEAD(EXPR,<OFFSET>,<DEFAULT>) OVER (
[query_partition_clause] order_by_clause )
【功能】表示根据COL1分组,在分组内部根据
COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)
lead () 下一个值 lag() 上一个值

【参数】
EXPR是从其他行返回的表达式
OFFSET是缺省为1 的正数,表示相对行数。希望检索的当前行分区的偏移量
DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。
【说明】Oracle分析函数

---TEST FOR ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
DROP TABLE TEST_Y
CREATE TABLE TEST_Y(
       ID VARCHAR2 (32) PRIMARY KEY ,
       NAME VARCHAR2 (20),
       AGE NUMBER(3 ),
       DETAILS VARCHAR2 (1000)
);
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',20 ,'面朝大海,春暖花开');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',30 ,'面朝大海,春暖花开');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '贝多芬',43 ,'致爱丽丝');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '普希金',34 ,'假如生活欺骗了你');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '杨过',23 ,'黯然销魂掌');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '小龙女',32 ,'神雕侠侣');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '李清照',21 ,'寻寻觅觅、冷冷清清');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '周芷若',18 ,'峨眉');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '赵敏',18 ,'自由');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '张无忌',20 ,'倚天屠龙记');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '张无忌',30 ,'倚天屠龙记');

SELECT * FROM TEST_Y;


----1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
---查询所有姓名,如果同名,则按年龄降序
SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

图片 1

----通过上面的语句可知,ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分组,按AGE字段排序的。
----如果只需查询出不重复的姓名即可,则可使用如下的语句
SELECT * FROM (SELECT NAME,AGE,DETAILS ,ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY AGE DESC)RN FROM TEST_Y )WHERE RN= 1;

图片 2

----由查询结果可知,姓名相同年龄小的数据被过滤掉了;可以使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)对部分子弹进行去重处理


----2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----跳跃排序
SELECT NAME ,AGE,DETAILS , RANK() OVER (PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

图片 3

----由查询结果可知,相同的并列,下一个则跳跃到并列所替的序列后:如有两个并列1,那么下一个则直接排为3,跳过2;

----3.DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----连续排序,当有多个并列时,下一个仍然连续有序

图片 4

----由查询结果可知,当两个并列为1时,下一个仍连续有序为2,不跳跃到3

 Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率.
  Lag和Lead偏移量函数,其用途是:可以查出同一字段下一个值或上一个值,并作为新列存在表中.
-----4.LAG(exp_str,offset,defval) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的下一个exp_str;defval当该函数无值可用的情况下返回该值。
(1) SELECT NAME ,AGE,DETAILS, LAG(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_

图片 5

(2) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

图片 6

(3) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

图片 7

----5.LEAD(EXP_STR,OFFSET,DEFVAL) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的上一个exp_str;
-----defval当该函数无值可用的情况下返回该值。
(1)SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM 

图片 8

(2) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

图片 9

(3) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

图片 10

-----6.SUM(COL1) OVER([PARTITION BY COL2 ] [ORDER BY COL3])
(1) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PART

图片 11

(2) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PAR

图片 12

(3)SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( ORDE

图片 13

(4) SELECT NAME ,AGE,DETAILS, SUM(AGE) OVER ()  FROM TEST_Y;

图片 14

 

以上内容摘抄自:

 

over()分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
例子:

  排名函数是Sql
Server2005新增的功能,下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。

 

    排名函数是SQL
Server2005新加的功能。在SQL
Server2005中有如下四个排名函数:

sum(x) over( partition by y ORDER BY z ) 分析

 

之前用过row_number(),rank()等排序与over( partition by … ORDER BY
…),这两个比较好理解: 先分组,然后在组内排名。

今天突然碰到sum(…) over( partition by … ORDER BY …
),居然搞不清除怎么执行的,所以查了些资料,做了下实操。

  1. 从最简单的开始

  sum(…) over( ),对所有行求和

  sum(…) over( order by … ),和 = 第一行 到
与当前行同序号行的最后一行的所有值求和,文字不太好理解,请看下图的算法解析。

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序,求和当前行所在顺序号的C列所有值
sum(c) over() sum2--无排序,求和 C列所有值

sum() over()

图片 15

  1. 与 partition by 结合

  sum(…) over( partition by… ),同组内所行求和

  sum(…) over( partition by… order by …
),同第1点中的排序求和原理,只是范围限制在组内

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
  FROM aa;

view sql

图片 16

 

以上内容摘抄自:

 

案例:

有圈子表CMSocial,圈子成员表CMSocialMember,圈子审核表CMSocialCheck,其中圈子审核被拒绝的话,修改信息后可以再次提交审核,也就是说圈子可以生成多条圈子审核信息。

如果要查询某用户的全部圈子,同时获取其中每条圈子对应的最近一条审核状态?(假设某用户MemberID=1 )

SQL语句可以这样写:

SELECT 
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
SC.CheckState,
SC.Notes,
SM.CMSocialMemberID,
SM.MemberID,
SM.MemberName,
SM.MemberIntegral,
SM.EnterTime,
SM.MemberState,
SM.MemberRank,
SM.IsRecommend
FROM (SELECT * FROM CMSocialMember WHERE MemberID=1 AND IsDelete<>1 AND IsAvailable=1) AS SM 
LEFT JOIN CMSocial AS S ON S.CMSocialID=SM.CMSocialID 
LEFT JOIN (
SELECT *
FROM ( 
    SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*  /* 根据 CMSocialID 分组,CreateTime倒序,生成分组内部序号 */
    FROM CMSocialCheck WHERE IsDelete<>1
    ) AS SCsub WHERE SCsub.group_index=1 /*取每个分组内部序号=1 的信息*/
) AS SC ON SC.CMSocialID=S.CMSocialID

 

注意:

SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*      /* 根据 CMSocialID
分组,CreateTime倒序,生成分组内部序号 */
FROM CMSocialCheck WHERE IsDelete<>1
) AS SCsub WHERE
SCsub.group_index=1 /*取每个分组内部序号=1 的信息*/

 

sql根据某一个字段重复只取第一条数据
使用分析函数row_number() over (partiion by … order by
…)来进行分组编号,然后取分组标号值为1的记录即可。目前主流的数据库都有支持分析函数,很好用。
其中,partition by
是指定按哪些字段进行分组,这些字段值相同的记录将在一起编号;order
by则是指定在同一组中进行编号时是按照怎样的顺序。
示例(SQL Server 2005或以上适用):

select s.*  
from ( 
    select *, row_number() over (partition by [手机号] order by [店铺]) as group_idx  
    from table_name
) s
where s.group_idx = 1

 

主表1条数据,对应子表,附表多条数据,取唯一:

DECLARE @Status INT;
SET @Status=1;
SELECT  p.CFProjectID AS ID,pc.Status AS StatusID,* FROM dbo.CFProject p
LEFT JOIN (
    select s.*  
    from ( 
        select *, row_number() over (partition by [SourceProjectID] order by [CFProjectID]) as group_idx  
        from dbo.CFProjectToCrowdSpace WHERE [Status]=@Status OR @Status IS NULL
    ) s
    where s.group_idx = 1
) pc ON pc.SourceProjectID=p.CFProjectId
WHERE p.CreatorID=100273  AND p.Status=3  AND p.OrganizationID=180  ORDER  BY p.CreationTime DESC

 

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

 

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

1. row_number

通过class班级进行分组,并根据score分数进行排序,用rank()函数排序方法为mm列赋予序号,然后mm=1就可以找到每组的第一名,当然可以根据score就行倒序可以找到最后一名。

图片 17图片 18

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;

2. rank

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

测试数据如下:

3. dense_rank

简单的说row_number()从1开始,为每一条分组记录返回一个数字,
row_number() over(order by score desc)是先把score
列降序,再为降序以后的没条xlh记录返回一个序号。(如果没有分组可以理解成将整个结果作为一个分组)

表结构和初始数据Sql

图片 19

4. ntile   
   
下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示:

row_number() over(partition by class order by score
desc)表示根据class分组,在分组内部根据 score
排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

 

 

图片 20

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

 

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

图1

作为分数函数中有关排序的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;

其中field1字段的类型是int,field2字段的类型是varchar

rank()
over是的作用是查出指定条件后进行一个排名,但是有一个特点。假如是对学生排名,那么实用这个函数,成绩相同的两名是并列(名次为1,2,2,4)

  图片 21

结果如下:

一、row_number

dense_rank()的作用和rank()很像,唯一区别就是,相同成绩并列以后,下一位同学并不空出并列所占的名次(名次为1,2,2,3)

 

图片 22

    row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示:

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]

 

  查询结果如下图所示:

  图片 23

  图中的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

  查询结果如下图所示:

  图片 24

  利用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

  查询结果如下图所示:

  图片 25

  注意:在使用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

  查询结果如下图所示:

  图片 26

  

 

 

对于多表查询,可以为空置加上一个判断来显示查询数据为空的数据。

二、RANK

  rank函数用于返回结果集的分区内每行的排名, 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。可能我描述的比较苍白,理解起来也比较吃力,我们直接上代码,rank函数的使用方法与row_number函数完全相同。

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

  查询结果如下图所示:

  图片 27

  由上图可以看出,rank函数在进行排名时,同一组的序号是一样的,而后面的则是根据当前的记录数依次类推,图中第一、二条记录的用户Id相同,所以他们的序号是一样的,第三条记录的序号则是3。  

 

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

select row_number() over(order by field1) as row_number,* from t_table

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]

  查询结果如下图所示:

  图片 28

  图中第一、二条记录的用户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;

    上面的SQL语句的查询结果如图2所示。

其他常用的分析函数:

四、NTILE

  ntile函数可以对序号进行分组处理,将有序分区中的行分发到指定数目的组中。 各个组有编号,编号从一开始。 对于每一个行,ntile
将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理:

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

  查询结果如下图所示:

  图片 29

  Order表的总记录数是6条,而上面的Sql语句ntile函数指定的组数是4,那么Sql
Server2005是怎么来决定每一组应该分多少条记录呢?这里我们就需要了解ntile函数的分组依据(约定)。

  ntile函数的分组依据(约定):

  1、每组的记录数不能大于它上一组的记录数,即编号小的桶放的记录数不能小于编号大的桶。**也就是说,第1组中的记录数只能大于等于第2组及以后各组中的记录数。**

  2、所有组中的记录数要么都相同,要么从某一个记录较少的组(命名为X)开始后面所有组的记录数都与该组(X组)的记录数相同。也就是说,如果有个组,前三组的记录数都是9,而第四组的记录数是8,那么第五组和第六组的记录数也必须是8。

  这里对约定2进行详细说明一下,以便于更好的理解。

  首先系统会去检查能不能对所有满足条件的记录进行平均分组,若能则直接平均分配就完成分组了;若不能,则会先分出一个组,这个组分多少条记录呢?就是
(总记录数/总组数)+1 条,之所以分配 (总记录数/总组数)+1
条是因为当不能进行平均分组时,总记录数%总组数肯定是有余的,又因为分组约定1,所以先分出去的组需要+1条。

  分完之后系统会继续去比较余下的记录数和未分配的组数能不能进行平均分配,若能,则平均分配余下的记录;若不能,则再分出去一组,这个组的记录数也是(总记录数/总组数)+1条。

  然后系统继续去比较余下的记录数和未分配的组数能不能进行平均分配,若能,则平均分配余下的记录;若还是不能,则再分配出去一组,继续比较余下的……这样一直进行下去,直至分组完成。

  举个例子,将51条记录分配成5组,51%5==1不能平均分配,则先分出去一组(51/5)+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.
}

 

图片 30图片 31

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,分组结果如图:

  图片 32

  比对算法与Sql
Server的分组结果是一致的,说明算法没错。:)

 

总结:

在使用排名函数的时候需要注意以下三点:

  1、排名函数必须有 OVER 子句。

  2、排名函数必须有包含
ORDER BY 的 OVER 子句。

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

 

感谢:

  在博文的最后我要感谢园友
海岸线,他写的 SQL2005四个排名函数(row_number、rank、dense_rank和ntile)的比较 对我帮助很大,非常感谢!

相关文章

留下评论

网站地图xml地图