一个用户访问页面，页面可以返回继续访问（如A-B-C-A-B-A），求每个用户访问一个页面的最大连续天数，
user_id url date
001 A 2020-07-01 00:09:01
001 B 2020-07-01 00:10:01
001 B 2020-07-01 00:10:22
001 A 2020-07-01 00:11:22
001 B 2020-07-01 00:12:20
001 A 2020-07-01 00:13:02
002 ................................
..
select e.user_id, e.url, max(ss)
from (select d.user_id, d.url, d.cn, count(1) ss
from (select c.*, c.dt - rn cn
from (select b.*,
row_number() over(partition by b.user_id, b.url order by dt) rn
from (select distinct a.user_id,
a.url,
trunc(to_date(dt,
'yyyy-mm-dd hh24:mi:ss')) dt
from urlrecords a) b) c) d
group by d.user_id, d.url, d.cn) e
group by e.user_id, e.url
网页日志记录表 urlrecord
create table urlrecord
(
id string,
url string,
action string,
)
partitioned by(dt string);
样例数据：
id url action dt
1 www.sohu.com view 20201001
2 www.baidu.com click 20201001
3 www.baidu.com click 20201001
4 www.bilibili.com click 20201001
5 www.sohu.com click 20201002
6 www.qq.com click 20201002
.. .. .. ..
题目： 求该表中每天的url被点击（action='click'）次数最多的top 5
输出数据格式 dt,url,pv(点击总数),rank（排名）
注意，如果有排名相同的，rank 排名取相同的值，后一位递增，比如 1 2 3 3 5 ，或者 1 2 3 4 5 5 等
结果数据示例:
dt url pv rank
20201001 www.58.com 3641 1
20201001 www.qq.com 3637 2
20201001 www.douban.com 3634 3
20201001 www.jd.com 3606 4
20201001 www.baidu.com 3574 5
20201001 www.youku.com 3574 5
20201002 www.jd.com 3643 1
select b.*, rank() over(partition by b.url, b.dt order by pv desc) rank
from (select a.dt, a.url, count(1) pv
from urlrecord a
where a.action = 'click'
group by a.url, a.dt) b
