oracle之partition by与group by的区别

发表于   |   分类于 ，   |   阅读次数

正文

前言

先来看两个表

  • 主表(webcheck——存储网站记录)
webcheckid website
1 www.baidu.com
2 www.google.com
3 www.soso.com
  • 副表(webrecord——访问网站记录表)
webrecordid webcheckid access_state access_date
1 1 成功 2018-01-10 18:03:49
2 1 成功 2018-01-09 18:03:49
3 2 失败 2018-01-11 18:03:49

我想要关联副表，查询每个网站最新访问记录，即使网站没有访问记录，也要能查询出来

  • 预计实现效果为
webcheckid website webrecordid access_state access_date
1 www.baidu.com 1 成功 2018-01-10 18:03:49
2 www.google.com 3 失败 2018-01-11 18:03:49
3 www.soso.com 
1
2
3
4
--以下是错误的sql，唯一难住我的是就是关联查询最新记录了，之前想当然地用group by来分组获取最新记录，但是会报错，group by的字段必须与查询的字段相对应，除非是聚合函数包裹着
select a.WEBCHECKID,a.WEBSITE,b.RECORDID,b.access_state,b.access_date from TFSWXTS_WEBCHECK a left join TFSWXTS_WEBRECORD b on a.WEBCHECKID=b.WEBCHECKID 
group by b.WEBCHECKID,b.access_date having b.access_state=max(b.access_state) 
--报错不是group by 的表达式

改为使用以下语句

1
2
3
4
5
row_number() over(partition by b.webcheckid order by b.access_date desc) 

--partition by 按字段分组
--order by 分组，按组内排序，desc即最新在第一条
--row_number() 按分组内的行个数，生成行号；1,2,3，……

推出新的sql

1
2
3
4
5
6
7
--行号作为新字段,并用一个新select包裹，获取第一行数据，即为新记录
select * from ( 
         select a.WEBCHECKID,a.WEBSITE,b.RECORDID,b.access_state,b.access_date,  
         
         row_number() over(partition by b.webcheckid order by b.access_date desc) as ran
          from TFSWXTS_WEBCHECK a left join TFSWXTS_WEBRECORD b on a.WEBCHECKID=b.WEBCHECKID 
        ) where recordid is  null or ran=1

– recordid is null是为了防止当前webrecord表里面没有数据，行号数据为混乱，行号此时就无法作为判断条件

  • 例如当soso和google在webrecord没有访问记录时，错误实现效果为，soso这一数据没有显示出来
webcheckid website webrecordid access_state access_date ran
1 www.baidu.com 1 成功 2018-01-10 18:03:49 1
2 www.google.com 1
  • 去外部条件，我们来看看效果，可以看到soso的行号为2，明显不正常，故需要增加 recordid is null，并需要or条件
webcheckid website webrecordid access_state access_date ran
1 www.baidu.com 1 成功 2018-01-10 18:03:49 1
1 www.baidu.com 1 成功 2018-01-09 18:03:49 2
2 www.google.com 1
1 www.soso.com 2

成功实现效果为

webcheckid website webrecordid access_state access_date ran
1 www.baidu.com 1 成功 2018-01-10 18:03:49 1
2 www.google.com 3 失败 2018-01-11 18:03:49 1
3 www.soso.com 2