人员也可转型金融人士 用 PostgreSQL 做量化投资剖析和经济开展预测 IT
滚动平均值是一个目的，它使咱们能够找到原本难以检测的趋向。它通常基于时期序列数据。在 SQL 中，咱们经常使用窗口函数来计算滚动平均值。
首先，咱们来谈谈什么是滚动平均值，以及为什么它们很有用。
什么是滚动平均值？
滚动平均值的计算，准许咱们经过基于数据集的不同子集创立一系列平均值，来剖析数据点。它也称为移动平均值、运转平均值、移动均值或滚动均值。您经常会看到，在时期序列数据中经常使用滚动平均值来剖析趋向，尤其是在短期动摇会暗藏常年趋向或周期的时刻。
为了展现一个用 SQL 计算滚动平均值的示例，咱们将经常使用股票市值的数据集。假定咱们有一个名为stock_values的表，如下所示：
|
date_time |
stock_price |
|
01/04/2021 17:00 |
100.00 |
|
01/05/2021 17:00 |
130.00 |
|
01/06/2021 17:00 |
90.00 |
|
01/07/2021 17:00 |
105.00 |
|
01/08/2021 17:00 |
110.00 |
|
01/09/2021 17:00 |
140.00 |
|
01/10/2021 17:00 |
87.00 |
|
01/11/2021 17:00 |
107.00 |
|
01/12/2021 17:00 |
147.00 |
|
01/13/2021 17:00 |
92.00 |
|
01/14/2021 11:00 |
110.00 |
|
01/15/2021 17:00 |
150.00 |
|
01/16/2021 17:00 |
155.00 |
|
01/17/2021 17:00 |
97.00 |
|
01/18/2021 17:00 |
112.00 |
|
01/19/2021 17:00 |
112.00 |
在下一个查问中，咱们将演示如何经常使用 SQL，依据前面的三个值和股票值，计算stock_price列的移动平均值：
date_timestock_priceTRUNCstock_price date_time moving_average stock_values此 SQL 查问对一组按date_time排序的值经常使用了窗口函数AVG()。子句ROWS BETWEEN 3 PRECEDING AND CURRENT ROW批示，只能经常使用行和前三行的stock_price值来计算平均值。而后，关于结果集中的每一行，将会基于一组不同的四个stock_price值，来计算滚动平均值。咱们可以在上方的公式中看到这一点：
rolling_average = (stock_pricerow+ stock_priceprevious_row+ stock_pricerow-2+ stock_pricerow-3) / 4
上方是上一个 SQL 查问的结果。请留意，当股票值极高或极低时，滚动平均值的极其值要小得多：
|
date_time |
stock_value |
rolling_average |
|
01/04/2021 17:00 |
100.00 |
|
|
01/05/2021 17:00 |
130.00 |
|
|
01/06/2021 17:00 |
90.00 |
|
|
01/07/2021 17:00 |
105.00 |
106.25 |
|
01/08/2021 17:00 |
110.00 |
108.75 |
|
01/09/2021 17:00 |
140.00 |
111.25 |
|
01/10/2021 17:00 |
87.00 |
110.50 |
|
01/11/2021 17:00 |
107.00 |
111.00 |
|
01/12/2021 17:00 |
147.00 |
120.25 |
|
01/13/2021 17:00 |
92.00 |
108.25 |
|
01/14/2021 11:00 |
110.00 |
114.00 |
|
01/15/2021 17:00 |
150.00 |
124.75 |
|
01/16/2021 17:00 |
155.00 |
126.75 |
|
01/17/2021 17:00 |
97.00 |
128.00 |
|
01/18/2021 17:00 |
112.00 |
128.50 |
|
01/19/2021 17:00 |
112.00 |
119.00 |
移动平均值宽泛用于金融和技术买卖，例如股票多少钱剖析，以检查短期和常年趋向。在下一张图中，咱们可以看到蓝色的stock_price曲线和橙色的rolling_average曲线。
在上方，咱们可以分明地看到，滚动平均值的曲线比stock_price曲线更平滑。此外，运转平均值曲线显示了一个小的回升趋向，这在stock_price曲线中是没法分明看到的。
经常使用滚动平均值发现新增用户的趋向
许多网站经常使用“新注册用户”目的来权衡网站的体现。在本节中，咱们将经常使用滚动平均值，依据每日新注册用户数来检测趋向。
假定咱们有一个名为user_activity的表：
|
user_name |
action |
user_type |
date_time |
|
mary1992 |
user_registration |
free |
2021-08-01 11:23:00 |
|
john_sailor |
user_registration |
free |
2021-08-01 17:33:00 |
|
mary1992 |
passwd_change |
free |
2021-08-03 01:22:00 |
|
florence99 |
user_registration |
free |
2021-08-03 14:02:00 |
|
clair2003 |
user_registration |
free |
2021-08-04 15:27:00 |
|
sailor |
upgrade_to_premium |
premium |
2021-08-05 01:18:00 |
|
florence99 |
passwd_change |
free |
2021-08-05 02:55:00 |
|
andy123 |
user_creation |
free |
2021-08-06 12:25:00 |
正如咱们在第一个示例中所看到的，有时表中的数据驳回了正确的格局，来计算滚动平均值。但是，在表user_activity中，咱们须要更改表数据的格局，以便咱们可以经常使用它。
假定咱们想要失掉每天注册的新用户数的运转平均值。为此，咱们须要一个蕴含列day和registered_users的表。SQL 有一个称为 CTE（公共表表白式）的概念，它准许咱们在查问执行时期创立一个伪表。而后，咱们可以在同一查问中经常使用该 CTE。上方是一个带有 CTE 的示例查问：
users_registered date_time:: registered_usersuser_activity registered_usersTRUNCregistered_users moving_average_10_daysTRUNCregistered_users moving_average_3_days users_registered前面的查问可以分为两个局部启动剖析。在第一局部中，咱们有一个CTE，它会生成一个名为users_registered的伪表；它蕴含了列day和registered_users。
查问的第二局部是滚动平均值的计算。与第一个例子相似，咱们经常使用AVG()窗口函数和子句OVER(ORDER BY day ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)。这会将AVG()函数运行于行及其前面的 9 行。该查问还计算三天的移动平均值；这里的想法是要显示两条滚动平均值曲线，并比拟它们的平滑水平。
上一个查问的结果包括过去 60 天的数据；上方是局部结果集：
|
day |
registered_users |
moving_average_10_days |
moving_average_3_days |
|
2021-08-08 |
33 |
33.00 |
32.33 |
|
2021-08-09 |
59 |
36.30 |
39.00 |
|
2021-08-10 |
60 |
39.00 |
50.66 |
|
2021-08-11 |
75 |
43.20 |
64,66 |
|
2021-08-12 |
67 |
46.10 |
67,33 |
|
2021-08-13 |
68 |
49.70 |
70.00 |
|
2021-08-14 |
59 |
52.60 |
64,66 |
|
2021-08-15 |
65 |
55.00 |
64.00 |
|
2021-08-16 |
62 |
57.30 |
62.00 |
|
2021-08-17 |
57 |
60.50 |
61.33 |
|
2021-08-18 |
67 |
63.90 |
62.00 |
|
2021-08-19 |
63 |
64.30 |
62.33 |
|
2021-08-20 |
89 |
67.20 |
73.00 |
下图显示了曲线users_registered、rolling_average_10_days和rolling_average_3_days。咱们可以看到rolling_average_10_days的曲线（橙色线）比rolling_average_3_days曲线（灰色线）更平滑。
经济学中的滚动平均值
在咱们最后一个移动平均值示例中，咱们将剖析一个虚拟国度的一些经济目的。假定咱们有过去 70 年的 GDP（国际消费总值）时期序列数据。咱们想知道这个国度的 GDP 年增长率，以及增长率是如何变动的。但是，每年或者有不同的起因影响 GDP 总量，例如天气、人造灾祸、抗争或经济危机。因此，咱们将经常使用 10 年和 20 年时期的滚动平均 GDP 来检查全体趋向。
咱们有一个名为yearly_gdp的表，它带有列year和amount。上方，您可以看到 1950 年至 1965 年的数据子集：
|
year |
gdp_amount |
|
1950 |
2396516 |
|
1951 |
1610296 |
|
1952 |
|
|
1953 |
1051886 |
|
1954 |
1113133 |
|
1955 |
2873493 |
|
1956 |
3295602 |
|
1957 |
4644432 |
|
1958 |
3312793 |
|
1959 |
2086353 |
|
1960 |
4727159 |
|
1961 |
3551490 |
|
1962 |
3282716 |
|
1963 |
3700999 |
|
1964 |
2260701 |
|
1965 |
1796435 |
以下 SQL 查问失掉基于过去 10 年和 20 年的 GDP 移动平均值。雷同，咱们将经常使用带有OVER子句的AVG()窗口函数，来计算过去 10 年或 20 年的平均值。请留意，咱们用了ORDER BY来确保记载按年份时期的顺序陈列：
gdp_amountTRUNCgdp_amount rolling_average_gdp_10_yearsTRUNCgdp_amount rolling_average_gdp_20_years yearly_gdp局部结果集显示在下图中。关于 1950 年至 1959 年，咱们没有用来计算 10 年滚动平均值的 GDP 值；这是正当的，由于咱们的记载始于 1950 年，咱们还没有足够的数据来算 10 年的平均值。1950 年至 1969 年间的 20 年移动平均值，也是如此。
|
year |
gdp_amount |
rolling_average_gdp_10_days |
rolling_average_gdp_20_days |
|
1950 |
2396516 |
||
|
1951 |
1610296 |
||
|
1952 |
3711316 |
||
|
1953 |
1051886 |
||
|
1954 |
1113133 |
||
|
1955 |
2873493 |
||
|
1956 |
3295602 |
||
|
1957 |
4644432 |
||
|
1958 |
3312793 |
||
|
1959 |
2086353 |
2609582 |
|
|
1960 |
4727159 |
2842646 |
|
|
1961 |
3551490 |
3036766 |
|
|
1962 |
3282716 |
2993906 |
|
|
1963 |
3700999 |
3258817 |
|
|
1964 |
2260701 |
3373574 |
|
|
1965 |
1796435 |
3265868 |
|
|
1966 |
2199231 |
3156231 |
|
|
1967 |
5007340 |
3192522 |
|
|
1968 |
5570332 |
3418276 |
|
|
1969 |
4614639 |
3671104 |
3140343 |
|
1970 |
2098413 |
3408230 |
3125438 |
|
1971 |
4899398 |
3543020 |
3289893 |
|
1973 |
5943866 |
3761279 |
3416272 |
在下一张图中，您可以看到三条曲线：gdp_amount曲线、10 年滚动平均值曲线（从 1960 年开局）和 20 年滚动平均值曲线。雷同，滚动平均值是比原始的 GDP 值曲线更平滑的曲线。
假设咱们从 10 年滚动平均值曲线中提取 GDP 年增长率，咱们将失掉 0-10% 的值。但是，假设咱们从 20 年滚动平均值曲线中提取 GDP 年增长率，咱们会失掉 3-6% 的值；20 年滚动平均值曲线比 10 年曲线更平滑。请留意，在 2000 年，GDP 大幅增长；但是，10 年曲线显示小幅回升，而 20 年曲线坚持相反的斜率。
最后，关于窗口函数，它们在计算目的（正如上方所看到的）和预备剖析报告时十分有用。
