Oracle 分析函数

Oracle 简介

Oracle 是数据库管理系统、云服务和企业软件解决方案的领先提供商。它成立于 1977 年，此后成为世界上最大的技术公司之一。Oracle 的主要产品是 Oracle 数据库，被各种规模的组织广泛用于存储、管理和检索其数据。

1977 年，Larry Ellison、Bob Miner 和 Ed Oates 在加利福尼亚州圣克拉拉创立了 Oracle 公司。公司最初名为软件开发实验室 (SDL)，主要专注于开发和销售名为 Oracle 的关系型数据库管理系统 (RDBMS)。

2009 年，Oracle 完成了对 Sun Microsystems 的收购，获得了 Java 和其他关键技术的控制权。此次收购使 Oracle 成为硬件和软件领域的主要参与者。

Oracle 数据库是一个关系型数据库管理系统 (RDBMS)，它为处理大量结构化和非结构化数据提供了强大且可扩展的平台。它提供了全面的数据管理功能集，包括数据建模、数据完整性、并发控制、备份和恢复以及数据安全性。该数据库支持 SQL（结构化查询语言）进行数据查询和操作，并提供了一组丰富的内置函数和运算符。

除了 Oracle 数据库，Oracle 还提供广泛的企业软件产品和解决方案，包括

• Oracle Fusion Applications：一套基于云的企业资源规划 (ERP)、人力资本管理 (HCM) 和客户体验 (CX) 应用程序。
  
• Oracle 云基础设施 (OCI)：一个全面的云计算平台，提供基础设施即服务 (IaaS)、平台即服务 (PaaS) 和软件即服务 (SaaS) 产品。
  
• Oracle 中间件：一系列软件产品和工具，支持应用程序和服务集成、开发和管理。
  
• Oracle E-Business Suite：一套集成的业务应用程序套件，用于管理各种业务功能，包括财务、供应链、制造和客户关系管理。
  
• Oracle Java：Oracle 也是 Java 编程语言的管理者，并提供 Java 开发工具、运行时环境和企业框架。
  

Oracle 分析函数

Oracle 提供了强大的分析函数，使您可以在 SQL 查询中对数据执行高级计算和分析。这些函数对一组行进行操作，并为查询结果集中的每一行返回一个结果。一些常用的 Oracle 分析函数包括

• RANK：根据指定的条件为查询结果集中的每一行分配一个排名。
• DENSE_RANK：类似于 RANK 函数，它分配连续的排名，没有间隙。
• ROW_NUMBER：为查询结果集中的每一行分配一个唯一的数字。
• LAG：根据指定的偏移量从结果集中的前一行返回一个值。
• LEAD：根据指定的偏移量从结果集中的后一行返回一个值。
• FIRST_VALUE：从结果集中的第一行返回值。
• LAST_VALUE：从结果集中的最后一行返回值。
• NTILE：将结果分成指定数量的等大小组，并为每一行分配一个组号。
• SUM、AVG、MIN、MAX：这些聚合函数也可以作为分析函数使用，通过与 OVER 子句结合，您可以计算累积和、移动平均值等。

排名

RANK 函数根据指定条件为查询结果集中的每一行分配一个排名。在您想确定一行相对于其他行在特定排序方面的相对位置的场景中，它会很有帮助。

RANK 函数的基本语法如下

以下组件是 Rank 函数的一部分

• RANK( )：这是函数本身。它在括号内不需要任何参数。
• OVER：此关键字指示排名操作是在特定的行窗口上执行的。
• PARTITION BY：此子句是可选的，允许您根据一个或多个列将行划分为分区。RANK 函数将在每个分区内分配单独的排名。
• ORDER BY：此子句指定确定排名的列。行根据指定的表达式按升序 (ASC) 或降序 (DESC) 排序。

这是一个演示 RANK 函数用法的示例

在此示例中，查询检索员工姓名、部门和薪水，并根据各自部门为每位员工的薪水分配排名。ORDER BY salary DESC 指定排名基于薪水降序排列。

