揭秘SQL中的窗口函数

一个被忽视的SQL操作的理论与实践

照片由 Marcus Woodbridge 拍摄。

介绍

IT领域以其不断的变革而著称，每天都有新的工具、框架、云服务商和大模型不断涌现。然而，在这个繁忙的世界中，一些原则、范式和工具似乎挑战了“没有什么是永恒不变”的固定思维。而在数据领域，没有什么比SQL语言更能证明这一点了。

自上世纪八十年代以来，它经历了数据仓库阶段，在Hadoop、数据湖和大数据领域以Hive的形式实现，至今仍然是Spark API中的一员。世界已经变得大不一样，但SQL不仅依然活跃，还非常普及和重要。

但是 SQL 就像国际象棋一样，虽然基本规则容易理解，但精通起来却很难！它是一种具有多种可能性的语言，解决同一问题有多条途径，包含多种功能和关键词。不幸的是，它还有许多被忽视的功能，如果能更好地了解这些功能，将大大有助于我们在构建查询时。

因此，在这篇帖子中，我想谈谈一个我发现在构建日常查询时非常有用的特性：不太为人知的SQL功能。

你知道什么是窗口函数吗？

传统的和最流行的SGBDs（PostgreSQL、MySQL和Oracle）基于关系代数理论。其中，行称为元组，表称为关系。关系是一组元组（在数学意义上），即元组之间没有顺序或连接。因此，表中的行没有默认顺序，对一行的计算不影响其他行，反之亦然。甚至像ORDER BY这样的子句也只是对结果进行排序，无法根据其他行的值进行计算。

简单来说，窗口函数解决了这一问题，扩展了SQL的功能性，使我们能够在行中基于其他行的值进行计算。

基础案例 / 基本结构

1-聚合但无聚合过程

最简单的例子来理解 Windows 函数是无需使用聚合功能即可完成聚合的能力。

当我们使用传统的GROUP BY进行数据聚合时，整个表会被压缩成一个新表，其中每一行代表一个组。而使用窗口函数时，我们不需要压缩行，而是在原表中新增一列来存储聚合结果。

比如说，如果你想把你支出表里所有的支出加总，传统上你会这么做之类的。

    SELECT SUM(value) AS total FROM myTable

这里是从 myTable 表中选择 value 列的总和（SUM 函数用于求和）。

使用 Windows 功能，你可以这样做……例如，比如这样使用 Windows 功能。

SELECT *, SUM(value) OVER() FROM myTable  
-- 注意，窗口函数是在查询中定义的

如下图所示，这是结果。

图片 1. 传统 GROUP BY 对比窗口函数。

不是创建一个新的表，而是会在新列中返回聚合值。注意，值保持不变，但表并没有被“汇总”，原始行保持不变——我们只是计算了未进行汇总的表的聚合值 :))

OVER子句表示我们正在使用一个窗口函数。此子句定义了计算将在哪些行上执行。因为该子句为空，所以它将对所有行进行SUM()计算。

这在我们需要基于列的总和（或平均值、最小值、最大值）进行计算时很有用。例如，计算每个支出占总计的百分比。

在实际情况中，我们也可能想按某些类别查看具体信息，比如在图2中，我们按部门查看公司的各项支出。同样，我们也可以通过简单的GROUP BY语句来计算每个部门的总支出。

    SELECT 部门, SUM(数值) FROM myTable GROUP BY 部门

或者在窗口函数中指定分区规则：

从myTable中选择所有列，并按depto分区计算value的总和：

SELECT *, SUM(value) OVER(PARTITION BY depto) FROM myTable

看看结果：

图2. 传统的 GROUP BY 与窗口函数第二部分.

这个例子帮助我们理解为什么这一操作被称为“窗口”函数的原因——OVER子句定义了一组行作为窗口，在这个窗口内，相关函数将执行。

