Oracle 中的 OVER()
函数及其数据窗口
在 Oracle 数据库中,OVER()
函数提供了一种强大的方式来进行分析计算,它允许对每行数据指定一个“数据窗口”,并在该窗口内计算聚合结果。这个“数据窗口”是一个动态的子集,会随着每一行数据的变化而变化。通过 OVER()
函数,你可以在不影响查询的情况下,使用多种窗口函数如 SUM()
、AVG()
、ROW_NUMBER()
等来分析数据。
1. OVER()
函数基础
1.1 什么是 OVER()
函数
OVER()
函数是用于定义数据窗口的一个子句,它可以与多种分析函数一起使用,例如:
- 聚合函数:
SUM()
、AVG()
、COUNT()
、MIN()
、MAX()
等 - 排名函数:
ROW_NUMBER()
、RANK()
、DENSE_RANK()
、NTILE()
等 - 位移函数:
LAG()
、LEAD()
1.2 OVER()
函数的基本语法
FUNCTION_NAME(<参数>) OVER (
[PARTITION BY <列名>]
[ORDER BY <列名> ASC|DESC]
[ROWS|RANGE BETWEEN <value> PRECEDING AND <value> FOLLOWING]
)
- FUNCTION_NAME:分析函数,如
SUM()
、AVG()
等。 - PARTITION BY:用于将数据分成多个分区,每个分区独立进行计算。
- ORDER BY:定义在每个分区内的排序方式,通常用于排名函数。
- ROWS 和 RANGE:指定数据窗口的范围。
2. PARTITION BY
和 ORDER BY
示例
有一个名为 employees
的表,包含以下数据:
CREATE TABLE employees (
department_id NUMBER,
employee_id NUMBER,
salary NUMBER
);
INSERT INTO employees VALUES (10, 1, 5000);
INSERT INTO employees VALUES (10, 2, 6000);
INSERT INTO employees VALUES (20, 3, 7000);
INSERT INTO employees VALUES (20, 4, 8000);
INSERT INTO employees VALUES (20, 5, 9000);
示例 1:按部门计算每个员工的工资排名
SELECT department_id, employee_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
- 数据根据
department_id
分区,即每个部门的数据独立计算。 - 在每个分区内,根据
salary
降序排序。 RANK()
函数返回每个员工的工资排名。- 输出结果:
DEPARTMENT_ID EMPLOYEE_ID SALARY SALARY_RANK ------------ ----------- ------- ----------- 10 2 6000 1 10 1 5000 2 20 5 9000 1 20 4 8000 2 20 3 7000 3
3. 使用 ROWS
和 RANGE
的 OVER()
函数
ROWS
和 RANGE
子句用于定义数据窗口的范围。它们的区别在于:
- ROWS:基于物理行的数目来定义窗口范围。
- RANGE:基于逻辑值的范围来定义窗口。
示例 2:计算每行数据的前后两行的和(ROWS
)
SELECT salary,
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_salary
FROM employees;
- 对每个
salary
计算其前一行、当前行和后一行的salary
之和。 - 输出结果:
SALARY SUM_SALARY ------- ---------- 5000 11000 6000 18000 7000 21000 8000 24000 9000 17000
示例 3:计算每行数据的前后相差最多1000的行的和(RANGE
)
SELECT salary,
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS sum_salary
FROM employees;
- 对每个
salary
计算在其上下相差不超过1000的行的salary
之和。 - 输出结果:
SALARY SUM_SALARY ------- ---------- 5000 11000 6000 11000 7000 15000 8000 15000 9000 17000
4. 更复杂的 OVER()
函数示例
示例 4:复杂的开窗操作
有一个更复杂的数据表 sales_data
,包含销售数据,并希望计算每个销售额在前50个和后150个记录范围内的总和。
创建并插入数据:
CREATE TABLE sales_data (
sales_id NUMBER,
sales_value NUMBER
);
-- 插入假数据
INSERT INTO sales_data VALUES (1, 100);
INSERT INTO sales_data VALUES (2, 200);
INSERT INTO sales_data VALUES (3, 300);
-- (继续插入数据)
执行 SQL 查询:
SELECT sales_id, sales_value,
SUM(sales_value) OVER (ORDER BY sales_value RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS sum_sales
FROM sales_data;
- 对每一行数据,计算其销售额在前50个和后150个范围内的总和。
- 这将生成一个窗口,该窗口随着每行数据的变化而调整。
OVER()
函数通过定义数据窗口提供了一种灵活的方式进行高级数据分析。在使用时:
PARTITION BY
可将数据分组。ORDER BY
用于排序数据。ROWS
和RANGE
子句决定了窗口的大小和形态。
这些功能组合在一起,为数据分析提供了极大的灵活性和控制力。
非常感谢您读到这里!如果您觉得这篇文章对您有帮助,可以关注一下博主。关注后,您将第一时间获得最新的AI、云计算、运维(Linux、数据库,容器等)技术,以及更多实用的技能干货。
让AI工具成为你的得力助手,感受AI工具的无限可能,让复杂的任务变得简单,让你的工作更加轻松和高效。