使用窗口函数指南

在BigQuery中轻松创建累计总计、移动平均值和排名。

如果你曾经搜索过或偶然发现过类似于“6个你需要了解的SQL技能，以通过面试”或者“我多年前就该了解的SQL概念”。很有可能，在这些列表中，窗口函数在某个地方得到了应有的提及。

窗口函数真是太棒了。

本文旨在帮助你理解这些窗口函数以及如何使用它们。在我们介绍完教程后，我准备了一些用例供你在项目中玩耍，因为我在这些示例中使用了公共数据。

我们将讨论以下内容：

• 什么是 窗口函数？
• 窗口函数的语法 —— 分区、排序和帧部分
• 如何创建一个7天移动平均值以及它的工作原理
• 你可以使用哪些 聚合 和 窗口函数？
• 最后，我们将通过一些用例演示如何应用窗口函数。

什么是窗口函数？

在SQL中使用“窗口”这个术语可能看起来有些奇怪（或者在计算机领域中）。通常，函数类型的名称会让你对它们的用法有所了解，比如：

• 聚合函数 —— 以一堆东西为输入，给出一个总结它们所有内容的结果
• 字符串函数 —— 提供一整套操作单词和句子的方法
• 数组函数 —— 一次处理一个集合或一组项目

等等……

那么，在SQL中，窗口函数是什么？就像现实世界中的窗户一样，它允许你查看一个特定的区域，而其余部分则不可见。你只专注于窗户所显示的内容。

回到数据世界，假设你有一个包含爱荷华州酒类店每月销售数据的表。

本示例使用的数据集是公开可访问的，由Google提供，并且在BigQuery中已经存在，如果你想自己尝试这些示例的话（链接）。

bigquery-public-data.iowa_liquour_sales.sales

下面的示例提供了按年份和月份划分的销售简单视图。

我将上面的内容保存为视图，以便我们未来的查询尽可能简化，集中关注应用窗口函数上。

如果你想使用这个视图，可以使用spreadsheep-20220603.Dashboard_Datasets.iowa_liqour_monthly_sales。

如果我们还想为每年单独添加一个月度平均销售额的列呢？

有几种方法可以实现这一点，如果你对窗口函数还不熟悉，你可以尝试将平均值计算为子查询，然后与原始表连接，如下所示。

这种方法完全可行，但是使用窗口函数可以让你在不使用子查询的情况下得到相同的答案！

上面的窗口函数允许我们对特定的行组执行聚合函数，例如avg函数，这些行组由按年分组的定义。

回想一下之前的窗口类比，按年分组部分在这种情况下就是我们的窗口。当然，我们面前有整个数据集，但是分组限制了我们只看到一年的数据。

现在是深入语法的时候了。

窗口语法

在上面的例子中，我们可以将函数分为两部分，函数名和窗口。

在这种情况下，函数名是熟悉的聚合函数AVG。然而，窗口部分有一点不同。

一旦指定了函数，您就可以使用over关键字开始您的窗口函数，后面必须跟着括号 ()。

在括号内，您可以使用partition by关键字指定要使用的窗口，后面跟着一个列的列表。在这里，我们只包括了一列year，但稍后我们将加入另一列。

partition by是可选的；如果您不包括partition by，聚合将包含数据集中的所有行。由于它存在于SELECT语句中，值得注意的是WHERE子句将在此窗口函数之前执行。

我是什么意思呢？使用我之前分享的例子，我使用partition by year指定了一个窗口。然而，在我的WHERE子句中，我设置了一个过滤器，只返回year = 2022的行。

这意味着数据集只有一个年份可见——2022年，当窗口函数运行时。因此，我的partition by year窗口是多余的，在这种情况下使用下面的语句将得到相同的结果。

让我们重新运行之前的查询，并且这次移除我们的WHERE子句。

在这里，我们可以看到2023年和2022年的不同值。现在显示了每年的平均月销售额。

例如，在第7行中，我们有2022年的平均每月销售额为3570万，而2023年（到目前为止）的平均每月销售额为3580万。

访问每月平均数据可以更容易地可视化和分析销售趋势。特别是，可以明显看出一年的下半年对销售额贡献很大。

我们使用窗口函数来计算每年的平均月销售额。然后，将该函数的结果应用于具有该年份的所有行。这就像之前我们看到的左连接子查询。

Order By

到目前为止，我们主要关注了聚合函数以及如何指定窗口。我们还可以确定窗口应该按照什么顺序执行任务，这是排序或运行总计/平均值解决方案的关键部分。

回到爱荷华数据集，让我们扩展我们的视图，包括store_name，并根据它们的总销售额为商店进行月度排名。

