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

--mySQL

select date_format(a.in_time,"%Y-%m-%d") as dt,round(sum(timestampdiff(second,a.in_time,out_time))/count(distinct a.uid),1) as avg_view_len_sec
from tb_user_log a
where year(a.in_time) = 2021 and month(a.in_time) = 11 and a.artical_id <> 0
group by date_format(a.in_time,"%Y-%m-%d")
order by 2 asc

--SQL SERVER

select CONVERT(varchar(10),a.in_time,120) dt,round(sum(DATEDIFF(SECOND,a.in_time,a.out_time))*1.0/COUNT(distinct a.uid),1) as avg_view_len_sec
from tb_user_log a
where year(a.in_time) = 2021 and month(a.in_time) = 11 and a.artical_id <> 0
group by CONVERT(varchar(10),a.in_time,120)
order by round(sum(DATEDIFF(SECOND,a.in_time,a.out_time))*1.0/COUNT(distinct a.uid),1) asc