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

-- 每天人均浏览文章时长 = 文章浏览总时长 / 总浏览人数
SELECT dt,
    ROUND(SUM(cost_time) / COUNT(DISTINCT uid), 1) AS avg_viiew_len_sec
FROM (
    SELECT uid,
        SUBSTRING(in_time, 1, 10) AS dt,
        TIMESTAMPDIFF(SECOND, in_time, out_time) AS cost_time
    FROM tb_user_log
    WHERE YEAR(in_time) = '2021'
        AND MONTH(in_time) = '11'
        AND artical_id != 0
) sel_table
GROUP BY dt
ORDER BY avg_viiew_len_sec