Oracle数据库分析函数用法

什么是分析函数？

分析函数是oracle数据库中的一种强大的查询工具，它可以统计分析查询结果，并返回预期的数据。

分析函数语法

analytic_function_name (expression) 
  OVER (
    [PARTITION BY partition_expression, ... ]
    [ORDER BY order_expression [ASC | DESC], ... ]
    [ROWS BETWEEN window_frame_start AND window_frame_end]
  )

• analytic_function_name：分析函数的名称，比如：SUM、AVG、COUNT、ROW_NUMBER等
• expression：要处理的列或表达式。
• PARTITION BY：与GROUP BY类似，按照指定的列来划分为不同的窗口，每个窗口内的聚合计算是相互独立的。
• ORDER BY：指定窗口内行的排序规则
• ROWS BETWEEN：用来定义行的窗口区间

分析函数示例

示例1：计算每个销售员的销售额及占比

假设有一个 sales 表，其中包含 salesman, sale_amount 两个列。我们要计算每个销售员的销售额及销售额占比。可以使用如下SQL：

SELECT 
  salesman,
  sale_amount,
  SUM(sale_amount) OVER () total_sale_amount, 
  ROUND(100 * sale_amount / SUM(sale_amount) OVER (), 2) as sale_rate
FROM 
  sales
ORDER BY 
  sale_amount DESC;

分析：

• SUM(sale_amount) OVER () 表示将 sale_amount 按照原表作为一个窗口，
• total_sale_amount 表示所有销售员销售额总和
• ROUND(100 * sale_amount / SUM(sale_amount) OVER (), 2) 表示计算占比，保留两位小数。

结果如下：

SALESMAN  SALE_AMOUNT  TOTAL_SALE_AMOUNT  SALE_RATE
--------  -----------  -----------------  --------
Wang         4300.00            12900.00     33.33
Zhang        3900.00            12900.00     30.23
Li           2700.00            12900.00     20.93
Zhao         2900.00            12900.00     22.44

示例2：计算每个销售员的累加销售额

假设有一个 sales 表，其中包含 salesman, sale_amount 两个列。我们要计算每个销售员的累加销售额。可以使用如下SQL：

SELECT 
  salesman,
  sale_amount,
  SUM(sale_amount) OVER (PARTITION BY salesman ORDER BY sale_amount) acc_sale_amount
FROM 
  sales
ORDER BY 
  salesman, sale_amount;

分析：

• SUM(sale_amount) OVER (PARTITION BY salesman ORDER BY sale_amount) 表示将 sale_amount 按照 salesman 分组，并按照 sale_amount 排序后再按照顺序累加计算
• acc_sale_amount 表示每个销售员的累加销售额

结果如下：

SALESMAN  SALE_AMOUNT  ACC_SALE_AMOUNT
--------  -----------  ---------------
Li            700.00            700.00
Li           1000.00           1700.00
Li           1000.00           2700.00
Zhang         900.00            900.00
Zhang        1000.00           1900.00
Zhang        2000.00           3900.00
Wang         1500.00           1500.00
Wang         1600.00           3100.00
Wang         2200.00           5300.00
Zhao         1100.00           1100.00
Zhao         1300.00           2400.00
Zhao         1500.00           3900.00

以上是 Oracle数据库分析函数用法 的完整攻略。