分组查询
什么是分组查询:分组查询是按照一定的规则进行分组,分组以后数据会聚合,需要使用聚合函数,但是使用聚合函数不一定要分组,分组的关键字是group by
场景描述
数据列表中包含不同作者,不同书籍,根据不同时间收藏人数的统计(每天存在同一书籍多次不同时间收藏人数的记录)
想要对列表中的数据进行作者的不同作品分类,选择同一作品中最新的记录进入统计
也即获取同一分组中最新的记录
实践
获取分组后的每组最新数据
select request_date, max(request_time) request_time,author,bookname from T_AUTHOR_REQUEST_STATISTIC group by author
执行该语句后报错
分析
含有group by的sql语句,只能展示group by的属性,以及聚合属性,其它列不能select,否则就会报上述错误
正确使用:
select author, max(request_time) request_time from T_AUTHOR_REQUEST_STATISTIC group by author
但是我同时想要展示其他的列,并查看相关信息
而上述方式需要在group by 后面加上所有想要展示的列,才能在结果中包含该列,这样比较麻烦,然后就发现网上有其它方式可以实现同样的需求
优化方法
首先,根据作者排序,然后对该作者的所有作品排序,再对同一作品进行指定日期的入库记录的时间排序
获取同一本书在指定日期的排序 最后一次收藏人数
获取组内数据的排序,除了指定日期,并没有对表内数据进行筛选
select t.* , row_number() over (partition by author,bookname order by request_time desc) rn
from T_AUTHOR_REQUEST_STATISTIC t where t.request_date=to_date('2020/11/24','yyyy/MM/dd')
执行结果:
- t.*
表示数据表中的所有列; - row_number()
表示对表中的数据 over (partition by col1,col2…, order by request_time desc) 按col1,col2…分组后按照request_time排序,rn即在col1分组中的序列号展示
我们关心的是最新的收藏人数,因此需要获取当前作品的最新统计(收藏人数)
筛选组内最新数据
select * from
(
select t.* , row_number() over (partition by author,bookname order by request_time desc) rn
from T_AUTHOR_REQUEST_STATISTIC t where t.request_date=to_date('2020/11/24','yyyy/MM/dd')
) where rn=1
执行结果:
- rn
rn代表的分组后组内的顺序,因为我们是按照时间倒序排列,因此rn=1的时候就是组内的最新数据
统计每个作者数据库记录中每本书的最新收藏人数
select sum(collectNum) collectNum,sum(GENERATED_COUNT) GENERATED_COUNT from(
select * from
(
select t.* , row_number() over (partition by author,bookname order by request_time desc) rn
from T_AUTHOR_REQUEST_STATISTIC t where t.request_date=to_date('2020/11/24','yyyy/MM/dd')
) where rn=1
) group by author
执行结果:
获取需要的统计结果