Redshift SQL实现按观看时长分步统计用户数量需求

需求说明 #

现有用户视频观看时长数据集，需统计每个时长点（1秒、2秒、3秒……）对应的至少观看了该时长的用户总数。

样本数据集 #

期望输出 #

解决方案 #

利用Redshift的generate_series函数生成连续时长序列，再关联原表统计符合条件的用户数：

WITH time_series AS (
    -- 生成从1到最大观看时长的连续整数序列
    SELECT generate_series(1, MAX(duration)) AS time_point
    FROM your_table_name
),
user_counts AS (
    -- 统计每个时长点下，观看时长>=该点的用户数
    SELECT 
        ts.time_point AS "Time",
        COUNT(DISTINCT ut.user_id) AS "Number of users"
    FROM time_series ts
    LEFT JOIN your_table_name ut ON ut.duration >= ts.time_point
    GROUP BY ts.time_point
    ORDER BY ts.time_point
)
SELECT * FROM user_counts;

代码说明 #

• time_series CTE：动态生成从1到数据集中最大观看时长的连续时长点，确保覆盖所有需要统计的时间节点。
• user_counts CTE：将生成的时长序列和原表关联，通过duration >= time_point筛选出至少观看了对应时长的用户，用COUNT(DISTINCT user_id)去重统计有效用户数。
• 最终按时长点升序排序，得到符合要求的结果。

注意事项 #

• 替换your_table_name为实际的表名。
• 如果数据集中存在duration为0的记录，需根据业务需求决定是否在统计时排除（可在generate_series起始值或关联条件中调整）。

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