PostgreSQL 的高级玩法，用滑动窗口做运行式统计

介绍

SQL 窗口函数对于计算移动的平均值或运行中的总计等复杂聚合计算非常有用。ROWS 子句允许您为计算指定行，从而可以启用更复杂的窗口框架。以下是在 SQL 中使用 ROWS BETWEEN 子句的 5 个实用示例。

窗口函数（也称为 OVER 函数）可根据滑动窗口框架（即一组行）计算其结果。它们类似于聚合函数，因为您可以计算一组行的平均值、总和值或最小值/最大值。但是，存在一些重要的差异：

• 窗口函数不会像聚合函数那样折叠行。因此，您仍然可以将单个行中的属性与窗口函数的结果混合。

• 窗口函数允许滑动窗口框架，这意味着，对于每个单独的行，用于计算窗口函数的行集可能都不同。

窗口函数的语法如下所示：

SELECT <column_1>, <column_2>,
  OVER (
    PARTITION BY <...>
    ORDER BY <...>
        <window_frame>) <window_column_alias>
FROM <table_name>;

当您在 SELECT 语句中使用窗口函数时，您基本上在使用这个函数计算出另一列：

• 首先指定一个函数（例如AVG()、SUM()、或COUNT()）。

• 然后，使用OVER关键字定义一组行。您还可以选择这样：

用PARTITION BY对行进行分组，以便在这些组内计算函数，而不是在整个行集中计算函数。

如果行的顺序很重要（例如，在计算汇总时），可使用 ORDER BY 对窗口框架内的行进行排序。

指定窗口框架与当前行的关系（例如，框架应该是当前行和前两行，或当前行和所有后续行等）。

窗口框架是使用ROWS、RANGE和GROUPS子句定义的。在本文中，我们将重点介绍ROWS子句及其选项。

ROWS 子句：语法和选项

ROWS 子句的用途是指定与当前行相关的窗口框架。语法为：

ROWS BETWEEN lower_bound AND upper_bound

界限可以是以下五个选项中的任何一个：

• UNBOUNDED PRECEDING – 当前行之前的所有行。

• n PRECEDING – 当前行之前的 n 行。

• CURRENT ROW – 仅当前行。

• n FOLLOWING – 当前行后的 n 行。

• UNBOUNDED FOLLOWING – 当前行之后的所有行。

图片

以下是使用ROWS子句定义窗口框架时需要记住的几点：

• 窗口框架在每个分区中单独计算。

• 默认选项取决于您是否使用ORDER BY：

带有ORDER BY时，默认框架为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

没有ORDER BY时，则默认框架为ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

• 如果其中一个边界是当前行，则可以跳过指定此界限，并使用较短版本的窗口框架定义：

UNBOUNDED PRECEDING与BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。

n PRECEDING与BETWEEN n PRECEDING AND CURRENT ROW相同。

n FOLLOWING与BETWEEN CURRENT ROW AND n FOLLOWING相同。

UNBOUNDED FOLLOWING与BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING相同。

让我们来看一些示例，看看它在实践中是如何工作的。

在窗口函数中使用 ROWS 子句的示例

示例 1

为了开始使用ROWS子句，我们将使用下表，其中有一个书店的销售数据。

表 sales

在第一个示例中，我们想要添加另一列，该列显示从第一行日期到当前行日期的总收入（即运行总计）。以下是我们可以使用的查询：

SELECT date, revenue,
    SUM(revenue) OVER (
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM sales
ORDER BY date;

要使用窗口函数计算运行总计，我们执行以下步骤：

• 使用SUM()聚合函数计算总收入。

• 按日期对窗口框架中的记录进行排序（默认为升序），因为在计算汇总时，行的顺序很重要。

• 通过将下限定义为UNBOUNDED PRECEDING和上限为CURRENT ROW来指定窗口框架。这将包括当前行之前的所有行（包括当前行）。请注意，这种情况和没有指定ROWS子句的默认行为是相同的。默认框架使用RANGE，而不是ROWS。由于每天的记录在表中只出现一次，因此RANGE和ROWS的结果将相同。因此，我们也可以使用以下查询，来获得相同的结果：

SELECT date, revenue,
    SUM(revenue) OVER (
      ORDER BY date) running_sum
FROM sales
ORDER BY date;

如您所见，查询按预期工作，我们在第三列中获得了汇总。第一天，它等于当天的销售额，即 1515.45 美元；第二天，它等于第一天和第二天的销售额总和，即 3860.80 美元；在下一行中，我们得到前三天的销售额总和，即 4764.79 美元，依此类推。

在接下来的示例中，我们将看到当记录被分成几组时，ROWS子句是如何工作的。

示例 2

在接下来的几个例子中，我们将使用下表。它包含两个中国城市（Shanghai 和 Beijing）连续五天的平均温度（以 °C 为单位）和总降水量（以毫米为单位），这些只是虚构数据。

表 weather

我们想分别计算每个城市的三天移动平均温度。为了分隔两个城市的计算，我们将包含子句PARTITION BY。然后，在指定窗口框架时，我们将考虑当天和前两天：

另请注意，我们已将窗口函数放在 ROUND() 函数中，以便将三天的移动平均值四舍五入到小数点后一位。结果如下：

Beijing 和 Shanghai 的移动平均值是分别计算的。对于 9 月 1 日，移动平均值等于日平均温度，因为我们之前没有任何记录。然后，在 9 月 2 日，移动平均值计算为 1 日和 2 日的平均温度（Beijing 为 17.5 °C，Shanghai 为 18.8 °C）。9 月 3 日，我们终于有足够的数据来计算三天（前两天和今天）的平均温度，结果是 Beijing 为 18.0 °C，Shanghai 为 19.2 °C。然后，将 9 月 4 日的三天移动平均值计算为 2 日、3 日和 4 日的平均温度，依此类推。

还有一点需要注意：窗口框架中的记录顺序，在指定要计算的行方面起着关键作用。

在上面的查询中，我们已按日期升序（使用默认设置）对窗口框架中的记录进行排序，即我们从最早的日期开始。然后，为了将当前日期的前两天包含在我们的计算中，我们将下限设置为2 PRECEDING。

但是，我们可以通过按降序对记录进行排序，然后将ROWS选项更改为包括2 FOLLOWING（后两天）而不是2 PRECEDING（前两天）：

SELECT city, date, temperature,
    ROUND(AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING), 1) mov_avg_3d_city
