網頁

▼

2019/11/8

Oracle GROUP BY範例，統計每日瀏覽器交易錯誤代碼總計

記錄一下今天組了半天的SQL，太久沒用都快忘光了。

原始需求為使用者可在頁面點選[瀏覽器交易錯誤記錄查詢]功能依照日期或月份查詢交易錯誤發生時的瀏覽器資訊及錯誤代碼，畫面類似下面。

瀏覽器交易錯誤記錄查詢



瀏覽器版本錯誤代碼(E001)錯誤代碼(E002)
Chrome7835
Chrome7895
Firefox7038
Firefox6957
IE1149
Safari1321

資料來源為交易記錄資料表TX_LOG，存放每筆交易時的各種資訊，包括瀏覽器資訊及錯誤代碼。

每一次交易發生時就會在TX_LOG記錄一筆資料，一天交易次數假設有100萬次，那麼就不能等到使用者查詢時才去資料庫查，因為資料量多查詢及計算的時間會太長，所以就利用離峰的時間例如臨晨跑排程(cron job schedule)執行查詢的SQL並把統計結果放到另一個資料表BROWSER_TX_ERROR_DAY_LOG，這樣使用者在查詢時只要來這份統計後的資料表查詢，速度會比較快。

麻煩的是必須要依瀏覽器類型BROWSER_TYPE及版本BROWSER_VERSION來統計不同錯誤代碼ERROR_CODE的每日總計次數，且各瀏覽器要被統計的版本也不太相同，例如IE 11以下，Chrome 60以下的版本就不統計。

下面是TX_LOG資料表及其中幾個要被查詢的欄位。

TX_LOG

CREATE TABLE TX_LOG(
  ...

  BROWSER_TYPE VARCHAR2(50), -- 瀏覽器類型 eg.Chrome, Firefox, IE, Safari, Edge, Opera...
  BROWSER_VERSION NUMBER(4), -- 瀏覽器主版本

  ERROR_CODE VARCHAR(4), -- 錯誤代碼 eg. E001, E002...

  CREATE_TIME NUMBER(18)
  ...
);

下面是查詢TX_LOG並統計後存放的BROWSER_TX_ERROR_DAY_LOG資料表

BROWSER_TX_ERROR_DAY_LOG

CREATE BROWSER_TX_ERROR_DAY_LOG(
  ...
  BROWSER_TYPE VARCHAR2(50), -- 瀏覽器類型 eg.Chrome, Firefox, IE, Safari, Edge, Opera...
  BROWSER_VERSION NUMBER(4), -- 瀏覽器主版本

  ERROR_CODE VARCHAR(4) NOT NULL, -- 錯誤代碼 eg. E001, E002...
  ERROR_CODE_DAILY_COUNT NUMBER(4) NOT NULL, -- 錯誤代碼每日總計

  CREATE_DATE DATE,
  ...
);

下面是跑排程統計的查詢SQL，查詢的結果就存入BROWSER_TX_ERROR_DAY_LOG。

SELECT 
    BROWSER_TYPE,
    BROWSER_VERSION,
    ERROR_CODE,
    COUNT(ERROR_CODE) AS ERROR_COUNT
FROM (
        SELECT
            CASE 
                WHEN BROWSER_TYPE NOT IN ('IE','CHROME', 'FIREFOX','SAFARI','EDGE') THEN 'OTHERS'
                WHEN BROWSER_TYPE IS NULL THEN 'OTHERS'
                ELSE BROWSER_TYPE
            END
            AS BROWSER_TYPE,
            BROWSER_VERSION,
            ERROR_CODE,
            CREATE_TIME
        FROM TX_LOG
        WHERE ERROR_CODE IN ('E001','E002')
        AND TO_DATE('1970/01/01', 'YYYY/MM/DD') + (CREATE_TIME/1000/60/60/24) BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE)
    )
WHERE 1 = 1
AND (
        CASE
            WHEN BROWSER_TYPE = 'IE' AND BROWSER_VERSION >= 11 THEN 1
            WHEN BROWSER_TYPE IN ('CHROME', 'FIREFOX') AND BROWSER_VERSION >= 60 THEN 1
            WHEN BROWSER_TYPE = 'SAFARI' AND BROWSER_VERSION >= 10 THEN 1
            ELSE 1
        END
    ) = 1
GROUP BY BROWSER_TYPE, BROWSER_VERSION, ERROR_CODE;

以下為本次查詢SQL的重點。

  • 使用CASE..WHEN..ELSE..END把不屬於主流瀏覽器的名稱或null改為OTHERS。
  • 使用WHERE搭配AND ( CASE..WHEN..ELSE..END ) = 1來排除不需要統計的瀏覽器版本。
  • 把毫秒單位轉成日期做為查前一天記錄的查詢條件。
  • 使用GROUP BY統計各瀏覽器，各版本，各錯誤代碼的次數總計。
  • 使用子查詢的原因是其他瀏覽器名稱轉成的OTHERS與null轉成的OTHERS不會被聚合在同一類，所以要先用子查詢轉換後再用GROUP BY聚合。

參考：

沒有留言:

張貼留言