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

九月 18, 2024

摘要:在本教程中,您将学习一些在 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 的情况下做这件事。如果您有以下数据,则运行汇总(也称为累积总和)是多少?

id date revenue_amount running_total
1 2021-05-01 12,573.25 12,573.25
2 2021-05-02 11,348.22 23,921.47
3 2021-05-02 14,895.13 38,816.60
4 2021-05-03 14,388.14 53,204.74
5 2021-05-04 18,847.54 72,052.28

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)和当前日期。这些是将要包含在运行汇总计算中的行。

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

id date revenue_amount running_total
1 2021-05-01 12,573.25 12,573.25
2 2021-05-02 11,348.22 38,816.60
3 2021-05-02 14,895.13 38,816.60
4 2021-05-03 14,388.14 53,204.74
5 2021-05-04 18,847.54 72,052.28
6 2021-05-05 9,845.29 81,897.57
7 2021-05-06 14,574.56 96,472.13
8 2021-05-07 11,500.63 107,972.76
9 2021-05-08 16,897.21 124,869.97
10 2021-05-09 9,634.56 134,504.53
11 2021-05-10 14,255.87 148,760.40
12 2021-05-11 11,248.33 160,008.73
13 2021-05-12 21,489.22 181,497.95
14 2021-05-13 14,448.65 195,946.60
15 2021-05-14 15,517.22 211,463.82
16 2021-05-15 6,874.23 218,338.05
17 2021-05-16 12,500.00 230,838.05
18 2021-05-17 9,784.33 240,622.38
19 2021-05-18 15,321.89 255,944.27
20 2021-05-19 12,235.50 268,179.77
21 2021-05-20 22,222.22 290,401.99
21 2021-05-21 14,800.65 305,202.64
22 2021-05-22 5,894.12 311,096.76
23 2021-05-23 18,845.69 329,942.45
24 2021-05-24 9,966.66 339,909.11
25 2021-05-25 13,250.69 353,159.80
26 2021-05-26 4,987.56 358,147.36
27 2021-05-27 17,784.25 375,931.61
28 2021-05-28 12,567.45 388,499.06
29 2021-05-29 19,874.26 408,373.32
30 2021-05-30 15,489.36 423,862.68
31 2021-05-31 14,987.55 438,850.23

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

示例 2 – 计算移动平均值

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

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

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

date revenue_amount moving_avg
2021-05-01 12,573.25 12,573.25
2021-05-02 11,348.22 11,960.74
2021-05-03 14,388.14 12,868.18
2021-05-04 18,847.54 16,617.84

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。查询的输出结果如下:

shop date revenue_amount moving_avg
Shop 1 2021-05-01 12,573.25 12,573.25
Shop 1 2021-05-02 14,388.14 13,480.70
Shop 1 2021-05-03 9,845.29 12,116.72
Shop 1 2021-05-04 11,500.63 10,672.96
Shop 1 2021-05-05 9,634.56 10,567.60
Shop 1 2021-05-06 11,248.33 10,441.45
Shop 1 2021-05-07 14,448.65 12,848.49
Shop 1 2021-05-08 6,874.23 10,661.44
Shop 1 2021-05-09 9,784.33 8,329.28
Shop 1 2021-05-10 12,235.50 11,009.92
Shop 1 2021-05-11 14,800.65 13,518.08
Shop 1 2021-05-12 18,845.69 16,823.17
Shop 1 2021-05-13 13,250.69 16,048.19
Shop 1 2021-05-14 17,784.25 15,517.47
Shop 1 2021-05-15 19,874.26 18,829.26
Shop 2 2021-05-01 11,348.22 11,348.22
Shop 2 2021-05-02 18,847.54 15,097.88
Shop 2 2021-05-03 14,574.56 16,711.05
Shop 2 2021-05-04 16,897.21 15,735.89
Shop 2 2021-05-05 14,255.87 15,576.54
Shop 2 2021-05-06 21,489.22 17,872.55
Shop 2 2021-05-07 15,517.22 18,503.22
Shop 2 2021-05-08 12,500.00 14,008.61
Shop 2 2021-05-09 15,321.89 13,910.95
Shop 2 2021-05-10 22,222.22 18,772.06
Shop 2 2021-05-11 5,894.12 14,058.17
Shop 2 2021-05-12 9,966.66 7,930.39
Shop 2 2021-05-13 4,987.56 7,477.11
Shop 2 2021-05-14 12,567.45 8,777.51
Shop 2 2021-05-15 15,489.36 14,028.41

示例 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。如果你不更改它,你会得到错误的结果。正确的结果是:

