使用SQL识别单视频播放量占比超75%的Youtube偶发爆红频道

需求与基础表说明 #

• 核心判定规则：频道存在单支视频播放量占该频道总播放量75%以上的视频，即为目标频道
• 假设使用的youtube_table表结构：
  • channel_name：频道名称
  • video_name：视频名称
  • views：单支视频累计播放量
• 表粒度约定：每一行对应一个频道下的唯一视频，无重复视频记录

原有代码的逻辑问题 #

1. CTE t1逻辑冗余：按channel_name + video_name分组取max(views)，在当前单视频粒度的表结构下，等价于直接读取原表的views字段，无实际计算价值
2. 过滤阈值与需求不匹配：题目要求判定阈值为75%，原代码设置为>70%
3. 输出维度不符合需求：需求是输出符合条件的频道列表，原代码输出所有视频的播放占比明细，未做频道去重

修正后的可执行SQL #

-- 计算每个频道的总播放量、发布视频数
WITH channel_total_stat AS (
    SELECT
        channel_name,
        SUM(views) AS total_channel_views,
        COUNT(video_name) AS video_publish_cnt
    FROM youtube_table
    GROUP BY channel_name
),
-- 计算每个频道的最高单视频播放量
channel_top_video_stat AS (
    SELECT
        channel_name,
        MAX(views) AS highest_single_video_views
    FROM youtube_table
    GROUP BY channel_name
)
-- 筛选符合要求的偶发爆红频道
SELECT
    t1.channel_name,
    t2.video_publish_cnt,
    ROUND(
        CAST(t1.highest_single_video_views AS NUMERIC) / t2.total_channel_views * 100,
        2
    ) AS top_video_view_ratio_pct
FROM channel_top_video_stat t1
JOIN channel_total_stat t2
ON t1.channel_name = t2.channel_name
-- 核心判定：最高单视频播放占比≥75%
WHERE CAST(t1.highest_single_video_views AS NUMERIC) / t2.total_channel_views >= 0.75
-- 可选扩展：排除仅发布1支视频的频道，可根据业务需求开关
-- AND t2.video_publish_cnt >= 2

实现逻辑说明 #

• 拆分两个CTE分别聚合频道维度的指标，避免重复扫描原表，执行效率更高
• 直接取频道最高播放视频的占比做判断，天然满足“存在任意单视频占比75%以上”的规则（其余视频播放量低于最高值，占比必然更小）
• 强制转换为NUMERIC类型做计算，避免整数除法导致的精度丢失
• 预留扩展开关，可灵活过滤仅发布1支视频的频道，避免不符合“偶发爆红”定义的样本被计入

如果实际业务表为「视频+发布日期」的日粒度播放表（即同一支视频有多条按天统计的播放记录），可在最前面增加一层CTE先聚合每个频道下每个视频的总播放量，再做后续计算：

WITH video_daily_agg AS (
    SELECT 
        channel_name, 
        video_name, 
        SUM(views) AS video_total_views
    FROM youtube_daily_table
    GROUP BY channel_name, video_name
)
-- 后续频道维度的聚合逻辑与上述修正版SQL一致，仅需将原表引用替换为video_daily_agg

示例数据适配 #

基于给定的两条测试数据：

• 若Mr Beast仅发布《Selling Houses For $1》这1支视频，总播放为125943320，占比100%，会被筛选为目标频道
• 若3blue1brown除《But how does bitcoin actually work?》外的所有视频总播放为220万，该视频占比约78%，符合筛选条件；若其他视频总播放超过258万，占比低于75%则不会入选

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