举个场景例子 我们要计算app内在每小时区间内访问量前2的服务 根据访问日志处理完后的数据集如下所示
visit_hour | service_name | visit_cnt |
---|---|---|
2021062401 | A | 421 |
2021062401 | B | 710 |
2021062401 | C | 235 |
2021062401 | D | 839 |
2021062402 | A | 63 |
2021062402 | D | 5452 |
2021062402 | E | 152 |
代码如下:
select *
from
(select visit_hour, service_name, visit_cnt,
row_number() over (partition by visit_hour order by visit_cnt desc) as rank_no
from service_visit) a
where a.rank_no <= 2
order by visit_hour, visit_cnt;
(以上只是一个简单场景 其他的的周末补充