移动平均

移动平均是时间序列分析中最常用的平滑方法，用于消除短期波动、揭示长期趋势。

📊 支持情况

1. 基础移动平均

固定窗口移动平均

-- MySQL - 3日移动平均
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma_3d
FROM daily_sales;

中心移动平均

-- MySQL - 中心对称移动平均
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS ma_center_3
FROM daily_sales;

按月移动平均

-- MySQL - 3个月移动平均
SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma_3m
FROM monthly_revenue;

2. 扩展窗口移动平均

累计平均

-- 从开始到当前的平均
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS UNBOUNDED PRECEDING
    ) AS running_avg
FROM daily_sales;

加权移动平均

-- MySQL 8.0+ - 线性权重
SELECT
    sale_date,
    amount,
    SUM(amount * weight) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) / SUM(weight) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS wma_3d
FROM (
    SELECT
        sale_date,
        amount,
        ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
    FROM daily_sales
) t
CROSS JOIN (
    SELECT 1 AS weight UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) w;

3. 指数移动平均 (EMA)

简单EMA实现

-- MySQL - 简单EMA
SELECT
    sale_date,
    amount,
    -- EMA_t = alpha * price_t + (1-alpha) * EMA_{t-1}
    ROUND(
        0.3 * amount +
        (1 - 0.3) * COALESCE(
            LAG(amount) OVER (ORDER BY sale_date) +
            0.3 * (amount - LAG(amount) OVER (ORDER BY sale_date)),
            amount
        ),
        2
    ) AS ema_0_3
FROM daily_sales;

递归CTE实现EMA

-- Oracle / MySQL 8.0+
WITH RECURSIVE ema_calc AS (
    SELECT
        sale_date,
        amount,
        amount AS ema_value,  -- 第一天用实际价格
        0.3 AS alpha,
        1 AS rn
    FROM daily_sales
    ORDER BY sale_date
    LIMIT 1

    UNION ALL

    SELECT
        s.sale_date,
        s.amount,
        ROUND(
            e.alpha * s.amount +
            (1 - e.alpha) * e.ema_value,
            2
        ) AS ema_value,
        e.alpha,
        e.rn + 1
    FROM daily_sales s
    JOIN ema_calc e ON 1=1
    WHERE s.sale_date > (
        SELECT sale_date FROM daily_sales ORDER BY sale_date LIMIT 1 OFFSET e.rn - 1
    )
    ORDER BY sale_date
    LIMIT 1
)
SELECT sale_date, amount, ema_value FROM ema_calc;

4. 实战案例

场景1：股价移动平均线

