求助：如何用SQL计算标题连续进入Top10的周数？

问题背景 #

需要复刻Netflix的“Top10上榜周数”功能，基于包含Title、User_id、Watch_time、Date的原始数据，计算每个标题连续进入周度Top10的周数，同时统计该标题的累计独立观众数。

分步SQL实现 #

1. 生成周度聚合数据

先从原始数据按周、标题分组，计算核心指标：

with weekly_agg as (
    select 
        title,
        count(distinct user_id) as viewers,
        sum(watch_time) / 3600 as hours_watched,
        -- 生成周结束日期（按周日收尾的周，可根据需求调整周起始规则）
        date_trunc('week', date) + interval '6 days' as week_end
    from raw_data
    group by title, date_trunc('week', date)
),

2. 筛选周度Top10标题

计算每周排名，仅保留进入Top10的记录：

ranked_data as (
    select 
        title,
        viewers,
        hours_watched,
        week_end,
        row_number() over (partition by week_end order by viewers desc) as title_rank
    from weekly_agg
    -- 不同SQL方言语法可能不同：Snowflake用qualify，Hive/Spark需用子查询筛选
    qualify title_rank <= 10
),

3. 用缺口岛屿算法计算连续上榜周数

通过标记连续周的分组键，识别连续上榜的区间：

continuous_weeks as (
    select 
        title,
        week_end,
        row_number() over (partition by title order by week_end) as rn,
        -- 连续周的group_key会相同，非连续周会生成新的key
        date_add(week_end, interval -rn day) as group_key
    from ranked_data
),

4. 统计最终结果

计算每个标题的最大连续上榜周数，以及累计独立观众数：

final_result as (
    select 
        c.title,
        max(count(c.week_end)) as weeks_in_top_10,
        -- 统计该标题的累计独立观众数
        (select count(distinct user_id) from raw_data where title = c.title) as viewers
    from continuous_weeks c
    group by c.title, c.group_key
    order by weeks_in_top_10 desc
)
select title, weeks_in_top_10, viewers from final_result;

最终输出 #

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