Oracle 分析函數實戰：查找狀態全為 1 的 ID 完整指南

前言

在資料分析場景中，常需找出「某群組內所有記錄均滿足特定條件」的群組鍵值。本文以「查找狀態全為 1 的 ID」為例，示範如何靈活運用 Oracle 分析函數高效解決此類問題，並對比傳統寫法優缺點。

一、場景建模與測試資料準備

1.1 表結構設計

CREATE TABLE TAB_FXHS (
    ID VARCHAR2(32),  -- 主鍵 ID（允許重複，代表群組鍵）
    ZT VARCHAR2(2)    -- 狀態碼
);

COMMENT ON TABLE TAB_FXHS IS '用於統計狀態值全為正常的 ID 值';
COMMENT ON COLUMN TAB_FXHS.ID IS '主鍵 ID';
COMMENT ON COLUMN TAB_FXHS.ZT IS '狀態';

1.2 測試資料

INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10125', '0');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10161', '0');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10141', '0');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10126', '1');  -- 單筆狀態=1
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10102', '0');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10103', '0');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10121', '0');  -- ID 10121 混合狀態
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10121', '1');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10121', '0');
INSERT INTO TAB_FXHS (ID, ZT) VALUES ('10121', '3');
COMMIT;

預期結果：僅 10126 應被選出（單筆且狀態=1）；10121 因包含非 1 狀態而排除。

二、核心需求分析

• 表 TAB_FXHS 中 ID 可重複，ZT 為狀態欄位。
• 需找出：該 ID 下所有記錄的 ZT 均為 '1' 的 ID 值。
• 等價邏輯：COUNT(*) = COUNT(CASE WHEN ZT='1' THEN 1 END) OVER (PARTITION BY ID)，或 MIN(ZT) = '1' AND MAX(ZT) = '1'。

三、分析函數解法詳細拆解

3.1 原始 SQL（含潛在問題）

WITH ZT_PARTITION_BY_ID AS (
    SELECT ID, ZT, 
           COUNT(ZT) OVER (PARTITION BY ID ORDER BY ID) ID_ZT
    FROM TAB_FXHS
    GROUP BY ID, ZT
)
SELECT *
FROM ZT_PARTITION_BY_ID
WHERE ID_ZT = 1
  AND ZT = '1';

3.2 邏輯漏洞與修正

問題：原查詢先 GROUP BY ID, ZT 再計算 COUNT(ZT) OVER...，實際統計的是「該 ID 下有幾種不同的 ZT 值」，而非「該 ID 總記錄數」。若某 ID 只有狀態 1，但有多筆記錄，GROUP BY 後僅剩 1 列，COUNT 為 1，恰好通過；但若該 ID 有 3 筆狀態 1，GROUP BY 仍為 1 列，邏輯看似成立，實則語意不清。

3.3 修正版：不使用 GROUP BY，直接窗口聚合

WITH ID_STATUS_STATS AS (
    SELECT 
        ID,
        ZT,
        COUNT(*) OVER (PARTITION BY ID) AS TOTAL_CNT,
        COUNT(CASE WHEN ZT = '1' THEN 1 END) OVER (PARTITION BY ID) AS ZT1_CNT
    FROM TAB_FXHS
)
SELECT DISTINCT ID
FROM ID_STATUS_STATS
WHERE TOTAL_CNT = ZT1_CNT
  AND TOTAL_CNT > 0;

優勢：邏輯直觀、無需自連接、單次掃描、效能優於 NOT EXISTS 或 HAVING 子查詢寫法。

四、替代寫法對比

五、效能優化建議

1. 索引策略：在 (ID, ZT) 建立複合索引，支援索引快速全掃描或範圍掃描。
2. 分區表：若資料量極大，考慮按 ID 雜湊分區，配合並行查詢加速。
3. 統計資訊：定期收集表與欄位統計資訊，確保優化器選擇最佳執行計劃。

六、總結

分析函數（窗口函數）為「群組內全滿足條件」類問題提供了優雅且高效的解決路徑。關鍵在於：先以窗口聚合計算群組總數與符合條件數，再於外層比對。避免過早 GROUP BY 導致語意偏差。實務上，配合適當索引與統計資訊維護，該模式在千萬級資料下仍能保持亞秒級回應。