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

SELECT
    dt,
    ROUND(SUM(time_diff) / COUNT(DISTINCT uid), 1) AS avg_viiew_len_sec # 人均浏览时长
FROM(
    SELECT
        uid,
        DATE(out_time) AS dt,
        TIMESTAMPDIFF(SECOND, in_time, out_time) AS time_diff
    FROM tb_user_log
    WHERE DATE_FORMAT(out_time, '%Y-%m') = '2021-11' AND artical_id != 0 # 条件2021年11月, artical-id不能为0
) AS tb1
GROUP BY dt
ORDER BY avg_viiew_len_sec ASC # 短到长排序