结果将包括 employee_name、department、salary 和 salary_rank 列，其中 salary_rank 表示每位员工在其部门内薪水的排名。

注意：如果多行具有相同的值并被分配相同的排名，则会跳过后续排名。例如，如果两名员工的薪水最高，他们将被分配排名 1，下一个排名将是 3，而不是 2。

DENSE_RANK

Oracle 分析函数 DENSE_RANK 类似于 RANK 函数，但为行分配连续的排名，没有任何间隙。如果多行具有相同的值并被分配相同的排名，则不会跳过下一个排名。

DENSE_RANK 函数的语法类似于 RANK 函数

以下是 Dense_Rank 函数的组件列表

• DENSE_RANK()：这是函数本身。它在括号内不需要任何参数。
• OVER：此关键字指示排名操作是在特定的行窗口上执行的。
• PARTITION BY：此子句是可选的，允许您根据一个或多个列将行划分为分区。DENSE_RANK 函数将在每个分区内分配单独的排名。
• ORDER BY：此子句指定确定排名的列。行根据指定的表达式按升序 (ASC) 或降序 (DESC) 排序。

这是一个演示 DENSE_RANK 函数用法的示例

在此示例中，查询检索员工姓名、部门和薪水，并根据各自部门为每位员工的薪水分配密集排名。ORDER BY salary DESC 指定排名基于薪水降序排列。

结果将包括 employee_name、department、salary 和 salary_dense_rank 列，其中 salary_dense_rank 表示每位员工在其部门内薪水的密集排名。

注意：与 RANK 函数不同，DENSE_RANK 函数确保排名是连续的，没有间隙，即使多行具有相同的值并获得相同的排名。

ROW_NUMBER

Oracle 中的 ROW_NUMBER 函数是一个分析函数，它为查询结果集中的每一行分配一个唯一的顺序号。与 RANK 和 DENSE_RANK 函数不同，ROW_NUMBER 函数不考虑任何特定的排序条件。它只是根据行在结果集中的位置为每一行分配一个唯一的数字。

ROW_NUMBER 函数的基本语法如下

以下是 Row_Number 函数的组件列表

• ROW_NUMBER()：这是函数本身。它在括号内不需要任何参数。
• OVER：此关键字指示行编号操作是在特定的行窗口上执行的。
• ORDER BY：此子句指定根据哪些列对行进行排序。行根据指定的表达式按升序 (ASC) 或降序 (DESC) 排序。

以下是演示 ROW_NUMBER 函数用法的示例

在此示例中，查询检索员工姓名、部门和薪水，并根据薪水降序为每位员工分配唯一的行号。

结果将包括 employee_name、department、salary 和 row_number 列，其中 row_number 表示分配给每一行的顺序号。

注意：ROW_NUMBER 函数不提供排名或密集排名功能。它只是根据行在结果集中的位置为每一行分配一个唯一的数字。具有相同值的多行仍将获得不同的行号。

LAG 函数

Oracle 中的 LAG 函数是一个分析函数，它允许您访问结果集中前一行中特定列的值。它可以计算当前行和前一行之间的差异或变化。

LAG 函数的基本语法如下

以下是 LAG 函数的组件

• LAG：这是函数本身。
• column_expression：这指定了您要从前一行检索其值的列。
• Offset：此可选参数指示您要向前查看多少行以获取前一个值。默认值为 1，表示前一行。
• default_value：这是一个可选参数，指定在没有前一行可用时返回的值。如果未提供，默认返回 NULL。
• OVER：此关键字指示 LAG 操作是在特定的行窗口上执行的。
• ORDER BY：此子句指定根据哪些列对行进行排序。它定义了 LAG 函数评估行的顺序。

以下是我们可以展示 LAG 函数用法的代码

在此示例中，查询检索订单日期和总金额，并使用 LAG 函数从上一个订单中检索总金额。ORDER BY order_date 子句指定 LAG 函数应根据订单日期评估行。

结果将包括 order_date、total_amount 和 previous_amount 列，其中 previous_amount 表示上一个订单的总金额。

