九月 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-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。