如何按月拆分跨时段设备状态数据并计算月度时长？

问题背景 #

现有设备状态数据表mtable，结构及数据如下：

ID  Machine  State  FromDateTime       ToDateTime         DurDay
------------------------------------------------------------------
1   340      SB     1/7/2017 00:00:00  2/7/2017 09:58:03  1.4
2   340      PR     3/7/2017 01:23:55  30/9/2022 15:00:35 1915.57

需要将跨多个月的记录按月拆分，生成每个对应年月的时长数据（DurDay2）用于绘制图表。当前采用多次UNION的方式只能获取起止月份，无法覆盖中间所有年月，无法满足需求。期望输出需包含记录时间范围内的所有年月，并计算对应月度时长。

解决方案 #

核心思路 #

通过递归CTE生成记录时间范围内的所有年月区间，再与原表关联，计算每个年月与原记录的重叠时长，最终得到拆分后的月度数据。

Oracle 实现 #

WITH month_range AS (
    -- 获取所有记录的最小起始月和最大结束月
    SELECT 
        TRUNC(MIN(FromDateTime), 'MM') AS month_start,
        TRUNC(MAX(ToDateTime), 'MM') AS month_end
    FROM mtable
    UNION ALL
    -- 递归生成后续所有年月
    SELECT 
        ADD_MONTHS(month_start, 1),
        month_end
    FROM month_range
    WHERE month_start < month_end
),
all_months AS (
    -- 转换为当月的完整时间范围（起始到月末最后一秒），并生成RefMonth格式
    SELECT 
        month_start,
        LAST_DAY(month_start) + INTERVAL '23:59:59' HOUR TO SECOND AS month_end,
        TO_CHAR(month_start, 'MM-YYYY') AS RefMonth
    FROM month_range
)
-- 关联原表计算月度时长
SELECT 
    t.ID,
    t.Machine,
    t.State,
    t.FromDateTime,
    t.ToDateTime,
    t.DurDay,
    am.RefMonth,
    -- 计算重叠时间段的天数，保留两位小数
    ROUND(
        (LEAST(t.ToDateTime, am.month_end) - GREATEST(t.FromDateTime, am.month_start)) * 24 / 24,
        2
    ) AS DurDay2
FROM mtable t
JOIN all_months am 
    -- 匹配与记录时间范围有重叠的年月
    ON am.month_start <= TRUNC(t.ToDateTime, 'MM') 
    AND am.month_end >= TRUNC(t.FromDateTime, 'MM')
ORDER BY t.ID, am.month_start;

PostgreSQL 实现 #

WITH RECURSIVE month_range AS (
    -- 获取所有记录的最小起始月和最大结束月
    SELECT 
        DATE_TRUNC('month', MIN(FromDateTime)) AS month_start,
        DATE_TRUNC('month', MAX(ToDateTime)) AS month_end
    FROM mtable
    UNION ALL
    -- 递归生成后续所有年月
    SELECT 
        (month_start + INTERVAL '1 month'),
        month_end
    FROM month_range
    WHERE month_start < month_end
),
all_months AS (
    -- 转换为当月的完整时间范围（起始到月末最后一秒），并生成RefMonth格式
    SELECT 
        month_start,
        (month_start + INTERVAL '1 month' - INTERVAL '1 second') AS month_end,
        TO_CHAR(month_start, 'MM-YYYY') AS RefMonth
    FROM month_range
)
-- 关联原表计算月度时长
SELECT 
    t.ID,
    t.Machine,
    t.State,
    t.FromDateTime,
    t.ToDateTime,
    t.DurDay,
    am.RefMonth,
    -- 计算重叠时间段的天数，保留两位小数
    ROUND(
        EXTRACT(EPOCH FROM (LEAST(t.ToDateTime, am.month_end) - GREATEST(t.FromDateTime, am.month_start))) / (3600 * 24),
        2
    ) AS DurDay2
FROM mtable t
JOIN all_months am 
    -- 匹配与记录时间范围有重叠的年月
    ON am.month_start <= DATE_TRUNC('month', t.ToDateTime)
    AND am.month_end >= DATE_TRUNC('month', t.FromDateTime)
ORDER BY t.ID, am.month_start;

关键说明 #

• 递归CTEmonth_range自动生成覆盖所有记录时间范围的年月序列，无需手动添加UNION
• all_months将年月转换为精确到秒的时间区间，确保时长计算准确
• 通过LEAST和GREATEST函数找到原记录与当月区间的重叠部分，转换为天数得到DurDay2
• 最终结果按记录ID和年月排序，方便后续图表绘制

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