Oracle 高级分析函数

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 分析函数（窗口函数）处理复杂数据[1]：

类型：

• 排名
• 聚合
• 偏移
• 窗口
• 统计

详细见：Oracle 分析函数。

---

2. 排名函数

2.1 ROW_NUMBER

SELECT 
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

2.2 RANK / DENSE_RANK

SELECT 
  name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

2.3 NTILE

-- 分桶
SELECT 
  name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

2.4 分组排名

SELECT 
  name, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;

---

3. 聚合函数

3.1 SUM / AVG / COUNT

SELECT 
  name, dept_id, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
  COUNT(*) OVER (PARTITION BY dept_id) AS dept_count
FROM employees;

3.2 累计

-- 累计求和
SELECT 
  sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS cumulative
FROM sales;

3.3 滑动窗口

-- 7 日移动平均
SELECT 
  sale_date, amount,
  AVG(amount) OVER (
    ORDER BY sale_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM sales;

---

4. 偏移函数

4.1 LAG

-- 前一行
SELECT 
  sale_date, amount,
  LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS diff
FROM sales;

4.2 LEAD

-- 后一行
SELECT 
  sale_date, amount,
  LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

4.3 LAG / LEAD 参数

-- 偏移 N 行
LAG(amount, 3) OVER (ORDER BY sale_date)
-- 默认值
LAG(amount, 3, 0) OVER (ORDER BY sale_date)

---

5. FIRST_VALUE / LAST_VALUE

5.1 FIRST_VALUE

SELECT 
  name, dept_id, salary,
  FIRST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS top_earner
FROM employees;

5.2 LAST_VALUE

-- 注意窗口
SELECT 
  name, dept_id, salary,
  LAST_VALUE(name) OVER (
    PARTITION BY dept_id 
    ORDER BY salary DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;

---

6. NTH_VALUE（11g+）

-- 第 N 值
SELECT 
  name, dept_id, salary,
  NTH_VALUE(name, 3) OVER (
    PARTITION BY dept_id 
    ORDER BY salary DESC
  ) AS third_earner
FROM employees;

---

7. 窗口

7.1 ROWS

-- 行范围
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

7.2 RANGE

-- 值范围
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

7.3 区别

• ROWS：物理行数
• RANGE：逻辑值

---

8. RATIO_TO_REPORT

SELECT 
  name, dept_id, salary,
  RATIO_TO_REPORT(salary) OVER (PARTITION BY dept_id) AS pct_of_dept
FROM employees;

---

9. PERCENT_RANK / CUME_DIST

SELECT 
  name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;

---

10. PERCENTILE

SELECT 
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_disc
FROM employees;

---

11. KEEP

11.1 FIRST / LAST

SELECT 
  dept_id,
  MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) AS first_salary,
  MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) AS last_salary
FROM employees
GROUP BY dept_id;

---

12. LISTAGG（11g R2+）

12.1 基本

SELECT 
  dept_id,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS employees
FROM employees
GROUP BY dept_id;

12.2 窗口

SELECT 
  name, dept_id,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) 
    OVER (PARTITION BY dept_id) AS dept_employees
FROM employees;

12.3 12c R2 截断

LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY name)

---

13. 统计函数

13.1 VAR_POP / VAR_SAMP

SELECT 
  VAR_POP(salary) AS pop_var,
  VAR_SAMP(salary) AS sample_var,
  STDDEV_POP(salary) AS pop_stddev,
  STDDEV_SAMP(salary) AS sample_stddev
FROM employees;

13.2 CORR / COVAR

SELECT 
  CORR(salary, age) AS correlation,
  COVAR_POP(salary, age) AS covar_pop,
  COVAR_SAMP(salary, age) AS covar_samp
FROM employees;

13.3 REGR

SELECT 
  REGR_SLOPE(salary, age) AS slope,
  REGR_INTERCEPT(salary, age) AS intercept,
  REGR_R2(salary, age) AS r2
FROM employees;

---

14. 层次 + 分析

14.1 员工层级

SELECT 
  employee_id, last_name, manager_id,
  LEVEL,
  ROW_NUMBER() OVER (PARTITION BY manager_id ORDER BY salary DESC) AS rank_in_mgr
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

详细见：Oracle 层次查询。

---

15. 性能

15.1 性能优势

- 自连接减少
- 多次扫描减少
- 高效

15.2 索引

- PARTITION BY 列
- ORDER BY 列
- 复合索引

15.3 执行计划

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));
-- WINDOW SORT

---

16. 应用场景

16.1 TOP N

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees e
) WHERE rn <= 10;

16.2 去重

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn
  FROM employees e
) WHERE rn = 1;

16.3 累计

SELECT 
  sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS cum_total
FROM sales;

16.4 同比环比

SELECT 
  sale_date, amount,
  LAG(amount, 12) OVER (ORDER BY sale_date) AS last_year,
  (amount - LAG(amount, 12) OVER (ORDER BY sale_date)) / 
    LAG(amount, 12) OVER (ORDER BY sale_date) AS yoy_growth
FROM sales;

---

17. 常见坑与排错

17.1 LAST_VALUE 陷阱

- 默认窗口到当前行
- 需 UNBOUNDED FOLLOWING

17.2 PARTITION BY 错误

- 分区列错误
- 结果不一致

17.3 ORDER BY 影响

- 加 ORDER BY：累积
- 不加：全分区

---

18. 最佳实践

1. PARTITION BY 分组：典型
2. ORDER BY 排序：合理
3. 窗口精确：避免陷阱
4. 索引：性能
5. 执行计划：验证
6. 替代自连接：性能
7. TOP N：ROW_NUMBER
8. 去重：ROW_NUMBER
9. 累计：SUM
10. 测试：正确性

---

19. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Analytic Functions” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Analytic-Functions.html