发布于 由admin

如何利用`WINDOW`函数实现移动平均（`Moving Average`）？

利用WINDOW函数实现移动平均

大家好，今天我们来探讨如何利用SQL中的WINDOW函数来实现移动平均（Moving Average）。移动平均是一种常用的数据平滑技术，它通过计算一段时间内数据的平均值来减少短期波动，从而揭示数据的长期趋势。WINDOW函数为我们在SQL中实现这种计算提供了强大的工具。

1. 移动平均的基本概念

移动平均（Moving Average，MA）是一种时间序列分析方法，用于平滑数据并识别趋势。它的基本思想是计算一个特定窗口期内数据的平均值，然后将该窗口向前移动，重复计算，直到覆盖整个数据集。

例如，一个3天的简单移动平均（Simple Moving Average，SMA）的计算方法如下：

  • 对于每一天，计算该天以及前两天数据的平均值。
  • 将计算出的平均值作为该天的移动平均值。

2. WINDOW函数简介

WINDOW函数允许我们在SQL查询中对一组相关的行（称为“窗口”）执行计算。与聚合函数（如SUM、AVG等）不同，WINDOW函数不会将结果集折叠成单个行，而是为每一行返回一个值，该值基于其所在的窗口。

WINDOW函数的基本语法如下：

function_name() OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list [ASC | DESC]]
    [frame_clause]
)
  • function_name(): 要应用的窗口函数，例如 AVG(), SUM(), ROW_NUMBER() 等。
  • OVER(...): 定义窗口的规范。
  • PARTITION BY column_list: 将结果集划分为多个分区，每个分区独立计算窗口函数。
  • ORDER BY column_list: 定义窗口内行的排序方式。这对于移动平均至关重要，因为它定义了时间顺序。
  • frame_clause: 定义窗口的大小和形状。这是实现移动平均的关键部分。

3. frame_clause详解

frame_clause 用于定义窗口的大小。它基于当前行指定要包含在窗口中的行。常见的 frame clause 语法如下：

{ROWS | RANGE} BETWEEN {UNBOUNDED PRECEDING | CURRENT ROW | value PRECEDING | value FOLLOWING}
AND {UNBOUNDED FOLLOWING | CURRENT ROW | value PRECEDING | value FOLLOWING}
  • ROWS 或 RANGE: 指定 frame 的单位。ROWS 基于物理行数，而 RANGE 基于 ORDER BY 子句中列的值的范围。 对于移动平均，我们通常使用ROWS。
  • UNBOUNDED PRECEDING: 窗口从分区的第一行开始。
  • UNBOUNDED FOLLOWING: 窗口延伸到分区的最后一行。
  • CURRENT ROW: 窗口包括当前行。
  • value PRECEDING: 窗口包括当前行之前的 value 行。
  • value FOLLOWING: 窗口包括当前行之后的 value 行。

例如，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了一个窗口，包括当前行和前两行。

4. 使用WINDOW函数实现简单移动平均 (SMA)

假设我们有一个包含每日销售额的表 sales，表结构如下：

CREATE TABLE sales (
    sale_date DATE,
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, sales_amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 120.00),
('2023-01-03', 130.00),
('2023-01-04', 110.00),
('2023-01-05', 140.00),
('2023-01-06', 150.00),
('2023-01-07', 160.00),
('2023-01-08', 130.00),
('2023-01-09', 120.00),
('2023-01-10', 140.00);

要计算3天的简单移动平均，可以使用以下SQL查询：

SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_3_day
FROM
    sales;

这个查询会返回一个结果集，其中包含每天的销售额以及对应的3天移动平均值。

sale_date sales_amount moving_average_3_day
2023-01-01 100.00 100.00
2023-01-02 120.00 110.00
2023-01-03 130.00 116.67
2023-01-04 110.00 120.00
2023-01-05 140.00 126.67
2023-01-06 150.00 133.33
2023-01-07 160.00 150.00
2023-01-08 130.00 146.67
2023-01-09 120.00 136.67
2023-01-10 140.00 130.00

5. 使用PARTITION BY计算分组移动平均

如果我们需要计算每个类别或分区的移动平均，可以使用 PARTITION BY 子句。 假设我们有一个包含产品类别和每日销售额的表 product_sales，表结构如下：

CREATE TABLE product_sales (
    sale_date DATE,
    product_category VARCHAR(50),
    sales_amount DECIMAL(10, 2)
);

INSERT INTO product_sales (sale_date, product_category, sales_amount) VALUES
('2023-01-01', 'Electronics', 100.00),
('2023-01-02', 'Electronics', 120.00),
('2023-01-03', 'Electronics', 130.00),
('2023-01-01', 'Clothing', 50.00),
('2023-01-02', 'Clothing', 60.00),
('2023-01-03', 'Clothing', 70.00),
('2023-01-04', 'Electronics', 110.00),
('2023-01-05', 'Electronics', 140.00),
('2023-01-04', 'Clothing', 80.00),
('2023-01-05', 'Clothing', 90.00);

要计算每个产品类别的3天移动平均，可以使用以下SQL查询：

