用滑动窗口做运转式统计 的初级玩法 PostgreSQL
引见
SQL 窗口函数关于计算移动的平均值或运转中的总计等复杂聚算计算十分有用。ROWS 子句准许您为计算指定行，从而可以启用更复杂的窗口框架。以下是在 SQL 中经常使用 ROWS BETWEEN 子句的 5 个适用示例。
窗口函数（也称为 OVER 函数）可依据滑动窗口框架（即一组行）计算其结果。它们相似于聚合函数，由于您可以计算一组行的平均值、总和值或最小值/最大值。然而，存在一些关键的差异：
•窗口函数不会像聚合函数那样折叠行。因此，您依然可以将单个行中的属性与窗口函数的结果混合。
•窗口函数准许滑动窗口框架，这象征着，关于每个独自的行，用于计算窗口函数的行集或者都不同。
窗口函数的语法如下所示：
column_1 column_2 window_frame window_column_alias table_name当您在 SELECT 语句中经常使用窗口函数时，您基本上在经常使用这个函数计算出另一列：
•首先指定一个函数（例如AVG()、SUM()、或COUNT()）。
•而后，经常使用OVER关键字定义一组行。您还可以选用这样：
用PARTITION BY对前启动分组，以便在这些组内计算函数，而不是在整个行集中计算函数。
假设行的顺序很关键（例如，在计算汇总时），可经常使用 ORDER BY 对窗口框架内的前启动排序。
指定窗口框架与行的相关（例如，框架应该是行和前两行，或行和一切后续行等）。
窗口框架是经常使用ROWS、RANGE和GROUPS子句定义的。在本文中，咱们将重点引见ROWS子句及其选项。
ROWS 子句：语法和选项
ROWS 子句的用途是指定与行相关的窗口框架。语法为：
ROWS BETWEENlower_boundANDupper_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
|
record_id |
date |
revenue |
|
1 |
2021-09-01 |
1515.45 |
|
2 |
2021-09-02 |
2345.35 |
|
3 |
2021-09-03 |
903.99 |
|
4 |
2021-09-04 |
2158.55 |
|
5 |
2021-09-05 |
1819.80 |
revenuerevenue running_total sales 要经常使用窗口函数计算运转总计，咱们口头以下步骤：
•经常使用SUM()聚合函数计算总支出。
•经过将下限定义为UNBOUNDED PRECEDING和下限为CURRENT ROW来指定窗口框架。这将包括行之前的一切行（包括行）。请留意，这种状况和没有指定ROWS子句的自动行为是相反的。自动框架经常使用RANGE，而不是ROWS。由于每天的记载在表中只出现一次性，因此RANGE和ROWS的结果将相反。因此，咱们也可以经常使用以下查问，来取得相反的结果：
SELECT date, revenue,SUM(revenue) OVER (ORDER BY date) running_sumFROM salesORDER BY date;|
date |
revenue |
running_total |
|
2021-09-01 |
1515.45 |
1515.45 |
|
2021-09-02 |
2345.35 |
3860.80 |
|
2021-09-03 |
903.99 |
4764.79 |
|
2021-09-04 |
2158.55 |
6923.34 |
|
2021-09-05 |
1819.80 |
8743.14 |
如您所见，查问按预期上班，咱们在第三列中取得了汇总。第一天，它等于今日的开售额，即 1515.45 美元；第二天，它等于第一天和第二天的开售额总和，即 3860.80 美元；在下一行中，咱们失掉前三天的开售额总和，即 4764.79 美元，依此类推。
在接上去的示例中，咱们将看到当记载被分红几组时，ROWS子句是如何上班的。
示例 2
在接上去的几个例子中，咱们将经常使用下表。它蕴含两个中国市区（Shanghai 和 Beijing）延续五天的平均温度（以 °C 为单位）和总降水量（以毫米为单位），这些只是虚拟数据。
表 weather
|
record_id |
date |
city |
temperature |
precipitation |
|
101 |
2021-09-01 |
Shanghai |
18.5 |
7 |
|
102 |
2021-09-01 |
Beijing |
17.3 |
5 |
|
103 |
2021-09-02 |
Shanghai |
18.0 |
20 |
|
104 |
2021-09-02 |
Beijing |
17.0 |
15 |
|
105 |
2021-09-03 |
Shanghai |
20.1 |
12 |
|
106 |
2021-09-03 |
Beijing |
19.0 |
10 |
|
107 |
2021-09-04 |
Shanghai |
20.2 |
0 |
|
108 |
2021-09-04 |
Beijing |
19.6 |
0 |
|
109 |
2021-09-05 |
Shanghai |
22.5 |
0 |
|
110 |
2021-09-05 |
Beijing |
20.4 |
0 |
咱们想区分计算每个市区的三天移动平均温度。为了分隔两个市区的计算，咱们将蕴含子句PARTITION BY。而后，在指定窗口框架时，咱们将思索今日和前两天：
另请留意，咱们已将窗口函数放在ROUND() 函数中，以便将三天的移动平均值四舍五入到小数点后一位。结果如下：
|
city |
date |
temperature |
mov_avg_3d_city |
|
Beijing |
2021-09-01 |
17.3 |
17.3 |
|
Beijing |
2021-09-02 |
17.6 |
17.5 |
|
Beijing |
2021-09-03 |
19.0 |
18.0 |
|
Beijing |
2021-09-04 |
19.6 |
18.7 |
|
Beijing |
2021-09-05 |
20.4 |
19.7 |
|
Shanghai |
2021-09-01 |
18.5 |
18.5 |
|
Shanghai |
2021-09-02 |
19.0 |
18.8 |
|
Shanghai |
2021-09-03 |
20.1 |
19.2 |
|
Shanghai |
2021-09-04 |
20.2 |
19.8 |
|
Shanghai |
2021-09-05 |
22.5 |
20.9 |
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 日的平均温度，依此类推。
还有一点须要留意：窗口框架中的记载顺序，在指定要计算的行方面起着关键作用。
然而，咱们可以经过按降序对记载启动排序，而后将ROWS选项更改为包括2 FOLLOWING（后两天）而不是2 PRECEDING（前两天）：
city temperaturetemperature city mov_avg_3d_city weather city 此查问输入了齐全相反的结果。
示例 3
在此示例中，咱们将区分计算两个市区过去三天的总降雨量（即三天的运转总降雨量）。
city precipitationprecipitation city running_total_3d_city weather city 在此查问中，咱们再次按市区对数据启动分区。咱们经常使用SUM()函数来计算过去三天（包括今日）的总降雨量。另请留意，咱们经常使用了缩写模式来定义窗口框架，只指定了下限：2 PRECEDING。
上方是上述查问的输入：
|
city |
date |
precipitation |
running_total_3d_city |
|
Beijing |
2021-09-01 |
5 |
5 |
|
Beijing |
2021-09-02 |
15 |
20 |
|
Beijing |
2021-09-03 |
10 |
30 |
|
Beijing |
2021-09-04 |
0 |
25 |
|
Beijing |
2021-09-05 |
0 |
10 |
|
Shanghai |
2021-09-01 |
7 |
7 |
|
Shanghai |
2021-09-02 |
20 |
27 |
|
Shanghai |
2021-09-03 |
12 |
39 |
|
Shanghai |
2021-09-04 |
0 |
32 |
|
Shanghai |
2021-09-05 |
0 |
12 |
截至 9 月 3 日，咱们失掉 Beijing 延续三天的降雨总量：30 毫米。这是 9 月 1 日降雨量 5 毫米、第 2 天降雨量 15 毫米和第 3 天降雨量 10 毫米之和。
您知道咱们是如何在 9 月 5 日为 Shanghai 取得 12 毫米的总降雨量的吗？请尝试依据咱们输入表中的结果启动思索，以确保您了解窗口函数如何与特定窗口框架一同上班。
如今让咱们继续来看一些新的数据和示例。
示例 4
在接上去的两个示例中，咱们将经常使用如下所示的数据。它包括无关三个社交网络（Instagram、Facebook 和 LinkedIn）新订阅者数量的每日消息。
表 subscribers
|
record_id |
date |
social_network |
new_subscribers |
|
11 |
2021-09-01 |
|
40 |
|
12 |
2021-09-01 |
|
12 |
|
13 |
2021-09-01 |
|
5 |
|
14 |
2021-09-02 |
|
67 |
|
15 |
2021-09-02 |
|
23 |
|
16 |
2021-09-02 |
|
2 |
|
17 |
2021-09-03 |
|
34 |
|
18 |
2021-09-03 |
|
25 |
|
19 |
2021-09-03 |
|
10 |
|
20 |
2021-09-04 |
|
85 |
|
21 |
2021-09-04 |
|
28 |
|
22 |
2021-09-04 |
|
20 |
上方是满足此恳求的 SQL 查问：
social_network new_subscribersnew_subscribers social_network running_total_network subscribers social_network 输入如下所示：
|
date |
social_network |
new_subscribers |
running_total_network |
|
2021-09-01 |
|
12 |
12 |
|
2021-09-02 |
|
23 |
35 |
|
2021-09-03 |
|
25 |
60 |
|
2021-09-04 |
|
28 |
88 |
|
2021-09-01 |
|
40 |
40 |
|
2021-09-02 |
|
67 |
107 |
|
2021-09-03 |
|
34 |
141 |
|
2021-09-04 |
|
85 |
226 |
|
2021-09-01 |
|
5 |
5 |
|
2021-09-02 |
|
2 |
7 |
|
2021-09-03 |
|
10 |
17 |
|
2021-09-04 |
|
20 |
37 |
在结果表中，您可以看到，关于每个新记载，如何将新订阅者的数量减少到累积总数中。依照窗口函数中的指定，将为每个网络独自计算运转总计。
示例 5
在最后一个示例中，咱们来演示下，如何经常使用窗口函数和 ROWS 子句，显示一组特定记载的第一个值和最后一个值。这一次性，让咱们在输入中减少两列：
•第一天减少的新订阅者数量，以及
•最后一天减少的新订阅者数量。
经过为每个社交网络独自计算这些消息，比拟咱们开局的中央和如今的状况，咱们可以看到每天的体现。
以下是失掉所需输入的 SQL 查问：
SELECT social_network, date, new_subscribers,FIRST_VALUE(new_subscribers) OVER(PARTITION BY social_networkORDER BY date) AS first_day,LAST_VALUE(new_subscribers) OVER(PARTITION BY social_networkORDER BY dateROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_dayFROM subscribersORDER 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，以确保计算一切记载。
上方是结果集：
|
date |
social_network |
new_subscribers |
first_day |
last_day |
|
2021-09-01 |
|
12 |
12 |
28 |
|
2021-09-02 |
|
23 |
12 |
28 |
|
2021-09-03 |
|
25 |
12 |
28 |
|
2021-09-04 |
|
28 |
12 |
28 |
|
2021-09-01 |
|
40 |
40 |
85 |
|
2021-09-02 |
|
67 |
40 |
85 |
|
2021-09-03 |
|
34 |
40 |
85 |
|
2021-09-04 |
|
85 |
40 |
85 |
|
2021-09-01 |
|
5 |
5 |
20 |
|
2021-09-02 |
|
2 |
5 |
20 |
|
2021-09-03 |
|
10 |
5 |
20 |
|
2021-09-04 |
|
20 |
5 |
20 |
依据要求，咱们为每个社交网络区分计算了第一天和最后一天的新订阅者数量。