新视图

spreadsheep-20220603.Dashboard_Datasets.iowa_liqour_monthly_sales_inc_store

与聚合不同，对于排名函数（排名函数是窗口函数的特有函数），您不需要在函数本身内指定列。

然而，如果您尝试按照上述方式运行此代码，您将会收到一个错误。

问题在于我们告诉Bigquery我们想要为结果排序，但我们没有指定它们应如何排序，我们可以使用ORDER BY来实现。

这为我们提供了一个按店铺级别排列的月度销售视图，每个店铺都有一个排名。然后，您可以进一步回答其他问题，例如2022年每个月的前3家商店是哪些？

在本文末尾的示例之一中，我们将使用一个名为QUALIFY的新子句，它允许您轻松地对窗口函数给出的结果进行过滤。

到目前为止，我们的窗口函数适用于每个分区中的所有行，但如果我们只想要分区的一部分呢？例如，过去七天的平均每日销售额？为此，我们需要指定一个窗口框架。

窗口框架

是时候引入一个新的数据集，介绍一下芝加哥出租车！这是另一个公开数据集（CC0许可证），如果您想要尝试，可以使用它。（链接）

bigquery-public-data.chicago_taxi_trips.taxi_trips

公共数据集非常大，占用了您每月免费的100GB查询限额。因此，我创建了一个新表，只保存了2023年的数据，这样我们就可以在不产生大量费用的情况下玩弄数据。

我已经公开了这个表，所以我建议您尝试我的数据集进行测试。

spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data

无论如何，回到主题…什么是窗口框架？这个子句允许我们定义在分区内需要使用的行或范围。这个子句的一个常见用途是创建移动平均。

SELECT  date(trip_start_timestamp) as trip_start_date,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-05-01" and "2023-06-30"GROUP BY  trip_start_dateORDER BY  trip_start_date DESC

这个查询提供了2023年5月至6月之间的日期收入。

移动平均在时间序列数据中非常常见，因为它可以让您轻松地将特定日期或事件月份的性能与给定期间的典型结果进行比较。

首先，让我们创建一个简单的移动平均值，并且为了避免重复的日期转换和收入取整，我将我们最初的查询放在了一个CTE（公共表达式）中。

WITH daily_data as(SELECT  date(trip_start_timestamp) as trip_start_date,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-05-01" and "2023-06-30"GROUP BY  trip_start_dateORDER BY  trip_start_date DESC)SELECT  trip_start_date,  trip_total_revenue,  avg(trip_total_revenue) over (order by trip_start_date asc) as moving_averageFROM  daily_data

如果我们看前五行，我们可以看到第一个平均值等于trip_total_revenue。这是因为它是窗口的起始，我们按照trip_start_date按升序排序了数据。因此，还没有什么可以进行平均计算。

然而，现在我们有了第二行的第一行和第二行之间的日均值。我们有了第三行的第一行、第二行和第三行之间的日均值。

这是一个很好的开始，显示我们的移动平均值在起作用，但让我们更进一步。让我们创建一个移动平均值，它只包括过去七天的收入，如果窗口不包含七天，则显示空值。

为了指定窗口范围，有三个关键字需要记住：

• 当前行
• 向前
• 向后

然后，您可以使用行或范围构建窗口（稍后我将解释两者之间的差异），后面跟随between <<start>>和<<end>>。

rows between 7 preceding and one preceding

上面的示例是我们问题所需的窗口帧。我们指定窗口从当前行之前的七行开始，到当前行之前的一行结束。

下面是一个简单的示例，演示了如何在此窗口框架中使用sum聚合函数。

select  numbers,  sum(numbers) over   (    order by numbers asc     rows between 7 preceding and one preceding  )   as moving_sum_sevenfrom  test_data

如您所见，当我们到达第八行时，移动总和的值达到7，窗口现在包含七行数据。如果将窗口切换到6个前面的行和当前行，您将看到窗口已经移动以包括当前行。

在本节结束时，我将提供一些用例示例，以突出它们的用途，但现在回到手头的任务！

让我们将窗口范围放入我们的移动平均计算。

with daily_data as (SELECT  date(trip_start_timestamp) as trip_start_date,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-05-01" and "2023-06-30"GROUP BY  trip_start_dateORDER BY  trip_start_date DESC)SELECT  trip_start_date,  trip_total_revenue,  avg(trip_total_revenue) over (order by trip_start_date asc rows between 7 preceding and one preceding) as moving_averageFROM  daily_dataORDER BY  trip_start_date DESC

现在我们还有一个最后的挑战，如果窗口包含少于七行数据，我们如何使值为空？嗯，我们可以使用IF语句来检查。

  if  (    COUNT(*) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) = 7,    AVG(trip_total_revenue) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING),    NULL   ) AS moving_average

