题解 | #首个点击位置的频率分布#

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