使用 PostgreSQL 窗口函数进行数据处理

您可以在 PostgreSQL 或 TimescaleDB 中使用窗口函数对与当前行相关的行集（称为“窗口”）执行复杂计算。

窗口函数或分析函数使用数据库表中一行或多行的值来执行计算并返回值。

窗口函数与聚合函数的不同之处在于，行不会分组到单个输出中。在窗口函数中，每一行都可以保持独立，但函数不仅可以访问当前行中的数据。

窗口函数总是在查询后直接使用 OVER 子句。这个子句是窗口函数与普通函数的不同之处。OVER 子句通过确定查询中的多少行被分割到每个计算中来创建数据行中的窗口框架。当您使用窗口函数时，该行的值是根据与当前行位于同一分区中的所有行计算的。

您可以将窗口函数与 PARTITION BY 和 ORDER BY 一起使用。PARTITION BY 定义记录必须匹配才能成为窗口框架的一部分的条件。ORDER BY 确定记录的顺序。

OVER、PARTITION BY 和 ORDER BY 语法

OVER ([PARTITION BY <columns>] [ORDER BY <columns>])

ROWS BETWEEN 用于指定相对于当前行的窗口框架。

ROWS BETWEEN 语法

OVER ([PARTITION BY <columns>] [ORDER BY <columns>] [ROWS BETWEEN <lower_bound> AND <upper_bound>])

ROWS BETWEEN 中的边界可以是以下五种情况中的任何一种

• UNBOUNDED PRECEDING：当前行之前的所有行。

• n PRECEDING：当前行之前的 n 行。

• CURRENT ROW：仅当前行。

• n FOLLOWING：当前行之后的 n 行。

• UNBOUNDED FOLLOWING：当前行之后的所有行。

使用 WINDOW 创建一个窗口子句，将窗口函数与 SELECT 子句分开。

WINDOW 语法

OVER w FROM WINDOW w AS ([PARTITION BY <columns>] [ORDER BY <columns>] [ROWS BETWEEN <lower_bound> AND <upper_bound>])

• 对结果集中的所有行使用窗口函数

• 对窗口框架中的记录进行排序

• 对窗口框架中的记录进行分区

• 对窗口框架中的记录进行排序和分区

• 使用窗口子句

• 在窗口子句中使用 ROWS BETWEEN

这些示例使用名为 sales_data 的数据库表中的销售数据，如下所示

对结果集中的所有行使用窗口函数

如果在使用 OVER 时没有定义 PARTITION BY、ORDER BY 或 ROWS 子句，则计算将针对包含记录集中所有行的窗口执行。以下是一个获取销售摘要的示例查询

SELECT branch, SUM(total) OVER() AS sum FROM sales_data;

结果

sum 列中的金额是表中所有值的总和。

对窗口框架中的记录进行排序

如果将 ORDER BY 子句与 OVER 结合使用，则会针对当前行和结果集中所有先前行执行聚合。这是因为，默认情况下，窗口框架使用 UNBOUNDED PROCEEDING 进行聚合。

此示例查询还获取销售摘要，但它按时间列对结果进行排序

SELECT branch, SUM(total) OVER(ORDER BY id) AS sum FROM sales_data;

结果

sum 中的金额是销售额的累积总计。

如果按包含重复值的列对结果进行排序，则结果会有所不同。例如

SELECT branch, SUM(total) OVER(ORDER BY sale_time) AS sum FROM sales_data;

结果

聚合总和仍然是累积总计，但与上一个示例不同。这是因为窗口包括所有先前行，还包括销售时间匹配的行。

对窗口框架中的记录进行分区

PARTITION BY 在窗口框架中的作用类似于 GROUP BY。它根据您设置的条件对所有结果进行分组。此示例使用 GROUP BY 获取数据中每个分支的销售总和

SELECT branch, SUM(total) AS sum FROM sales_data sd GROUP BY branch;

结果

此示例在窗口框架上使用 PARTITION BY

SELECT id, branch, SUM(total) OVER(PARTITION BY branch) AS sum FROM sales_data;

结果

两个示例中的总和相同，但第二个示例不需要对它们进行分组。

对窗口框架中的记录进行排序和分区

在 OVER 中同时使用 ORDER BY 和 PARTITION BY 时，可以指定应用窗口函数的每个分区中结果的顺序。此示例检索数据集中按位置划分的销售额累积总计

SELECT sale_time, branch, SUM(total) OVER(PARTITION BY branch ORDER BY sale_time) AS sum FROM sales_data;

结果

使用窗口子句