在上面的案例中，SUM()函数会在depto列（RH和SALES）生成的每个分区上运行——它会各自计算RH和SALES两个部门‘value’列值的总和。‘Total’列的值会根据所在部门（RH或SALES）来确定。

2 — ：时间和排序意识

有时候我们需要根据其他行的数据来计算某一行中的数值。一个典型的例子是通过当前值和前一个值来计算每年的GDP增长，比如计算一个国家每年的GDP增长。

这类计算，其中我们需要去年的值，当前行和下一行的差值，序列中的第一个值等等，都彰显了窗口函数的强大。事实上，我不确定这是否可以用标准的SQL命令实现！可能可以，但查询会相当复杂……

但窗口功能使这变得很简单，如下图所示（记录某个小孩的身高）。

如图所示，图像3，分析功能示例。

    SELECT   
      年份, 高度,   
      LAG(高度) OVER (ORDER BY 年份) AS 上一年度高度  
    FROM myTable

LAG(‘column’) 函数负责引用‘column’列的前一行的值。你可以将其想象为一系列步骤：在第二行时，看第一行的值；在第三行时，看第二行的值；依此类推……第一行不算（因此是 NULL），因为它没有前一行。

当然，我们需要某种排序标准来定义什么是“上一行”。这也是 Windows 函数中一个重要的概念：分析性函数。

与传统的SQL函数相比，分析函数（如LAG）认为行之间存在顺序——这种顺序在OVER关键字内的ORDER BY子句中定义。这些函数的主要特点是能够引用相对于当前行的其他行：LAG引用前一行，LEAD引用下一行，FIRST引用分区内的首行，等等。

LAG 和 LEAD 都接受一个名为偏移量的第二个参数。这个参数用来指定向前查看（对于 LEAD）或向后查看（对于 LAG）多少行。

    SELECT   
        LAG(高度, 2) OVER (ORDER BY 年份) AS 两年之前的身高,  
        LAG(高度, 3) OVER (ORDER BY 年份) AS 三年之前的身高,  
        LEAD(高度)    OVER (ORDER BY 年份) AS 明年的身高  
    FROM ...

而且，使用这些函数来进行计算也是完全可以的：

    SELECT   
        100*height/(LAG(height) OVER (ORDER BY 年))   
        AS "annual_growth_%"  
    FROM ...

3 — 时间感知与整合

时间和空间其实是一回事——爱因斯坦曾经说过类似这样的话，我也不太清楚 ¯\(ツ)/¯（表示不确定）

现在我们知道了如何划分和排序，可以将这两个步骤结合起来了！回到之前的例子，那张桌子上有了更多的孩子，我们需要计算每个孩子的成长率。这非常简单，只需结合排序和分组！按年份排序，按孩子的名字分组统计。

SELECT 1 - 身高 / LAG(身高) OVER (PARTITION BY 名称 ORDER BY 年) ...

图 4. ORDER BY 和 PARTITION BY

这个查询做了以下事情——按 child 列分区表，在每个分区中按年份排序，并将当前年的高度值除以前一年的高度值（然后从一减去该结果）。

我们越来越接近对“窗口”这个概念的完整理解！它是根据PARTITION BY定义的列进行分组的一系列行，并按照ORDER BY中的字段顺序排列，所有的计算都是基于同一分区内的行，并按特定排序进行的。

4-:排名与位置

Windows 函数可以分为三类，其中我们之前已经讨论了两类：聚合函数（计数、求和、平均值、最大值等）和分析函数（滞后、领先、首次值、末次值等）。

第三个组是最简单的——排名功能，其中最典型的是 row_number() 函数，它返回一个整数，表示行在组中的位置，基于定义的顺序。

    SELECT 行号() OVER(ORDER BY score) -- 计算行号并按分数排序

排名函数，顾名思义，会根据定义的排序准则返回行的位置。ROW_NUMBER、RANK和NTILE是最常用的几种。

图像 5. 排名函数例子

