由 John Doe 九月 14, 2024
摘要:在本文中,我们将展示在 PostgreSQL 中使用 SQL 计算滚动平均值的三个不同示例。
目录
滚动平均值是一个指标,它使我们能够找到原本难以检测的趋势。它通常基于时间序列数据。在 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
列的移动平均值:
SELECT
date_time,
stock_price,
TRUNC(AVG(stock_price)
OVER(ORDER BY date_time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2)
AS moving_average
FROM 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 的示例查询:
WITH users_registered AS (
SELECT
date_time::date AS day,
COUNT(*) AS registered_users
FROM user_activity
WHERE action = 'user_registration'
GROUP BY 1
)
SELECT
day,
registered_users,
TRUNC(AVG(registered_users)
OVER(ORDER BY day ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 2)
AS moving_average_10_days,
TRUNC(AVG(registered_users)
OVER(ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)
AS moving_average_3_days
FROM 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 | 3711316 |
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
来确保记录按年份时间的顺序排列:
SELECT
year,
gdp_amount,
TRUNC(AVG(gdp_amount) OVER(ORDER BY year ROWS BETWEEN 9 PRECEDING AND CURRENT ROW )) AS rolling_average_gdp_10_years,
TRUNC(AVG(gdp_amount) OVER(ORDER BY year ROWS BETWEEN 19 PRECEDING AND CURRENT ROW )) AS rolling_average_gdp_20_years
FROM 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 年曲线保持相同的斜率。
最后,关于窗口函数,它们在计算指标(正如上面所看到的)和准备分析报告时非常有用。