PostgreSQL 学习笔记 - 窗口函数

简介

PG 窗口函数是 PostgreSQL 中一种强大的数据分析工具，它允许用户对查询结果进行分组、排序、排名、聚合等操作，并支持高级分析函数，比如 LEAD、LAG、FIRST_VALUE、LAST_VALUE 等。窗口函数基于某个指定的窗口集合进行操作，它不会改变原始查询结果，而是对结果集中的特定行应用计算和操作，可以帮助用户更方便地获取所需的数据信息。

窗口函数的语法格式

PG 窗口函数的语法格式如下：

aggregate_function(expression) OVER (
  [PARTITION BY partition_expression, ... ]
  [ORDER BY sort_expression [ASC|DESC], ... ]
  [frame_clause]
)

其中，aggregate_function 是窗口函数名称，expression 是需要计算的表达式，PARTITION BY 表示分组字段，ORDER BY 表示排序字段，ASC 和 DESC 分别表示升序和降序，frame_clause 表示窗口函数的窗口集合。窗口函数支持多个分组字段和排序字段，以及多种窗口集合类型，包括 ROWS, RANGE和GROUPS 等。

窗口函数的应用场景

分组计算

窗口函数可以对查询结果进行分组计算，计算每个分组内的聚合值，并将聚合值添加到每一行的结果集中。

SELECT
  department_id,
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary,
  MAX(salary) OVER (PARTITION BY department_id) AS max_salary
FROM
  employees
ORDER BY
  department_id, employee_id;

排名

窗口函数可以对查询结果进行排名，根据指定的排序字段对每一行进行排名，并将排名结果添加到每一行的结果集中。

SELECT
  product_name,
  sales_amount,
  DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM
  sales
GROUP BY
  product_name, sales_amount
ORDER BY
  rank;

分析和对比

窗口函数可以对查询结果进行分析和对比，计算每一行与分组内平均值、前一行、后一行等的差异或变化情况，并将分析结果添加到每一行的结果集中。

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

窗口统计

窗口函数可以对查询结果进行窗口统计，计算每一行所在窗口内的聚合值，并将聚合值添加到每一行的结果集中。

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

窗口函数的常用类型

聚合函数

聚合函数是窗口函数中最常用的类型之一，它可以对分组内的行进行聚合计算，包括 SUM, AVG, MAX, MIN, COUNT 等。聚合函数可以在 PARTITION BY 子句中指定分组字段，也可以在 ORDER BY 子句中指定排序字段。

SELECT
  department_id,
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary,
  MAX(salary) OVER (PARTITION BY department_id) AS max_salary
FROM
  employees
ORDER BY
  department_id, employee_id;

排名函数

排名函数可以对查询结果进行排名，根据指定的排序字段对每一行进行排名，并将排名结果添加到每一行的结果集中。常见的排名函数包括 RANK, DENSE_RANK, ROW_NUMBER 等。

SELECT
  product_name,
  sales_amount,
  DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM
  sales
GROUP BY
  product_name, sales_amount
ORDER BY
  rank;

窗口函数

窗口函数可以对查询结果进行窗口统计，计算每一行所在窗口内的聚合值，并将聚合值添加到每一行的结果集中。常见的窗口函数包括 LEAD, LAG, FIRST_VALUE, LAST_VALUE 等。

SELECT
  employee_id,
  department_id,
  salary,
  LAG(salary) OVER (
    PARTITION BY department_id
    ORDER BY employee_id
  ) AS prev_salary,
  LEAD(salary) OVER (
    PARTITION BY department_id
    ORDER BY employee_id
  ) AS next_salary
FROM
  employees;

窗口函数的窗口集合类型

ROWS 类型

ROWS 类型是窗口函数的默认窗口集合类型，它表示基于行的窗口集合，窗口的大小和位置由 ROWS BETWEEN 子句指定。ROWS BETWEEN 子句可以使用 UNBOUNDED PRECEDING, CURRENT ROW 和 UNBOUNDED FOLLOWING 等关键字来指定窗口的大小和位置。

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

RANGE 类型

RANGE 类型表示基于值的窗口集合，窗口的大小和位置由 RANGE BETWEEN 子句指定。RANGE BETWEEN 子句可以使用 UNBOUNDED PRECEDING, CURRENT ROW 和 UNBOUNDED FOLLOWING 等关键字来指定窗口的大小和位置。需要注意的是，RANGE 类型的窗口集合在处理重复值时可能会产生不同的结果，因为窗口的大小和位置是根据值而不是行来确定的。

SELECT
  employee_id,
  department_id,
  salary,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY salary
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cum_sum
FROM
  employees;

窗口函数的应用场景

窗口函数可以用于多种场景，包括分组统计、排名、分组排名、前 N 排名、滑动平均、累计求和等。

分组统计

窗口函数可以在 PARTITION BY 子句中指定分组字段，实现对每个分组内的行进行统计计算。

SELECT
  department_id,
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary,
  MAX(salary) OVER (PARTITION BY department_id) AS max_salary
FROM
  employees
ORDER BY
  department_id, employee_id;

排名

窗口函数可以使用排名函数实现对查询结果进行排名，并将排名结果添加到每一行的结果集中。

SELECT
  product_name,
  sales_amount,
  DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM
  sales
GROUP BY
  product_name, sales_amount
ORDER BY
  rank;

分组排名

窗口函数可以结合分组字段和排名函数实现对每个分组内的行进行排名计算。

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

前 N 排名

窗口函数可以使用排名函数实现对查询结果的前 N 行进行筛选。

SELECT
  product_name,
  sales_amount,
  DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM
  sales
WHERE
  DENSE_RANK() OVER (ORDER BY sales_amount DESC) <= 10
ORDER BY
  rank;

滑动平均

窗口函数可以结合 ROWS 类型的窗口集合和聚合函数实现对每一行所在窗口内的平均值计算。

SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  ) AS moving_avg
FROM
  data;

累计求和

窗口函数可以使用聚合函数和 ROWS 类型的窗口集合实现对每一行之前的所有行的求和计算。

SELECT
  date,
  value,
  SUM(value) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cum_sum
FROM
  data;

总结

窗口函数可以实现对查询结果进行高级计算，包括分组统计、排名、分组排名、前 N 排名、滑动平均、累计求和等。通过灵活使用窗口函数，可以提高查询结果的精度和效率，减少查询复杂度和计算量。

需要注意的是，在使用窗口函数时需要谨慎处理重复值和窗口大小的问题，以免产生不同的计算结果。此外，窗口函数的使用场景需要根据实际业务需求和数据结构来确定，不应滥用窗口函数。