Oracle开发之分析函数简介Over用法

Oracle分析函数提供了高级数据处理功能，支持在查询中执行复杂的数据分析操作，如窗口函数和聚合计算。

Oracle数据库中的分析函数是用于在查询中执行复杂运算的高级函数，它们可以在一组行上执行计算，返回单个结果，这类函数特别适用于报表生成和数据分析等场景。OVER 子句是分析函数的核心部分，它定义了分析函数操作的窗口或数据集的范围。

分析函数的基本概念

分析函数通常与 SELECT、UPDATE 或 DELETE 语句一起使用，并且总是与 OVER 子句结合使用。OVER 子句可以指定：

1、PARTITION BY 将结果集分区成多个部分，每部分独立进行聚合计算。

2、ORDER BY 对每个分区内的数据进行排序。

3、ROWS/RANGE 定义窗口范围，即分析函数考虑的行集合。

这些选项可以组合使用，以创建复杂的计算逻辑。

常见的分析函数

以下是一些Oracle数据库中常用的分析函数：

ROW_NUMBER() 为结果集的每一行分配一个唯一的数字。

RANK() 为结果集中的每个唯一值分配一个排名。

DENSE_RANK() 像 RANK() 一样分配排名，但排名之间没有间隔。

NTILE(n) 将结果集分为n个大致相等的部分，并为每行返回其所属部分的编号。

LEAD(column, n, default) 访问当前行之后的行中的值。

LAG(column, n, default) 访问当前行之前的行中的值。

FIRST_VALUE(column) 获取窗口中的第一个值。

LAST_VALUE(column) 获取窗口中的最后一个值。

SUM(column), AVG(column), MIN(column), MAX(column) 对窗口中的值进行聚合运算。

OVER 用法示例

假设我们有一个销售数据表 sales_data，包含以下字段：region, product, sale_date, quantity，我们想要计算每个区域每个月的总销售量，可以使用如下查询：

SELECT region, 
       to_char(sale_date, 'YYYY-MM') as month, 
       SUM(quantity) OVER (PARTITION BY region, to_char(sale_date, 'YYYY-MM')) as monthly_sales
FROM sales_data;

在这个例子中，PARTITION BY 子句按 region 和月份分区数据，SUM 函数计算每个分区的总销售量。

相关问题与解答

Q1: 分析函数和普通聚合函数有什么区别？

A1: 分析函数可以提供每行数据的计算结果，而普通聚合函数只返回整个结果集的单个聚合值。

Q2: ROW_NUMBER() 函数有什么用途？

A2: ROW_NUMBER() 为结果集中的每一行分配一个唯一的序号，常用于生成行的标识或分页查询。

Q3: RANK() 和 DENSE_RANK() 有何不同？

A3: RANK() 会在排名之间留出空隙，如果两个记录有相同的值，它们会得到相同的排名，但下一个排名会跳过；而 DENSE_RANK() 不会留出空隙，即使有相同的值，下一个排名也会紧接着当前最大的排名。

Q4: 如何使用 LEAD 和 LAG 函数来比较当前行与前一行或后一行的数据？

A4: LEAD(column, n, default) 访问当前行之后第n行的column的值，LAG(column, n, default) 则访问当前行之前第n行的column的值，如果超出范围，则返回default值，这些函数通常用于比较相邻行之间的差异。