FROM weather
ORDER BY city, date;

此查询输出了完全相同的结果。

示例 3

在此示例中，我们将分别计算两个城市过去三天的总降雨量（即三天的运行总降雨量）。

SELECT city, date, precipitation,
    SUM(precipitation) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS 2 PRECEDING) running_total_3d_city
FROM weather
ORDER BY city, date;

在此查询中，我们再次按城市对数据进行分区。我们使用SUM()函数来计算过去三天（包括当天）的总降雨量。另请注意，我们使用了缩写方式来定义窗口框架，只指定了下限：2 PRECEDING。

下面是上述查询的输出：

截至 9 月 3 日，我们得到 Beijing 连续三天的降雨总量：30 毫米。这是 9 月 1 日降雨量 5 毫米、第 2 天降雨量 15 毫米和第 3 天降雨量 10 毫米之和。

您知道我们是如何在 9 月 5 日为 Shanghai 获得 12 毫米的总降雨量的吗？请尝试根据我们输出表中的结果进行思考，以确保您了解窗口函数如何与特定窗口框架一起工作。

现在让我们继续来看一些新的数据和示例。

示例 4

在接下来的两个示例中，我们将使用如下所示的数据。它包括有关三个社交网络（Instagram、Facebook 和 LinkedIn）新订阅者数量的每日信息。

表 subscribers

我们首先分别计算每个网络的新订阅者数量的运行总数。基本上，对于每一天，我们想看看，从我们开始收集数据到当前行的日期内，有多少人订阅了。

下面是满足此请求的 SQL 查询：

SELECT social_network, date, new_subscribers,
    SUM(new_subscribers) OVER (
      PARTITION BY social_network
      ORDER BY date
      ROWS UNBOUNDED PRECEDING) running_total_network
FROM subscribers
ORDER BY social_network, date;

我们首先使用 SUM() 聚合函数计算新订阅者的总数。然后，我们使用 PARTITION BY 子句计算每个网络的单独总数。我们还按日期升序（默认排序）对记录进行排序。最后，我们将窗口框架定义为 UNBOUNDED PREDEDED，以包括当前记录之前的所有记录。

输出如下所示：

在结果表中，您可以看到，对于每个新记录，如何将新订阅者的数量添加到累积总数中。按照窗口函数中的指定，将为每个网络单独计算运行总计。

示例 5

在最后一个示例中，我们来演示下，如何使用窗口函数和 ROWS 子句，显示一组特定记录的第一个值和最后一个值。这一次，让我们在输出中添加两列：

• 第一天添加的新订阅者数量，以及

• 最后一天添加的新订阅者数量。

通过为每个社交网络单独计算这些信息，比较我们开始的地方和现在的情况，我们可以看到每天的表现。

以下是获取所需输出的 SQL 查询：

SELECT social_network, date, new_subscribers,
    FIRST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date) AS first_day,
    LAST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day
FROM subscribers
ORDER BY social_network, date;

如您所见，我们使用FIRST_VALUE()和LAST_VALUE()函数，分别获取第一天和最后一天的信息。另请注意，我们如何为每个函数指定窗口框架：

• 我们没有在 FIRST_VALUE() 函数上包含 ROWS 子句，因为默认行为（即RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）就符合我们的要求。

• 但是，我们确实在LAST_VALUE()函数指定了窗口框架，因为默认行为将使用当前行值作为每条记录的最后一个值；这不是我们在此示例中要获得的内容。我们指定窗口框架ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，以确保计算所有记录。

下面是结果集：

根据要求，我们为每个社交网络分别计算了第一天和最后一天的新订阅者数量。