SELECT
    sale_date,
    product_category,
    sales_amount,
    AVG(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_3_day
FROM
    product_sales;

这个查询会返回一个结果集，其中包含每个产品类别的每日销售额以及对应的3天移动平均值。注意，每个产品类别都会独立计算移动平均。

sale_date product_category sales_amount moving_average_3_day
2023-01-01 Clothing 50.00 50.00
2023-01-02 Clothing 60.00 55.00
2023-01-03 Clothing 70.00 60.00
2023-01-04 Clothing 80.00 70.00
2023-01-05 Clothing 90.00 80.00
2023-01-01 Electronics 100.00 100.00
2023-01-02 Electronics 120.00 110.00
2023-01-03 Electronics 130.00 116.67
2023-01-04 Electronics 110.00 120.00
2023-01-05 Electronics 140.00 126.67

6. 处理边界情况：NULL值处理

在计算移动平均时，我们需要特别注意边界情况，尤其是在窗口期开始时。如果窗口期内的某些行不存在（例如，第一天没有前两天的数据），AVG() 函数会自动忽略 NULL 值。 但如果所有的值都是NULL，结果也将是NULL。

7. 不同的frame clause示例

除了 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，我们还可以使用其他 frame_clause 来定义不同的窗口。

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 计算从分区开始到当前行的累计平均值。

    SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_average
FROM
    sales;

  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 计算从当前行到分区结束的平均值。

    SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS future_average
FROM
    sales;

  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 计算当前行及其前后各一行的平均值（相当于3天中心移动平均）。

    SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS centered_moving_average
FROM
    sales;

8. 加权移动平均 (WMA)

简单移动平均 (SMA) 对窗口内的所有数据点赋予相同的权重。而加权移动平均 (WMA) 允许我们为不同的数据点分配不同的权重，更强调某些数据点的影响。

虽然SQL WINDOW 函数本身没有直接提供 WMA 的函数，但我们可以通过自定义计算来实现。 例如，我们可以给最近的数据更高的权重。

假设我们要计算一个3天的加权移动平均，其中今天的权重为3，昨天的权重为2，前天的权重为1。 我们可以使用以下SQL查询：

WITH RankedSales AS (
    SELECT
        sale_date,
        sales_amount,
        ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
    FROM
        sales
),
WeightedSales AS (
    SELECT
        sale_date,
        sales_amount,
        rn,
        CASE
            WHEN rn >= 3 THEN (
                LAG(sales_amount, 2, 0) OVER (ORDER BY rn) * 1 +
                LAG(sales_amount, 1, 0) OVER (ORDER BY rn) * 2 +
                sales_amount * 3
            ) / 6
            WHEN rn = 2 THEN (
                LAG(sales_amount, 1, 0) OVER (ORDER BY rn) * 2 +
                sales_amount * 3
            ) / 5
            WHEN rn = 1 THEN sales_amount
        END AS weighted_average
    FROM
        RankedSales
)
SELECT
    sale_date,
    sales_amount,
    weighted_average
FROM
    WeightedSales;

这个查询使用 ROW_NUMBER() 函数为每一行分配一个序号，然后使用 LAG() 函数获取前两天的销售额。最后，根据权重计算加权平均值。 注意，我们必须处理起始日期的特殊情况，避免除以0.

9. 指数移动平均 (EMA)

指数移动平均 (EMA) 是一种更复杂的移动平均方法，它对历史数据的权重呈指数衰减。EMA 对最近的数据赋予更高的权重，并且对所有历史数据都有影响，只是影响越来越小。

虽然SQL WINDOW 函数本身并没有直接支持 EMA 的函数，但我们可以使用递归公共表表达式 (Recursive CTE) 来实现 EMA。 这比WMA更复杂，但能实现更平滑的曲线。

假设我们要计算一个 α = 0.2 的指数移动平均。可以使用以下SQL查询：

WITH RECURSIVE EMA AS (
    SELECT
        sale_date,
        sales_amount,
        sales_amount AS ema,
        ROW_NUMBER() OVER (ORDER BY sale_date) AS rn,
        CAST(sale_date AS VARCHAR(20)) as path
    FROM
        sales
    WHERE ROW_NUMBER() OVER (ORDER BY sale_date) = 1

    UNION ALL

    SELECT
        s.sale_date,
        s.sales_amount,
        (0.2 * s.sales_amount + (1 - 0.2) * ema.ema) AS ema,
        s.rn,
        ema.path || '->' || CAST(s.sale_date AS VARCHAR(20))
    FROM
        (SELECT sale_date, sales_amount, ROW_NUMBER() OVER (ORDER BY sale_date) AS rn FROM sales) AS s
        INNER JOIN EMA ON s.rn = EMA.rn + 1
)
SELECT sale_date, sales_amount, ema FROM EMA;

这个查询使用递归 CTE 来迭代计算 EMA。 初始值设置为第一个销售额。 然后，递归部分使用公式 EMA = α * 当前值 + (1 - α) * 前一个EMA 来计算后续的EMA值。 path列只是为了演示递归过程，可以删除。

10. 不同数据库系统的兼容性

虽然 WINDOW 函数是SQL标准的一部分，但不同的数据库系统可能对某些功能的支持程度有所不同。 例如，一些数据库可能不支持所有的 frame_clause 选项。 因此，在实际应用中，需要根据所使用的数据库系统查阅相应的文档，以确保 SQL 查询的正确性和兼容性。

11. 性能考量

使用 WINDOW 函数进行移动平均计算可能会对性能产生影响，尤其是在处理大型数据集时。 WINDOW 函数需要在查询执行期间对数据进行排序和分组，这可能会消耗大量的计算资源。

为了提高性能，可以考虑以下几点：

  • 确保在 ORDER BY 子句中使用的列上建立了索引。
  • 尽量减少窗口的大小，只包含必要的行。
  • 对于复杂的移动平均计算（例如，WMA、EMA），可以考虑使用预计算或物化视图来提高查询效率。

总结：WINDOW函数的强大之处，灵活的frame clause，以及更复杂算法的实现

我们学习了如何使用SQL的WINDOW函数来实现移动平均。关键在于理解frame_clause的用法，它可以帮助我们定义窗口的大小和形状，从而灵活地计算各种类型的移动平均。虽然WINDOW函数本身没有直接支持WMA和EMA的函数，但我们可以通过自定义计算和递归CTE来实现这些更复杂的算法。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注