窗口函数

窗口函数是 PostgreSQL 中的一个强大特性，允许开发者在查询结果集的“窗口”内进行操作。与聚合函数不同，窗口函数不对结果进行分组，而是在整个查询的结果集或特定分区内进行计算。常用于排名、累计和分析等任务。

窗口函数的基本语法

窗口函数的基本语法如下：

SELECT column1, column2, window_function() OVER (PARTITION BY column ORDER BY column)
FROM table_name;

• window_function() 是要应用的窗口函数。
• PARTITION BY 用于将数据分区，不同分区的数据会分别进行窗口函数计算。
• ORDER BY 用于定义窗口内的排序顺序。

常见的窗口函数

排名函数

排名函数用于在分区内为每一行分配一个排名。常见的排名函数包括：

• ROW_NUMBER()：为结果集中的每一行分配一个唯一的排名。
• RANK()：为结果集中的每一行分配一个排名，但若有重复的值，则排名会跳过。
• DENSE_RANK()：与 RANK() 类似，但排名不会跳过。

示例：使用 ROW_NUMBER()

SELECT name, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

示例：使用 RANK()

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

示例：使用 DENSE_RANK()

SELECT name, salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

聚合函数

窗口聚合函数用于在窗口内进行聚合计算，不会将结果分组。常见的聚合函数包括：

• SUM()：计算窗口内的总和。
• AVG()：计算窗口内的平均值。
• COUNT()：计算窗口内的行数。
• MIN()、MAX()：计算窗口内的最小值和最大值。

示例：使用 SUM()

SELECT name, salary, 
       SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM employees;

窗口函数的帧规范

窗口函数可以定义不同的帧规范来控制计算范围，主要的帧规范包括：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从当前行的前面所有行到当前行。
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到所有后续行。
• ROWS BETWEEN x PRECEDING AND x FOLLOWING：指定前后多少行作为窗口。

示例：使用 SUM() 和帧规范

SELECT name, salary, 
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

窗口函数与排序

排序在窗口函数中非常重要，它决定了数据如何被窗口函数处理。通过 ORDER BY 子句来指定排序规则。

示例：使用 ORDER BY 排序

SELECT name, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

复杂查询中的窗口函数应用

示例：计算部门内员工的平均工资

SELECT name, department_id, salary, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

示例：计算每个员工的工资在其部门中的百分位

SELECT name, department_id, salary, 
       PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS salary_percentile
FROM employees;

窗口函数与分区

PARTITION BY 用于将数据划分为不同的“分区”，窗口函数将在每个分区内单独计算。分区可以基于一个或多个列来划分数据。

示例：按部门分区，计算每个员工的工资排名

SELECT name, department_id, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS department_rank
FROM employees;

窗口函数与联接

窗口函数也可以与联接一起使用，可以在联接后的结果集中进行计算。

示例：计算每个员工的薪资和部门内平均薪资差异

SELECT e.name, e.department_id, e.salary, 
       AVG(e.salary) OVER (PARTITION BY e.department_id) AS avg_salary_in_department,
       e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id) AS salary_difference
FROM employees e;

小结

窗口函数是 PostgreSQL 中强大而灵活的功能，能够帮助开发者在查询中进行更为复杂的计算和分析。通过使用窗口函数，可以轻松地实现排名、聚合、累计等操作，而无需改变数据的结构或进行多次查询。掌握窗口函数的使用，可以极大地提升数据处理的效率与灵活性。