题解 | #2021年11月每天的人均浏览文章时长#

#1 先聚合出每一天每个uid浏览文章的总时长
#2 再聚合出每一天的人均文章浏览时长

select
dt
,round(avg(viiew_len_sec), 1) avg_viiew_len_sec
from
(
    select
    date(in_time) dt
    ,uid
    ,sum(timestampdiff(second, in_time, out_time)) viiew_len_sec
    from tb_user_log
    where date(in_time) like '2021-11%' and artical_id != 0
    group by date(in_time), uid
) t
group by dt
order by round(avg(viiew_len_sec), 1)