MySQL按周序列中断分组：拆分连续区间统计需求

问题背景 #

执行以下SQL查询项目资源推荐运行数据时，发现部分资源的周序列存在中断（例如resource_id_reassigned为103046779的记录中，2022-w34后直接跳到2022-w41）：

select *
from rpt.project_resource_recommendation_runs pr
where pr.project_id = 'a004M00000VEdN3QAL' and pr.run_id = '202208021659477396' 

当前使用的统计SQL仅能获取每个资源ID的整体起止周，无法按周序列的中断点拆分出连续周区间：

select min(week_of_year),max(week_of_year) ,resource_id_reassigned
from rpt.project_resource_recommendation_runs pr
where pr.project_id = 'a004M00000VEdN3QAL' and pr.run_id = '202208021659477396' 
group by resource_id_reassigned 

期望输出按中断点拆分后的连续周区间统计结果：

2022-w31    2022-w34    103046779
2022-w35    2024-w26    103046779
2022-w35    2022-w40    106987979
2022-w32    2024-w20    1000
2022-w34    2024-w16    1005

解决方案 #

核心思路是为每个连续周区间生成唯一分组标识，通过窗口函数实现：

1. 将week_of_year转换为可计算的数值（拆分年份和周数，计算为年份*52 + 周数），方便判断周连续性；
2. 用LAG窗口函数获取同一资源的上一条记录的周数值；
3. 对比当前周与上一周的差值，若差值不等于1则标记为新分组起点；
4. 累计分组标识，最后按分组标识和资源ID分组，取每个分组的起止周。

完整SQL示例：

WITH processed_data AS (
    SELECT 
        resource_id_reassigned,
        week_of_year,
        -- 转换周字符串为可计算的数值
        CAST(SUBSTRING(week_of_year, 1, 4) AS INT)*52 + CAST(SUBSTRING(week_of_year, 6, 2) AS INT) AS week_num,
        -- 判断是否为新分组起点
        CASE 
            WHEN LAG(CAST(SUBSTRING(week_of_year, 1, 4) AS INT)*52 + CAST(SUBSTRING(week_of_year, 6, 2) AS INT)) 
                 OVER (PARTITION BY resource_id_reassigned ORDER BY week_of_year) 
                 = CAST(SUBSTRING(week_of_year, 1, 4) AS INT)*52 + CAST(SUBSTRING(week_of_year, 6, 2) AS INT) -1
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM rpt.project_resource_recommendation_runs pr
    WHERE pr.project_id = 'a004M00000VEdN3QAL' and pr.run_id = '202208021659477396'
),
grouped_data AS (
    SELECT 
        resource_id_reassigned,
        week_of_year,
        -- 生成连续区间的分组ID
        SUM(is_new_group) OVER (PARTITION BY resource_id_reassigned ORDER BY week_of_year) AS group_id
    FROM processed_data
)
SELECT 
    MIN(week_of_year) AS start_week,
    MAX(week_of_year) AS end_week,
    resource_id_reassigned
FROM grouped_data
GROUP BY resource_id_reassigned, group_id
ORDER BY resource_id_reassigned, start_week;

补充说明 #

• 若数据库支持日期解析函数（比如PostgreSQL的TO_DATE(week_of_year, 'IYYY-IW')），可替代手动字符串拆分，用日期差值判断连续性，结果更严谨；
• PARTITION BY resource_id_reassigned确保仅在同一资源的记录内计算连续周；
• ORDER BY week_of_year保证周序列排序正确，避免计算错误。

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