Oracle数据库按月份分组计算工单平均开放天数的最优SQL实现

表结构（简化） #

核心思路 #

用递归CTE自动生成所有需要统计的月份范围，关联每个工单覆盖的月份，计算工单在对应月份的实际开放天数，最后按月份聚合求平均。无需硬编码月份或维护临时表，适配动态时间范围。

完整SQL代码 #

WITH date_bounds AS (
    -- 获取统计范围的最早/最晚月份边界
    SELECT
        TRUNC(MIN(created_at), 'MM') AS first_month,
        TRUNC(COALESCE(MAX(solved_at), SYSDATE), 'MM') AS last_month
    FROM your_table
),
months AS (
    -- 递归生成每个月份的起始和结束日期
    SELECT
        first_month AS month_start,
        LAST_DAY(first_month) AS month_end
    FROM date_bounds
    UNION ALL
    SELECT
        month_start + INTERVAL '1' MONTH,
        LAST_DAY(month_start + INTERVAL '1' MONTH)
    FROM months
    WHERE month_start < (SELECT last_month FROM date_bounds)
),
ticket_month_contribution AS (
    -- 计算每个工单在对应月份的开放天数
    SELECT
        m.month_start,
        t.id,
        GREATEST(0, 
            LEAST(COALESCE(t.solved_at, m.month_end), m.month_end) 
            - GREATEST(t.created_at, m.month_start) 
            + 1
        ) AS open_days
    FROM your_table t
    JOIN months m
        -- 关联条件：工单开放区间与当前月份有重叠
        ON t.created_at <= m.month_end
        AND (t.solved_at IS NULL OR t.solved_at >= m.month_start)
)
-- 按月份分组计算平均开放天数
SELECT
    TO_CHAR(month_start, 'YYYY-MM') AS month,
    SUM(open_days) AS total_open_days,
    COUNT(DISTINCT id) AS active_ticket_count,
    ROUND(SUM(open_days) / COUNT(DISTINCT id), 1) AS average_open_days
FROM ticket_month_contribution
GROUP BY month_start
ORDER BY month_start;

代码解释 #

1. date_bounds：自动识别工单的最早创建月份和最晚结束月份（未解决工单用当前日期），确定统计的时间边界。
2. months：递归生成每个月份的第一天和最后一天，覆盖所有需要统计的月份，无需手动维护时间列表。
3. ticket_month_contribution：关联工单与月份，筛选出开放区间与月份重叠的记录，计算工单在当月的实际开放天数：
   • 取工单创建日和当月第一天的较大值作为当月开放起始点
   • 取工单解决日（未解决则用当月最后一天）和当月最后一天的较小值作为当月开放结束点
   • 天数为结束日减起始日加1（包含首尾两天）
4. 最终聚合：按月份分组，统计总开放天数、活跃工单数量，计算平均值并保留1位小数。

结果验证（匹配你的逻辑） #

• 2023-10：工单1贡献15天（29-15+1）、工单2贡献12天（31-20+1），平均(15+12)/2=13.5（注：你示例中的4天应为笔误，按自然日逻辑计算结果如上）
• 2023-11：工单2贡献30天、工单3贡献30天、工单4贡献6天（20-15+1），平均(30+30+6)/3=22.0

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