题解 | #每个人的累计搜索点击数排名#

select 
t1.uid,
t1.search_num,
t1.click_num,
t1.m as search_rank,
t1.py as click_rank
from(
select
t.uid,
t.search_num,
t.click_num,
dense_rank()over(order by t.search_num desc) as m,
dense_rank()over(order by t.click_num desc) as py
from(
select 
tg.uid,
ifnull(count(distinct tg.id),0) as search_num,
ifnull(count(distinct cb.id),0) as click_num
from search_log_tb tg left join click_log_tb cb 
on tg.uid=cb.uid
group by tg.uid
) as t 
) as t1
where
t1.m+1<=3
or
t1.py+1<=3