數據倉庫、審計追蹤、流水號管理等場景常需檢測序列 (如訂單號、批次號、傳感器讀數 ID) 中的缺失間隙。傳統 PL/SQL 循環逐行比較效能極差 (O(N) 上下文切換),自連接寫法複雜易錯。Oracle 分析函數 LAG/LEAD 提供單次掃描、集合化的高效解決方案,本文詳細演示實現邏輯、參數配置、分區擴展與效能對比。
-- 基礎語法
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;
-- 等效邏輯:檢查「下一行」與「當前行」的差值
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; -- 排除最後一行無後繼
| 方法 | 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) 命名中間結果,優化器可物化/內聯選擇最佳策略。MATCH_RECOGNIZE 進行更複雜的模式識別 (如連續缺失模式)。