Oracle分析函數全解析：從ROLLUP到RATIO_TO_REPORT的實戰建模指南

前言：分析函數——SQL建模的「核武器」

Oracle 8.1.6引入分析函數徹底改變了複雜報表的編寫方式：原本需要自連接、子查詢、臨時表、甚至存儲過程才能實現的邏輯，如今一條OVER()子句即可優雅解決。本文系統梳理6大類核心分析函數，配合真實業務數據集實戰演示。

基礎數據集：電信計費明細（簡化版）

BILL_MONTH  AREA_CODE  NET_TYPE  LOCAL_FARE
----------  ---------  --------  ----------
200405      5761       G         7393344.04
200405      5761       J         5667089.85
200405      5762       G         6315075.96
200405      5762       J         6328716.15
... (共36行，跨3個月、5個區域、2種網絡類型)

類別一：多維彙總 —— ROLLUP / CUBE / GROUPING SETS

業務需求

生成「月-區域-網絡」三維度的全彙總報表：明細行 + 月小計 + 區域小計 + 網絡小計 + 總計。

標準SQL（繁瑣）

SELECT bill_month, area_code, net_type, SUM(local_fare)
  FROM t GROUP BY bill_month, area_code, net_type
UNION ALL
SELECT bill_month, area_code, NULL, SUM(local_fare)
  FROM t GROUP BY bill_month, area_code
UNION ALL ... -- 共7個GROUP BY

分析函數一行解決

SELECT 
  bill_month, area_code, net_type,
  SUM(local_fare) AS fare_sum,
  GROUPING_ID(bill_month, area_code, net_type) AS grp_id  -- 識別彙總層級
FROM t
GROUP BY ROLLUP (bill_month, area_code, net_type)
ORDER BY bill_month, area_code, net_type;

關鍵函數

• ROLLUP：層次化彙總（月→區域→網絡→總計），N+1行；
• CUBE：全排列彙總（所有維度組合），2^N行；
• GROUPING SETS：自定義彙總組合，靈活控制輸出行數；
• GROUPING(col) / GROUPING_ID(...)：區分「業務NULL」與「彙總NULL」。

類別二：排序與排名 —— RANK / DENSE_RANK / ROW_NUMBER

業務需求

各月各區域下，按收入排名Top 3的網絡類型；處理並列排名業務語義差異。

實戰對比

SELECT 
  bill_month, area_code, net_type, local_fare,
  RANK()       OVER (PARTITION BY bill_month, area_code ORDER BY local_fare DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY bill_month, area_code ORDER BY local_fare DESC) AS drnk,
  ROW_NUMBER() OVER (PARTITION BY bill_month, area_code ORDER BY local_fare DESC) AS rn
FROM t;

語義差異表

類別三：行間對比 —— LAG / LEAD

業務需求

計算各區域各網絡類型的「環比增長率」：(本月 - 上月) / 上月。

實戰

SELECT 
  bill_month, area_code, net_type, local_fare,
  LAG(local_fare) OVER (PARTITION BY area_code, net_type ORDER BY bill_month) AS prev_fare,
  ROUND((local_fare - LAG(local_fare) OVER (...)) / 
        NULLIF(LAG(local_fare) OVER (...), 0) * 100, 2) AS mom_pct
FROM t
ORDER BY area_code, net_type, bill_month;

進階：多期對比

LAG(local_fare, 1) OVER (...)  AS prev_1m,   -- 上月
LAG(local_fare, 3) OVER (...)  AS prev_3m,   -- 上季
LAG(local_fare, 12) OVER (...) AS prev_12m   -- 同比

類別四：移動窗口統計 —— SUM/AVG OVER (ROWS/RANGE BETWEEN)

業務需求

計算各區域「3個月移動平均收入」，平滑季節性波動。

實戰

SELECT 
  bill_month, area_code, net_type, local_fare,
  AVG(local_fare) OVER (
    PARTITION BY area_code, net_type 
    ORDER BY bill_month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 含當前行共3個月
  ) AS mov_avg_3m,
  SUM(local_fare) OVER (
    PARTITION BY area_code, net_type 
    ORDER BY bill_month
    RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW  -- 語義窗口
  ) AS mov_sum_3m_range
FROM t;

ROWS vs RANGE 核心差異

• ROWS：物理行數窗口，確定性強，性能優；
• RANGE：邏輯值範圍窗口，支持日期/數值間隔語義，但要求ORDER BY列唯一，否則可能產生非確定性結果。

類別五：佔比分析 —— RATIO_TO_REPORT

業務需求

計算各網絡類型在區域月收入中的佔比。

實戰

SELECT 
  bill_month, area_code, net_type, local_fare,
  ROUND(RATIO_TO_REPORT(local_fare) OVER (PARTITION BY bill_month, area_code) * 100, 2) AS pct_of_area
FROM t;

等價標準SQL（對比）

local_fare / SUM(local_fare) OVER (PARTITION BY bill_month, area_code)

類別六：首尾值提取 —— FIRST / LAST (KEEP FIRST/LAST)

業務需求

查詢各區域「最早月份」和「最新月份」的收入值（而非彙總）。

實戰

SELECT 
  area_code,
  MIN(bill_month) KEEP (DENSE_RANK FIRST ORDER BY bill_month) AS first_month,
  MAX(local_fare) KEEP (DENSE_RANK FIRST ORDER BY bill_month) AS first_fare,
  MIN(bill_month) KEEP (DENSE_RANK LAST ORDER BY bill_month)  AS last_month,
  MAX(local_fare) KEEP (DENSE_RANK LAST ORDER BY bill_month)  AS last_fare
FROM t
GROUP BY area_code;

性能優化核心原則

1. 分區裁剪優先： PARTITION BY列應包含表分區鍵，實現分區級並行；
2. 排序消除： 若ORDER BY列與索引順序一致，優化器可跳過SORT操作（WINDOW NOSORT）；
3. 物化視圖預計算： 高頻報表將分析函數結果物化，查詢秒級返回；
4. 避免窗口過大： ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING等價於全分區排序，慎用。

結語

分析函數將「行間計算、多維彙總、佔比排名」等複雜邏輯封裝為聲明式SQL，不僅代碼量減少80%，更通過優化器深度整合實現了極致性能。建議建立分析函數模式庫，將常見報表模式（如YoY、MoM、移動平均、Top N、佔比）標準化為可復用SQL模板。