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

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

一、Oracle窗口函数概述

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

二、Oracle窗口函数的特点

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

三、窗口函数的基本语法

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

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

• PARTITION BY：用于定义窗口的分区。窗口函数将分别对每个分区执行计算。
• ORDER BY：用于定义窗口内行的排序顺序。
• ROWS/RANGE子句（可选）：用于定义窗口的大小，即包含哪些行进行聚合计算。

四、Oracle窗口函数的分类

1. 聚合窗口函数

   • SUM(column) OVER(...): 计算指定列的总和。
   • AVG(column) OVER(...): 计算指定列的平均值。
   • COUNT(column) OVER(...): 计算指定分区中的行数。
   • MIN(column) OVER(...): 返回指定分区中的最小值。
   • MAX(column) OVER(...): 返回指定分区中的最大值。

2. 分析窗口函数

   • ROW_NUMBER() OVER(...): 为结果集中的每一行分配一个唯一的序号。
   • RANK() OVER(...): 为结果集中的每一行分配一个排名，相同值的行具有相同的排名。
   • DENSE_RANK() OVER(...): 类似于RANK()，但排名是连续的，没有间隔。
   • NTILE(n) OVER(...): 将结果集分成指定数量的组，并为每组分配一个编号。
   • LEAD(column) OVER(...): 获取当前行的下一行的值。
   • LAG(column) OVER(...): 获取当前行的上一行的值。

五、Oracle窗口函数的使用示例

示例1：计算每个部门员工的累计薪资

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

SELECT
    dept_id,
    emp_id,
    salary,
    SUM(salary) OVER (PARTITION BY dept_id ORDER BY emp_id) AS cumulative_salary
FROM
    employees;
1
2
3
4
5
6
7

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

示例2：为员工分配排名

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

SELECT
    emp_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;
1
2
3
4
5
6

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

六、注意事项

• 当在SQL语句中使用窗口函数时，请确保正确指定了PARTITION BY和ORDER BY子句，以确保得到预期的结果。
• 窗口函数原则上只能写在SELECT子句中。

七、总结

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