基于多有效条目分组统计合同工时的PostgreSQL实现方案

现有contracts表结构及数据如下：

预期按时间段汇总总工时的结果：

以下是适配PostgreSQL的解决方案：

WITH date_ranges AS (
    -- 定义统计的时间范围，可按需修改起止日期
    SELECT 
        '2022-01-01'::date AS period_start,
        '2023-12-31'::date AS period_end
),
contract_validity AS (
    SELECT 
        Hours,
        -- 转换字符串日期为date类型
        TO_DATE(ValidFrom, 'DD.MM.YYYY') AS valid_from,
        -- 将永久有效标记(31.12.9999)替换为统计周期结束日
        CASE 
            WHEN ValidUntil = '31.12.9999' THEN (SELECT period_end FROM date_ranges)
            ELSE TO_DATE(ValidUntil, 'DD.MM.YYYY')
        END AS valid_until,
        -- 计算合同与统计周期的实际交集起始日
        GREATEST(TO_DATE(ValidFrom, 'DD.MM.YYYY'), (SELECT period_start FROM date_ranges)) AS actual_start,
        -- 计算合同与统计周期的实际交集结束日
        LEAST(
            CASE WHEN ValidUntil = '31.12.9999' THEN (SELECT period_end FROM date_ranges) ELSE TO_DATE(ValidUntil, 'DD.MM.YYYY') END,
            (SELECT period_end FROM date_ranges)
        ) AS actual_end
    FROM contracts
    -- 过滤完全不在统计周期内的合同
    WHERE 
        TO_DATE(ValidFrom, 'DD.MM.YYYY') <= (SELECT period_end FROM date_ranges)
        AND (
            ValidUntil = '31.12.9999' 
            OR TO_DATE(ValidUntil, 'DD.MM.YYYY') >= (SELECT period_start FROM date_ranges)
        )
),
split_ranges AS (
    -- 拆分跨年度的合同时间段为年度区间
    SELECT 
        Hours,
        actual_start AS range_start,
        CASE 
            WHEN EXTRACT(YEAR FROM actual_start) = EXTRACT(YEAR FROM actual_end) THEN actual_end
            ELSE (DATE_TRUNC('YEAR', actual_start) + INTERVAL '1 year - 1 day')::date
        END AS range_end
    FROM contract_validity
    UNION ALL
    SELECT 
        Hours,
        (DATE_TRUNC('YEAR', actual_start) + INTERVAL '1 year')::date AS range_start,
        actual_end AS range_end
    FROM contract_validity
    WHERE EXTRACT(YEAR FROM actual_start) < EXTRACT(YEAR FROM actual_end)
)
-- 按时间段分组汇总工时，转回原日期格式输出
SELECT 
    SUM(Hours) AS Hours,
    TO_CHAR(range_start, 'DD.MM.YYYY') AS ValidFrom,
    TO_CHAR(range_end, 'DD.MM.YYYY') AS ValidUntil
FROM split_ranges
WHERE range_start <= range_end
GROUP BY range_start, range_end
ORDER BY range_start;

关键逻辑说明 #

1. date_ranges：统一管理统计的起止日期，修改此处即可适配不同统计周期。
2. contract_validity：处理日期格式转换，替换永久有效标记，计算合同与统计周期的实际重叠区间，过滤无效数据。
3. split_ranges：将跨年度的合同时间段拆分为独立的年度区间，确保分组统计的准确性。
4. 最终分组汇总工时，并将日期转回原字符串格式输出。

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