Oracle 分析函数（Analytic Functions）详解

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

---

1. 概述

分析函数（Analytic Functions） 用于对结果集进行复杂计算[1]：

核心特性：

• 不聚合结果（保留每行）
• 支持窗口
• 支持排序
• 高性能

---

2. 语法

function_name(argument1, argument2, ...)
OVER (
  [PARTITION BY partition_expression]
  [ORDER BY sort_expression [ASC|DESC] [NULLS FIRST|LAST]]
  [windowing_clause]
)

---

3. 窗口子句

3.1 ROWS

-- 当前行 + 前 1 行 + 后 1 行
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- 从开始到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 前 3 行
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

3.2 RANGE

-- 范围窗口
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING

---

4. 排名函数

4.1 ROW_NUMBER

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

4.2 RANK

-- 排名（并列跳号）
SELECT 
  name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- 10000 → 1
-- 10000 → 1
-- 9000 → 3（跳过 2）

4.3 DENSE_RANK

-- 紧凑排名（并列不跳号）
SELECT 
  name,
  salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- 10000 → 1
-- 10000 → 1
-- 9000 → 2（不跳）

4.4 NTILE

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

---

5. 偏移函数

5.1 LAG

-- 上一行
SELECT 
  name,
  salary,
  LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS prev_salary,
  salary - LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS diff
FROM employees;

5.2 LEAD

-- 下一行
SELECT 
  name,
  salary,
  LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

5.3 FIRST_VALUE / LAST_VALUE

-- 部门最高/最低薪资
SELECT 
  name,
  dept_id,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS max_sal,
  LAST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_sal
FROM employees;

---

6. 聚合函数

6.1 累计

-- 累计求和
SELECT 
  name,
  salary,
  SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
FROM employees;

6.2 移动平均

-- 3 行移动平均
SELECT 
  name,
  salary,
  AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

6.3 分组聚合

-- 部门平均薪资
SELECT 
  name,
  dept_id,
  salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

---

7. 统计函数

7.1 CUME_DIST

-- 累积分布
SELECT 
  name,
  salary,
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;

7.2 PERCENT_RANK

-- 百分比排名
SELECT 
  name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;

7.3 PERCENTILE_CONT

-- 中位数
SELECT 
  dept_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees
GROUP BY dept_id;

7.4 STDDEV / VARIANCE

-- 标准差/方差
SELECT 
  name,
  salary,
  STDDEV(salary) OVER (PARTITION BY dept_id) AS std_dev,
  VARIANCE(salary) OVER (PARTITION BY dept_id) AS variance
FROM employees;

---

8. 报表函数

8.1 RATIO_TO_REPORT

-- 占比
SELECT 
  name,
  salary,
  RATIO_TO_REPORT(salary) OVER () AS salary_pct
FROM employees;

8.2 分组占比

-- 部门内占比
SELECT 
  name,
  dept_id,
  salary,
  RATIO_TO_REPORT(salary) OVER (PARTITION BY dept_id) AS dept_pct
FROM employees;

---

9. 应用场景

9.1 Top N

-- 每部门前 3 名
SELECT * FROM (
  SELECT 
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
)
WHERE rn <= 3;

9.2 去重

-- 保留每组最新
DELETE FROM employees
WHERE rowid IN (
  SELECT rowid FROM (
    SELECT 
      rowid,
      ROW_NUMBER() OVER (PARTITION BY email ORDER BY update_time DESC) AS rn
    FROM employees
  )
  WHERE rn > 1
);

9.3 同比环比

-- 月度销售同比环比
SELECT 
  month,
  sales,
  LAG(sales, 12) OVER (ORDER BY month) AS last_year,  -- 同比
  LAG(sales, 1) OVER (ORDER BY month) AS last_month,  -- 环比
  (sales - LAG(sales, 1) OVER (ORDER BY month)) / LAG(sales, 1) OVER (ORDER BY month) AS growth_rate
FROM monthly_sales;

9.4 累计排名

-- 累计排名
SELECT 
  name,
  hire_date,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS all_rank,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;

---

10. 性能优化

10.1 索引

-- 排序列加索引
CREATE INDEX idx_emp_salary ON employees(salary);
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

10.2 并行

-- 并行执行
SELECT /*+ PARALLEL(e 4) */ 
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees e;

10.3 避免重复计算

-- 使用 CTE
WITH ranked AS (
  SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 10;

---

11. 常见坑与排错

11.1 LAST_VALUE 错误

-- 错误：LAST_VALUE 默认到当前行
LAST_VALUE(salary) OVER (ORDER BY salary DESC) AS min_sal
-- 返回当前行的 salary

-- 修复：指定窗口
LAST_VALUE(salary) OVER (ORDER BY salary DESC 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_sal

11.2 NULL 处理

-- NULLS FIRST / LAST
SELECT 
  name,
  commission,
  RANK() OVER (ORDER BY commission DESC NULLS LAST) AS rank
FROM employees;

11.3 性能差

修复：

-- 1. 加索引
-- 2. 并行
-- 3. 简化窗口
-- 4. 检查执行计划

---

12. 最佳实践

1. Top N 用 ROW_NUMBER：清晰
2. 并列排名用 DENSE_RANK：不跳号
3. 累计用 SUM OVER：高效
4. 移动平均用 AVG OVER：分析
5. 同比环比用 LAG/LEAD：时间序列
6. 占比用 RATIO_TO_REPORT：报表
7. 加索引提升性能：排序列
8. 用 CTE 简化：可读性
9. NULL 处理：NULLS FIRST/LAST
10. 测试大数据量：验证性能

---

13. 参考资料

[1] Oracle Database Data Warehousing Guide 19c, “Analytic Functions” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/analytic-functions.html

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