窗口函数是编写高效且易于理解的SQL代码的关键。了解这些函数的工作原理和使用时机,将为解决报表问题带来新的方法。
本文的目标是以易于理解的方式逐步解释SQL中的窗口函数,使你不必仅仅依靠记忆语法来了解它们。
以下是我们将涵盖的内容:
对于如何看待窗口函数的解释。
逐渐增加难度的多个示例。
查看一个具体的实际案例,将所学知识应用到实践中。
回顾我们所学到的内容。
我们的数据集很简单,是2023年两个地区的六行收入数据。
二、窗口函数是子分组
如果我们使用这个数据集,对每个地区的收入进行GROUP BY求和,结果只会剩下两行,每个地区一行,然后是收入的总和:
我希望你将窗口函数视为类似于这样的方式,但聚合并不是减少行数,而是在“后台”运行,并将值添加到现有行中。
首先,举个例子:
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER () as total_revenue
FROM
sales
请注意,我们没有使用任何GROUP BY,数据集保持不变。然而,我们还是能够获得所有收入的总和。在我们更深入地了解它的工作原理之前,让我们先快速了解一下全部语法,然后再开始积累知识。
三、窗口函数语法
语法如下:
SUM([some_column]) OVER (PARTITION BY [some_columns] ORDER BY [some_columns])
将每个部分拆开来看,我们就会发现这些内容:
聚合或窗口函数:SUM、AVG、MAX、RANK、FIRST_VALUE。
OVER关键字表示这是一个窗口函数。
PARTITION BY部分用于定义分组。
ORDER BY部分定义了是否是运行函数(我们稍后会介绍)。
暂时不必急着解释这些字符串的含义,当我们讲解示例时,它们会变得清晰。现在只需知道我们将使用OVER关键字来定义窗口函数。正如我们在第一个示例中看到的那样,这是唯一的要求。
四、逐步建立、加深我们的理解
接下来,我们将在函数中应用一个分组,这才是真正有用的东西。保留初始计算是为了向大家展示我们可以同时运行多个窗口函数,这意味着我们可以在同一个查询中同时进行不同的聚合,而无需使用子查询。
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region) as region_total,
SUM(revenue) OVER () as total_revenue
FROM sales
如前所述,我们使用PARTITION BY来定义由聚合函数使用的分组(窗口)。因此,在数据集保持不变的情况下,我们可以得到:
每个地区的总收入。
整个数据集的总收入。
我们也不局限于单个组。与GROUP BY类似,我们也可以根据地区和季度对数据进行分区,例如:
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY
region,
date_trunc('quarter', date)
) AS region_quarterly_revenue
FROM sales
从图中我们可以看到,同一地区和季度仅有的两个数据点被分组在一起!
到目前为止,希望你清楚地了解到,我们可以将其视为在原地执行GROUP BY操作,而不会减少数据集中的行数。当然,我们并不总是希望这样,但是经常会看到查询中有人对数据进行分组,然后将其与原始数据集连接起来,使本来可以使用单个窗口函数的操作变得复杂起来。
继续讲解ORDER BY关键字。这个关键字定义了一个运行窗口函数。你可能在生活中听说过运行求和(Running Sum),但如果没有听说过,我们将从举例开始,以便一切都清楚明了。
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (ORDER BY id) as running_total
FROM sales
这里发生的是,我们逐行逐行地将收入与之前的所有值相加。这是按照id列的顺序进行的,但也可以是任何其他列。
这个具体的例子并不是特别有用,因为我们是在随机的几个月份和两个地区之间求和,但是根据我们所学到的知识,我们现在可以找到每个地区的累计收入。我们可以通过在每个组内应用滚动求和来实现这一点。
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region ORDER BY date) as running_total
FROM sales
请花点时间确保你理解这里发生了什么:
对于每个地区,我们逐月累加收入。
一旦对该地区完成累加,我们就转到下一个地区,从头开始,并再次逐月累加。
值得注意的是,在编写这些滚动函数时,我们有其他行的“上下文”。也就是说,要获得某一点的运行总和,我们必须知道前几行的前值。当我们知道可以手动选择要聚合的前/后多少行时,这一点就变得更加明显了。
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
AS useless_sum
FROM
sales
在这个查询中,我们指定要查看每一行后面的一行和前面的两行,这意味着我们可以得到这个范围的总和!根据你要解决的问题,这可能会非常强大,因为它可以让你完全控制如何对数据进行分组。
最后,在我们进入一个更复杂的示例之前,想要提到的最后一个函数是RANK函数。这个函数在面试中经常被问到,它背后的逻辑与我们迄今为止所学到的所有知识都是一样的。
SELECT
*,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) as rank,
RANK() OVER (ORDER BY revenue DESC) as overall_rank
FROM
sales
ORDER BY region, revenue DESC
与之前一样,我们使用ORDER BY来指定我们将逐行遍历的顺序,并使用PARTITION BY来指定子组。
第一列对每个地区内的每一行进行排名,这意味着数据集中将有多个“排名第一”的行。第二列计算的是数据集中所有行的排名。
五、前向填充缺失数据
这是一个时不时会出现的问题,要在SQL上解决这个问题,需要大量使用窗口函数。为了解释这个概念,我们将使用一个包含时间戳和温度测量值的不同数据集。我们的目标是用最后一次测量的值填充缺少温度测量值的行。
以下是我们期望最终得到的结果:
在开始之前,需要提一下,如果你使用的是Pandas,可以通过运行df.ffill()来简单地解决这个问题,但如果你使用的是SQL,问题就会变得有点棘手。
解决这个问题的第一步是以某种方式将NULL值与前一个非NULL值分组。可能不清楚我们如何做到这一点,但希望大家能清楚的是,这需要一个运行函数。也就是说,它是一个会“逐行遍历”的函数,知道我们何时遇到NULL值和非NULL值。
解决方案是使用COUNT函数,更具体地说,就是计算温度测量值的数量。在以下的查询中,同时运行了一个普通的累加计数和一个针对温度值的计数。
SELECT
*,
COUNT() OVER (ORDER BY timestamp) as normal_count,
COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor
在第一次计算中,我们简单地、逐行递增地计算了每一行。
在第二次计算中,我们计算了我们遇到的每个温度值的数量,但不计算NULL值。
normal_count列对我们来说是无用的,只是想展示一下运行COUNT的样子。然而,我们的第二个计算,即group_count计算,使我们离解决问题更近了一步!
请注意,这种计数方式确保了在NULL值开始之前的第一个值被计入,然后,每当函数遇到一个NULL值时,不会发生任何操作。这确保了我们用停止测量时的相同计数“标记”了每个后续的NULL值。
接下来,我们现在需要将被标记的第一个值复制到同一组中的所有其他行中。这意味着对于第2组,所有的值都应填充为15.0。
在这里,我们正在使用PARTITION BY进行简单的窗口聚合,当然,这个问题有不止一个答案。
SELECT
*,
FIRST_VALUE(temperature) OVER (PARTITION BY group_count) as filled_v1,
MAX(temperature) OVER (PARTITION BY group_count) as filled_v2
FROM (
SELECT
*,
COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor
) as grouped
ORDER BY timestamp ASC
我们可以使用FIRST_VALUE或MAX来实现我们的目标。唯一的目标是获取第一个非NULL值。由于我们知道每个组都包含一个非NULL值和一堆NULL值,所以这两个函数都可以使用!
这个示例是练习窗口函数的好方法。如果你想尝试类似的挑战,可以尝试添加两个传感器,然后用该传感器的上一个读数向前填充数值。类似于这样的情况:
到目前为止,我们已经了解了关于如何在SQL中使用窗口函数的所有知识,让我们快速回顾一下吧!
六、回顾时间
我们使用OVER关键字来编写窗口函数。
我们使用PARTITION BY来指定子组(窗口)。
如果我们只提供了OVER()关键字,我们的窗口就是整个数据集。
当我们想要运行一个连续的函数时,我们使用ORDER BY,这意味着我们的计算将逐行进行。
窗口函数在我们想要对数据进行分组聚合,但又想保持数据集的原样时非常有用。