如何统计日期区间内每个自然月的实际包含天数

核心实现逻辑分三步：

• 生成数据集覆盖时间范围内所有自然月的月初、月末日期
• 关联原表数据，筛选出和每条记录的日期区间存在交集的月份
• 计算日期区间和每个月的交集长度，即为该月实际覆盖的天数

以下是Spark SQL/Hive环境可直接运行的代码：

-- 构造原始测试数据
WITH source_data AS (
    SELECT 'A' AS TYPE, DATE '2021-03-22' AS DTIN_DATE, DATE '2021-05-26' AS DTOUT_DATE
    UNION ALL
    SELECT 'B' AS TYPE, DATE '2021-03-30' AS DTIN_DATE, DATE '2021-04-09' AS DTOUT_DATE
),
-- 生成全量月份维度表，包含每个月的月初、月末、yyyy-MM格式月份值
month_dim AS (
    SELECT 
        DATE_FORMAT(month_first_day, 'yyyy-MM') AS MONTH,
        month_first_day,
        LAST_DAY(month_first_day) AS month_last_day
    FROM (
        SELECT ADD_MONTHS(TRUNC(min_dt, 'MM'), pos) AS month_first_day
        FROM (
            -- 取全表最早开始日期、最晚结束日期，确定月份生成范围
            SELECT MIN(DTIN_DATE) AS min_dt, MAX(DTOUT_DATE) AS max_dt FROM source_data
        ) date_range
        -- 生成从起始月到结束月所有月份的第一天
        LATERAL VIEW POSEXPLODE(
            SEQUENCE(0, MONTHS_BETWEEN(TRUNC(max_dt, 'MM'), TRUNC(min_dt, 'MM')))
        ) tmp AS pos, val
    ) month_gen
)
-- 关联计算每个月的实际覆盖天数
SELECT 
    s.TYPE,
    m.MONTH,
    DATEDIFF(
        LEAST(s.DTOUT_DATE, m.month_last_day),
        GREATEST(s.DTIN_DATE, m.month_first_day)
    ) + 1 AS NO_DAYS
FROM source_data s
INNER JOIN month_dim m
    ON m.month_first_day <= s.DTOUT_DATE 
    AND m.month_last_day >= s.DTIN_DATE
ORDER BY s.TYPE, m.MONTH;

运行后输出结果和预期完全一致：

+-----+-------+-------+
| TYPE|MONTH  |NO_DAYS|
+-----+-------+-------+
|A    |2021-03|10     |
|A    |2021-04|30     |
|A    |2021-05|26     |
|B    |2021-03|2      |
|B    |2021-04|9      |
+-----+-------+-------+

逻辑说明：

• 不需要单独区分首月不足整月、中间整月、末月不足整月的场景，通过GREATEST取区间起点和当月月初的较大值作为交集起点，LEAST取区间终点和当月月末的较小值作为交集终点，两个日期做差加1即可得到准确天数，适配所有边界情况
• 如果使用MySQL 8.0+、PostgreSQL等其他支持递归CTE的SQL引擎，只需要把月份生成部分替换为递归CTE写法，核心天数计算逻辑完全通用

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