跳到主要内容

PostgreSQL 窗口函数

介绍​

在PostgreSQL中，窗口函数（Window Functions）是一种强大的工具，允许你在查询结果集的某个“窗口”内执行计算。与普通的聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行返回一个值。这使得窗口函数非常适合用于排名、累积计算、移动平均等场景。

窗口函数通常与 OVER 子句一起使用，OVER 子句定义了窗口的范围和排序方式。

基本语法​

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

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: 定义窗口的框架范围，例如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

常用窗口函数​

以下是一些常用的窗口函数：

  1. ROW_NUMBER(): 为每一行分配一个唯一的行号。
  2. RANK(): 为每一行分配一个排名，相同值的行会有相同的排名，后续排名会跳过。
  3. DENSE_RANK(): 类似于 RANK()，但不会跳过后续排名。
  4. SUM(): 计算窗口内行的累积和。
  5. AVG(): 计算窗口内行的平均值。
  6. LEAD(): 获取当前行之后的某一行的值。
  7. LAG(): 获取当前行之前的某一行的值。

示例​

示例1：使用 ROW_NUMBER() 分配行号​

假设我们有一个 sales 表，包含以下数据：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    salesperson VARCHAR(50),
    amount NUMERIC
);

INSERT INTO sales (salesperson, amount) VALUES
('Alice', 100),
('Bob', 200),
('Alice', 150),
('Bob', 300),
('Alice', 200);

我们可以使用 ROW_NUMBER() 为每个销售人员的销售记录分配行号：

SELECT
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount) AS row_num
FROM
    sales;

输出结果：

salespersonamountrow_num
Alice1001
Alice1502
Alice2003
Bob2001
Bob3002

示例2：使用 SUM() 计算累积和​

我们可以使用 SUM() 函数计算每个销售人员的累积销售额：

SELECT
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY amount) AS cumulative_sum
FROM
    sales;

输出结果：

salespersonamountcumulative_sum
Alice100100
Alice150250
Alice200450
Bob200200
Bob300500

示例3：使用 RANK() 和 DENSE_RANK() 进行排名​

我们可以使用 RANK() 和 DENSE_RANK() 对销售人员的销售额进行排名：

SELECT
    salesperson,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM
    sales;

输出结果：

salespersonamountrankdense_rank
Bob30011
Alice20022
Bob20022
Alice15043
Alice10054

实际应用场景​

场景1：计算移动平均​

假设我们有一个 stock_prices 表，记录某只股票的每日收盘价。我们可以使用窗口函数计算该股票的5日移动平均价：

SELECT
    date,
    close_price,
    AVG(close_price) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
    stock_prices;

场景2：查找每个部门的最高薪员工​

假设我们有一个 employees 表，包含员工的姓名、部门和薪水。我们可以使用窗口函数查找每个部门薪水最高的员工：

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM
    employees
WHERE
    rank = 1;

总结​

PostgreSQL窗口函数是一种强大的工具，可以帮助你在查询结果集的某个窗口内执行复杂的计算。通过使用 OVER 子句，你可以定义窗口的范围和排序方式，从而实现排名、累积计算、移动平均等功能。

附加资源与练习​

  • 练习1: 使用 LEAD() 和 LAG() 函数，计算每个销售人员的销售额与前一天的差异。
  • 练习2: 使用 NTILE() 函数，将销售人员的销售额分成4个等级。
提示

如果你对窗口函数的使用还有疑问，可以查阅PostgreSQL官方文档，或者尝试在实际项目中应用这些函数，以加深理解。