背景是这样的,我有一个表是以毫秒级维度存储的数据,但是实际需求需要先以秒级维度做一个聚合分组查询,然后对于查询出来的数据,再以某一列做分组查询,显然,使用group by 列1,列2是不能实现的
举一个类似的例子说明:
🌰:我有一个在线枪战游戏,对应一个开枪记录表,表中记录的字段有开枪时刻(毫秒),开枪人等
开枪时刻(毫秒) | 开枪人 |
2021-10-13 00:00:01 | 张三 |
2021-10-13 00:00:02 | 张三 |
2021-10-13 00:00:03 | 张三 |
2021-10-13 00:00:01 | 李四 |
2021-10-13 00:00:05 | 李四 |
2021-10-13 00:00:09 | 李四 |
2021-10-13 00:00:01 | 王二麻子 |
2021-10-13 00:00:51 | 王二麻子 |
2021-10-13 00:00:01 | 赵四 |
我这里有一个同学录名单,现在我想要知道昨天一天的时间内,同学录名单中每个人的最大开枪qps是多少
这里对需求分析步骤
- 因为表中是毫秒记录的时间,先对时间做秒维度和开枪人的聚合分组,对开枪qps做倒排 然后再对开枪人做聚合分组,取最大的qps
- 但由于不能一次性做两个group by,这里使用创建临时表解决
直接看解法
create table tmp as
select 开枪人,from_unixtime(time/1000) as time ,count(1) as qps from 开枪记录表
where log__partition_time='20211012' and 开枪人 in (开枪人名单)
group by from_unixtime(time/1000),开枪人
order by qps desc limit 10000000;
select 开枪人,MAX(qps) FROM tmp group by 开枪人;
问题解决
但是不要忘记删除创建的临时表
DROP table tmp;
我们应该举一反三
总结:
当判断出来我们的sql语句需要进行多次分阶段分组查询时,也就是需要group by 多条件分组查询但条件不能在分组并列查询时,可以考虑分阶段查询,并把每个阶段的值存放在临时表解决。