如何用GROUP BY按日期统计满足累计观影时长要求的有效观影数

该需求完全可以通过SQL实现，不需要在应用层处理，以下分别对应两个统计规则的实现方案：

场景1：仅按用户维度统计有效观影 #

原有写法问题说明 #

你原有按user_id分组直接取created_at的写法存在问题：MySQL5.7未开启ONLY_FULL_GROUP_BY sql_mode时，分组后取非聚合非分组字段会返回分组内第一条匹配的记录值，并非你需要的累计时长达标日期，所以会出现计数错误归到2021-10-15的问题。

MySQL 5.7 实现代码 #

SELECT IFNULL(COUNT(t.reach_date),0) AS cnt, d.created_at
FROM (
    -- 先拿到所有日期列表
    SELECT DISTINCT created_at FROM watch_time
) d
LEFT JOIN (
    -- 计算每个用户首次达标日期
    SELECT user_id, MIN(created_at) AS reach_date
    FROM (
        -- 用用户变量计算每个用户的累计观看时长
        SELECT 
            w.user_id,
            w.created_at,
            @sum_duration := IF(@current_user = w.user_id, @sum_duration + w.duration, w.duration) AS total_duration,
            @current_user := w.user_id
        FROM watch_time w
        CROSS JOIN (SELECT @current_user := 0, @sum_duration := 0) init
        ORDER BY w.user_id, w.created_at
    ) t
    WHERE t.total_duration >= 120
    GROUP BY user_id
) t ON d.created_at = t.reach_date
GROUP BY d.created_at
ORDER BY d.created_at;

运行该代码返回结果完全匹配你给出的第一版预期。

场景2：按用户+影片维度统计有效观影（对应更新2需求） #

仅需要调整累计时长统计的分组维度为user_id + film_id即可，实现代码如下：

SELECT IFNULL(COUNT(t.reach_date),0) AS cnt, d.created_at
FROM (
    SELECT DISTINCT created_at FROM watch_time
) d
LEFT JOIN (
    SELECT user_id, film_id, MIN(created_at) AS reach_date
    FROM (
        SELECT 
            w.user_id,
            w.film_id,
            w.created_at,
            @sum_duration := IF(@current_user = w.user_id AND @current_film = w.film_id, @sum_duration + w.duration, w.duration) AS total_duration,
            @current_user := w.user_id,
            @current_film := w.film_id
        FROM watch_time w
        CROSS JOIN (SELECT @current_user := 0, @current_film := 0, @sum_duration := 0) init
        ORDER BY w.user_id, w.film_id, w.created_at
    ) t
    WHERE t.total_duration >= 120
    GROUP BY user_id, film_id
) t ON d.created_at = t.reach_date
GROUP BY d.created_at
ORDER BY d.created_at;

运行该代码返回结果完全匹配你更新2后的预期。

Postgres版本适配 #

如果切换到Postgres可以用窗口函数实现，逻辑更清晰，也不会有用户变量的兼容性问题，场景2的Postgres实现示例：

SELECT COALESCE(COUNT(t.reach_date),0) AS cnt, d.created_at
FROM (
    SELECT DISTINCT created_at FROM watch_time
) d
LEFT JOIN (
    SELECT user_id, film_id, MIN(created_at) AS reach_date
    FROM (
        SELECT 
            user_id,
            film_id,
            created_at,
            SUM(duration) OVER (PARTITION BY user_id, film_id ORDER BY created_at) AS total_duration
        FROM watch_time
    ) t
    WHERE total_duration >= 120
    GROUP BY user_id, film_id
) t ON d.created_at = t.reach_date
GROUP BY d.created_at
ORDER BY d.created_at;

内容的提问来源于stack exchange，提问作者Serg