注意：如果上一个订单不可用（对于结果集中最早的订单），LAG 函数将返回指定的默认值（在本例中为 0）。

Oracle 中的 LEAD 函数

Oracle 中的 LEAD 函数是一个分析函数，它允许您访问结果集中后续行中特定列的值。它可以计算当前行和下一行之间的差异或变化。

LEAD 函数的基本语法如下

以下是 LEAD 函数的主要组件

• LEAD：这是函数本身。
• Column_expression：这指定了您要从下一行检索其值的列。
• Offset：此可选参数指示您要向前查看多少行以获取下一个值。默认值为 1，表示下一行。
• Default_value：此可选参数指定在没有下一行可用时返回的值。如果未提供，默认返回 NULL。
• OVER：此关键字指示 LEAD 操作是在特定的行窗口上执行的。
• ORDER BY：此子句指定根据哪些列对行进行排序。它定义了 LEAD 函数评估行的顺序。

以下是我们可以展示 LEAD 函数用法的示例

在此示例中，查询检索订单日期和总金额，并使用 LEAD 函数从后续订单中检索总金额。ORDER BY order_date 子句指定 LEAD 函数应根据订单日期评估行。

结果将包括 order_date、total_amount 和 next_amount 列，其中 next_amount 表示后续订单的总金额。

注意：如果后续订单不可用（对于结果集中最新的订单），LEAD 函数将返回指定的默认值（在本例中为 0）。

FIRST_VALUE 函数

Oracle 分析函数 FIRST_VALUE 根据指定的排序从结果集中的第一行检索特定列的值。它允许您访问与给定排序中最早或排名最低的行关联的列的值。

FIRST_VALUE 函数的基本语法如下

以下是此函数的组件

• FIRST_VALUE：这是函数本身。
• column_expression：这指定了您要从第一行检索其值的列。
• OVER：此关键字指示 FIRST_VALUE 操作是在特定的行窗口上执行的。
• ORDER BY：此子句指定根据哪些列对行进行排序。行根据指定的表达式按升序 (ASC) 或降序 (DESC) 排序。

这是一个演示 FIRST_VALUE 函数用法的示例

在此示例中，查询检索员工姓名、部门和薪水，并使用 FIRST_VALUE 函数根据薪水升序从第一行检索薪水。

结果将包括 employee_name、department、salary 和 first_salary，其中 first_salary 表示指定排序中第一行的薪水值。

NTILE 函数

Oracle 分析函数 NTILE 用于将结果集划分为指定数量的等大小组或桶。它根据指定的组数，为每一行分配一个组号。

NTILE 函数的基本语法如下

以下是 NTILE 函数的组件

• NTILE：这是函数本身。
• Number_of_buckets：这指定了希望将结果集划分为的等大小组或桶的数量。
• OVER：此关键字指示 NTILE 操作是在特定的行窗口上执行的。
• ORDER BY：此子句指定根据哪些列对行进行排序。行根据指定的表达式按升序 (ASC) 或降序 (DESC) 排序。

这是一个演示 NTILE 函数用法的示例

在此示例中，查询检索员工姓名、部门和薪水。它使用 NTILE 函数根据薪水降序将结果集划分为 4 个等大小的组。

结果将包括 employee_name、department、salary 和 salary_bucket 列，其中 salary_bucket 表示根据指定桶数分配给每位员工薪水的组号。

NTILE 函数确保每个组中的行数尽可能相等。如果行数不能被指定的桶数整除，则某些组可能多包含一行。

SUM、AVERAGE、MIN、MAX 函数

在 Oracle 中，聚合函数 SUM、AVG、MIN 和 MAX 也可以通过与 OVER 子句结合作为分析函数使用。将这些聚合函数用作分析函数的基本语法如下

计算每个部门内的销售额累计总和

查找每个职位在过去三行中的平均薪水

确定指定范围内的每日最低和最高温度

这些示例展示了 SUM、AVG、MIN 和 MAX 聚合函数如何在 Oracle 中用作分析函数，以便在结果集中的特定窗口或行分区上执行计算。