PostgreSQL 窗口函数 - 程序员自由职业

在 PostgreSQL 中，窗口函数是一种特殊类型的 SQL 函数，它在查询结果集的子集上执行计算，并能够沿着行之间的窗口进行操作。窗口函数通常用于执行对结果集的聚合、排序、排名等操作，而不影响查询结果的行数。以下是一些关于 PostgreSQL 窗口函数的基本概念和使用方法：

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

   SELECT
       column1,
       column2,
       window_function(column3) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN start_offset AND end_offset) AS result
   FROM
       your_table;

   在这个语法中：

   - window_function 是窗口函数的名称，比如 SUM、AVG、ROW_NUMBER 等。
   - PARTITION BY 子句用于定义窗口的分区，即窗口函数将在每个分区内独立计算。
   - ORDER BY 子句用于指定窗口函数计算时的排序规则。
   - ROWS BETWEEN start_offset AND end_offset 子句用于指定计算窗口的行范围。

2. 常见的窗口函数： PostgreSQL 提供了许多内置的窗口函数，包括但不限于：

   - ROW_NUMBER(): 分配唯一的整数给结果集中的每一行，通常用于生成行号。
   - RANK(): 为每一行分配一个排名，相同数值的行将具有相同的排名，但下一个排名将会跳过相同数值的行。
   - DENSE_RANK(): 类似于 RANK()，但没有跳过相同数值的排名，即相同数值的行具有相同的排名。
   - SUM(), AVG(), MIN(), MAX(): 在窗口内计算指定列的总和、平均值、最小值、最大值等。
   - LEAD() 和 LAG(): 分别获取后一行和前一行的值，可用于计算行与行之间的差异。

3. 示例：以下是一个示例，演示如何使用窗口函数计算每个部门中员工的平均工资以及每个员工相对于部门平均工资的差异：

   SELECT
       employee_id,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS salary_diff
   FROM
       employees;

在这个例子中，AVG(salary) OVER (PARTITION BY department_id) 计算了每个部门的平均工资，而 salary - AVG(salary) OVER (PARTITION BY department_id) 计算了每个员工相对于部门平均工资的差异。

窗口函数提供了在查询结果集的特定子集上执行聚合和分析操作的强大工具，使得复杂的分析任务更容易实现。

转载请注明出处：http://www.zyzy.cn/article/detail/8113/PostgreSQL