题解 | #每天的平均uv点击率和平均曝光点击率#

with cte as (
select ds,vid,
       count(distinct if(is_click=1,uid,null))/count(distinct uid) as uctr,
       sum(is_click)/count(*) as pctr
from video_recom_click_log_tb
where left(ds,6)='202209'
group by ds,vid )
select ds,concat(round(avg(uctr)*100,1),'%') as avg_uctr,
          concat(round(avg(pctr)*100,1),'%') as avg_pctr
from cte
group by ds
order by ds desc