SQL按自然月拆分跨月日期区间 生成周期分段日期记录

核心逻辑 #

要按自然月拆分跨月日期区间，不需要写复杂的循环，通过月份序列关联+交集计算即可实现：

• 生成覆盖原表所有日期范围的连续自然月序列，记录每个月的第一天、最后一天两个边界值
• 将原业务表与月份序列关联，匹配所有和原日期区间存在重叠的月份
• 对每一组匹配的原记录+月份，计算两者的日期交集作为拆分后的起止日期，rate直接沿用原记录值即可

通用实现（支持MySQL 8.0+/PostgreSQL/SQL Server/Hive等支持递归CTE的数据库） #

假设原业务表名为rate_records，date_start、date_end字段均为DATE类型，实现代码如下：

WITH RECURSIVE month_dim AS (
    -- 锚点：取原表最早的日期所在月的第一天作为序列起点
    SELECT DATE_FORMAT(MIN(date_start), '%Y-%m-01') AS month_start
    FROM rate_records
    UNION ALL
    -- 递归逐月生成，直到覆盖原表最晚的结束日期
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM month_dim
    WHERE month_start < (SELECT LAST_DAY(MAX(date_end)) FROM rate_records)
)
SELECT
    GREATEST(r.date_start, md.month_start) AS date_start,
    LEAST(r.date_end, LAST_DAY(md.month_start)) AS date_end,
    r.rate
FROM rate_records r
JOIN month_dim md
  ON md.month_start <= r.date_end
  AND LAST_DAY(md.month_start) >= r.date_start
ORDER BY date_start, date_end;

常见场景适配 #

• MySQL 5.x等不支持递归CTE的环境：提前创建一张存储0~N连续整数的辅助表nums(n)，用数字偏移生成月份即可，示例代码：

SELECT
    GREATEST(r.date_start, ADDDATE(r.base_month, INTERVAL n.n MONTH)) AS date_start,
    LEAST(r.date_end, LAST_DAY(ADDDATE(r.base_month, INTERVAL n.n MONTH))) AS date_end,
    r.rate
FROM (
    SELECT
        *,
        DATE_FORMAT(MIN(date_start) OVER(), '%Y-%m-01') AS base_month,
        PERIOD_DIFF(
            DATE_FORMAT(date_end, '%Y%m'),
            DATE_FORMAT(MIN(date_start) OVER(), '%Y%m')
        ) AS offset_max
    FROM rate_records
) r
JOIN nums n ON n.n <= r.offset_max
ORDER BY date_start, date_end;

• Oracle数据库：递归CTE去掉RECURSIVE关键字，日期函数替换为Oracle原生函数：TRUNC(date, 'mm')取当月第一天，ADD_MONTHS做月份偏移，LAST_DAY取当月最后一天即可。

结果校验 #

用题目给出的示例数据执行上述代码，输出结果和预期完全一致：

注意：如果原表中date_start、date_end是字符串格式（比如题目示例的dd.MM.yyyy格式），需要先通过日期转换函数转成DATE类型再做计算，避免日期比较逻辑出错。

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