自定义周起始规则下，如何实现周数与数值的累加计算？

无需日期转换的实现方案 #

核心思路是拆分周数字段为年和周数，结合预先定义的各年份总周数表，通过递归计算处理跨年场景，具体步骤如下：

1. 拆分原周字段：将字符串格式的week拆分为年份（year_part）和周数（week_part）两个数值字段。
2. 构建年份周数辅助表：根据你的周规则（每年第一个周五为第1周），预先统计出每个年份的总周数，存入辅助表year_week_counts。
3. 递归计算新周数：通过递归CTE逐步处理跨年情况，若累加后的周数超过当前年份的总周数，则减去当前年总周数并年份加1，直到剩余周数落在目标年份的周数范围内。

完整SQL示例

-- 1. 拆分原表的周字段为年和周数
WITH tableA_with_parts AS (
    SELECT
        week,
        num,
        CAST(SPLIT(week, '-')[0] AS INT) AS year_part,
        CAST(SPLIT(week, '-')[1] AS INT) AS week_part
    FROM tableA
),
-- 2. 预先定义各年份的总周数（根据你的周规则统计）
year_week_counts AS (
    SELECT 2021 AS year, 52 AS total_weeks UNION ALL
    SELECT 2022 AS year, 52 AS total_weeks UNION ALL
    SELECT 2023 AS year, 53 AS total_weeks UNION ALL
    SELECT 2024 AS year, 52 AS total_weeks
    -- 可根据业务需要扩展更多年份
),
-- 3. 递归计算最终的年和周数
recursive_week_calc AS (
    SELECT
        week,
        num,
        year_part,
        week_part + num AS remaining_weeks
    FROM tableA_with_parts
    UNION ALL
    SELECT
        r.week,
        r.num,
        r.year_part + 1,
        r.remaining_weeks - ywc.total_weeks
    FROM recursive_week_calc r
    JOIN year_week_counts ywc ON ywc.year = r.year_part
    WHERE r.remaining_weeks > ywc.total_weeks
)
-- 4. 拼接最终的new_week字段
SELECT
    r.week,
    r.num,
    CONCAT(r.year_part, '-', r.remaining_weeks) AS new_week
FROM recursive_week_calc r
JOIN year_week_counts ywc ON ywc.year = r.year_part
WHERE r.remaining_weeks <= ywc.total_weeks
ORDER BY r.week;

关键说明

• 辅助表的准确性：year_week_counts中的total_weeks必须严格符合你的周规则（每年第一个周五为第1周）。你可以通过一次日期计算生成这个表：比如对每个年份，找到当年第一个周五的日期，再计算到下一年第一个周五前一天的周数，即为该年的总周数。
• 递归的适用性：如果业务中累加的num不会太大（最多跨1-2年），递归的性能不会有问题；若需处理超大数值累加，也可以用数学计算结合辅助表的累计周数优化，但递归方式更直观易懂。

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