如果不想使用内联窗口函数，可以将其转换为窗口子句。以下是使用窗口子句重写的上一个示例查询。它以两种格式返回相同的结果。如果要在查询中使用多个窗口函数，这将非常有用

SELECT sale_time, branch, SUM(total) OVER w AS sum 
FROM sales_data WINDOW w AS (PARTITION BY branch ORDER BY sale_time);

在窗口子句中使用 ROWS BETWEEN

这些示例使用包含来自几个城市五天的降水量和温度数据的数据集。此数据位于名为 city_data 的表中

在窗口子句中使用 ROWS BETWEEN 时，ORDER BY 子句的工作方式略有不同。

在窗口框架中使用 ORDER BY 时，默认框架为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。但是，如果不使用 ORDER BY，则默认框架为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

重要的是要考虑如何在窗口框架中使用 ORDER BY 子句，尤其是在同时使用 ROWS 子句时。

例如，如果要计算每个城市温度的三天移动平均值，可以使用以下查询

SELECT city, date, temperature,
    AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_3day
FROM city_data
ORDER BY city, date;

要获取每个城市温度的三天移动平均值，请首先按城市对窗口框架进行分区。然后，必须对每个城市分区中的日期进行排序，以便可以根据当前行的位置选择三天的行集。然后，可以按降序对日期进行排序，并使用当前行和接下来的两行来计算平均温度

结果

因为 ROWS 子句依赖于窗口框架中的 ORDER BY 子句，所以可以通过在窗口框架中按升序对日期进行排序，并使用当前行加上前两行来计算平均值来获得相同的结果，如下所示

SELECT city, date, temperature,
    AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) avg_3day
FROM city_data
ORDER BY city, date;

更多 PostgreSQL 窗口函数

CUME_DIST

CUME_DIST() 计算一组值中值的累积分布。此函数在统计分析中特别有用。

SELECT salesperson_id, COUNT(*), CUME_DIST() OVER (ORDER BY COUNT(*) DESC)
FROM sales
GROUP BY salesperson_id;

DENSE_RANK

DENSE_RANK() 为窗口分区中的每一行分配一个排名，排名值之间没有间隙。

SELECT salesperson_id, COUNT(*), DENSE_RANK() OVER (ORDER BY COUNT(*) DESC)
FROM sales
GROUP BY salesperson_id;

FIRST_VALUE

FIRST_VALUE() 返回有序值集中的第一个值。

SELECT product_name, sales, FIRST_VALUE(product_name) OVER (ORDER BY sales DESC)
FROM product_sales;

LAG

LAG() 从同一结果集中的上一行获取值。

SELECT product_name, sales, LAG(sales) OVER (ORDER BY sales)
FROM product_sales;

LAST_VALUE

LAST_VALUE() 返回有序值集中的最后一个值。

SELECT product_name, sales, LAST_VALUE(product_name) OVER (ORDER BY sales DESC)
FROM product_sales;

LEAD

LEAD() 从同一结果集中的下一行获取值。

SELECT product_name, sales, LEAD(sales) OVER (ORDER BY sales)
FROM product_sales;

NTILE

NTILE(n) 将有序结果集分成 n 个大致相等的组。

SELECT product_name, sales, NTILE(4) OVER (ORDER BY sales)
FROM product_sales;

NTH_VALUE

NTH_VALUE(n) 返回窗口框架第一行起的第 n 行的值。

SELECT product_name, sales, NTH_VALUE(product_name, 2) OVER (ORDER BY sales DESC)
FROM product_sales;

PERCENT_RANK

PERCENT_RANK() 计算一组值中值的百分比排名。

SELECT salesperson_id, COUNT(*), PERCENT_RANK() OVER (ORDER BY COUNT(*) DESC)
FROM sales
GROUP BY salesperson_id;

RANK

RANK() 为窗口分区中的每个不同行提供唯一排名。

SELECT salesperson_id, COUNT(*), RANK() OVER (ORDER BY COUNT(*) DESC)
FROM sales
GROUP BY salesperson_id;

ROW_NUMBER

ROW_NUMBER() 为窗口分区中的每一行分配唯一的行号。

SELECT salesperson_id, COUNT(*), ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC)
FROM sales
GROUP BY salesperson_id;

延伸阅读

有关窗口函数以及如何在 PostgreSQL 中使用它们的更多信息，请参阅 PostgreSQL 文档。要了解有关 PostgreSQL 中如何处理窗口函数的更多信息，请参阅 PostgreSQL 文档的此部分。有关窗口函数语法的更多详细信息，请参阅 此部分。有关如何在查询中使用窗口函数的更多示例，请查看以下 Timescale 文档部分

• 高级分析查询

• 创建连续聚合