shop date revenue_amount last_value
Shop 1 2021-05-01 12,573.25 19,874.26
Shop 1 2021-05-02 14,388.14 19,874.26
Shop 1 2021-05-03 9,845.29 19,874.26
Shop 1 2021-05-04 11,500.63 19,874.26
Shop 1 2021-05-05 9,634.56 19,874.26
Shop 1 2021-05-06 11,248.33 19,874.26
Shop 1 2021-05-07 14,448.65 19,874.26
Shop 1 2021-05-08 6,874.23 19,874.26
Shop 1 2021-05-09 9,784.33 19,874.26
Shop 1 2021-05-10 12,235.50 19,874.26
Shop 1 2021-05-11 14,800.65 19,874.26
Shop 1 2021-05-12 18,845.69 19,874.26
Shop 1 2021-05-13 13,250.69 19,874.26
Shop 1 2021-05-14 17,784.25 19,874.26
Shop 1 2021-05-15 19,874.26 19,874.26
Shop 2 2021-05-01 11,348.22 15,489.36
Shop 2 2021-05-02 18,847.54 15,489.36
Shop 2 2021-05-03 14,574.56 15,489.36
Shop 2 2021-05-04 16,897.21 15,489.36
Shop 2 2021-05-05 14,255.87 15,489.36
Shop 2 2021-05-06 21,489.22 15,489.36
Shop 2 2021-05-07 15,517.22 15,489.36
Shop 2 2021-05-08 12,500.00 15,489.36
Shop 2 2021-05-09 15,321.89 15,489.36
Shop 2 2021-05-10 22,222.22 15,489.36
Shop 2 2021-05-11 5,894.12 15,489.36
Shop 2 2021-05-12 9,966.66 15,489.36
Shop 2 2021-05-13 4,987.56 15,489.36
Shop 2 2021-05-14 12,567.45 15,489.36
Shop 2 2021-05-15 15,489.36 15,489.36

示例 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定义。

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

shop date revenue_amount number_of_days
Shop 2 2021-05-13 4,987.56 2
Shop 2 2021-05-11 5,894.12 3
Shop 1 2021-05-08 6,874.23 2
Shop 1 2021-05-05 9,634.56 4
Shop 1 2021-05-09 9,784.33 4
Shop 1 2021-05-03 9,845.29 4
Shop 2 2021-05-12 9,966.66 4
Shop 1 2021-05-06 11,248.33 4
Shop 2 2021-05-01 11,348.22 4
Shop 1 2021-05-04 11,500.63 5
Shop 1 2021-05-10 12,235.50 7
Shop 2 2021-05-08 12,500.00 6
Shop 2 2021-05-14 12,567.45 5
Shop 1 2021-05-01 12,573.25 5
Shop 1 2021-05-13 13,250.69 4
Shop 2 2021-05-05 14,255.87 5
Shop 1 2021-05-02 14,388.14 6
Shop 1 2021-05-07 14,448.65 6
Shop 2 2021-05-03 14,574.56 8
Shop 1 2021-05-11 14,800.65 8
Shop 2 2021-05-09 15,321.89 7
Shop 2 2021-05-15 15,489.36 5
Shop 2 2021-05-07 15,517.22 5
Shop 2 2021-05-04 16,897.21 2
Shop 1 2021-05-14 17,784.25 2
Shop 1 2021-05-12 18,845.69 2
Shop 2 2021-05-02 18,847.54 2
Shop 1 2021-05-15 19,874.26 1
Shop 2 2021-05-06 21,489.22 2
Shop 2 2021-05-10 22,222.22 2

让我来解释一下这个结果告诉了你什么。如果选取第一行,则列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。您想检查结果吗?此范围之间有哪两条记录?显然,前两条:

shop date revenue_amount number_of_days
Shop 2 2021-05-13 4,987.56 2
Shop 2 2021-05-11 5,894.12 3

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

shop date revenue_amount number_of_days
Shop 2 2021-05-13 4,987.56 2
Shop 2 2021-05-11 5,894.12 3
Shop 1 2021-05-08 6,874.23 2

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

示例 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 天。结果如下:

shop date revenue_amount max_revenue
Shop 1 2021-05-01 12,573.25 18,847.54
Shop 2 2021-05-01 11,348.22 18,847.54
Shop 1 2021-05-02 14,388.14 18,847.54
Shop 2 2021-05-02 18,847.54 18,847.54
Shop 1 2021-05-03 9,845.29 18,847.54
Shop 2 2021-05-03 14,574.56 18,847.54
Shop 1 2021-05-04 11,500.63 18,847.54
Shop 2 2021-05-04 16,897.21 18,847.54
Shop 1 2021-05-05 9,634.56 21,489.22
Shop 2 2021-05-05 14,255.87 21,489.22
Shop 1 2021-05-06 11,248.33 21,489.22
Shop 2 2021-05-06 21,489.22 21,489.22
Shop 2 2021-05-07 15,517.22 21,489.22
Shop 1 2021-05-07 14,448.65 21,489.22
Shop 2 2021-05-08 12,500.00 21,489.22
Shop 1 2021-05-08 6,874.23 21,489.22
Shop 2 2021-05-09 15,321.89 22,222.22
Shop 1 2021-05-09 9,784.33 22,222.22
Shop 1 2021-05-10 12,235.50 22,222.22
Shop 2 2021-05-10 22,222.22 22,222.22
Shop 1 2021-05-11 14,800.65 22,222.22
Shop 2 2021-05-11 5,894.12 22,222.22
Shop 2 2021-05-12 9,966.66 22,222.22
Shop 1 2021-05-12 18,845.69 22,222.22
Shop 1 2021-05-13 13,250.69 22,222.22
Shop 2 2021-05-13 4,987.56 22,222.22
Shop 1 2021-05-14 17,784.25 19,874.26
Shop 2 2021-05-14 12,567.45 19,874.26
Shop 2 2021-05-15 15,489.36 19,874.26
Shop 1 2021-05-15 19,874.26 19,874.26

我们来检查一下2021-05-05的结果。它的范围是按日期从2021-05-022021-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。