over的功能是什么
OVER
是 SQL 窗口函数的一个关键字,用于定义一个窗口或帧,窗口函数将在这个窗口或帧上进行计算操作。OVER
关键字与窗口函数一起使用,如 SUM()
, AVG()
, ROW_NUMBER()
, RANK()
, 等等。
功能和用途
OVER
允许您在不缩减结果集的情况下,对结果集进行计算。这与传统的聚合函数(如 SUM()
, COUNT()
)不同,后者会将结果集缩减成较少的行数。
语法
sql
复制代码
<窗口函数> OVER (
[PARTITION BY <partition_col1>, <partition_col2>, ...]
[ORDER BY <order_col1> [ASC|DESC], <order_col2> [ASC|DESC], ...]
[ROWS|RANGE <frame_clause>]
)
子句
-
PARTITION BY:
- 将结果集按一个或多个列分区。
- 每个分区内独立进行窗口函数计算。
- 类似于
GROUP BY
,但不缩减结果集的行数。
sql
复制代码
SUM(salary) OVER (PARTITION BY department_id)
-
ORDER BY:
- 指定分区内的行顺序,有时结合
PARTITION BY
使用。 - 确定窗口函数的计算顺序。
sql
复制代码
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date)
- 指定分区内的行顺序,有时结合
-
ROWS 或 RANGE:
- 定义计算窗口的具体范围或帧。
ROWS
指定确切的行数范围,例如当前行的前两行和当前行。RANGE
基于值范围,例如当前行和与当前行值相等的所有其他行。
sql
复制代码
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
示例和解释
基本示例:ROW_NUMBER()
sql
复制代码
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;
在这个查询中:
PARTITION BY department_id
将数据按部门分组。- 在每个部门内,
ORDER BY salary DESC
按薪水从高到低排序。 ROW_NUMBER()
为每个分区内的每一行分配唯一的行号。
聚合函数示例:SUM()
sql
复制代码
SELECT
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary
FROM employees;
在这个查询中:
PARTITION BY department_id
根据部门进行分区。ORDER BY hire_date
指定部门内的排序规则。SUM(salary) OVER (...)
计算每个部门内按雇佣日期排序的累计工资。
高级示例:移动平均
sql
复制代码
SELECT
order_id,
order_date,
amount,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;
在这个查询中:
ORDER BY order_date
指定按订单日期排序。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
定义窗口帧为当前行及其前两行。AVG(amount) OVER (...)
计算指定窗口内的移动平均值。
总结
OVER
子句使得窗口函数变得非常强大和灵活,它允许在不缩减结果集的情况下进行各种复杂的计算和分析操作。通过使用 PARTITION BY
、ORDER BY
和窗口帧定义,可以对数据进行详细且复杂的分析,适用于诸如累积总和、移动平均、行排名等各种场景。
了解和掌握 OVER
子句的使用,将大大提升您在SQL数据查询和分析中的能力。
请给我举几个例子
当然,下面是一些使用OVER
子句的常见窗口函数的例子。
1. ROW_NUMBER()
示例:为每个部门的员工分配一个唯一的行号。
sql
复制代码
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;
解释:
PARTITION BY department_id
将结果集按部门分区。ORDER BY salary DESC
按薪水降序排列每个部门内的员工。ROW_NUMBER()
为每个分区内的每一行分配唯一的行号。
2. RANK()
示例:为每个部门的员工分配排名,相同薪水的员工会有相同的排名。
sql
复制代码
SELECT
employee_id,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
解释:
PARTITION BY department_id
将结果集按部门分区。ORDER BY salary DESC
按薪水降序排列每个部门内的员工。RANK()
为每个分区内的每一行分配排名,薪水相同的员工会有相同的排名,下一跳会跳过这些相同排名的行数。
3. DENSE_RANK()
示例:为每个部门的员工分配排名,相同薪水的员工会有相同的排名,不会跳跃。
sql
复制代码
SELECT
employee_id,
department_id,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;
解释:
PARTITION BY department_id
将结果集按部门分区。ORDER BY salary DESC
按薪水降序排列每个部门内的员工。DENSE_RANK()
为每个分区内的每一行分配排名,薪水相同的员工会有相同的排名,且不会跳跃。
4. LAG()
示例:获取当前行员工的前一个员工的薪水(按雇佣日期排序)。
sql
复制代码
SELECT
employee_id,
hire_date,
salary,
LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;
解释:
ORDER BY hire_date
按雇佣日期排序。LAG(salary, 1)
获取上一行的薪水。
5. LEAD()
示例:获取当前行员工的下一个员工的薪水(按雇佣日期排序)。
sql
复制代码
SELECT
employee_id,
hire_date,
salary,
LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;
解释:
ORDER BY hire_date
按雇佣日期排序。LEAD(salary, 1)
获取下一行的薪水。
6. 聚合函数作为窗口函数
示例:计算每个部门每个员工的累计薪水。
sql
复制代码
SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary
FROM employees;
解释:
PARTITION BY department_id
将结果集按部门分区。ORDER BY hire_date
按雇佣日期排序。SUM(salary) OVER (...)
计算每个分区内的累计薪水。
7. 移动平均
示例:计算过去三行的移动平均。
sql
复制代码
SELECT
order_id,
order_date,
amount,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;
解释:
ORDER BY order_date
按订单日期排序。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
确定窗口帧为当前行及其前两行。AVG(amount) OVER (...)
计算在窗口帧内的移动平均值。
8. 使用 NTILE()
示例:将结果集按薪水分成4个等分的组,并分配一个组编号。
sql
复制代码
SELECT
employee_id,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS salary_group
FROM employees;
解释:
ORDER BY salary DESC
按薪水降序排列员工。NTILE(4)
将结果集分成四等分,并为每行分配一个组编号。
这些示例展示了使用OVER
子句的各种窗口函数的强大功能和灵活性。通过这些查询,您可以在不缩减结果集的情况下执行复杂的分析和计算。
能不能给我一个带数据表的例子
当然可以。让我们看一个具体的数据表和相关的SQL查询示例,以便更好地理解窗口函数和 OVER
子句的用法。
示例数据表
假设我们有一个表 employees
,其中包含以下数据:
employee_id | department_id | hire_date | salary |
---|---|---|---|
1 | 1 | 2020-01-01 | 5000 |
2 | 1 | 2020-02-01 | 6000 |
3 | 1 | 2020-03-01 | 5500 |
4 | 2 | 2020-01-15 | 8000 |
5 | 2 | 2020-02-20 | 7000 |
6 | 2 | 2020-03-10 | 7200 |
示例1:ROW_NUMBER()
目的:为每个部门的员工分配一个唯一的行号。
sql
复制代码
SELECT
employee_id,
department_id,
hire_date,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS row_num
FROM employees;
结果:
employee_id | department_id | hire_date | salary | row_num |
---|---|---|---|---|
1 | 1 | 2020-01-01 | 5000 | 1 |
2 | 1 | 2020-02-01 | 6000 | 2 |
3 | 1 | 2020-03-01 | 5500 | 3 |
4 | 2 | 2020-01-15 | 8000 | 1 |
5 | 2 | 2020-02-20 | 7000 | 2 |
6 | 2 | 2020-03-10 | 7200 | 3 |
示例2:RANK()
目的:在每个部门中,根据薪水为员工分配排名,相同薪水的员工会有相同的排名。
sql
复制代码
SELECT
employee_id,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
结果:
employee_id | department_id | salary | rank |
---|---|---|---|
2 | 1 | 6000 | 1 |
3 | 1 | 5500 | 2 |
1 | 1 | 5000 | 3 |
4 | 2 | 8000 | 1 |
6 | 2 | 7200 | 2 |
5 | 2 | 7000 | 3 |
示例3:LAG()
目的:获取当前行员工的前一个员工的薪水(按雇佣日期排序)。
sql
复制代码
SELECT
employee_id,
hire_date,
salary,
LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;
结果:
employee_id | hire_date | salary | prev_salary |
---|---|---|---|
1 | 2020-01-01 | 5000 | NULL |
4 | 2020-01-15 | 8000 | 5000 |
2 | 2020-02-01 | 6000 | 8000 |
5 | 2020-02-20 | 7000 | 6000 |
3 | 2020-03-01 | 5500 | 7000 |
6 | 2020-03-10 | 7200 | 5500 |
示例4:SUM() OVER (PARTITION BY ...)
目的:计算每个部门每个员工的累计薪水。
sql
复制代码
SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary
FROM employees;
结果:
employee_id | department_id | salary | cumulative_salary |
---|---|---|---|
1 | 1 | 5000 | 5000 |
2 | 1 | 6000 | 11000 |
3 | 1 | 5500 | 16500 |
4 | 2 | 8000 | 8000 |
5 | 2 | 7000 | 15000 |
6 | 2 | 7200 | 22200 |
示例5:移动平均
目的:计算过去两行的移动平均薪水。
sql
复制代码
SELECT
employee_id,
hire_date,
salary,
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;
结果:
employee_id | hire_date | salary | moving_avg |
---|---|---|---|
1 | 2020-01-01 | 5000 | 5000.00 |
4 | 2020-01-15 | 8000 | 6500.00 |
2 | 2020-02-01 | 6000 | 6333.33 |
5 | 2020-02-20 | 7000 | 7000.00 |
3 | 2020-03-01 | 5500 | 6166.67 |
6 | 2020-03-10 | 7200 | 6566.67 |
这些示例展示了如何使用窗口函数来执行复杂的分析和计算。通过这些查询,可以在不缩减结果集的情况下,对数据进行详细的分析。