一、窗口函数介绍

PostgreSQL 窗口函数是一组特殊的函数，它们可以在一组相关的行上执行计算，这组行被称为“窗口”。与传统的聚合函数（如 SUM()、AVG()）不同，窗口函数不会将多行数据聚合成一个单一的结果，而是为每一行都返回一个结果。这使得窗口函数特别适合于那些需要同时考虑聚合数据和详细信息的应用场景。

窗口函数通常与 OVER() 子句一起使用，用来定义窗口的规则。OVER() 子句可以包含 PARTITION BY，用于将数据分为不同的组（分区），以及 ORDER B`，用于确定窗口内行的顺序。

这里我们创建一个测试数据表，用于演示窗口函数的使用。这个表将模拟一个简单的销售数据集，包含销售员、销售月份和销售额等信息。

CREATE TABLE sales_data (    salesperson TEXT,    sale_month DATE,    amount INTEGER);INSERT INTO sales_data VALUES('Alice', '2023-01-01', 100),('Alice', '2023-02-01', 150),('Alice', '2023-03-01', 200),('Bob', '2023-01-01', 90),('Bob', '2023-02-01', 180),('Bob', '2023-03-01', 180),('Charlie', '2023-01-01', 110),('Charlie', '2023-02-01', 130),('Charlie', '2023-03-01', 210);

这个表包含三个字段：salesperson（销售员）、sale_month（销售月份）和amount（销售额）。以下窗口函数相关示例将使用此表。

二、常用窗口函数

PostgreSQL 中常用的窗口函数包括以下几种：

1. 聚合窗口函数：

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

2. 排名窗口函数：

- ROW_NUMBER()：为窗口内的每一行分配一个唯一的连续整数。
- RANK()：为窗口内的行分配一个排名，排名相等的行会分配相同的排名，并且排名之间会跳号。
- DENSE_RANK()：为窗口内的行分配一个排名，排名相等的行会分配相同的排名，并且排名之间不会跳号。
- PERCENT_RANK()：计算窗口内当前行的百分位排名。
- CUME_DIST()：计算窗口内当前行的累积分布。

3. 相关位置值函数：

- LEAD()：可以访问窗口中当前行之后的行的数据。
- LAG()：可以访问窗口中当前行之前的行的数据。
- FIRST_VALUE()：返回窗口中的第一个值。
- LAST_VALUE()：返回窗口中的最后一个值。
- NTH_VALUE()：返回窗口中的第 n 个值。

4. 分桶函数：

- NTILE()：将窗口中的行划分为指定数量的桶，并为每行分配一个桶号。

这些窗口函数通常与 OVER() 子句结合使用，通过 PARTITION BY 和 ORDER BY 子句来定义窗口的相关范围顺序等。

• ROW_NUMBER

ROW_NUMBER() 为窗口内的每一行分配一个唯一的连续整数。这个编号是根据 ORDER BY 子句指定的顺序进行的，如果没有指定 ORDER BY，则按照数据在表中的物理顺序进行编号。

ROW_NUMBER() 适用于为结果集中的每一行生成一个唯一的序号，常用于获取每组数据中的排名，或者在需要时为数据分配一个序号等。

【示例】假设我们想要为每个销售员在每个月的销售数据上分配一个顺序号，以便我们可以看到每个销售员每个月的销售额排名。我们可以使用 ROW_NUMBER() 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售额降序排序。

SELECT   salesperson,   sale_month,   amount,   ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS sales_rankFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上分配一个序号，其中序号 1 表示该销售员在该月最高的销售额，序号 2 表示次高的销售额，依此类推。

• RANK

RANK()为窗口内的行分配一个排名。如果存在并列的情况，即如果有多个行具有相同的排名依据值，这些行将会被分配相同的排名，并且接下来的排名会跳过这些并列的行数。这意味着，如果有两个第二名，那么下一个排名将是第四名。

【示例】假设我们想要对每个销售员在每个月的销售额进行排名，以便我们可以看到每个销售员每个月的销售额排名，包括并列情况。我们可以使用 RANK() 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售额降序排序。

SELECT   salesperson,   sale_month,   amount,   RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS sales_rankFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上分配一个排名，如果两个月份的销售额相同，它们将得到相同的排名，并且下一个排名将会跳过并列的次数。例如，如果有两个月份的销售额都是最高的，它们都会被标记为第一名，下一个销售额将排名第三。

• DENSE_RANK

DENSE_RANK()为窗口内的行分配一个排名，并且在存在并列情况时，排名之间不会出现间隔。这意味着，如果有两个第二名，那么接下来的排名将是第三名，而不是跳过排名。

DENSE_RANK() 函数适用于需要对数据进行排名，并且在有并列情况时希望排名连续的场景。

【示例】假设我们想要对每个销售员在每个月的销售额进行连续排名，即使存在并列情况。我们可以使用 DENSE_RANK() 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售额降序排序。

SELECT   salesperson,   sale_month,   amount,   DENSE_RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS sales_rankFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上分配一个连续的排名。如果两个月份的销售额相同，它们将得到相同的排名，并且接下来的排名不会跳过任何数字。例如，如果有两个月份的销售额都是最高的，它们都会被标记为第一名，下一个销售额将排名第二名。

• PERCENT_RANK

PERCENT_RANK计算窗口内当前行的百分位排名。百分位排名是相对于窗口内其他行的位置的一个度量，它的值介于 0 到 1 之间（不包括 1）。PERCENT_RANK 函数的值是通过以下公式计算的：

(当前行的排名 - 1) / (窗口内的行数 - 1)

PERCENT_RANK 函数用于计算当前行在其分区内的相对位置。它可以帮助了解某一行的数据在整体数据分布中的位置，特别是在需要比较不同分区的数据时。

【示例】假设我们想要计算每个销售员每个月的销售额在其所有月份销售额中的百分位排名。我们可以使用 PERCENT_RANK 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售额排序。

SELECT   salesperson,   sale_month,   amount,   PERCENT_RANK() OVER (PARTITION BY salesperson ORDER BY amount) AS percent_rankFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上计算其在所有月份销售额中的百分位排名。例如，如果一个销售员在某一月的销售额是最低的，那么该月的 percent_rank 将为 0。如果一个销售员在某一月的销售额是最高的，那么该月的 percent_rank 将接近但小于 1。这个函数可以帮助我们理解每个销售额在整体分布中的位置。

• CUME_DIST

CUME_DIST 计算窗口内当前行的累积分布。累积分布是指在当前行的 ORDER BY 子句定义的顺序之前，包括当前行在内的行数占窗口内总行数的比例。CUME_DIST 函数的值介于 0 到 1 之间。

CUME_DIST 函数用于计算当前行相对于窗口内所有行的累积分布百分比。它可以帮助了解某一行的数据在整体数据分布中的位置，特别是在需要分析数据分布的累计效应时。CUME_DIST 的结果是一个百分比，它可以用来评估数据点在整个数据集中的位置。

【示例】假设我们想要计算每个销售员每个月的销售额在其所有月份销售额中的累积分布。我们可以使用 CUME_DIST 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售额排序。

SELECT   salesperson,   sale_month,   amount,   CUME_DIST() OVER (PARTITION BY salesperson ORDER BY amount) AS cume_distFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上计算其在所有月份销售额中的累积分布。例如，如果一个销售员在某一月的销售额是最低的，那么该月的 cume_dist 将小于 0.5，表示低于中位数的销售额。如果一个销售员在某一月的销售额是最高的，那么该月的 cume_dist 将为 1。这个函数可以帮助我们理解每个销售额在整体分布中的累计位置。

• LEAD

LEAD允许你访问窗口中当前行之后的行的数据。LEAD 函数可以指定一个偏移量，用于确定要访问的后续行的位置，如果没有指定偏移量，则默认为 1，即访问紧随当前行之后的行。

LEAD 函数非常适用于需要比较当前行与后续行数据的情况，例如，分析数据的变化趋势、计算连续数据的差值等。通过指定不同的偏移量，可以访问不同位置的后续行数据。

【示例】假设我们想要查看每个销售员每个月的销售额与其下一个月的销售额之间的差异。我们可以使用 LEAD 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售月份排序。

SELECT   salesperson,   sale_month,   amount,   LEAD(amount, 1) OVER (PARTITION BY salesperson ORDER BY sale_month) AS next_month_amountFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上显示下一个月的销售额。例如，对于销售员 Alice 在 2023 年 1 月的销售额，LEAD 函数将返回 2023 年 2 月的销售额。如果没有下一个月的数据（比如对于最后一个月份），则返回 NULL。

• LAG

LAG允许你访问窗口中当前行之前的行的数据。LAG 函数可以指定一个偏移量，用于确定要访问的前一行或前几行的位置，如果没有指定偏移量，则默认为 1，即访问紧邻当前行之前的行。

LAG 函数常用于需要比较当前行与前一行数据的情况，例如，分析数据的变化趋势、计算与前一期数据的差异等。通过指定不同的偏移量，可以访问不同位置的前一行数据。

【示例】假设我们想要查看每个销售员每个月的销售额与其上一个月的销售额之间的差异。我们可以使用 LAG 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售月份排序。

SELECT   salesperson,   sale_month,   amount,   LAG(amount, 1) OVER (PARTITION BY salesperson ORDER BY sale_month) AS previous_month_amountFROM   sales_data;

这个查询将为每个销售员在每个月的销售额上显示上一个月的销售额。例如，对于销售员 Alice 在 2023 年 2 月的销售额，LAG 函数将返回 2023 年 1 月的销售额。如果没有上一个月的数据（比如对于第一个月份），则返回 NULL。

• FIRST_VALUE

FIRST_VALUE 返回窗口中的第一个值。这个函数非常有用，当你想要从每组行的集合中获取某个字段的第一个值时，例如，获取每个销售员的第一笔销售金额或者每个部门中工资最低的员工的工资。

【示例】假设我们想要查看每个销售员在一年中的第一笔销售额。我们可以使用 FIRST_VALUE 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售月份排序。

SELECT   salesperson,   sale_month,   amount,   FIRST_VALUE(amount) OVER (PARTITION BY salesperson ORDER BY sale_month ASC) AS first_sale_of_the_yearFROM   sales_data;

这个查询将为每个销售员显示在一年中的第一笔销售额。结果集将包括每个销售员的每个月份的销售额，以及他们每年的第一笔销售额。通过按销售月份升序排序，FIRST_VALUE 函数确保了每个分区内（即每个销售员）的第一行是年度的第一笔销售。

• LAST_VALUE

LAST_VALUE 返回窗口中的最后一个值。这个函数通常与 ORDER BY 子句一起使用，以便确定哪个是“最后一个”值。如果没有指定 ORDER BY，LAST_VALUE 将返回窗口中的最后一行。

LAST_VALUE 函数用于获取窗口框架中的最后一个值。它可以在各种场景中使用，例如，获取每个销售员最后一笔交易的金额，或者获取每个部门中工资最高的员工的工资。

【示例】假设我们想要查看每个销售员在一年中的最后一笔销售额。我们可以使用 LAST_VALUE 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售月份排序。

SELECT   salesperson,   sale_month,   amount,   LAST_VALUE(amount) OVER (PARTITION BY salesperson ORDER BY sale_month DESC) AS last_sale_of_the_yearFROM   sales_data;

这个查询将为每个销售员显示在一年中的最后一笔销售额。结果集将包括每个销售员的每个月份的销售额，以及他们每年的最后一笔销售额。通过按销售月份降序排序，确保了每个分区内（即每个销售员）的最后一行是年度的最后一笔销售。

• NTH_VALUE

NTH_VALUE返回窗口中第 n 个指定值。这个函数非常有用，当你想要从每组行的集合中获取某个字段的第 n 个值时，例如，获取每个销售员的第三高销售额或者每个部门中工资排名第五的员工的工资。

NTH_VALUE 函数允许你指定一个表达式和一个整数 n，并返回窗口框架中从 ORDER BY 子句定义的顺序的第 n 个表达式的值。

【示例】假设我们想要查看每个销售员在一年中的第二高销售额。我们可以使用 NTH_VALUE 函数，并通过 PARTITION BY 按销售员分组，然后通过 ORDER BY 按销售额降序排序。

SELECT   salesperson,   sale_month,   amount,   NTH_VALUE(amount, 2) OVER (PARTITION BY salesperson ORDER BY amount DESC) AS second_highest_saleFROM   sales_data;

这个查询将为每个销售员显示在一年中的第二高销售额。结果集将包括每个销售员的每个月份的销售额，以及他们每年的第二高销售额。如果某个销售员在一年中的销售额少于两个，则 NTH_VALUE 函数将返回 NULL。

• NTILE

NTILE将窗口中的行划分为指定数量的桶（tiles），并为每行分配一个桶号。这个函数在数据分桶分析中非常有用，例如，将数据分为几个等高的桶，以便进行分位数计算或者进行其他类型的分组分析。

NTILE 函数接受一个整数参数，表示要创建的桶的数量。它会将窗口内的行分配到这些桶中，使得每个桶中大约有相同数量的行。如果窗口内的行数不能被桶的数量整除，那么一些桶可能比其他桶多一行。

【示例】假设我们想要将销售数据分为三个桶，以便分析每个桶中的销售额分布。我们可以使用 NTILE 函数，并通过 ORDER BY 按销售额排序，以便每个桶包含大致相等的销售额。

SELECT   salesperson,   sale_month,   amount,   NTILE(3) OVER (ORDER BY amount) AS sales_bucketFROM   sales_data;

这个查询将为每个销售记录分配一个桶号，从 1 到 3。结果集将包括每个销售员的每个月份的销售额，以及他们各自的销售额分配到的桶号。通过按销售额排序，NTILE 函数确保了每个桶中的销售额分布是均匀的。这样的分桶可以帮助我们快速识别高销售额、中等销售额和低销售额的销售记录。