SQL实现用户重复观看直播的行为统计需求求助

表结构与需求 #

我是SQL新手，现有需求如下：

• 有MySQL表t_pv，记录用户观看直播信息，核心字段：
  • user_id（用户ID）
  • account_id（直播ID）
  • enter_time（进入直播间时间，格式如HH:MM:SS）
  • leave_time（离开直播间时间）
  • stay_time（停留时长，单位秒）
• 用户可能多次观看同一直播，存在多位用户。

表数据示例 #

预期输出 #

需要生成新表，统计用户重复观看直播的行为，输出字段：

• user_id：用户ID
• most_retargeting_count：用户进入次数最多的直播的进入次数
• most_retargeting_account_id：该直播ID
• avg_most_enter_retargeting_gap：该直播的平均进入间隔（本次进入时间与上一次离开时间的间隔平均值）
• avg_most_watching_time：该直播的平均观看时长

预期结果示例：

尝试的错误SQL #

我写了下面的SQL，但存在明显错误，无法得到正确结果：

INSERT OVERWRITE TABLE t_new
SELECT 
t_cnt.most_retargeting_account_count most_retargeting_account_count,
t_cnt.most_retargeting_account_id most_retargeting_account_id,
t_avg.avg_watching_time avg_watching_time,
t_avg.avg_retargeting_time_time

FROM  (
    (select 
        t_pv.account_id as most_retargeting_account_id, count(t_pv.account_id) as most_retargeting_account_count
        from t_pv
        group by user_id
        order by count(t_pv.account_id) desc
    ) t_cnt 
    right join(
        (select 
            t_pv.user_id as user_id,
            avg(t_pv.stay_time) as avg_watching_time, avg(datediff(lead(t_pv.enter_local_time, 1), t_pv.leave_local_time, 'ss')) as avg_retargeting_time
        from t_pv
        group by user_id
        ) t_avg
    )
    on t_cnt.user_id=t_avg.user_id
)

正确SQL实现 #

以下是满足需求的MySQL代码：

-- 若需要覆盖新表数据，先执行此语句
-- TRUNCATE TABLE t_new;

INSERT INTO t_new
SELECT 
    user_id,
    enter_count AS most_retargeting_count,
    account_id AS most_retargeting_account_id,
    ROUND(avg_enter_gap, 1) AS avg_most_enter_retargeting_gap,
    ROUND(avg_stay_time, 1) AS avg_most_watching_time
FROM (
    SELECT 
        user_id,
        account_id,
        COUNT(*) AS enter_count,
        AVG(stay_time) AS avg_stay_time,
        -- 计算平均进入间隔：取上一次离开时间与本次进入时间的差值，再求平均
        AVG(
            TIMESTAMPDIFF(SECOND, 
                LAG(leave_time) OVER (PARTITION BY user_id, account_id ORDER BY enter_time), 
                enter_time
            )
        ) AS avg_enter_gap,
        -- 标记每个用户进入次数最多的直播，并列时按account_id排序调整
        RANK() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC, account_id) AS rnk
    FROM t_pv
    GROUP BY user_id, account_id
) AS user_live_stats
WHERE rnk = 1;

代码说明 #

1. 分组统计基础数据：按user_id和account_id分组，统计每个用户对每个直播的进入次数、平均停留时长。
2. 计算平均进入间隔：用LAG()窗口函数获取当前记录的上一次离开时间，通过TIMESTAMPDIFF()计算时间差（秒），AVG()会自动忽略第一次进入的NULL值（示例中直播a的两个间隔为5秒和9秒，平均7秒）。
3. 筛选目标直播：用RANK()窗口函数对每个用户的直播进入次数排序，取排名第一的记录；若有多个直播进入次数相同，可调整ORDER BY后的规则（比如按直播ID排序）。
4. 数据插入：MySQL不支持INSERT OVERWRITE，需覆盖数据时先执行TRUNCATE TABLE t_new;。

内容的提问来源于stack exchange，提问作者Leon.L