Oracle存储过程实现：计算指定时段内发行方未调用服务的间隔

前提假设 #

假设合并后的无重叠时段表issuer_calls_merged结构如下：

• issuer_id VARCHAR2/ NUMBER：发行方唯一标识
• start_time DATE：调用服务的起始时间
• end_time DATE：调用服务的结束时间

解决方案 #

以下存储过程接收起始日期(p_from_date)和结束日期(p_end_date)，计算每个发行方在指定时段内的未调用间隙，输出间隙的起始时间、结束时间及总分钟数：

CREATE OR REPLACE PROCEDURE calculate_issuer_call_gaps(
    p_from_date IN VARCHAR2,
    p_end_date IN VARCHAR2
)
IS
    v_from_date DATE := TO_DATE(p_from_date, 'MM/DD/YYYY');
    v_end_date DATE := TO_DATE(p_end_date, 'MM/DD/YYYY');
BEGIN
    -- 输出表头
    DBMS_OUTPUT.PUT_LINE('ISSUER_ID | GAP_START          | GAP_END            | GAP_MINUTES');
    DBMS_OUTPUT.PUT_LINE('----------|---------------------|---------------------|------------');

    FOR rec IN (
        SELECT
            issuer_id,
            -- 确定间隙的起始时间：要么是指定时段开始，要么是上一个调用时段的结束
            GREATEST(
                NVL(LAG(end_time) OVER (PARTITION BY issuer_id ORDER BY start_time), v_from_date),
                v_from_date
            ) AS gap_start,
            -- 确定间隙的结束时间：要么是下一个调用时段的开始，要么是指定时段结束
            LEAST(
                NVL(LEAD(start_time) OVER (PARTITION BY issuer_id ORDER BY start_time), v_end_date),
                v_end_date
            ) AS gap_end
        FROM (
            -- 先筛选出指定时段内有重叠的调用记录，同时补充每个发行方的首尾边界
            SELECT issuer_id, start_time, end_time FROM issuer_calls_merged
            WHERE start_time < v_end_date AND end_time > v_from_date
            UNION ALL
            -- 为每个发行方补充起始边界（确保即使无调用记录也能生成全时段间隙）
            SELECT DISTINCT issuer_id, v_end_date, v_end_date FROM issuer_calls_merged
            UNION ALL
            SELECT DISTINCT issuer_id, v_from_date, v_from_date FROM issuer_calls_merged
        ) t
    ) LOOP
        -- 只输出有效间隙（起始时间 < 结束时间）
        IF rec.gap_start < rec.gap_end THEN
            DBMS_OUTPUT.PUT_LINE(
                RPAD(rec.issuer_id, 10) || '|' ||
                TO_CHAR(rec.gap_start, 'YYYY-MM-DD HH24:MI:SS') || ' |' ||
                TO_CHAR(rec.gap_end, 'YYYY-MM-DD HH24:MI:SS') || ' |' ||
                ROUND((rec.gap_end - rec.gap_start) * 24 * 60)
            );
        END IF;
    END LOOP;

    -- 处理无任何调用记录的发行方（如果存在）
    FOR rec IN (
        SELECT DISTINCT issuer_id FROM issuer_calls_merged
        MINUS
        SELECT DISTINCT issuer_id FROM issuer_calls_merged
        WHERE start_time < v_end_date AND end_time > v_from_date
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.issuer_id, 10) || '|' ||
            TO_CHAR(v_from_date, 'YYYY-MM-DD HH24:MI:SS') || ' |' ||
            TO_CHAR(v_end_date, 'YYYY-MM-DD HH24:MI:SS') || ' |' ||
            ROUND((v_end_date - v_from_date) * 24 * 60)
        );
    END LOOP;
END;
/

关键逻辑说明 #

1. 边界处理：通过UNION ALL补充每个发行方的指定时段首尾边界，确保即使发行方在指定时段内无调用记录，也能生成完整的未调用间隙。
2. 间隙计算：使用LAG()和LEAD()分析函数，获取当前调用时段的上一个结束时间和下一个开始时间，从而确定间隙的起止范围。
3. 有效性过滤：只保留gap_start < gap_end的记录，避免输出无效的零时长间隙。
4. 分钟数计算：通过(gap_end - gap_start) * 24 * 60将日期差转换为分钟数，并用ROUND()取整。

示例调用 #

SET SERVEROUTPUT ON;
EXEC calculate_issuer_call_gaps('11/20/2022', '11/28/2022');

注意事项 #

• 如果issuer_calls_merged表中存在发行方的调用时段完全超出指定范围，存储过程会自动忽略这些记录。
• 若需要将结果存入表而非打印，可将查询结果插入到预先创建的结果表中（例如issuer_call_gaps_result）。

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