[SQL挖掘机] - 窗口函数介绍

介绍:

窗口函数也称为 OLAP 函数。OLAP 是 OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。窗口函数是一种用于执行聚合计算和排序操作的功能强大的sql函数。它们可以在查询结果集中创建一个窗口（window），并在该窗口上进行计算，而不影响结果集的整体。

窗口函数通常与over子句一起使用，以定义窗口的范围。over子句可以指定窗口的排序方式、分区方式和边界等。

为了便于理解，称之为 窗口函数。常规的 select 语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

用法:

窗口函数的通用形式：

<窗口函数> over ([ partition by <列名> ] [ order by <排序用列名> ])  

[ ]中的内容可以省略。

窗口函数最关键的是搞明白关键字 partiton by 和 order by 的作用。

• partiton by 子句 可选参数，指示如何将查询行划分为组，类似于 group by 子句的分组功能，但是 partition by 子句并不具备 group by 子句的汇总功能，并不会改变原始表中记录的行数。
• order by 子句 可选参数，指示如何对每个分区中的行进行排序，即决定窗口内，是按那种规则(字段)来排序的。

注意:

虽然 partiton by 子句 和 order by 子句 都是可选参数，但是两个参数不能同时没有（最少二选一）。不然， <窗口函数> over( ) 这种用法没用实际意义（窗口由所有查询行组成，窗口函数使用所有行计算结果）。

分类:

常用的窗口函数有：

• row_number()：为每一行返回一个唯一的数值，通常用于给结果集中的行进行编号。
• rank()和dense_rank()：根据指定的排序顺序，为结果集中的每一行分配一个排名。rank()在遇到相同的值时会跳过相同的排名，而dense_rank()不会跳过。
• lag()和lead()：lag函数用于获取当前行之前的某一行的值，lead函数用于获取当前行之后的某一行的值。它们与排序有关，可以用于查找前一行或后一行的值。
• sum()、avg()、min()、max()等聚合函数：这些聚合函数可以在窗口范围内进行计算，并返回结果集中每一行的聚合值。

除了以上列举的函数外，窗口函数还有其他一些类型和变种，可以根据具体的需求选择使用。

窗口函数在sql中的应用非常广泛，可以用于计算移动平均、累计求和、分组内排序等。它们提供了一种灵活且高效的方式来处理复杂的查询需求。