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

select date(in_time) as dt, round(sum(timestampdiff(second,in_time,out_time))/COUNT(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where date_format(in_time,"%Y-%m")='2021-11' and artical_id != 0
group by dt
order by avg_viiew_len_sec;

问题分解：2021年11月每天的人均浏览文章时长

• 限定条件：2021年11月，where date_format(in_time,"%Y-%m")='2021-11' and artical_id !=0
• 每天：按天进行分组，group by date_format(in_time,%Y-%m-%d)
• 人均浏览文章时长,总人数需要去重，round(sum(timestampdiff('second',in_time,out_time))count(distinct uid),1) as avg_viiew_len_sec
• 排序：order by avg_viiew_len_sec