如何按CustomerId将起止日期区间拆分为月度间隔生成对应数据表？

原数据表 #

目标数据表 #

以下是主流数据库的具体实现方案：

MySQL（8.0+）实现 #

利用递归CTE生成月度序列：

WITH RECURSIVE date_range AS (
    SELECT 
        CustomerId,
        StartDate AS EffectiveDate,
        EndDate
    FROM your_table
    UNION ALL
    SELECT 
        CustomerId,
        DATE_ADD(EffectiveDate, INTERVAL 1 MONTH),
        EndDate
    FROM date_range
    WHERE DATE_ADD(EffectiveDate, INTERVAL 1 MONTH) <= EndDate
)
SELECT CustomerId, EffectiveDate
FROM date_range
ORDER BY CustomerId, EffectiveDate;

PostgreSQL 实现 #

两种方式可选，generate_series更简洁高效：

-- 方式1：递归CTE
WITH RECURSIVE date_range AS (
    SELECT 
        CustomerId,
        StartDate AS EffectiveDate,
        EndDate
    FROM your_table
    UNION ALL
    SELECT 
        CustomerId,
        (EffectiveDate + INTERVAL '1 month')::DATE,
        EndDate
    FROM date_range
    WHERE (EffectiveDate + INTERVAL '1 month')::DATE <= EndDate
)
SELECT CustomerId, EffectiveDate
FROM date_range
ORDER BY CustomerId, EffectiveDate;

-- 方式2：generate_series函数
SELECT 
    t.CustomerId,
    generate_series(t.StartDate, t.EndDate, INTERVAL '1 month')::DATE AS EffectiveDate
FROM your_table t
ORDER BY t.CustomerId, EffectiveDate;

SQL Server 实现 #

递归CTE或数字表方案均可：

-- 方式1：递归CTE
WITH date_range AS (
    SELECT 
        CustomerId,
        StartDate AS EffectiveDate,
        EndDate
    FROM your_table
    UNION ALL
    SELECT 
        CustomerId,
        DATEADD(MONTH, 1, EffectiveDate),
        EndDate
    FROM date_range
    WHERE DATEADD(MONTH, 1, EffectiveDate) <= EndDate
)
SELECT CustomerId, EffectiveDate
FROM date_range
ORDER BY CustomerId, EffectiveDate
OPTION (MAXRECURSION 0); -- 区间超过100个月时需添加此选项

-- 方式2：数字表关联
WITH numbers AS (
    SELECT TOP (DATEDIFF(MONTH, (SELECT MIN(StartDate) FROM your_table), (SELECT MAX(EndDate) FROM your_table)) + 1)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM sys.all_columns
)
SELECT 
    t.CustomerId,
    DATEADD(MONTH, n, t.StartDate) AS EffectiveDate
FROM your_table t
JOIN numbers n ON DATEADD(MONTH, n, t.StartDate) <= t.EndDate
ORDER BY t.CustomerId, EffectiveDate;

注意事项 #

• 若原表中StartDate不是当月第一天，可先将日期统一为当月起始日，比如MySQL用DATE_FORMAT(StartDate, '%Y-%m-01')，PostgreSQL/SQL Server用DATE_TRUNC('month', StartDate)
• 超大时间跨度场景下，数字表或generate_series方案比递归CTE性能更优

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