SQL PARTITION BY

    前两天看前辈们的老代码,看到了一句神奇的SQL,生平第一次见:

select ……………… from
	(select xx1,xx2,xx3,……,row_number() 
	over(partition by xx4,xx5 order by xx6 desc,xx7 desc) rownum 
	from xxxx_tbl 
	where xxx8='sdfdsf' ……)
where rownum =1

为防止公司说泄露源码,就只能这样表示一下意思了,这句sql的灵魂之处在于row_number() over(partition by xx4,xx5 order by xx6 desc,xx7 desc),你们见没见过我不知道,反正我以前没见过。所以一度没看懂。然后就一探究竟了,为了更加直观,我们还是拿前一篇limit重复问题里的那种表来进行举例。

案例

表结构如下:

字段类型注释
idvarchar(20)主键
col1varchar(20)col1
col2varchar(20)col2
col3varchar(20)col3

全表查询:

SELECT * FROM test1 ORDER BY col1 DESC;

数据为:

idcol1col2col3
155910
12256
14278
16245
11123

其中col1字段不是唯一的,第二第三第四行的col1都是2。

上关键sql:

SELECT id,col1,col2,col3, 
row_number() over (PARTITION BY col1 ORDER BY id DESC) AS row_num 
FROM test1 ;

返回结果:

idcol1col2col3row_num
111231
162451
142782
122563
1559101

我们看这个返回结果,其中row_num列即为这段神奇的sql产生的,这一列的序号怎么来的呢:他是按照col1进行分组,然后按照id列进行倒序排序,row_num为分组后组内排序的结果。

分析

    看完案例中的sql和执行结果,其实我们就能猜出开头的那个sql的目的了,他是要获取分组后每组的第一个值。其实我们经常会遇到这样的案例,比如:给你一个全年级学生的分数表,我要获取每个班分数最高的前三名。如果说这个年级有多少个班是已知的,我们可以通过union一个一个子查询拼接起来,但是如果班级个数未知,那这时候如果想用一句sql就有点无奈了。
    同样,如果我们想要获取的是每个班级分数最高的一个人,我们也可以通过group by加max函数再加子查询解决,但是这里不是一个。

partition by与group by

    一开始其实没搞明白,同样是分组partition by和group by有什么区别。从用法上来看:
partition by

select xx1,xx2,xx3,……,row_number() 
	over(partition by xx4,xx5 order by xx6 desc,xx7 desc) rownum 
	from xxxx_tbl

group by

select xx1,max(xx2)
	from xxxx_tbl group by xx1

partition by是用在返回参数中的,而group by是用在约束里的。而深层里去理解,partition by是分组后进行组内逐条分析,比如这里的row_number() over,而groupy by则是分组后进行整组的聚合分析,比如上面的max()。

扩展

    既然是用于分析的,肯定有一些常用的与之配合的分析函数,比如group常和sum、min、max等组合使用。partition by除了上面的row_number外还有以下一些常用的配合:

  • max:获取组内已排序的最大值
SELECT id,col1,col2,col3, MAX(col3) 
over (PARTITION BY col1 ORDER BY id DESC) AS row_num FROM test1 ;
idcol1col2col3row_num
111233
162455
142788
122568
15591010
  • rank:排名的时候用row_number不是很好,原因是如果有两行order by的id相同,那么row_number就会漏掉其中的一行,而rank则不会漏,同时rank是跳跃排名,比如有两个第二名,那第四个就是第四名,而不是第三名
SELECT id,col1,col2,col3, rank() 
over (PARTITION BY col1 ORDER BY id DESC) AS row_num FROM test1 ;
idcol1col2col3row_num
111231
162451
142782
122563
1559101
  • dense_rank:和rank一样,也是能够查出所有的记录,但是他不是跳跃排名,两个第二名之后是第三名。
SELECT id,col1,col2,col3, dense_rank() over (PARTITION BY col1 ORDER BY id DESC) AS row_num FROM test1 ;
idcol1col2col3row_num
111231
162451
142782
122563
1559101
  • 1
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值