分组计算【永利皇宫登录网址】,Server排名函数

近日在MySQL中遇见分组排序查询时,突然意识MySQL中绝非row_number()
over(partition by colname)那样的分组排序。
并且鉴于MySQL中一向不像样于SQL
Server中的row_number()、rank()、dense_rank()等排名函数,全部找到以下完结格局,在此轻巧记录一下。

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

前日在运用多字段去重时,由于一些字段有两种可能,只需依附一些字段张开去重,在互连网来看了rownumber()
over(partition by col壹 order by
col二)去重的措施,很不利,在此记录分享下:
  row_number() OVE奥迪Q3 ( PARTITION BY COL一 O逍客DE奔驰M级 BY COL二)
表示依照COL一分组,在分组内部依据COL二排序,而此函数总括的值就代表每组内部排序后的逐条编号(组内一连的绝无仅有的).
  与rownum的区分在于:使用rownum进行排序的时候是先对结果集参与伪列rownum然后再开始展览排序,而此函数在含蓄排序从句后是先排序再总括行号码.

row_number()rownum大概,功用越来越强一点(可以在相继分组内从一开时排序).
rank()是跳跃排序,有五个第1名时接下去就是第肆名(同样是在家家户户分组内).
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()是跳跃排序,有几个第叁名时接下去就是第陆名(一样是在1一分组内)
dense_rank()l是接连排序,有五个第3名时还是跟着第二名。
【表明】Oracle分析函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功效】表示根据COL一分组,在分组内部根据COL2排序,而以此值就意味着每组内部排序后的依次编号(组内接二连三的唯一的)
row_number() 重返的第3是“行”的音讯,并从未排名
【参数】
【表达】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 )
【功用】表示依据COL一分组,在分组内部根据COL二排序,而那些值就表示每组内部排序后的一一编号(组内连续的绝无仅有的)
lead () 下多少个值 lag() 上一个值

【参数】
EXPOdyssey是从别的行再次回到的表达式
OFFSET是缺省为一 的正数,表示相对行数。希望物色的脚下行分区的偏移量
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

 

上述内容摘要自:

 

 

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

 

前边用过row_number(),rank()等排序与over( partition by … OEscortDE路虎极光 BY
…),那三个比较好领悟: 先分组,然后在组内排行。

先天黑马遇上sum(…) over( partition by … ORubiconDELAND BY …
),居然搞不免除怎么实行的,所以查了些资料,做了下实操。

  1. 从最简单易行的始发

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

  sum(…) over( order by … ),和 = 第2行 到
与目前行同序号行的末梢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 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 …
),同第3点中的排序求和法则,只是范围限定在组内

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=一 )

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
分组,Create提姆e倒序,生成分组内部序号 */
FROM CMSocialCheck WHERE IsDelete<>1
) AS SCsub WHERE
SCsub.group_index=1 /*取每种分组内部序号=一 的新闻*/

 

sql依照某1个字段重复只取第2条数据
行使分析函数row_number() over (partiion by … order by
…)来拓展分组编号,然后取分组标号值为一的笔录就能够。方今主流的数据库都有帮助分析函数,很好用。
里面,partition by
是点名按什么字段实行分组,这个字段值同样的笔录将在一道编号;order
by则是点名在同样组中开始展览编号时是依照什么样的次第。
以身作则(SQL Server 二〇〇六或以上适用):

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

 

率先创设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;

测试数据如下:

永利皇宫登录网址 17

 

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

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

结果如下:

永利皇宫登录网址 18

 

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

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

结果如下:

永利皇宫登录网址 19

 

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

网站地图xml地图