如上图所示，根据每个玩家的得分生成了一行号

而且确实，它犯了这种从1开始的可怕编程错误。

5: 窗口尺寸

到目前为止，我们介绍的所有函数在计算结果时都会考虑分区/组中的所有行。比如，第一个例子中描述的 SUM() 会考虑各部门的行来得出总数。

但是可以指定较小的窗口大小，也就是计算时需要考虑当前行前后多少行。这在计算移动平均值或滚动窗口时非常有用，计算时考虑当前行前后的行数。

下面考虑以下示例，其中包含某种疾病的每日病例数量的表格，我们需要计算当前日期及其前两天的平均病例数。需要注意的是，可以使用之前提到的 LAG 函数（之前展示的）来解决这个问题。

    SELECT  
    ( n_cases + LAG(n_cases, 1) + LAG(n_cases, 2) ) / 3  
    OVER ( ORDER BY date_reference )

-- 计算每日期引用的病例数的三日均值

不过，我们可以通过使用框架概念更优雅地达到同样的效果：

    SELECT  
    AVG(n_cases)  
    OVER (  
        ORDER BY date_reference  
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW      
    )

下面的SQL语句计算了每三天内平均的n_cases值（包括当前记录），按date_reference排序。

如上所述，我们只需计算包括当前行在内的前两行的平均值。如果我们希望考虑上一行、当前行和下一行，我们可以调整框架。

    AVG(n_cases)  
    OVER (  
        ORDER BY date_reference  
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  
    )

而且帧就是这样——一种方法来限制函数的作用范围，使其仅在特定范围内生效。默认情况下（通常），窗口函数（也称为窗口分析）会考虑以下的范围。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  
-- 所有之前的行，加上无界的当前行

图6. 探索窗口大小的设定

希望这段介绍能让你更清楚地了解Windows函数是什么，它们是如何工作的，以及它们的实际用法。当然，Windows函数里还可以用很多其他关键词。但我认为这已经涵盖了你日常生活中常用的许多命令。现在来看我每天解决问题时用到的一些有趣实用小技巧——其中一些还很有趣呢！

基于窗口功能的一些有趣的应用场景 累积总和随时间变化

这是使用窗口函数的一个非常经典的案例。

想象有一张表格，列出了你每个月的薪水，你想知道每个月为止累计赚了多少钱（包括之前所有月份的薪水），这便是计算方法：

图7. 实际应用示例 — 累计SUM

挺简单的，对吧？

有趣的是，在此查询中，SUM() 函数会把当前行及其之前的所有行都考虑进去计算总计，正如之前提到的。

日志表中的事件时长

我最近在我的帖子我的第一百亿行（在DuckDB中）中使用了这种方法，我在其中处理了巴西电子投票机的日志记录，如果你对处理大量数据感兴趣的话，这篇文章很值得一看。

总之，想象一个日志表，其中每个事件由表示开始时间的时间戳、名称和唯一标识组成。考虑到这一点，每个事件只有在前一个事件结束时才开始，我们可以很容易地添加一列来表示事件的持续时间，如下所示：

图片 8. 实际例子 — 日志里的事件时长

填充缺失值（用最后一次出现的值）

用 pandas 做机器学习的经典操作！填个 fillna, bfill 或啥都行，就完了，用最后一个有效值来填充空值。

这在SQL里怎么弄？超级简单！

实际填充缺失值的示例 I

当我们刚开始学习机器学习时，我们经常使用pandas并熟悉它们的高级功能。然而，在实际项目中处理数据时，数据量可能非常庞大，所以我们可能没有那么幸运能够直接使用pandas，而是需要切换到像PySpark、Snowflake、Hive+Hadoop这样的工具——这些工具从某种意义上讲都可以通过SQL进行操作。因此，我认为学习如何用SQL来进行这些处理和预处理是非常重要的。

用前面行的平均值来填补缺失值

