【oracle】窗口函数（分析函数）详解

叶阿猪

已于 2024-06-17 18:06:20 修改

阅读量854

点赞数 15

分类专栏：数据库文章标签： oracle 数据库

于 2024-06-17 18:06:00 首次发布

本文链接：https://blog.csdn.net/MAOZI8/article/details/139750948

版权

9 篇文章 0 订阅

订阅专栏

在数据库查询中，我们经常会遇到需要对数据集进行复杂分析的场景，而不仅仅是简单的聚合或排序。Oracle窗口函数（也称为分析函数）正是为了满足这些需求而设计的。本文将详细介绍Oracle窗口函数的概念、特点、分类和使用方法。

Oracle窗口函数允许用户对查询结果的每一行执行计算，而不会改变原始查询结果的行数或顺序。这些函数特别适用于需要跨多行或分区（子集）的数据执行聚合操作（如求和、平均值等）的场景。

无需分组：与普通的SQL聚合函数（如SUM、AVG）不同，窗口函数不需要使用GROUP BY子句来分组数据。它们可以直接在SELECT子句中使用，为结果集中的每一行返回一个值。
跨行操作：窗口函数可以对当前行以及与其相关的其他行（如前面的行、后面的行或分区内的所有行）进行操作。
与ORDER BY和PARTITION BY结合使用：窗口函数通常与OVER子句一起使用，其中可以包含PARTITION BY和ORDER BY子句来定义窗口的分区和排序顺序。

窗口函数的基本语法通常包含OVER()子句，用于定义窗口的范围和排序方式。

<窗口函数> OVER (
    [PARTITION BY <列名,...>] -- 定义窗口的分区
    [ORDER BY <列名,...>]     -- 定义窗口内的排序顺序
    [ROWS BETWEEN <范围>]     -- 定义窗口内的行范围（可选）
)

聚合窗口函数
- SUM(column) OVER(...): 计算指定列的总和。
- AVG(column) OVER(...): 计算指定列的平均值。
- COUNT(column) OVER(...): 计算指定分区中的行数。
- MIN(column) OVER(...): 返回指定分区中的最小值。
- MAX(column) OVER(...): 返回指定分区中的最大值。
分析窗口函数
- ROW_NUMBER() OVER(...): 为结果集中的每一行分配一个唯一的序号。
- RANK() OVER(...): 为结果集中的每一行分配一个排名，相同值的行具有相同的排名。
- DENSE_RANK() OVER(...): 类似于RANK()，但排名是连续的，没有间隔。
- NTILE(n) OVER(...): 将结果集分成指定数量的组，并为每组分配一个编号。
- LEAD(column) OVER(...): 获取当前行的下一行的值。
- LAG(column) OVER(...): 获取当前行的上一行的值。

假设我们有一个名为employees的表，其中包含员工的薪资信息。我们可以使用窗口函数来计算每个员工的累计薪资。

SELECT
    dept_id,
    emp_id,
    salary,
    SUM(salary) OVER (PARTITION BY dept_id ORDER BY emp_id) AS cumulative_salary
FROM
    employees;

在这个示例中，SUM窗口函数计算了每个部门内员工的累计薪资，并且按照员工ID（emp_id）进行排序。PARTITION BY子句指定了按部门（dept_id）进行分区，这样每个部门的累计薪资都是独立计算的。

我们还可以使用RANK窗口函数为员工薪资分配排名。

SELECT
    emp_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

在这个示例中，RANK窗口函数按照薪资从高到低为员工分配排名。

Oracle窗口函数为数据分析提供了强大的工具，允许用户在不改变数据原始分组的情况下执行复杂的计算。通过结合不同的窗口函数和OVER子句中的选项，用户可以灵活地定义窗口的范围和排序方式，以满足各种分析需求。

关注