Oracle 分析函数（Analytic Functions）

Oracle 的分析函数（Analytic Functions）是一类特殊的函数，用于在查询结果的窗口（window）内执行计算（如排名、累计求和、移动平均等），不会聚合结果行，而是为每一行返回一个计算结果。它们通常与 OVER() 子句结合使用，是处理复杂分析需求（如分组排名、累计统计等）的高效工具。

分析函数核心语法

analytic_function([arguments]) 
OVER (
  [PARTITION BY partition_clause]  -- 将数据划分为多个窗口（类似GROUP BY）
  [ORDER BY order_clause]          -- 定义窗口内的排序规则
  [window_clause]                  -- 定义窗口范围（如滑动窗口）
)

常见分析函数分类

经典示例

CREATE TABLE sales (
  product VARCHAR2(20),
  sale_date DATE,
  amount NUMBER
);

INSERT INTO sales VALUES ('A', DATE '2023-01-01', 100);
INSERT INTO sales VALUES ('A', DATE '2023-01-02', 200);
INSERT INTO sales VALUES ('B', DATE '2023-01-01', 150);
INSERT INTO sales VALUES ('B', DATE '2023-01-03', 300);


计算每个产品的累计销售额

SELECT 
  product,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY product 
    ORDER BY sale_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_amount
FROM sales;


PRODUCT | SALE_DATE  | AMOUNT | CUMULATIVE_AMOUNT
--------|------------|--------|-------------------
A       | 2023-01-01 | 100    | 100
A       | 2023-01-02 | 200    | 300
B       | 2023-01-01 | 150    | 150
B       | 2023-01-03 | 300    | 450

计算每个产品的销售额排名
SELECT 
  product,
  sale_date,
  amount,
  RANK() OVER (
    PARTITION BY product 
    ORDER BY amount DESC
  ) AS sales_rank
FROM sales;

PRODUCT | SALE_DATE  | AMOUNT | SALES_RANK
--------|------------|--------|-----------
A       | 2023-01-02 | 200    | 1
A       | 2023-01-01 | 100    | 2
B       | 2023-01-03 | 300    | 1
B       | 2023-01-01 | 150    | 2

获取每个产品的前一行销售额（LAG）

SELECT 
  product,
  sale_date,
  amount,
  LAG(amount, 1, 0) OVER (
    PARTITION BY product 
    ORDER BY sale_date
  ) AS prev_amount
FROM sales;

PRODUCT | SALE_DATE  | AMOUNT | PREV_AMOUNT
--------|------------|--------|------------
A       | 2023-01-01 | 100    | 0          -- 无前一行，默认0
A       | 2023-01-02 | 200    | 100
B       | 2023-01-01 | 150    | 0
B       | 2023-01-03 | 300    | 150

关键子句详解

1. PARTITION BY
   将数据划分为多个窗口，每个窗口独立计算。类似于 GROUP BY，但不会减少行数。

2. ORDER BY
   定义窗口内的排序规则，影响排名、累计计算等。

3. 窗口帧（Window Frame）
   通过 ROWS 或 RANGE 定义窗口范围，常见用法：

   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从第一行到当前行（累计计算）。

   • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：前一行到后一行（滑动窗口）。

常见问题

1. 分析函数与聚合函数的区别？
   聚合函数（如 SUM()）会合并多行为一行，而分析函数会为每一行返回结果。

2. 如何优化分析函数性能？
   合理使用 PARTITION BY 和索引，避免全表扫描。

3. RANK() vs DENSE_RANK() vs ROW_NUMBER()

   • RANK()：允许并列排名，后续排名跳跃（如 1,1,3）。

   • DENSE_RANK()：允许并列排名，后续排名连续（如 1,1,2）。

   • ROW_NUMBER()：无并列，严格递增（如 1,2,3）。