用稍微复杂一点的方式来填充缺失值，但依然十分简单。

图8. 实际例子 — 填补缺失数据二

尽管窗口函数看起来复杂且特殊，它们实际上可以像普通列一样使用！可以将它们包含在 CASE 语句中，也可以对它们进行计算。我知道的几个限制中的一个是，它们不能直接在 WHERE 子句中使用。

    SELECT * FROM  
    WHERE SUM() OVER() > 10 -- 这在 postgres 里行不通

基于一组列的行去重

另一个窗函数的经典用例！有时我们可能需要基于某一列来去重。

当然，在 SQL 中我们有 DISTINCT 子句，但是它只在整行完全重复时才有效。如果一个表中有几行在 ID 列上有相同的值但在其他列中却有不同的值，可以使用以下逻辑消除重复：

图片9, 实际示例 — 重复数据删除

选择 * 
 从 (
  选择 
  行号() 分区 由 id 作为 行号 
 ) 
 其中 行号 = 1

从(id分区后带有行号的子查询)中选择行号为1的记录。

此操作还支持数据版本管理！例如，每次用户在系统中更改姓名时，都保存一条新的记录，并记录更改的日期，我们就可以查询到每个用户的当前姓名：

选择从子查询中获取所有列，该子查询按以下方式执行：

选择
    名称,
    行号() 按 id 分区 按 日期 降序 排序 作为 行号
从 myTable

其中行号等于1，获取每组最新的记录。

计算组或类在总行数中所占的百分比

考虑一个列出各种宠物的表格，这些宠物可以是狗、猫或鸟。我们需要在每一行添加一列，表示每种宠物类型所占的百分比。这个任务是通过使用两个窗口函数（window function）完成的。

图片10. 实际例子 — 出现次数百分比

在上面的图片中，为了让它更具有教育性，我添加了两列以表示每种窗口函数的结果，但只有最右边的列才是实际创建的。

你有没有什么有趣的窗口功能例子想分享的？可以在评论区留言分享哦！

结语

我可不敢说 SQL 是复古或经典，因为这都是褒义，但毕竟指的是过去。对我来说，SQL 是现在的，无处不在的，并且毫无疑问地，对于所有在数据领域工作的人来说，它都是必不可少的语言。

然而，仅靠SQL来解决一些问题可能会显得相当复杂，而在这些时候，拥有良好的SQL语言知识及其功能真的非常重要。如果没有窗口函数，许多从Python的角度来看常见的问题将变得非常难以解决，甚至根本无法解决。但如果我们能正确使用这些工具，就能做出一些神奇的事情来！

我希望这篇帖子能帮助你更好地了解Windows功能的工作方式以及它们在实践中可以解决什么类型的问题。这里展示的所有内容主要基于PostgreSQL的语法，可能不一定能直接在其他数据库中运行，但最关键的是背后的逻辑。和往常一样，我不是这方面的专家，我强烈建议任何对此主题感兴趣的人都应该深入阅读并多多实践。

谢谢大家的阅读！😊

参考文献

所有的代码都在 这个 GitHub 仓库 中。喜欢这种类型的工作吗？可以访问我的 文章仓库。

[1]PostgreSQL窗口函数的数据处理任务. (n.d.). Timescale. 链接.
[2] Kho, J. (2022年6月5日). 高级SQL窗口函数的简易指南 — 数据科学. Medium.
[3] Markingmyname. (2023年11月16日). Transact-SQL中的分析功能 — SQL Server. Microsoft Learn.
[4] PostgreSQL教程. (2021年4月27日). PostgreSQL窗口函数完整指南. 链接.
[5] VanMSFT. (2023年5月23日). OVER子句 (Transact-SQL) — SQL Server. Microsoft Learn.
[6]窗口函数. (n.d.). SQLite官方文档.
[7]窗口函数. (2014年7月24日). PostgreSQL官方文档.

本文中的所有图片均由作者制作。