Oracle 分析函數實戰大全：RANK、SUM、LAG/LEAD、ROLLUP/CUBE 完整範例

前言

分析函數（Analytic Functions）是 Oracle SQL 進階查詢的核心利器，能在不改變結果集行數的前提下，進行群組內排序、累計、比較、統計等運算。本文以「打工收入統計」為情境，完整示範常用分析函數語法與結果差異。

一、建立測試資料

create table earnings (
  earnmonth varchar2(6),   -- 月份 200912
  area      varchar2(20),  -- 地區
  sno       varchar2(10),  -- 工號
  sname     varchar2(20),  -- 姓名
  times     int,           -- 次數
  singleincome number(10,2), -- 單價
  personincome number(10,2)  -- 總收入
);

-- 插入 20 筆測試資料（2 個月 × 2 地區 × 5 人）
insert into earnings values('200912','北平','511601','大魁',11,30,330);
insert into earnings values('200912','北平','511602','大凱',8,25,200);
insert into earnings values('200912','北平','511603','小東',30,6.25,187.5);
insert into earnings values('200912','北平','511604','大亮',16,8.25,132);
insert into earnings values('200912','北平','511605','賤敬',30,11,330);
insert into earnings values('200912','金陵','511301','小玉',15,12.25,183.75);
insert into earnings values('200912','金陵','511302','小凡',27,16.67,450.09);
insert into earnings values('200912','金陵','511303','小妮',7,33.33,233.31);
insert into earnings values('200912','金陵','511304','小俐',0,18,0);
insert into earnings values('200912','金陵','511305','雪兒',11,9.88,108.68);
insert into earnings values('201001','北平','511601','大魁',0,30,0);
insert into earnings values('201001','北平','511602','大凱',14,25,350);
insert into earnings values('201001','北平','511603','小東',19,6.25,118.75);
insert into earnings values('201001','北平','511604','大亮',7,8.25,57.75);
insert into earnings values('201001','北平','511605','賤敬',21,11,231);
insert into earnings values('201001','金陵','511301','小玉',6,12.25,73.5);
insert into earnings values('201001','金陵','511302','小凡',17,16.67,283.39);
insert into earnings values('201001','金陵','511303','小妮',27,33.33,899.91);
insert into earnings values('201001','金陵','511304','小俐',16,18,288);
insert into earnings values('201001','金陵','511305','雪兒',11,9.88,108.68);
commit;

二、群組統計函數：GROUP BY / ROLLUP / CUBE

1. 基本 GROUP BY

select earnmonth, area, sum(personincome)
from earnings
group by earnmonth, area;

2. ROLLUP：層級小計（月份 → 總計）

select earnmonth, area, sum(personincome)
from earnings
group by rollup(earnmonth, area);

3. CUBE：交叉小計（所有維度組合）

select earnmonth, area, sum(personincome)
from earnings
group by cube(earnmonth, area)
order by earnmonth, area nulls last;

4. GROUPING 函數辨識小計列

select
  decode(grouping(earnmonth), 1, '所有月份', earnmonth) 月份,
  decode(grouping(area), 1, '全部地區', area) 地區,
  sum(personincome) 總金額
from earnings
group by cube(earnmonth, area)
order by earnmonth, area nulls last;

三、開窗排序函數：RANK / DENSE_RANK / ROW_NUMBER

select
  earnmonth 月份, area 地區, sname 打工者, personincome 收入,
  rank()        over (partition by earnmonth, area order by personincome desc) 排名_RANK,
  dense_rank()  over (partition by earnmonth, area order by personincome desc) 排名_DENSE_RANK,
  row_number()  over (partition by earnmonth, area order by personincome desc) 排名_ROW_NUMBER
from earnings;

差異對照

四、開窗彙總函數：SUM / MAX / MIN / AVG

1. 累計收入（依收入遞增）

select earnmonth, area, sname,
  sum(personincome) over (partition by earnmonth, area order by personincome) 累計收入
from earnings;

2. 群組內最大/最小/平均/總和（不需 GROUP BY）

select distinct
  earnmonth 月份, area 地區,
  max(personincome) over(partition by earnmonth, area) 最高值,
  min(personincome) over(partition by earnmonth, area) 最低值,
  avg(personincome) over(partition by earnmonth, area) 平均值,
  sum(personincome) over(partition by earnmonth, area) 總額
from earnings;

五、LAG / LEAD：跨行比較

select
  earnmonth 本月, sname 打工者,
  lag(decode(nvl(personincome,0),0,'沒賺','賺了'), 1, '無資料')
    over(partition by sname order by earnmonth) 上月,
  lead(decode(nvl(personincome,0),0,'沒賺','賺了'), 1, '無資料')
    over(partition by sname order by earnmonth) 下月
from earnings;

語法： LAG/LEAD(expr, offset, default) OVER (PARTITION BY ... ORDER BY ...)

六、開窗子句（OVER Clause）完整語法

OVER (
  [PARTITION BY 分群欄位]
  [ORDER BY 排序欄位]
  [WINDOWING CLAUSE]  -- ROWS/RANGE BETWEEN ...
)

七、實務應用場景

• 同期比較：LAG(sales, 12) OVER (PARTITION BY product ORDER BY month)
• 累計銷售：SUM(amount) OVER (PARTITION BY year ORDER BY month ROWS UNBOUNDED PRECEDING)
• Top-N 查詢：ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) <= 3
• 資料品質：LEAD(event_time) OVER (ORDER BY event_time) - event_time 計算間隔