题解 | #计算每天的有点比和query平均点击数#

select date(hd) dt,round(avg(if(times>0,1,0)),3) click_rate,round(avg(times),3) avg_click_cnt from

(select t1.uid,hd,count(t2.event_time) times

from

(select uid,event_time hd,lead(event_time,1,now()) over(partition by uid order by event_time) ed

from search_log_tb) t1 left join click_log_tb t2 on t1.uid = t2.uid and t2.event_time between t1.hd and t1.ed

group by uid,hd) as e group by date(hd)

order by dt