如何计算头部X%观众贡献的观看量占比及现有SQL逻辑校验

原SQL逻辑存在的问题 #

• 头部定义不符合需求：硬编码nw>30作为头部观众的判断条件，无法匹配「头部X%观众」的比例类要求，不同时间范围、不同内容池的观众观看分布差异大，固定阈值无法对应固定比例的头部群体
• 语法存在错误：缺少CTE的声明关键字WITH，第一个统计观众总观看的CTE未命名，代码中引用的relationship表未定义，无法直接运行
• 维度适配性差：默认统计全平台所有视频的整体数据，不支持单视频、单日期维度的占比计算，无法匹配你给出的单视频单日样本的计算需求
• 结果需要二次处理：用UNION ALL返回两行数据后还需人工计算占比，容易出错且效率低

更优实现方案 #

方案思路 #

1. 先按分析维度（可选择全平台、单视频、单日等）聚合每个观众的总观看次数
2. 按观众总观看次数降序排序，计算每个观众的累计占比排位，筛选出头部X%的观众群体
3. 直接聚合计算头部群体的观看量占总观看量的比例，一次查询返回结果

通用SQL示例（支持按视频+日期维度拆分） #

WITH user_watch_stats AS (
    -- 按维度聚合每个观众的总观看次数，要算全平台整体占比就去掉video_id、date的分组和筛选
    SELECT 
        watcher_id,
        video_id,
        date,
        SUM(number_of_watches) AS total_watches
    FROM table_a
    WHERE date BETWEEN 'xxxx-xx-xx' AND 'yyyy-yy-yy'
    GROUP BY watcher_id, video_id, date
),
user_ranks AS (
    -- 计算每个观众在对应维度下的排位百分位，percent_rank返回0-1的数值，0代表头部
    SELECT 
        *,
        PERCENT_RANK() OVER (PARTITION BY video_id, date ORDER BY total_watches DESC) AS watch_rank_pct
    FROM user_watch_stats
),
total_agg AS (
    -- 计算对应维度下的总观众数、总观看量
    SELECT 
        video_id,
        date,
        COUNT(DISTINCT watcher_id) AS total_watchers,
        SUM(total_watches) AS total_watches
    FROM user_watch_stats
    GROUP BY video_id, date
),
top_agg AS (
    -- 计算头部X%观众的总观看量，这里以头部5%为例，修改WHERE条件的阈值即可调整头部比例
    SELECT 
        video_id,
        date,
        SUM(total_watches) AS top_watches
    FROM user_ranks
    WHERE watch_rank_pct <= 0.05
    GROUP BY video_id, date
)
-- 最终返回占比结果
SELECT 
    t1.video_id,
    t1.date,
    t1.total_watchers,
    t1.total_watches,
    t2.top_watches,
    ROUND(0.05 * 100, 2) AS top_watcher_pct,
    ROUND(t2.top_watches / t1.total_watches * 100, 2) AS top_watch_pct
FROM total_agg t1
JOIN top_agg t2 ON t1.video_id = t2.video_id AND t1.date = t2.date

样本数据验证 #

你给出的单视频单日样本中，3个观众总观看次数分别为5、1、1：

• 头部33%的观众筛选阈值设为watch_rank_pct <=0.33，刚好匹配观看次数为5的1位观众
• 计算得头部观众占比33.33%，贡献观看量占比为5/7*100≈71.43%，和预期结果一致

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