滚动平均值是一个指标，它使我们能够找到原本难以检测的趋势。它通常基于时间序列数据。在 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 年曲线保持相同的斜率。
最后，关于窗口函数，它们在计算指标（正如上面所看到的）和准备分析报告时非常有用。
本网站的文章部分内容可能来源于网络和网友发布，仅供大家学习与参考，如有侵权，请联系站长进行删除处理，不代表本网站立场，转载者并注明出处：https://www.jmbhsh.com/xinwenzixun/35675.html
