Oracle分析函式之LEAD和LAG實際應用

Oracle分析函式之LEAD和LAG實際應用
在前幾天的工作中按照客戶的需求，需要對客戶資訊進行資料分析，即某人存在多個狀態的賬號，將客戶資訊賬號狀態分析出結果，和客戶確認彙報，根據保留規則，保留唯一賬號，以保證程式可用性。起初，根據聚合函式進行查詢分析，需要寫一大串的SQL，即不美觀又複雜，很容易產生錯誤。後續想到Oracle分析函式中的lead和lag，SQL簡潔了很多且容易產生報告資料。

建立一個測試表用於測試分析，具體資料：

SELECT * FROM TMP_HBWH_USER_ACCTNO;

聚合函式和分析函式的區別

聚合函式和分析函式最大的區別在於，分析函式可以查詢多個欄位的資料，但是聚合函式只能查詢出函式和分組欄位的資料。

SELECT ACCTNO,COUNT(1) FROM TMP_HBWH_USER_ACCTNO GROUP BY ACCTNO;

SELECT ACCTNO,ACCTNAME,STATES,COUNT(1) OVER (PARTITION BY ACCTNO ORDER BY ACCTNO) CN FROM TMP_HBWH_USER_ACCTNO;

而我們如果想通過聚合函式實現查詢上述欄位資訊，則需要

SELECT A.ACCTNO,ACCTNAME,STATES,CN FROM TMP_HBWH_USER_ACCTNO A INNER JOIN 
(SELECT ACCTNO,COUNT(1) CN FROM TMP_HBWH_USER_ACCTNO GROUP BY ACCTNO) B ON A.ACCTNO=B.ACCTNO ORDER BY A.ACCTNO;

通過以上查詢可以很清楚的看到，如果我們需要通過聚合函式與分析函式查詢一樣多的欄位資訊，則需要使用更多的SQL進行查詢

而以上查詢結果明顯不能達到客戶的需求，也無法在報告中體現資料的分析內容，我們接下來需要使用分析函式LEAD()將結果展示的更完善

LEAD(value_expr,[offset],[default]]) over ([partition_clause] order_by_clause)

value_expr:代表的是我們需要返回的欄位資訊；
offset:代表的是我們需要返回第幾行的資料；
default:代表的是如果後面沒有資料，則顯示的預設值；
partition_clause:代表根據某個欄位進行分組；

SELECT ACCTNAME,
       ACCTNO,
       STATES,
       COUNT(1) OVER(PARTITION BY ACCTNO ORDER BY ACCTNO) CN,
       LEAD(STATES, 1, 'NULL') OVER(PARTITION BY ACCTNO ORDER BY ACCTNO) STATE_NEXT
  FROM TMP_HBWH_USER_ACCTNO;

再使用較多的LEAD()就可以分析出我們想要的結果：

SELECT A.ACCTNO,
       A.ACCTNAME,
       A.CN,
       A.STATES_NEW,
       REGEXP_COUNT(STATES_NEW, '正常') NORMAL_COUNT
  FROM (SELECT ACCTNO,
               ACCTNAME,
               STATES,
               COUNT(1) OVER(PARTITION BY ACCTNO ORDER BY ACCTNO) CN,
               CASE
                 WHEN (COUNT(1) OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)) = 1 THEN
                  STATES
                 WHEN (COUNT(1) OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)) = 2 THEN
                  STATES || '-' || LEAD(STATES, 1, 'NULL')
                  OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)
                 WHEN (COUNT(1) OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)) = 3 THEN
                  STATES || '-' || LEAD(STATES, 1, 'NULL')
                  OVER(PARTITION BY ACCTNO ORDER BY ACCTNO) || '-' ||
                  LEAD(STATES, 2, 'NULL')
                  OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)
                 WHEN (COUNT(1) OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)) = 4 THEN
                  STATES || '-' || LEAD(STATES, 1, 'NULL')
                  OVER(PARTITION BY ACCTNO ORDER BY ACCTNO) || '-' ||
                  LEAD(STATES, 2, 'NULL')
                  OVER(PARTITION BY ACCTNO ORDER BY ACCTNO) || '-' ||
                  LEAD(STATES, 3, 'NULL')
                  OVER(PARTITION BY ACCTNO ORDER BY ACCTNO)
                 ELSE
                  'NULL'
               END STATES_NEW
          FROM TMP_HBWH_USER_ACCTNO) A
 WHERE NOT REGEXP_LIKE(STATES_NEW, 'NULL');

這樣，我們就可以把具體的重複賬號、重複賬號梳理、賬號存在的狀態和存在的正常賬號的數量梳理出來。