Oracle SQL 查詢與函數進階教學 (中)：操作符、單行函數、群組函數、分析函數完整詳解

操作符優先級與分類

Oracle SQL 操作符按優先級從高到低：

1. 算術操作符：+、-、*、/
2. 串接操作符：||
3. 比較操作符：=、!=/^=/<>、>、<、>=、<=、BETWEEN ... AND、IN、LIKE、IS [NOT] NULL
4. 邏輯操作符：NOT、AND、OR
5. 集合操作符：UNION/UNION ALL、INTERSECT、MINUS

使用括號 () 可強制改變評估順序，提升可讀性。

單行函數：逐行運算，逐行返回

字元函數

數值函數

日期函數

轉換函數

• TO_CHAR(number/date, 'fmt')：格式化輸出。
• TO_NUMBER(str, 'fmt')：字串轉數值，格式模型如 '999G999D99'。
• TO_DATE / TO_TIMESTAMP：字串轉日期/時間戳。
• CAST(expr AS type)：ANSI 標準轉換，如 CAST('123' AS NUMBER)。

通用函數 (空值處理、條件邏輯)

群組函數：多行輸入，單行輸出

關鍵子句：GROUP BY 分組、HAVING 過濾群組結果 (可用群組函數)、ROLLUP/CUBE/GROUPING SETS 多維彙總。

群組函數實戰範例

-- 各部門薪資統計
SELECT deptno,
       COUNT(*) AS emp_cnt,
       AVG(sal) AS avg_sal,
       MAX(sal) AS max_sal,
       SUM(sal) AS total_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
ORDER BY total_sal DESC;

-- 多維彙總
SELECT deptno, job,
       COUNT(*),
       SUM(sal),
       GROUPING(deptno) AS grp_dept,
       GROUPING(job) AS grp_job
FROM emp
GROUP BY ROLLUP (deptno, job);

分析函數：分組內排序、排名、移動統計

語法：analytic_function([args]) OVER (PARTITION BY ... ORDER BY ... [WINDOWING])

三大排名函數差異

常用分析函數

• LAG/LEAD(expr, offset, default)：取前/後 N 行的值。
• FIRST_VALUE / LAST_VALUE：視窗首/尾值。
• NTILE(n)：分桶 (如四分位數)。
• SUM/AVG/COUNT... OVER：移動聚合 (配合 ROWS BETWEEN ... / RANGE BETWEEN ...)。
• RATIO_TO_REPORT：佔比。

分析函數實戰：每部門薪資前三名

SELECT deptno, ename, sal,
       ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
FROM emp
QUALIFY rn <= 3;  -- Oracle 12c+ 支援 QUALIFY，舊版需包一層子查詢

-- 移動平均：最近 3 個月銷售額平均
SELECT sales_month, amount,
       AVG(amount) OVER (ORDER BY sales_month
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mov_avg_3m
FROM monthly_sales;

綜合實戰：銷售報表 (單行+群組+分析)

WITH sales_clean AS (
  SELECT TO_DATE(sale_dt, 'YYYYMMDD') AS sale_date,
         prod_id,
         NVL(qty,0) AS qty,
         NVL(amt,0) AS amt
  FROM staging_sales
  WHERE sale_dt IS NOT NULL
),
daily_agg AS (
  SELECT sale_date,
         SUM(amt) AS daily_amt,
         COUNT(DISTINCT prod_id) AS prod_cnt
  FROM sales_clean
  GROUP BY sale_date
),
final_report AS (
  SELECT sale_date,
         daily_amt,
         prod_cnt,
         AVG(daily_amt) OVER (ORDER BY sale_date
                              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
         SUM(daily_amt) OVER (ORDER BY sale_date
                              ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS sum_30d,
         LAG(daily_amt, 7) OVER (ORDER BY sale_date) AS amt_7d_ago,
         CASE WHEN daily_amt > LAG(daily_amt, 7) OVER (ORDER BY sale_date)
              THEN 'UP' ELSE 'DOWN' END AS wow_trend
  FROM daily_agg
)
SELECT TO_CHAR(sale_date, 'YYYY-MM-DD') AS date,
       daily_amt,
       ROUND(avg_7d,2) AS avg_7d,
       sum_30d,
       amt_7d_ago,
       wow_trend
FROM final_report
ORDER BY sale_date DESC;

最佳實務提醒

• 函數嵌套慎用：UPPER(TRIM(SUBSTR(...))) 在大表 WHERE 條件會導致全表掃描，改用函數索引或應用層預處理。
• 隱性轉換殺手：WHERE num_col = '123' → 隱性 TO_NUMBER，索引失效。統一用綁定變數或顯式轉換。
• NULL 語意：NULL = NULL 是 UNKNOWN，非 TRUE。用 IS NULL/NVL/COALESCE。
• 分析函數視窗：預設 RANGE UNBOUNDED PRECEDING (含同值行)，大數據量時改用 ROWS 明確物理偏移，效能更穩定。
• 群組 vs 分析：需明細行保留時用分析函數；僅需彙總行用群組函數，資源消耗差異大。