在 PostgreSQL 中计算滚动平均值

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曲线。

rolling average in SQL

在上面,我们可以清楚地看到,滚动平均值的曲线比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中,我们需要更改表数据的格式,以便我们可以使用它。

假设我们想要获取每天注册的新用户数的运行平均值。为此,我们需要一个包含列dayregistered_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的伪表;它包含了列dayregistered_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_registeredrolling_average_10_daysrolling_average_3_days。我们可以看到rolling_average_10_days的曲线(橙色线)比rolling_average_3_days曲线(灰色线)更平滑。

rolling average in SQL

经济学中的滚动平均值

在我们最后一个移动平均值示例中,我们将分析一个虚构国家的一些经济指标。假设我们有过去 70 年的 GDP(国内生产总值)时间序列数据。我们想知道这个国家的 GDP 年增长率,以及增长率是如何变化的。但是,每年可能有不同的因素影响 GDP 总量,例如天气、自然灾害、战争或经济危机。因此,我们将使用 10 年和 20 年期间的滚动平均 GDP 来查看整体趋势。

我们有一个名为yearly_gdp的表,它带有列yearamount。下面,您可以看到 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 值曲线更平滑的曲线。

rolling average in SQL

如果我们从 10 年滚动平均值曲线中提取 GDP 年增长率,我们将得到 0-10% 的值。然而,如果我们从 20 年滚动平均值曲线中提取 GDP 年增长率,我们会得到 3-6% 的值;20 年滚动平均值曲线比 10 年曲线更平滑。请注意,在 2000 年,GDP 大幅增长;然而,10 年曲线显示小幅上升,而 20 年曲线保持相同的斜率。

最后,关于窗口函数,它们在计算指标(正如上面所看到的)和准备分析报告时非常有用。