优享资讯 | PostgerSQL技术问答16

掘金后端 ( ) • 2024-07-01 18:20

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是PostgreSQL中一个非常强大的数据分析功能集合: Window，即窗口操作和函数。

什么是窗口函数

在SQL标准中，Window(窗口函数)是一类聚合计算和统计分析的功能集合。在SQL标准中，于2003版本中首次引用，在2011版中，相关的定义和范式已经基本完善。所以，主流的关系数据库系统，包括PostgreSQL，对Window函数和功能基本上都是有比较好支持的，可能在实现细节和兼容性上略有差异。

Window相关功能和语句的设计目标是为了提供更完善和强大的关系数据的分析能力。所以，它在聚合函数和分析的基础上，扩展了相关的功能，可以分析记录和集合，以及集合内记录和记录之间的关系，极大的扩展了这类抽象化业务应用的使用场景和灵活性。

如何比较窗口函数(Window)和聚合函数(Aggrate)

笔者体会，窗口函数和聚合函数虽然都涉及到集合的计算，但它们的设计实现出发点和应用的差异还是比较大的，可以看成是两类适应不同应用场景的功能集合。要理解这个判断，有下面几个要点：

窗口函数的本体，是以普通的记录查询为基础，并且扩展聚合计算和统计结果

相对而言，聚合函数的初始构想，就是以分类集合作为核心，所有的实现和设计都围绕这个进行展开。例如select结果集字段列表、Group By关键字和语句、Having过滤模式等等。

而窗口函数则就是以普通的Select查询作为基础，以记录为核心来扩展聚合查询相关的功能。

虽然在实践上不太推荐，但实际上，可以使用窗口函数，完全的实现简单聚合函数相关的功能。当然，这样使用，可能会导致一些数据的冗余和重复计算。

窗口函数计算可以体现当前记录，和聚合记录集之间的关系

聚合函数，只能体现集合本身的特性，这在进行高级分析的时候，是一个比较大的缺陷和限制。而窗口函数可以很好的处理当前记录和数据集合之间的关系。

有一个典型的用例学生成绩单，原来的聚合查询，可以查询各个班级成绩的最高分，无法直接获取这个最高分对应的是那个学生，也进一步无法查询第二高分是多少和对应哪个学生。窗口函数就可以处理这一类的问题，它的处理是从学生记录出发，然后按照班级进行分组，并且在班级内部按照成绩进行排序。这样，就可以基于排序，看到第一名和第二名分别是谁，成绩如何了。这个实际上就是在分析和处理记录和集合之间的关系。

窗口函数计算还可以体现在窗口记录集内部，记录之间的关系

还是以学生成绩单作为例子，我们需要在查询学生成绩的时候，由于某种分析的需求，希望看到当前学生排名后面那个学生的成绩，当然我们可以通过遍历记录集，使用外部程序处理来获得这个结果，但其实也可以使用窗口函数的相关特性(如lag和lead)，在当前的查询语句和记录中，直接计算和查询这个信息。这个应用案例，其实就是在处理集合内部，记录之间的某种关系。

需要特别注意，这类函数和功能，通常都和记录在记录集中的位置相关，这都会基于某种排序规则。

统计学处理

基于以上的一些功能和扩展方式，窗口函数就可以实现更高级，更复杂的统计和分析功能。比如ntile可以人为的将集合内部再次平均分组，可以作为某些统计方法的计算基础。

窗口函数有哪些应用场景

相对而言，在SQL语言中，窗口函数是比较新和高级的功能，所以我们更有必要了解和理解窗口函数的功能和应用场景，才能有机会能够结合业务需求，将窗口函数的功能合理而有效的运用到实际应用当中去。这里笔者根据自己的理解和实际的经验，例举了一些常见的窗口函数的应用场景，希望对读者有所启发：

聚合统计，就是以前聚合函数提供的那些统计和分析功能
给记录进行分组的编号，先按照特性进行分组，然后在组内基于某种排序规则进行编号
记录查重，在编号的基础上，提取出每组第一个记录，可以用于查重计算
排名，给记录进行分组排名，包括百分比排名
在分组内部的累积聚合计算
在分组内部分片，然后做进一步分析如再次聚合计算等等
常见分组统计学分析，如累计分布、方差、标准差等
查找特定排名的记录
利用位置信息，在集合内构造关联数据，并分析数据间的关系

如何使用窗口函数

Window函数，并不是一个简单的方法或者函数，而是一套相关的语法结构，语句，关键字和方法的组合。它一般就是用普通的Select语句当中。标准的窗口函数表达式的形式如下：

