SQL计算忽略同视频重复值的video time spent累加和方法

核心逻辑是通过标记每个视频的首次出现行，避免重复累加时长，仅用窗口函数即可实现全链路计算，性能最优且兼容所有主流SQL引擎。

实现步骤 #

• 第一步：对所有行按encoding_bytes倒序排序后，按video分组给同视频内的行编号，编号为1的即为该视频在排序后首次出现的行
• 第二步：计算时长累加和时，仅对编号为1的行累加对应video_time_spent，其余行累加0，即可得到去重后的时长累加和
• 第三步：过滤累加时长小于阈值X的行，即为符合要求的结果

可直接运行的测试SQL #

-- 替换语句末尾的 ? 为你的实际阈值X即可
SELECT * 
FROM (
    SELECT 
        *,
        SUM(encoding_bytes) OVER(ORDER BY encoding_bytes DESC) AS encoding_bytes_running_sum,
        -- 兼容所有SQL引擎的CASE WHEN写法，不支持IF的引擎可直接用
        SUM(CASE WHEN video_row_num = 1 THEN video_time_spent ELSE 0 END) OVER(ORDER BY encoding_bytes DESC) AS video_time_spent_running_sum_expected
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER(PARTITION BY video ORDER BY encoding_bytes DESC) AS video_row_num
        FROM (
            VALUES
                ('a', 1, 1, 500),
                ('a', 2, 1, 400),
                ('b', 3, 2, 300),
                ('b', 4, 2, 200),
                ('b', 5, 2, 100),
                ('b', 6, 2, 100)
        ) AS t (video, encoding, video_time_spent, encoding_bytes)
    ) t1
) t2
WHERE video_time_spent_running_sum_expected < ?

效果验证 #

以阈值X=4为例，返回结果的video_time_spent_running_sum_expected列值依次为1、1、3、3、3、3，和给出的预期结果完全一致。该方案仅需要一次全表扫描+两次窗口计算，没有额外的关联或聚合操作，性能最优，即使是千万级以上的大表也可以高效运行。

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