Oracle 視圖實作：同表查詢多時段流量成功率

Oracle 視圖實作：同表查詢多時段流量成功率

專案開發中常遇到需從同一張表查詢不同時段資料並橫向對比的需求。本文以「小時流量表」為例，示範如何建立視圖一次查出通道近三個小時的發送成功率。

資料表結構

簡化後的 CHANNEL_FLUX 表結構如下：

需求分析

建立視圖輸出：通道 ID、上一小時成功率、上二小時成功率、上三小時成功率。若 SEND_NUM 為 0 或 NULL，結果回傳 NULL。以 14:00 為例，需分別取得 13:00、12:00、11:00 三個時段的成功率。

核心邏輯實作

1. 跨日小時差計算

使用 TO_CHAR(SYSDATE,'HH24') - TO_CHAR(REPORT_TIME,'HH24') 取得小時差，但需處理跨午夜情況（如 00:00 減 23:00 會得負數）。解法：

DECODE(SIGN(hour_diff), -1, hour_diff + 24, hour_diff)

SIGN 回傳正負號，負數時加 24 轉正，確保小時差落在 1~3 區間。

2. 除零與 NULL 防護

成功率計算公式：NVL(SUCCESS_NUM,0) / NVL(SEND_NUM,1)。先用 DECODE(NVL(SEND_NUM,0), 0, NULL, ...) 判斷分母為 0 時直接回傳 NULL，避免除零錯誤。

3. 完整查詢語句

SELECT vmhr.v_mo,
       vmhr.org_code,
       CASE hour_diff WHEN 1 THEN (SELECT DECODE(NVL(send_num,0),0,NULL,NVL(success_num,0)/NVL(send_num,1))
                                   FROM channel_flux WHERE v_mo = vmhr.v_mo) END last_1_suss_rate,
       CASE hour_diff WHEN 2 THEN (SELECT DECODE(NVL(send_num,0),0,NULL,NVL(success_num,0)/NVL(send_num,1))
                                   FROM channel_flux WHERE v_mo = vmhr.v_mo) END last_2_suss_rate,
       CASE hour_diff WHEN 3 THEN (SELECT DECODE(NVL(send_num,0),0,NULL,NVL(success_num,0)/NVL(send_num,1))
                                   FROM channel_flux WHERE v_mo = vmhr.v_mo) END last_3_suss_rate
FROM   channel_flux vmhr
WHERE  (SELECT DECODE(SIGN(TO_CHAR(SYSDATE,'HH24')-TO_CHAR(report_time,'HH24')),
                      -1, TO_CHAR(SYSDATE,'HH24')-TO_CHAR(report_time,'HH24')+24,
                        TO_CHAR(SYSDATE,'HH24')-TO_CHAR(report_time,'HH24'))
        FROM dual) BETWEEN 1 AND 3;

4. 建立視圖

CREATE VIEW v_channel_last3hr_succ_rate AS
[上述查詢語句];

關鍵函數說明

• SIGN(n)：回傳數值正負號（-1, 0, 1）
• DECODE(expr, search, result [, search, result]... [, default])：Oracle 專有條件判斷函數
• CASE expr WHEN val THEN res ... ELSE res END：ANSI 標準條件表達式

效能建議

若表資料量大，建議在 REPORT_TIME 與 V_MO 上建立複合索引，並考慮使用物化視圖定時重新整理以提升查詢效能。