-- MySQL
SELECT
    trade_date,
    close_price,
    AVG(close_price) OVER (
        PARTITION BY stock_code
        ORDER BY trade_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS ma_5d,
    AVG(close_price) OVER (
        PARTITION BY stock_code
        ORDER BY trade_date
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS ma_20d
FROM stock_prices
ORDER BY stock_code, trade_date;

场景2：销售趋势分析

-- MySQL - 7日和30日移动平均
SELECT
    DATE(sale_date) AS day,
    sales,
    ROUND(AVG(sales) OVER (
        ORDER BY DATE(sale_date)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS ma_7d,
    ROUND(AVG(sales) OVER (
        ORDER BY DATE(sale_date)
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2) AS ma_30d
FROM daily_sales
ORDER BY day;

场景3：季节性调整

-- MySQL - 同比移动平均
SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS ma_12m,
    revenue - AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS seasonal_adjustment
FROM monthly_revenue
ORDER BY month;

场景4：库存周转分析

-- Oracle - 移动平均库存
SELECT
    stock_date,
    quantity,
    AVG(quantity) OVER (
        ORDER BY stock_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d_stock
FROM inventory_daily;

5. 特殊移动平均

按分组移动平均

-- MySQL - 按产品类别分别计算
SELECT
    category,
    sale_date,
    sales,
    AVG(sales) OVER (
        PARTITION BY category
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma_3d
FROM daily_sales_by_category;

带过滤条件的移动平均

-- 只计算工作日
SELECT
    sale_date,
    sales,
    AVG(sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM (
    SELECT
        DATE(sale_date) AS sale_date,
        SUM(sales) AS sales
    FROM sales
    WHERE DAYOFWEEK(sale_date) NOT IN (1, 7)  -- 排除周末
    GROUP BY DATE(sale_date)
) t;

双指数移动平均（DEMA）

-- Oracle
SELECT
    t.date,
    t.price,
    -- DEMA = 2 * EMA_fast - EMA_slow
    ROUND(
        2 * AVG(t.price) OVER (
            ORDER BY t.date
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) -
        AVG(t.price) OVER (
            ORDER BY t.date
            ROWS BETWEEN 21 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS dema
FROM (
    SELECT trade_date AS date, close_price AS price
    FROM stock_prices
    WHERE stock_code = '600000'
) t;

6. 移动窗口选择

窗口大小选择

窗口帧语法

ROWS BETWEEN [start] AND [end]

-- start/end 可选值：
-- UNBOUNDED PRECEDING    - 从分区第一行开始
-- n PRECEDING            - 往前n行
-- CURRENT ROW            - 当前行
-- n FOLLOWING            - 往后n行
-- UNBOUNDED FOLLOWING    - 到分区最后一行

⚠️ 注意事项

1. NULL值处理

-- 移动平均会跳过NULL
SELECT
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS 2 PRECEDING) AS ma
FROM (
    SELECT '2024-01-01' AS sale_date, 100 AS amount
    UNION ALL SELECT '2024-01-02', NULL
    UNION ALL SELECT '2024-01-03', 150
) t;
-- 结果：100, 100, 125（NULL被跳过）

2. 数据排序

-- 确保日期排序正确
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date) AS ma
FROM sales
ORDER BY sale_date;  -- 必须排序

3. 大数据量性能

-- ✅ 建议：先按日期聚合再计算移动平均
SELECT
    day,
    daily_sales,
    AVG(daily_sales) OVER (ORDER BY day ROWS 6 PRECEDING) AS ma_7d
FROM (
    SELECT DATE(sale_date) AS day, SUM(amount) AS daily_sales
    FROM orders
    GROUP BY DATE(sale_date)
) daily;

📝 练习题

-- 建表
CREATE TABLE stock_prices (
    trade_date DATE,
    stock_code STRING,
    close_price DECIMAL(10,2)
);

INSERT INTO stock_prices VALUES
('2024-01-08', '600000', 10.5),
('2024-01-09', '600000', 10.8),
('2024-01-10', '600000', 10.6),
('2024-01-11', '600000', 11.0),
('2024-01-12', '600000', 11.2),
('2024-01-15', '600000', 11.0),
('2024-01-16', '600000', 11.5);

要求：

1. 计算3日简单移动平均
2. 计算5日简单移动平均
3. 计算累计平均（从第一天开始）
4. 计算移动平均的同时标记趋势方向

-- 参考答案（MySQL）
-- 1. 3日移动平均
SELECT
    trade_date,
    close_price,
    ROUND(AVG(close_price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS ma_3d
FROM stock_prices
ORDER BY trade_date;

-- 2. 5日移动平均
SELECT
    trade_date,
    close_price,
    ROUND(AVG(close_price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ), 2) AS ma_5d
FROM stock_prices
ORDER BY trade_date;

-- 3. 累计平均
SELECT
    trade_date,
    close_price,
    ROUND(AVG(close_price) OVER (
        ORDER BY trade_date
        ROWS UNBOUNDED PRECEDING
    ), 2) AS running_avg
FROM stock_prices
ORDER BY trade_date;

-- 4. 趋势标记
SELECT
    trade_date,
    close_price,
    ma,
    CASE
        WHEN ma > LAG(ma) OVER (ORDER BY trade_date) THEN '↑'
        WHEN ma < LAG(ma) OVER (ORDER BY trade_date) THEN '↓'
        ELSE '-'
    END AS trend
FROM (
    SELECT
        trade_date,
        close_price,
        ROUND(AVG(close_price) OVER (
            ORDER BY trade_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2) AS ma
    FROM stock_prices
) t
ORDER BY trade_date;