SQL 教学文档：正常写法 vs 变量写法

一、背景介绍

MACD 指标常用于金融量化分析，核心计算公式：

1. EMA12 = 12 日指数移动平均
2. EMA26 = 26 日指数移动平均
3. DIF = EMA12 - EMA26
4. DEA = DIF 的 9 日 EMA
5. MACD = 2 × (DIF - DEA)

难点：EMA 是递推公式，不能直接用 AVG() 这种窗口函数来算。

二、正常写法（递归 CTE）

原理

• MySQL 8.0 支持 递归 CTE，可以一行行地递推计算。
• 第一天初始化 EMA = 当日收盘价，DEA = DIF。
• 后续每天按公式迭代。

示例 SQL

WITH RECURSIVE ema_calc AS (
    -- 初始化：取最早一天
    SELECT
        date,
        normalization_net_asset_value AS price,
        normalization_net_asset_value AS ema12,
        normalization_net_asset_value AS ema26,
        0.0 AS dea
    FROM funds_normalization
    WHERE date = (SELECT MIN(date) FROM funds_normalization)

    UNION ALL

    -- 递推计算
    SELECT
        f.date,
        f.normalization_net_asset_value AS price,
        e.ema12 + (2.0/13) * (f.normalization_net_asset_value - e.ema12) AS ema12,
        e.ema26 + (2.0/27) * (f.normalization_net_asset_value - e.ema26) AS ema26,
        e.dea + (2.0/10) * (( (e.ema12 + (2.0/13) * (f.normalization_net_asset_value - e.ema12))
                            - (e.ema26 + (2.0/27) * (f.normalization_net_asset_value - e.ema26))) - e.dea) AS dea
    FROM ema_calc e
    JOIN funds_normalization f
      ON f.date = (SELECT MIN(date) FROM funds_normalization WHERE date > e.date)
)
SELECT
    date,
    price,
    ema12,
    ema26,
    (ema12 - ema26) AS dif,
    dea,
    2 * ((ema12 - ema26) - dea) AS macd
FROM ema_calc;

特点

✅ 可读性强，逻辑清晰
❌ 每次递归要扫表，性能差，数据量大时速度慢

三、变量写法（性能优化）

原理

• MySQL 用户变量可以在 SELECT 中保存中间结果。
• 按 ORDER BY date 顺序遍历数据，逐行迭代计算 EMA、DEA。
• 只需一次扫描，性能 O(n)。

示例 SQL

-- 初始化变量
SET @ema12 := NULL;
SET @ema26 := NULL;
SET @dea   := 0.0;

SELECT
    date,
    price,
    @ema12 := IF(@ema12 IS NULL, price, @ema12 + (2.0/13) * (price - @ema12)) AS ema12,
    @ema26 := IF(@ema26 IS NULL, price, @ema26 + (2.0/27) * (price - @ema26)) AS ema26,
    (@ema12 - @ema26) AS dif,
    @dea   := IF(@ema12 IS NULL, 0.0, @dea + (2.0/10) * ((@ema12 - @ema26) - @dea)) AS dea,
    2 * ((@ema12 - @ema26) - @dea) AS macd
FROM (
    SELECT date, normalization_net_asset_value AS price
    FROM funds_normalization
    ORDER BY date
) t;

特点

✅ 高性能（一次扫描即可）
✅ 代码简洁
❌ 可移植性差（依赖 MySQL 用户变量，Oracle/SQL Server 不一定支持）
❌ 逻辑不如 CTE 直观

四、两种方法对比

五、总结

• 递归 CTE：适合教学和原理展示，逻辑直观但慢。
• 用户变量：适合实战和大数据分析，性能好但写法技巧性强。

👉 在实际项目里，推荐先用变量写法算 EMA/MACD，如果要跨平台或写报告，可以用递归 CTE 展示公式逻辑。