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

SELECT
    dt,
    ROUND(SUM(duration) / COUNT(DISTINCT uid), 1) avg_viiew_len_sec
FROM
    (
        SELECT
            uid,
            artical_id,
            SUBSTRING(in_time, 1, 10) dt,
            TIMESTAMPDIFF (SECOND, in_time, out_time) duration
        FROM
            tb_user_log
        WHERE
            SUBSTRING(in_time, 1, 10) BETWEEN '2021-11-01' AND '2021-11-30'
            AND artical_id != 0
    ) t1
GROUP BY
    dt
ORDER BY
    avg_viiew_len_sec;
	
	采用子查询的方法提取中间表格，这样处理起来比较方便。