我们引入了第二个窗口函数，它计算窗口框架中存在多少行，如果等于7，将提供移动平均值的结果。

ROWS和RANGE的区别

在SQL中，ROWS和RANGE子句都有助于控制窗口函数在分组内使用哪些行。

ROWS子句适用于固定数量的行。它计算当前行之前或之后的特定数量的行，无论它们的值如何。这些行包含在窗口函数中。

RANGE子句根据行的值来处理行。它考虑相对于当前行的特定范围内具有值的行。实际值确定哪些行包含在窗口函数的计算中。

因此，虽然ROWS子句关注行的物理位置，RANGE子句则根据行的逻辑值来确定它们是否包含在窗口函数中。

试试下面的例子来看它的运行情况

with sales_data as (SELECT'2023-01-01' AS DATE, 100 AS SALESUNION ALLSELECT'2023-01-02' AS DATE, 50 AS SALESUNION ALLSELECT'2023-01-03' AS DATE, 250 AS SALESUNION ALLSELECT'2023-01-03' AS DATE, 200 AS SALESUNION ALLSELECT'2023-01-04' AS DATE, 300 AS SALESUNION ALLSELECT'2023-01-05' AS DATE, 150 AS SALES)SELECT  *,  SUM(SALES) OVER (ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_rows,  SUM(SALES) OVER (ORDER BY DATE RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_rangeFROM sales_data

仔细观察第3行和第4行，以比较这两个子句。ROWS子句将每一行都加入到总计中，即使有重复的销售日期。但是，使用RANGE子句，具有相同销售日期的行将作为一个范围进行分组。例如，在这种情况下，所有日期为2023–01–03的行将被视为一个范围。

窗口函数是什么？

有很多函数可以与窗口函数一起使用。

对于聚合函数，您可以尝试：

• SUM：计算数值列的总和。
• AVG：计算数值列的平均值。
• MIN：从列中检索最小值。
• MAX：从列中检索最大值。
• COUNT：计算列中的行数。
• COUNT DISTINCT：计算列中不同值的数量。

然后，您有一堆窗口函数独有的新函数，称为分析函数：

• ROW_NUMBER：为窗口帧内的每一行分配一个唯一数字。
• RANK：根据窗口帧中指定的顺序为每一行分配一个排名。
• DENSE_RANK：根据窗口帧中指定的顺序为每一行分配一个排名，没有间隔。
• LAG：从窗口帧内的前一行检索值。
• LEAD：从窗口帧内的后一行检索值。
• FIRST_VALUE：从窗口帧内的第一行检索值。
• LAST_VALUE：从窗口帧内的最后一行检索值。

上述函数的名称都链接到BigQuery的文档。

工作示例

每日累积总额

窗口函数的一个较简单的用例是累积总额。对于芝加哥出租车数据集，我们可以按月份计算收入，但需要一个新的列来跟踪到目前为止全年的总收入。

with daily_data as (SELECT  date(timestamp_trunc(trip_start_timestamp,month)) as trip_month,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-01-01" and "2023-06-30"GROUP BY  trip_monthORDER BY  trip_month DESC)SELECT  trip_month,  trip_total_revenue,  round(sum(trip_total_revenue) over (order by trip_month asc),2) AS running_total_revenue,FROM  daily_dataORDER BY  trip_month DESC

12周移动平均

本文的教程重点介绍了在处理时间序列数据时移动平均值非常常见。

with daily_data as (SELECT  date(timestamp_trunc(trip_start_timestamp,week(monday))) as trip_week,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-01-01" and "2023-06-30"GROUP BY  trip_weekORDER BY  trip_week DESC)SELECT  trip_week,  trip_total_revenue,  if  (    COUNT(*) OVER (ORDER BY trip_week ASC ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING) = 12,    AVG(trip_total_revenue) OVER (ORDER BY trip_week ASC ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING),    NULL   ) AS moving_averageFROM  daily_dataORDER BY  trip_week DESC

将收入与移动平均线绘制在一起，可以看出正趋势，因为自四月以来，移动平均线每周都在持续上升。如果没有移动平均线，我们的注意力可能会被低表现的周所吸引，而忽视了更大的趋势。

计算异常检测的Z分数

Z分数计算 = (x – 平均值) / 标准差

Z分数是一种衡量某个数字与其他数字组的异常程度或典型程度的方法。它告诉您一个特定数字与该组的平均值相比的标准差有多远。

  if  (    COUNT(*) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) = 30,    round    (      (        trip_total_revenue -         AVG(trip_total_revenue) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)      ) / stddev(trip_total_revenue) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)    ,1),    NULL   ) AS z_score_30_day

在这个例子中，我们取了trip_total_revenue的实际值，并减去了过去30天内我们观察到的平均每日收入。

然后，我们将该数字除以这30天的标准差。这告诉我们一个特定收入日期与平均值的接近程度，或者该值与平均值相比的标准差数。

这是一个方便的度量指标，可以在图表上进行绘制，如下所示，它可以为数据提供上下文。尽管我们只能看到最近的30天，但z分数可以轻松地与前30天进行比较，我们可以看到峰值和低谷似乎不重要，直到z分数突出了该天与正常情况相比的差异。

通过这些报告，您应该设置一个建议您发生异常事件的值。我不会说上面图表中的任何日期都是异常的，但一个典型的做法是使用一个值为3（即三个标准差）。然而，这完全取决于您的数据的波动性。

完整查询

with daily_data as (SELECT  (trip_start_timestamp) as trip_start_date,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-01-01" and "2023-06-30"GROUP BY  trip_start_dateORDER BY  trip_start_date DESC)SELECT  trip_start_date,  trip_total_revenue,  if  (    COUNT(*) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) = 30,    round    (      (        trip_total_revenue -         AVG(trip_total_revenue) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)      ) / stddev(trip_total_revenue) OVER (ORDER BY trip_start_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)    ,1),    NULL   ) AS z_score_30_dayFROM  daily_dataORDER BY  trip_start_date DESC

每月排名前三的表现优秀公司

芝加哥出租车数据集中有许多出租车公司，我们可能会问自己每个月的前三个表现优秀的公司是哪些。

为了实现这一点，我们可以使用排名分析函数，按照trip_month进行分区，并按照trip_total_revenue降序排序。

 rank() over (partition by trip_month order by trip_total_revenue desc) AS ranking

然而，这仍然会为数据集中的所有公司提供每个月的结果，而不仅仅是前三名。因此，我们可以使用QUALIFY子句，它类似于WHERE子句，允许您过滤数据。

qualify子句只能与窗口函数一起使用，并且可以引用您在select语句中创建的窗口函数。更多详细信息请参见此处。

下面的结果清楚地表明，三家主要公司正在主导出租车行业。

with daily_data as (SELECT  date(timestamp_trunc(trip_start_timestamp,month)) as trip_month,  company,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-01-01" and "2023-06-30"GROUP BY  trip_month,  companyORDER BY  trip_month DESC)SELECT  trip_month,  company,  trip_total_revenue,  rank() over (partition by trip_month order by trip_total_revenue desc) AS rankingFROM  daily_dataQUALIFY  ranking <= 3ORDER BY  trip_month DESC

月度/季度对比

月度和季度报告对于跟踪关键绩效指标以及帮助判断企业发展方向至关重要。然而，在BigQuery中创建报告（提供月度对比）可能会变得棘手，一旦你知道如何操作。

一旦你的数据达到所需的级别，比如在下面的示例中是按月统计的，你可以使用LAG或LEAD函数返回上个月的收入，从而可以计算出百分比差异。

你可以使用LAG或LEAD函数，两者在数据排序方式不同的情况下都可以实现相同效果。由于我们要获取上个月的收入数据，因此在这里使用LAG函数更合理一些。

with daily_data as (SELECT  date(timestamp_trunc(trip_start_timestamp,month)) as trip_month,  round(sum(trip_total),2) as trip_total_revenueFROM  `spreadsheep-case-studies-2023.chicago_taxies_2023.trip_data`WHERE  date(trip_start_timestamp) between "2023-01-01" and "2023-06-30"GROUP BY  trip_monthORDER BY  trip_month DESC)SELECT  trip_month,  trip_total_revenue,  lead(trip_total_revenue) over (order by trip_total_revenue asc) AS previous_month_revenue,  round  (    (      (        trip_total_revenue - lag(trip_total_revenue) over (order by trip_total_revenue asc)      ) / lag(trip_total_revenue) over (order by trip_total_revenue asc)    ) * 100  , 1) || "%" AS perc_changeFROM  daily_dataORDER BY  trip_month DESC

本文到此结束。如果你有任何问题或挑战，请随时留言，我会尽快回答。

我经常为BigQuery和Looker Studio撰写文章。如果你感兴趣，请在VoAGI上关注我，以获取更多内容！

所有图片（除非另有说明）均由作者提供。

保持高雅，朋友们！Tom