PostgreSQL 教程: 在窗口函数上使用 RANGE 子句

摘要：在本教程中，您将学习一些在 PostgreSQL 中将 RANGE 子句与窗口函数结合使用的实际示例。

目录

RANGE 子句语法

这里谈论的RANGE子句，说的是 SQL 窗口函数中使用的子句，窗口函数具有以下语法：

OVER (  
  [ <PARTITION BY clause> ]
  [ <ORDER BY clause> ]
  [ <ROW or RANGE clause> ]
)

当你查看上面的语法时，你会发现ROW或者RANGE都可以是窗口函数的一部分。它们的语法如下：

[<ROWS or RANGE clause> BETWEEN <Start expr> AND <End expr>]

没有ORDER BY时的默认窗口框架是整个分区。但是，当您使用 ORDER BY 时，默认窗口框架为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

RANGE 和 ROW 子句是否相同？

两个子句并不相同。但是，它们具有相同的目的：指定了分区内的起点和终点，以限制行。但是，每个子句的作用都不同。ROW子句通过指定当前行之前或之后的固定行数，来实现此目的。

另一方面，RANGE子句是在逻辑上限制的行集；它指定的是与当前行的值相关的值范围。

可以在 RANGE 子句中使用哪些数据类型？

RANGE子句可以使用两种数据类型：数值和日期/时间类型。

现在让我们来看一些示例，看看在实践中RANGE是如何工作的！如果您不熟悉 SQL 窗口函数，也许您应该先了解下什么是窗口函数，以及它们与聚合函数的异同。

示例 1 – 计算运行汇总

我们将使用的数据在表 revenue 中。包括的列有：

• id – 数据的 ID 和表的主键（PK）。
• date – 收入日期。
• revenue_amount – 收入金额。

您的任务是，使用RANGE子句计算运行收入总额。让我们先在没有 SQL 的情况下做这件事。如果您有以下数据，则运行汇总（也称为累积总和）是多少？

2021-05-01 的运行汇总等于该日期的收入：12,573.25。这是因为计算中没有要包括的先前行。然后是 2021-05-02。汇总是今天的收入与前一天的收入相加：11,348.22 + 12,573.25 = 23,921.47。

请注意，还有另一行表示了 2021-05-02 的不同收入金额。也许这是针对另一个分支机构、国家、产品或其他任何东西。这里的计算方式相同：14,895.13 + 23,921.47 = 38,816.60。（即使有多个行具有相同的日期，RANGE 子句也是有效的。）接下来是 2021-05-03。此日期的汇总将为 14,388.14 + 38,816.60 = 53,204.74。最后，2021-05-04 的运行汇总将为 18,847.54 + 53,204 = 72,052.28。

如何使用RANGE子句来做同样的事情？你可以这样做：

