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

#还是先吐槽发布的题意没读懂。
#题意在用户在search_log_tb这表的,event_time时间与之后的时间之前，点击的情况。
#我之前有个想法，就是直接整天全求出来，后来发现要判断每天点击不为空的次数，因此，要求每人每个时间段的次数，然后在一层求有效点击和均点击值


with tiaojian as (
select 
uid,
event_time,
lead(event_time,1,(select max(event_time) from search_log_tb))over(partition by uid order by event_time asc) as m 
from search_log_tb
)

select 
date(t.event_time) as dt,
round(
avg(case when cnt>0 then 1 else 0 end),3) as click_rate,
round(
avg(cnt),3) as avg_click_cnt
from(
select 
t.uid,
t.event_time,
count(tb.id) as cnt
from tiaojian t left join click_log_tb tb on t.uid=tb.uid
and  tb.event_time between t.event_time and t.m
group by t.event_time,t.uid
) as t 
group by dt