日期扩展并插入至fct表的SQL实现需求咨询

嘿，我看你需要把Part表中满足b LIKE '%MT'条件的记录，按照指定周期（周/月）拆分日期区间后插入到FCT表中对吧？先帮你明确需求细节，再给你不同数据库下的可落地实现方案~

需求梳理 #

结合你给出的示例，先确认核心规则：

• 筛选条件：仅处理Part表中b LIKE '%MT'的记录，不依赖a列的值
• 日期拆分规则：
  • 当g = 'WEEK'：如果原日期范围[d, e]属于同一周，直接将结束日期作为单天区间（比如第一条Part记录，原d=29-04-22、e=30-04-22，拆分后变成d=30-04-22、e=30-04-22）；如果是跨周范围，则拆分为连续的每周子区间
  • 当g = 'MONTH'：将整个月份的日期范围[d, e]拆分为连续的周区间，最后一周若不足7天则取到当月最后一天，跨月的剩余日期单独作为一个区间

实现方案（分主流数据库） #

不同数据库生成日期序列的语法差异较大，下面给你针对性的示例：

1. PostgreSQL 实现

PostgreSQL可以用generate_series快速生成日期序列，结合日期函数计算每周起止：

-- 插入跨周/月拆分后的记录到FCT表
INSERT INTO FCT (a, b, c, d, e, f, g)
SELECT
    p.a,
    p.b,
    p.c,
    GREATEST(p.d, s.week_start),
    LEAST(p.e, s.week_end),
    p.f,
    p.g
FROM Part p
JOIN LATERAL (
    SELECT
        -- 生成每周起始日期（周一）
        generate_series(
            date_trunc('week', p.d)::date,
            date_trunc('week', p.e)::date,
            INTERVAL '1 week'
        )::date AS week_start,
        -- 生成每周结束日期（周日）
        (generate_series(
            date_trunc('week', p.d)::date,
            date_trunc('week', p.e)::date,
            INTERVAL '1 week'
        ) + INTERVAL '6 days')::date AS week_end
) s ON TRUE
WHERE p.b LIKE '%MT'
AND NOT (p.g = 'WEEK' AND s.week_start = date_trunc('week', p.d)::date AND s.week_end >= p.e);

-- 单独处理WEEK类型的单周特殊情况（对应示例第一条记录）
INSERT INTO FCT (a, b, c, d, e, f, g)
SELECT
    a, b, c, e, e, f, g
FROM Part
WHERE b LIKE '%MT'
AND g = 'WEEK'
AND date_trunc('week', d) = date_trunc('week', e);

2. SQL Server 实现

SQL Server用递归CTE生成日期序列，适配周区间拆分：

WITH DateRanges AS (
    -- 初始记录：每条符合条件的Part数据
    SELECT
        a, b, c, d, e, f, g,
        DATEADD(week, DATEDIFF(week, 0, d), 0) AS week_start, -- 周起始（周一）
        DATEADD(day, 6, DATEADD(week, DATEDIFF(week, 0, d), 0)) AS week_end -- 周结束（周日）
    FROM Part
    WHERE b LIKE '%MT'

    UNION ALL

    -- 递归生成后续周的区间
    SELECT
        a, b, c, d, e, f, g,
        DATEADD(week, 1, week_start) AS week_start,
        DATEADD(week, 1, week_end) AS week_end
    FROM DateRanges
    WHERE week_end < e
)
INSERT INTO FCT (a, b, c, d, e, f, g)
SELECT
    a, b, c,
    GREATEST(d, week_start) AS d,
    -- 处理WEEK类型的单周情况，直接取结束日期e
    CASE WHEN g = 'WEEK' AND week_start = DATEADD(week, DATEDIFF(week, 0, d), 0) AND week_end >= e THEN e
         ELSE LEAST(e, week_end) END AS e,
    f, g
FROM DateRanges
OPTION (MAXRECURSION 100); -- 根据最大日期范围调整递归次数

3. MySQL 8.0+ 实现

MySQL 8.0+支持递归CTE，也可以用数字辅助表生成序列：

-- 先创建数字辅助表（若已有可跳过）
CREATE TABLE IF NOT EXISTS nums (n INT PRIMARY KEY);
INSERT INTO nums VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

WITH DateRanges AS (
    SELECT
        p.a, p.b, p.c, p.d, p.e, p.f, p.g,
        DATE_SUB(p.d, INTERVAL WEEKDAY(p.d) DAY) AS week_start, -- 周起始（周一）
        DATE_ADD(DATE_SUB(p.d, INTERVAL WEEKDAY(p.d) DAY), INTERVAL 6 DAY) AS week_end -- 周结束（周日）
    FROM Part p
    WHERE p.b LIKE '%MT'

    UNION ALL

    SELECT
        a, b, c, d, e, f, g,
        DATE_ADD(week_start, INTERVAL 1 WEEK) AS week_start,
        DATE_ADD(week_end, INTERVAL 1 WEEK) AS week_end
    FROM DateRanges
    WHERE week_end < e
)
INSERT INTO FCT (a, b, c, d, e, f, g)
SELECT
    a, b, c,
    GREATEST(d, week_start) AS d,
    CASE WHEN g = 'WEEK' AND week_start = DATE_SUB(d, INTERVAL WEEKDAY(d) DAY) AND week_end >= e THEN e
         ELSE LEAST(e, week_end) END AS e,
    f, g
FROM DateRanges;

注意事项 #

• 周定义：示例默认周为周一到周日，如果你的业务周是周日到周六，需要调整日期函数的计算逻辑（比如PostgreSQL可通过date_trunc('week', p.d + INTERVAL '1 day') - INTERVAL '1 day'获取周日起始的周）
• 日期类型：确保d和e列是日期类型，若为字符串需先转换为日期再处理
• 性能优化：若Part表数据量大，建议给b列加索引，同时限制递归次数避免性能损耗

备注：内容来源于stack exchange，提问作者Priyal Jain