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

WITH t1 AS (
    SELECT DATE(in_time)                                 AS dt,
           SUM(TIMESTAMPDIFF(SECOND, in_time, out_time)) AS sum_viiew_len_sec,
           COUNT(DISTINCT uid)                           AS total
    FROM tb_user_log
    WHERE DATE (in_time) BETWEEN '2021-11-01' AND '2021-11-30' AND artical_id != 0
    GROUP BY dt
)
SELECT
    dt,
    ROUND(sum_viiew_len_sec / total,1) AS avg_viiew_len_sec
FROM t1
ORDER BY avg_viiew_len_sec ASC;

# 关键在于时间筛选和文章id不为0