场景准备
某张表里有字段:provinceid、hour、adid,表示省份id、小时段和广告id,每一行表示一份广告点击数据,模拟数据如下。
省份id 广告id
1 1 100
1 1 100
1 2 100
1 2 112
1 1 101
1 2 112
1 2 102
1 1 102
1 1 103
1 2 112
1 2 112
1 1 101
1 2 112
2 1 100
2 1 121
2 2 101
2 2 121
2 1 104
2 2 121
2 2 111
2 1 104
2 1 103
2 2 111
2 2 121
2 1 104
3 2 121
3 2 112
3 1 112
3 2 121
3 1 100
业务要求
统计每一个省份每一个小时点击量Top3的广告,格式如下:
proid hour adid countclick rnk
1 1 100 2 1
1 1 101 2 2
1 1 102 1 3
1 2 112 5 1
1 2 100 1 2
1 2 102 1 3
2 1 104 3 1
2 1 103 1 2
2 1 100 1 3
2 2 121 3 1
2 2 111 2 2
2 2 101 1 3
3 1 100 1 1
3 1 112 1 2
3 2 121 2 1
3 2 112 1 2
SQL分析
select
proid,
hour,
adid,
countClick,
rnk
from
(
select
proid,
hour,
adid,
countClick,
row_number() over(
distribute by proid, hour sort by countClick desc
) rnk
from
(
select
proid,
hour,
adid,
count(*) countClick
from
pro_hour_ad_tmp
group by
proid,
hour,
adid
) t1 # 分组统计省份、小时、广告和点击量
) t # 根据省份id、小时分区,按点击量降序排列,开窗口数据给排名函数
where
rnk <= 3;