如何用SQL计算用户实际观看视频的真实百分比？

问题描述 #

现有日志系统会高估用户实际“肉眼观看”的视频时长：示例中用户12345在观看至15%时滚动离开视频区域，但后台仍播放视频，日志记录到30%的播放进度，导致统计偏差。

尝试通过case when获取click_video_play和scroll事件的时间戳，计划通过scroll事件时间戳/视频总时长*100计算实际观看百分比，但不知道如何利用现有日志计算视频总时长，寻求解决方法。

解决方案 #

1. 从现有日志推导视频总时长 #

利用视频播放进度事件的时间间隔与对应进度的比例关系计算：

• 观察示例日志：从click_video_play（1473811220）到video_play_5_pct（1473811225）间隔5秒，对应5%的播放进度；后续每5秒触发一次5%的进度事件，说明视频播放速度是1%/秒，因此总时长为100% / 1%/秒 = 100秒。

用SQL实现推导逻辑：

WITH video_progress AS (
    SELECT 
        ts,
        -- 提取播放进度百分比
        CAST(SUBSTRING(event_name, CHARINDEX('_', event_name, 10)+1, 2) AS INT) AS play_pct
    FROM event_log
    WHERE user_id = 12345 
      AND url = '/video' 
      AND event_name LIKE 'video_play_%_pct'
    ORDER BY ts
)
SELECT 
    -- 用时间差和进度差的比例计算总时长
    (MAX(ts) - MIN(ts)) * 100.0 / (MAX(play_pct) - MIN(play_pct)) AS estimated_total_duration
FROM video_progress;

该查询通过已有的进度事件时间差和对应进度差，计算出视频总时长（示例结果为100秒）。如果进度间隔不稳定，可取多个间隔的平均值提升准确性。

2. 计算用户实际观看的最大百分比 #

核心思路是找到用户第一次滚动离开的时间，取该时间点之前最后一个播放进度事件的百分比：

SQL实现：

WITH user_video_events AS (
    SELECT 
        ts,
        event_name,
        -- 提取播放进度百分比
        CASE WHEN event_name LIKE 'video_play_%_pct' 
             THEN CAST(SUBSTRING(event_name, CHARINDEX('_', event_name, 10)+1, 2) AS INT) 
             ELSE NULL END AS play_pct,
        -- 标记用户第一次滚动事件的时间
        MIN(CASE WHEN event_name = 'scroll' THEN ts END) OVER () AS first_scroll_ts
    FROM event_log
    WHERE user_id = 12345 AND url = '/video'
)
SELECT MAX(play_pct) AS actual_watched_pct
FROM user_video_events
WHERE ts <= first_scroll_ts AND play_pct IS NOT NULL;

示例中该查询会返回15，即用户实际观看的最大百分比。

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