基于Oracle纯SQL实现批量调度系统作业运行时段直方图数据组装

核心思路 #

不用繁琐的UNION拼接，通过递归生成时段维度区间，再关联作业历史表统计每个时段内运行的作业数量，轻松调整时段粒度、每日划分数量和午夜偏移量。

可配置参数说明 #

在SQL中只需调整以下几个参数即可适配不同需求：

• p_interval_count：每日时段划分总数（如24=小时粒度，144=10分钟粒度，1440=分钟粒度）
• p_midnight_offset：午夜偏移量（单位：天，如偏移30分钟则为30/1440，偏移1小时为1/24）
• p_date_filter：作业数据的日期范围过滤（按需调整）

完整SQL示例 #

WITH params AS (
    -- 配置参数：按需修改
    SELECT 
        24 AS p_interval_count,       -- 每日划分24个时段（小时粒度）
        0/1440 AS p_midnight_offset,  -- 无午夜偏移
        DATE '2024-01-01' AS p_start_date,
        DATE '2024-01-02' AS p_end_date
    FROM DUAL
),
time_intervals AS (
    -- 生成所有时段区间
    SELECT
        (p_start_date + p_midnight_offset) + ((LEVEL - 1) / p_interval_count) AS interval_start,
        (p_start_date + p_midnight_offset) + (LEVEL / p_interval_count) AS interval_end,
        -- 生成时段标签（如"00:00-01:00"）
        TO_CHAR((p_start_date + p_midnight_offset) + ((LEVEL - 1)/p_interval_count), 'HH24:MI') || '-' ||
        TO_CHAR((p_start_date + p_midnight_offset) + (LEVEL/p_interval_count), 'HH24:MI') AS interval_label
    FROM params
    CONNECT BY LEVEL <= p_interval_count
)
SELECT
    ti.interval_label AS x_axis,
    COUNT(DISTINCT ah.AH_JOB_NAME) AS y_axis_job_count  -- 按作业名去重，若需统计运行次数则去掉DISTINCT
FROM time_intervals ti
LEFT JOIN your_job_history_table ah
    -- 判断作业是否在当前时段内运行：作业开始<=时段结束，且作业结束>=时段开始
    ON ah.AH_TimeStamp1 <= ti.interval_end
    AND ah.AH_TimeStamp4 >= ti.interval_start
    -- 过滤作业的日期范围
    AND ah.AH_TimeStamp1 BETWEEN (SELECT p_start_date FROM params) AND (SELECT p_end_date FROM params)
GROUP BY ti.interval_label, ti.interval_start
ORDER BY ti.interval_start;

关键逻辑说明 #

1. 时段生成：通过CONNECT BY LEVEL递归生成指定数量的时段区间，结合偏移量计算每个时段的起止时间，避免手动写UNION。
2. 作业匹配条件：只要作业的运行周期与时段区间有重叠，就计入该时段的统计数（符合直方图的统计逻辑）。
3. 灵活调整：
   • 改成10分钟粒度：将p_interval_count设为144（24*60/10）
   • 偏移30分钟：将p_midnight_offset设为30/1440
   • 统计运行次数而非作业数：去掉COUNT(DISTINCT)中的DISTINCT

示例调整 #

比如要统计10分钟粒度、偏移15分钟的作业数量，只需修改params部分：

params AS (
    SELECT 
        144 AS p_interval_count,       -- 24*60/10=144个10分钟时段
        15/1440 AS p_midnight_offset,  -- 偏移15分钟
        DATE '2024-01-01' AS p_start_date,
        DATE '2024-01-02' AS p_end_date
    FROM DUAL
)

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