Oracle 分析函數實戰 (一)：FIRST_VALUE / LAST_VALUE 取得群組極值與對應明細

業務場景

查詢某月各縣市銷量，並直接列出該月銷量 最高/最低 的縣市名稱與數值，避免子查詢或自聯結。

單月查詢

SELECT t.indent_month,
       t.dept_code,
       t.dept_name,
       SUM(t.amount) AS amount_sum,
       FIRST_VALUE(t.dept_name) OVER (ORDER BY SUM(t.amount) DESC) AS max_dept,
       FIRST_VALUE(SUM(t.amount)) OVER (ORDER BY SUM(t.amount) DESC) AS max_value,
       FIRST_VALUE(t.dept_name) OVER (ORDER BY SUM(t.amount) ASC)  AS min_dept,
       FIRST_VALUE(SUM(t.amount)) OVER (ORDER BY SUM(t.amount) ASC)  AS min_value
FROM   mv_jy_dept_sale_cig_month t
WHERE  t.nd = 2009
AND    t.indent_month = 12
GROUP  BY t.indent_month, t.dept_code, t.dept_name;

關鍵點：FIRST_VALUE 搭配 ORDER BY SUM(t.amount) DESC 直接取得排序後第一筆；ROWS UNBOUNDED PRECEDING 為預設視窗框架，可省略。

全年各月極值查詢 (加入 PARTITION BY)

SELECT a.indent_month,
       a.max_dept, a.max_value,
       a.min_dept, a.min_value
FROM (
  SELECT t.indent_month,
         t.dept_code,
         t.dept_name,
         SUM(t.amount) AS amount_sum,
         FIRST_VALUE(t.dept_name) OVER (PARTITION BY t.indent_month ORDER BY SUM(t.amount) DESC) AS max_dept,
         FIRST_VALUE(SUM(t.amount)) OVER (PARTITION BY t.indent_month ORDER BY SUM(t.amount) DESC) AS max_value,
         FIRST_VALUE(t.dept_name) OVER (PARTITION BY t.indent_month ORDER BY SUM(t.amount) ASC)  AS min_dept,
         FIRST_VALUE(SUM(t.amount)) OVER (PARTITION BY t.indent_month ORDER BY SUM(t.amount) ASC)  AS min_value
  FROM   mv_jy_dept_sale_cig_month t
  WHERE  t.nd = 2009
  GROUP  BY t.indent_month, t.dept_code, t.dept_name
) a
GROUP  BY a.indent_month, a.max_dept, a.max_value, a.min_dept, a.min_value
ORDER  BY a.indent_month;

進階：LAST_VALUE 陷阱與正確寫法

LAST_VALUE 預設視窗為 RANGE UNBOUNDED PRECEDING (目前列到最後一列)，不包含後續列，直接用會取到「目前列」而非「分組最後一列」。

正確寫法：明確指定視窗框架

LAST_VALUE(dept_name) OVER (
  PARTITION BY indent_month
  ORDER BY SUM(amount) DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_dept

或改用 FIRST_VALUE(... ORDER BY ... ASC) 達到同樣效果，較直觀。

效能提示

• 分析函數在 GROUP BY 後執行，不增加掃描輪數。
• 配合 PARTITION BY 可單次掃描完成多維度極值計算。
• 大量資料建議建立 (indent_month, dept_code) 複合索引支援分組排序。