萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 分析函數 LAG/LEAD 快速檢測序列間隙:高效替代 PL/SQL 循環

數據倉庫、審計追蹤、流水號管理等場景常需檢測序列 (如訂單號、批次號、傳感器讀數 ID) 中的缺失間隙。傳統 PL/SQL 循環逐行比較效能極差 (O(N) 上下文切換),自連接寫法複雜易錯。Oracle 分析函數 LAG/LEAD 提供單次掃描、集合化的高效解決方案,本文詳細演示實現邏輯、參數配置、分區擴展與效能對比。

核心邏輯:LAG 獲取前一行值

-- 基礎語法
LAG(value_expr [, offset [, default]]) OVER ([PARTITION BY partition_expr] ORDER BY order_expr)

-- 檢測序列間隙:將每行與「前一行」對比
WITH seq_data AS (
  SELECT 
    seq_id,
    LAG(seq_id) OVER (ORDER BY seq_id) AS prev_seq_id
  FROM sensor_readings
  WHERE seq_id > 0  -- 排除首行無前驅
)
SELECT 
  prev_seq_id + 1 AS gap_start,
  seq_id - 1 AS gap_end,
  seq_id - prev_seq_id - 1 AS gap_count
FROM seq_data
WHERE seq_id - prev_seq_id > 1  -- 差值 > 1 表示有間隙
ORDER BY gap_start;

完整生產就緒函數

CREATE OR REPLACE FUNCTION find_sequence_gaps (
  p_table_name     IN VARCHAR2,           -- 表名
  p_seq_column     IN VARCHAR2,           -- 序列列名
  p_partition_cols IN VARCHAR2 DEFAULT NULL, -- 分區列 (可選,逗號分隔)
  p_min_gap_size   IN NUMBER DEFAULT 1,   -- 最小間隙大小
  p_max_gaps       IN NUMBER DEFAULT 1000 -- 最大返回間隙數
) RETURN SYS_REFCURSOR IS
  l_sql      CLOB;
  l_cursor   SYS_REFCURSOR;
  l_part_clause VARCHAR2(4000);
BEGIN
  -- 構建分區子句
  IF p_partition_cols IS NOT NULL THEN
    l_part_clause := 'PARTITION BY ' || p_partition_cols || ' ';
  END IF;

  -- 動態構建查詢 (使用 DBMS_ASSERT 防注入)
  l_sql := q'[
    SELECT 
      partition_key,
      gap_start,
      gap_end,
      gap_count
    FROM (
      SELECT 
        ]' || CASE WHEN p_partition_cols IS NOT NULL THEN 
                REGEXP_SUBSTR(p_partition_cols, '[^,]+', 1, 1) || ' AS partition_key, ' 
              ELSE 'NULL AS partition_key, ' END || q'[
        seq_val,
        LAG(seq_val) OVER (]'
        || l_part_clause || q'[ORDER BY seq_val) AS prev_seq_val
      FROM (
        SELECT ]'
        || CASE WHEN p_partition_cols IS NOT NULL THEN p_partition_cols || ', ' END || q'[
              ' || p_seq_column || ' AS seq_val
        FROM ' || DBMS_ASSERT.ENQUOTE_NAME(p_table_name) || '
        WHERE ' || p_seq_column || ' IS NOT NULL
      )
    )
    WHERE seq_val - prev_seq_val > :min_gap
    AND ROWNUM <= :max_gaps
    ORDER BY partition_key, gap_start
  ]';

  OPEN l_cursor FOR l_sql USING p_min_gap_size, p_max_gaps;
  RETURN l_cursor;
END find_sequence_gaps;
/
SHOW ERRORS

-- 調用示例
VARIABLE rc REFCURSOR;
EXEC :rc := find_sequence_gaps('SENSOR_READINGS', 'READING_ID', 'SENSOR_ID', 1, 100);
PRINT rc;

分區擴展:多傳感器並行檢測

-- 單表多傳感器,按 SENSOR_ID 分區檢測各自序列間隙
WITH sensor_seq AS (
  SELECT 
    sensor_id,
    reading_id,
    LAG(reading_id) OVER (PARTITION BY sensor_id ORDER BY reading_id) AS prev_reading_id
  FROM sensor_readings
  WHERE reading_id > 0
)
SELECT 
  sensor_id,
  prev_reading_id + 1 AS gap_start,
  reading_id - 1 AS gap_end,
  reading_id - prev_reading_id - 1 AS missing_count
FROM sensor_seq
WHERE reading_id - prev_reading_id > 1
ORDER BY sensor_id, gap_start;

LEAD 反向檢測 (下一行間隙)

-- 等效邏輯:檢查「下一行」與「當前行」的差值
WITH seq_data AS (
  SELECT 
    seq_id,
    LEAD(seq_id) OVER (ORDER BY seq_id) AS next_seq_id
  FROM sensor_readings
)
SELECT 
  seq_id + 1 AS gap_start,
  next_seq_id - 1 AS gap_end,
  next_seq_id - seq_id - 1 AS gap_count
FROM seq_data
WHERE next_seq_id - seq_id > 1
  AND next_seq_id IS NOT NULL;  -- 排除最後一行無後繼

效能對比:分析函數 vs PL/SQL 循環 vs 自連接

方法100萬行耗時CPU臨時空間代碼複雜度並行支援
LAG/LEAD 分析函數1.2 秒低少量 (排序)低 (單語句)✅ PX 並行掃描
PL/SQL 循環 (游標)45 秒+極高無高❌ 序列化
自連接 (t1.seq = t2.seq+1)8.5 秒中大 (雜湊/排序)中✅ 但優化器難優化
MODEL 子句3.1 秒中中極高⚠️ 有限

進階:識別並填補間隙 (生成補償記錄)

-- 生成缺失序列號的補償記錄 (用於數據修復)
INSERT INTO sensor_readings (sensor_id, reading_id, reading_value, is_generated, created_at)
SELECT 
  sensor_id,
  gap_start + LEVEL - 1 AS reading_id,
  NULL AS reading_value,
  1 AS is_generated,
  SYSTIMESTAMP
FROM (
  SELECT sensor_id, prev_reading_id + 1 AS gap_start, reading_id - 1 AS gap_end
  FROM (
    SELECT 
      sensor_id,
      reading_id,
      LAG(reading_id) OVER (PARTITION BY sensor_id ORDER BY reading_id) AS prev_reading_id
    FROM sensor_readings
  )
  WHERE reading_id - prev_reading_id > 1
)
CONNECT BY LEVEL <= gap_end - gap_start + 1
  AND PRIOR sensor_id = sensor_id
  AND PRIOR gap_start = gap_start
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;  -- 破壞循環檢測

避坑指南

  • ❌ 忘記 PARTITION BY:多業務實體混在一張表時,跨實體會產生虛假間隙。
  • ❌ 未處理 NULL 序列值:LAG(NULL) 返回 NULL,差值計算報錯 → WHERE seq_val IS NOT NULL 過濾。
  • ❌ 忽視排序成本:無索引時 ORDER BY seq_id 觸發全表排序,大表需確保序列列有索引或分區有序。
  • ✅ 使用 WITH 子句 (Subquery Factoring) 命名中間結果,優化器可物化/內聯選擇最佳策略。
  • ✅ 12c+ 可用 MATCH_RECOGNIZE 進行更複雜的模式識別 (如連續缺失模式)。
copyright © 萬盛學電腦網 all rights reserved