winfun(param...) over (patition by ... order by ...)

这里有以下结构：

winfunc: 所有窗口函数的应用，都需要指定一个特定的函数，来满足一个业务需求
param: 所使用的窗口函数的参数，基于这个函数的定义
over: 关键字，意为需要根据后续的设置来定义窗口，如果没有内容，则将整个源记录集作为一个大的窗口
partition by: 关键字，分组集合的依据，类似于group by
order by: 在集合内部的排序方式，这会影响到记录和集合，以及记录之间的关系

下面有一些实际的例子，可以让读者更好的理解它的用法：

分组排名

with D(id,name,sclass,score) as ( values
(1,'关羽',1, 95),
(2,'张飞',1, 90),
(3,'典韦',2, 93),
(4,'张辽',2, 88),
(5,'吕布',3, 93),
(6,'赵云',1, 89)
) select *, row_number() over() rn, rank() over (partition by sclass  order by score desc ), max(score) over(partition by sclass) - score diff from D order by sclass;
 id | name | sclass | score | rn | rank | diff 
----+------+--------+-------+----+------+------
  1 | 关羽 |      1 |    95 |  1 |    1 |    0
  2 | 张飞 |      1 |    90 |  2 |    2 |    5
  6 | 赵云 |      1 |    89 |  3 |    3 |    6
  3 | 典韦 |      2 |    93 |  4 |    1 |    0
  4 | 张辽 |      2 |    88 |  5 |    2 |    5
  5 | 吕布 |      3 |    93 |  6 |    1 |    0

这是最经典和常规的用法。需要根据班级进行分区，并在分区内，使用分数进行排序（倒排），然后就可以使用聚合函数进行对应的计算了。

数据查重

下面是一个简单例子，用于查找重复的id：

with D(id,name) as ( values
(1,'Apple'),
(2,'Apple'),
(5,'Apple'),
(3,'Dell'),
(4,'IBM'),
(7,'IBM')
) select * from (select id, row_number() over (partition by name order by id) rn from D )R  where rn > 1;
 id | rn 
----+----
  2 |  2
  5 |  3
  7 |  2
(3 rows)

这里查重计算的原理是，基于查重条件(这里是名称)给记录进行分区，然后在分区内进行编号，最后选择所有大于1的，就是分区内重复的记录。

计算数据变化

SELECT date, price, price - LAG(price) OVER (ORDER BY date) AS price_change
FROM stock_prices;

上面这个例子，使用了lag函数，可以用于对比当前记录和上一行记录之间的差异。这种对比，经常在股票、温度等应用中进行数据变化的差异分析。当然我们就可以基于这个差异，进一步筛选数据变化比较大的时间点，这是一个常见和普遍的需求。

从这些例子可以看到，每个窗口函数都是针对特定函数和窗口定义而表达的，在同一个语句中还可以编写不同窗口逻辑的窗口函数表达式，而且这些表达式之间也不会相互干扰和影响。就是说，这种窗口函数的实现方式，可以在一个SQL语句中，包容和表达多种的业务需求，这无疑提供了很强的灵活性。

窗口函数的聚合计算，和聚合函数有什么区别

笔者专门编写这个章节，就是为了强调它们的不同，和窗口函数的一个重要特性，就是“累计聚合”。

其实，窗口函数的聚合计算，和聚合函数中的聚合计算，基本概念和使用方法虽然一样，但由于窗口函数强调记录在窗口中的关系，相比标准的聚合函数只是从聚合分组整体的角度处理外，它实际上使用“累计聚合”的方式进行处理。就是聚合计算的范围，并不是整个窗口分片，而是在本记录之前的窗口片段。这个记录前面的记录集合，依据的就是分区内的排序方式。

下面这个例子，应该可以很好的帮助读者对这个概念进行理解：

 with W(id,name,sclass,score) as ( values  
(1,'关羽',1, 95),
(2,'张飞',1, 90),
(3,'典韦',2, 93),
(4,'张辽',2, 88),
(5,'吕布',3, 93),
(6,'赵云',1, 89)
) select sclass, name, score, 
sum(score) over(partition by sclass order by score) s1, 
sum(score) over(partition by sclass order by id) s2, 
sum(score) over(partition by sclass) s3  
from W order by sclass;
 sclass | name | score | s1  | s2  | s3  