SELECT
  id,
  date,
  revenue_amount,
  SUM(revenue_amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM revenue;

我们在列revenue_amount上使用了SUM()函数；这是获取运行汇总所需的操作。要使SUM()函数成为窗口函数，您需要使用OVER()子句。窗口函数的计算按升序完成；那是因为想要确保收入从最早的日期到最新的日期相加。然后是RANGE子句。它将窗口框架限制为当前日期之前的日期（BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）和当前日期。这些是将要包含在运行汇总计算中的行。

以下是运行查询时得到的结果：

请注意，如果一个日期（2021-05-02）有多个值，则查询会在该日期的运行汇总计算中包括这两行。这就是为什么该日期的running_total列中有 38,816.60 的原因。

示例 2 – 计算移动平均值

现在让我们来看看，如何将RANGE子句与日期/时间数据类型一起使用，并且没有 id 列来提供辅助！我们将使用一个略微修改的表，它现在称为**revenue_per_shop**。它包含了两个商店的收入数据。包括的列有：

• date – 收入日期。
• shop – 商店的名称。
• revenue_amount – 该商店在该日期的收入金额。

首先，让我们确保您已了解什么是移动平均值。两天移动平均值包括了当天和前一天。以下是一些示例数据，向您展示了移动平均值的工作原理：

2021-05-01 的两天移动平均值是当日收入本身：12,573.25。这是因为计算中没有其他行要包括。2021-05-02 的计算包括两个日期的收入：(12,573.25 + 11,348.22) / 2 = 11,960.74。其他行也遵循两个日期的相同计算逻辑：当前日期和前一个日期。

那么，您如何分别为每个商店计算相同的指标呢？可以像这样：

SELECT
  shop,
  date,
  revenue_amount,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date ASC
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

该查询首先从表中选定一些列。然后是有意思的部分。这里在列revenue_amount上使用了AVG()函数，因为我们想要平均收入。同样，这是一个窗口函数，因此它必须具有OVER()子句。我们使用PARTITION BY来指定要聚合数据的列；它是列shop，因为想要每个商店的移动平均值。该操作再次按日期排序。在RANGE子句中，指定了要包含在计算中的行集。由于这里处理的是日期，因此我们通过声明来获取上一个日期：BETWEEN INTERVAL '1' DAY PRECEDING。查询的输出结果如下：

示例 3 – 查找范围内的最后一个值

使用RANGE子句的这种方式，可以查找定义范围内的最后一个值。例如，使用表**revenue_by_shop**，我可以分别获取每个商店的最后一个值。在本例中，最后一个值是指最后一个可用数据，即 2021-05-15 的收入。您如何获取这些数据？

当然，通过使用RANGE子句：

SELECT
  shop,
  date,
  revenue_amount,
  LAST_VALUE(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM revenue_per_shop;

这次用到的窗口函数是LAST_VALUE()。再一次，我们在列revenue_amount上使用它。我们已按商店对数据进行了分区，这和之前相同。我们按日期进行排序，又和之前一样。为了获得最后一个值，这里使用了RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。请记住，带有ORDER BY子句时的默认范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果你不更改它，你会得到错误的结果。正确的结果是：

示例 4 – 查找范围内的条目数

这里还有另一种使用RANGE子句的有意思也有用的方法。您如何找到任何商店的日收入在当前值上下 1000（美元、欧元等）范围内的次数？

这样的 SQL 可能会有所帮助：

SELECT
  shop,
  date,
  revenue_amount,
  COUNT(*) OVER (
    ORDER BY revenue_amount ASC
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS number_of_days
FROM revenue_per_shop;

这里使用了COUNT()窗口函数。由于我们对按商店划分收入不感兴趣，因此没有PARTITION BY子句。计数会根据收入金额按升序进行。范围由RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING定义。

以下是查询将返回的结果：

让我来解释一下这个结果告诉了你什么。如果选取第一行，则列number_of_days中的结果为 2。有两条记录，收入介于 3,987.56 和 5,987.56 之间。为什么是这个范围？2021-05-13 的收入为 4,987.56。所以 4,987.56 - 1,000 = 3,987.56，而 4,987.56 + 1,000 = 5,987.56。您想检查结果吗？此范围之间有哪两条记录？显然，前两条：

是否要检查第二行？结果显示在 4,894.12 和 6,894.12 之间有三条记录，也就是这三条：

相同的逻辑适用于其余的数据。

示例 5 – 查找最大值

这并不像看起来那么简单；这里谈论不是常见的最大值。要找到它，您不需要RANGE子句。但是，如何找到 5 天内的最大值（在本例中为收入）呢？这 5 天将包括当前日期、当前日期前 3 天和当前日期后 1 天。在所有这些RANGE使用的示例之后，您可能已经知道了其中的逻辑。这就是解决方案：

SELECT
  shop,
  date,
  revenue_amount,
  MAX(revenue_amount) OVER (
    ORDER BY DATE
    RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
  ) AS max_revenue
FROM revenue_per_shop;

这里将MAX()函数用作为窗口函数。再一次，我们将它与列revenue_amount一起使用。OVER()子句中没有PARTITION BY，因为我对任何级别上分离数据都不感兴趣。定义范围并不难：RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING。这将包括当前日期、日期前 3 天和日期后 1 天。结果如下：

我们来检查一下2021-05-05的结果。它的范围是按日期从2021-05-02到2021-05-06。为了获得该范围内的最大收入，SQL 将比较这些值：14,388.14、18,847.54、9,845.29、14,574.56、11,500.63、16,897.21、9,634.56、14,255.87、11,248.33、21,489.22。哪一个最高？它是 21,489.22。