PostgreSQL 窗口函数
PostgreSQL窗口函数是一种强大的工具，允许你在查询结果集的子集（称为“窗口”）上执行计算。与普通的聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行返回一个值，同时保留原始行的详细信息。这使得窗口函数非常适合用于排名、累计计算、移动平均等场景。
窗口函数的基本语法
窗口函数的基本语法如下：
sql
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[frame_clause]
)
- function_name: 窗口函数的名称，例如
ROW_NUMBER()、RANK()、SUM()等。 - expression: 需要计算的表达式或列。
- PARTITION BY: 将数据分成多个分区，每个分区独立计算。
- ORDER BY: 定义每个分区内的排序方式。
- frame_clause: 定义窗口的框架，即计算的范围。
常见的窗口函数
以下是一些常见的窗口函数及其用途：
- ROW_NUMBER(): 为每一行分配一个唯一的序号。
- RANK(): 为每一行分配一个排名，相同值的行会有相同的排名，后续排名会跳过。
- DENSE_RANK(): 类似于
RANK()，但不会跳过后续排名。 - SUM(): 计算窗口内行的累计和。
- AVG(): 计算窗口内行的平均值。
- LAG(): 返回当前行之前的某一行的值。
- LEAD(): 返回当前行之后的某一行的值。
实际案例
案例1：计算每个部门的员工工资排名
假设我们有一个 employees 表，包含员工的姓名、部门和工资。我们可以使用 RANK() 函数来计算每个部门内员工的工资排名。
sql
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
employees;
输入数据：
| name | department | salary |
|---|---|---|
| Alice | HR | 50000 |
| Bob | HR | 60000 |
| Charlie | IT | 70000 |
| David | IT | 80000 |
| Eve | IT | 70000 |
输出结果：
| name | department | salary | salary_rank |
|---|---|---|---|
| Bob | HR | 60000 | 1 |
| Alice | HR | 50000 | 2 |
| David | IT | 80000 | 1 |
| Charlie | IT | 70000 | 2 |
| Eve | IT | 70000 | 2 |
案例2：计算累计工资
我们可以使用 SUM() 函数来计算每个部门内员工的累计工资。
sql
SELECT
name,
department,
salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS cumulative_salary
FROM
employees;
输出结果：
| name | department | salary | cumulative_salary |
|---|---|---|---|
| Bob | HR | 60000 | 60000 |
| Alice | HR | 50000 | 110000 |
| David | IT | 80000 | 80000 |
| Charlie | IT | 70000 | 150000 |
| Eve | IT | 70000 | 150000 |
总结
PostgreSQL窗口函数是一种强大的工具，可以帮助你在查询结果集的子集上执行复杂的计算。通过使用 PARTITION BY 和 ORDER BY 子句，你可以灵活地定义窗口的范围和排序方式。窗口函数非常适合用于排名、累计计算、移动平均等场景。
提示
如果你想进一步学习窗口函数，可以尝试以下练习：
- 使用
LAG()和LEAD()函数来比较当前行与前一行或后一行的值。 - 使用
AVG()函数计算每个部门内员工的平均工资。 - 使用
ROW_NUMBER()函数为每个部门内的员工分配唯一的序号。
希望这篇内容能帮助你更好地理解PostgreSQL窗口函数的概念和应用！