--------+------+-------+-----+-----+-----
      1 | 赵云 |    89 |  89 | 274 | 274
      1 | 张飞 |    90 | 179 | 185 | 274
      1 | 关羽 |    95 | 274 |  95 | 274
      2 | 张辽 |    88 |  88 | 181 | 181
      2 | 典韦 |    93 | 181 |  93 | 181
      3 | 吕布 |    93 |  93 |  93 |  93
(6 rows)

同样是sum聚合，在不同的排序下，同一条记录，得到的结果可能是不同的，因为排序的方式会影响聚合的结果。我们还可以看到，如果不指定任何排序方式，它的计算就是一个普通的聚合计算。

累积聚合这个特性，给数据的分析操作带来了非常大的灵活性。例如，我们可以在一个语句中，就可以分析和输出学生在不同科目的排名，也可以实现财务分析中，分类目汇总和累计时间汇总等常见的数据分析方式。

常见的窗口函数有哪些

首先，作为一类扩展的统计功能，窗口函数包含所有的聚合函数，如count、sum、avg、max、min等等，然后在此基础上，提供了可以表述当前记录和窗口区域之间的某种统计关系的计算方式，包括：

row_number () → bigint 行号

返回当前记录所在当前所在分区中的行号，从1开始。

rank () → bigint 排序

返回当前记录所在当前分区中的排序。和行号的区别在于，可能有相同排序数值的时候，它们的排序是相同的，并且后续的排序会跳过计数。例如如果第三名有两个相同分数的时候，它们将会并列第三名，并且跳过第四名，后面的排名从第五名开始。

dense_rank () → bigint 密集排序

这种排序方法不会跳过相同分数的排名。上面的例子中，第三名有两个。后续就是第四名。这种排名方法，排名的数量和参与排序的数量可能不一致。

percent_rank () → 百分比排名

这是一种相对排名，或者归一化的排名方式。它会把排名值转换称为一个百分比(使用 (排名 - 1) / (窗口行数 - 1) 这个计算规则)。显然，这个排名的结果被限制在0和1之间，它可以在不需要知道总数的情况下，方便直观的判断当前记录排位的位置。

cume_dist () → double precision

在分区内部，针对当前记录的值，进行累计分布计算。取值的范围是 1/N和1之间。

ntile ( num_buckets integer ) → integer

可以使用指定的数量，给分区内的记录，进行进一步的分片，并尽量保证每个分片的记录数量比较平均。例如ntile(4)可以把分区内的记录成4片，然后就可以进行下一步的处理了。

lag ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible

lag的原意是延迟。在窗口函数中，这个计算可以查询按照排序规则，当前行前面前面某个偏移所在行的特定字段的值。默认偏移值为1。如果不存在这个行，则返回默认值或者null。

lead ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible

和lag相对，返回的是后面偏移行字段的值。

first_value ( value anyelement ) → anyelement

返回窗口分区内，按照排序规则进行排序后，第一个记录指定字段的值。

last_value ( value anyelement ) → anyelement

返回窗口分区内，按照排序规则进行排序后，最后一个记录指定字段的值。

nth_value ( value anyelement, n integer ) → anyelement

这个窗口函数，用于检索窗口函数的分区内，按照排序规则第n行某个字段的值。从1开始。first_value和last_value都是这个方法的特例。

Window子句

一般情况下，我们都是将窗口函数直接写在select语句中的字段选择字段区域，因为最终的窗口函数计算的结果，就是一个结果集字段的形式。如果我们需要使用多个相同窗口定义的不同的窗口函数，可能就需要重复的定义窗口，这显然不够简洁和优雅。

为了解决和改善这个问题，我们可以使用Window子句，然后在Select语句中，在不同的窗口函数中，共享引用这个Window定义。下面这个简单的例子可以方便我们理解这个问题：

SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER w AS dept_avg_salary,
    MAX(salary) OVER w AS dept_max_salary
FROM employees
WINDOW w AS (PARTITION BY department)
ORDER BY department, salary DESC;

Window函数有什么不足和缺点吗

当然，Window函数，由于其工作原理和设计目的，通常用于比较复杂的计算和分析。在数据比较大，业务需求比较复杂的时候，可能需要编写比较复杂的业务分区、聚合和分区分析计算的操作，中间涉及大量的数据和计算步骤，需要特别注意相关执行和性能的优化，要用好这个功能，对使用者和开发者的要求，是比较高的。

小结

本文探讨了一个常见的SQL高级数据分析的功能集合-Window(窗口)函数。比较了它和聚合函数之间的关系，列举了其一般的应用场景，并通过实例代码，分析了它的使用方式、特点和需要注意的问题。