在 PostgreSQL 中计算滚动平均值

摘要：在本文中，我们将展示在 PostgreSQL 中使用 SQL 计算滚动平均值的三个不同示例。

目录

滚动平均值是一个指标，它使我们能够找到原本难以检测的趋势。它通常基于时间序列数据。在 SQL 中，我们使用窗口函数来计算滚动平均值。

首先，我们来谈谈什么是滚动平均值，以及为什么它们很有用。

什么是滚动平均值？

滚动平均值的计算，允许我们通过基于数据集的不同子集创建一系列平均值，来分析数据点。它也称为移动平均值、运行平均值、移动均值或滚动均值。您经常会看到，在时间序列数据中使用滚动平均值来分析趋势，尤其是在短期波动会隐藏长期趋势或周期的时候。

为了展示一个用 SQL 计算滚动平均值的示例，我们将使用股票市值的数据集。假设我们有一个名为stock_values的表，如下所示：

在下一个查询中，我们将演示如何使用 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_price_row + stock_price_{previous_row} + stock_price_row-2 + stock_price_row-3) / 4

下面是上一个 SQL 查询的结果。请注意，当股票值极高或极低时，滚动平均值的极端值要小得多：

移动平均值广泛用于金融和技术交易，例如股票价格分析，以查看短期和长期趋势。在下一张图中，我们可以看到蓝色的stock_price曲线和橙色的rolling_average曲线。

在上面，我们可以清楚地看到，滚动平均值的曲线比stock_price曲线更平滑。此外，运行平均值曲线显示了一个小的上升趋势，这在stock_price曲线中是没法清楚看到的。

使用滚动平均值发现新增用户的趋势

许多网站使用“新注册用户”指标来衡量网站的表现。在本节中，我们将使用滚动平均值，根据每日新注册用户数来检测趋势。

假设我们有一个名为user_activity的表：

正如我们在第一个示例中所看到的，有时表中的数据采用了正确的格式，来计算滚动平均值。但是，在表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 天的数据；下面是部分结果集：

下图显示了曲线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 年的数据子集：

以下 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 年移动平均值，也是如此。

在下一张图中，您可以看到三条曲线：gdp_amount曲线、10 年滚动平均值曲线（从 1960 年开始）和 20 年滚动平均值曲线。同样，滚动平均值是比原始的 GDP 值曲线更平滑的曲线。

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

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