题解 | #首个点击位置的频率分布#
首个点击位置的频率分布
https://www.nowcoder.com/practice/81fef8ec0fe244cc9548883d28d48bd1
with f as ( select e.pos as first_pos, count(1) as pos_cnt from (select a.uid, a.event_time, min(b.event_time) as first_clicktime from ( select distinct uid, event_time, query_kw, lead(event_time,1,(select date_add(max(event_time),interval 1 second) from click_log_tb))over(partition by uid order by event_time asc) as next_eventtime from search_log_tb ) a left join click_log_tb b on a.uid = b.uid and (b.event_time >= a.event_time and b.event_time < a.next_eventtime) group by a.uid, a.event_time ) d left join ( select distinct uid, event_time, pos from click_log_tb ) e on d.uid = e.uid and d.first_clicktime = e.event_time group by e.pos having e.pos is not null ) select f.first_pos, f.pos_cnt, round(f.pos_cnt/g.sum_pos_cnt,3) as ratio from f join ( select sum(pos_cnt) as sum_pos_cnt from f ) g order by ratio desc,first_pos asc