PostgreSQL计算BTC的MACD结果异常，求问题排查

问题背景 #

基于TimescaleDB创建1分钟K线连续物化视图，自定义EMA函数及聚合来计算BTC的MACD、信号线和柱状图，但最终结果与参考平台不一致。相关代码如下：

1. 创建1分钟K线连续物化视图 #

CREATE MATERIALIZED VIEW IF NOT EXISTS candle_1_minute
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', time) as bucket,
       product_id,
       MAX(price) as high,
       MIN(price) as low,
       FIRST(price, time) as open,
       LAST(price, time) as close
FROM ticker
GROUP BY 2, 1
ORDER BY 1, 2
WITH NO DATA;

2. 自定义EMA函数与聚合 #

CREATE OR REPLACE FUNCTION ema_func(previous numeric, close numeric, observations numeric)
  returns numeric
  language plpgsql as
$$
begin
  raise info 'previous %, close %', $1, $2;
  return case
         when previous is null then close
         else (close * (2/observations+1)) + (previous * (1-(2/observations+1)))
         end;
end
$$;

CREATE AGGREGATE ema(numeric, numeric) (sfunc = ema_func, stype = numeric);

3. MACD及信号线计算 #

SELECT
    sub.bucket as time,
    sub.macd,
    ema(sub.macd, 9) over(ORDER BY sub.bucket rows between 8 preceding and current row) AS signal
FROM (
    SELECT
        bucket,
        product_id,
        ema(close, 12) OVER(ORDER BY bucket ROWS BETWEEN 11 PRECEDING AND CURRENT ROW ) - ema(close, 26) OVER(ORDER BY bucket ROWS BETWEEN 25 PRECEDING AND CURRENT ROW ) AS macd
    FROM candle_1_minute
    WHERE product_id = 'BTC-GBP'
    ORDER BY bucket
) sub
ORDER BY bucket;

4. MACD柱状图计算 #

SELECT
    sub.bucket as time,
    sub.macd - ema(sub.macd, 9) over(ORDER BY sub.bucket rows between 8 preceding and current row) as hist
FROM (
    SELECT
        bucket,
        close,
        ema(close, 12) OVER(ORDER BY bucket ROWS BETWEEN 11 PRECEDING AND CURRENT ROW ) - ema(close, 26) OVER(ORDER BY bucket ROWS BETWEEN 25 PRECEDING AND CURRENT ROW ) AS macd
    FROM candle_1_minute
    WHERE product_id = 'BTC-GBP'
    ORDER BY bucket
) sub
ORDER BY bucket;

核心错误点 #

1. EMA计算公式运算优先级错误 #

EMA的平滑因子应为 2/(N+1)，但代码中写成了 2/observations+1。SQL中除法与加法优先级相同，从左到右计算，实际变成 (2/observations) + 1，完全偏离正确的平滑因子数值，这是结果偏差的核心原因。

正确的计算逻辑需给 observations+1 加上括号：

(close * (2/(observations + 1))) + (previous * (1 - (2/(observations + 1))))

2. 滑动窗口逻辑不符合EMA本质 #

EMA是指数加权移动平均，依赖从第一条数据到当前行的所有历史数据累积计算，而非固定N条的滑动窗口。代码中使用 ROWS BETWEEN 11 PRECEDING AND CURRENT ROW 这类固定窗口，只会计算最近N条数据的"伪EMA"，和标准EMA的累积逻辑完全不同。

正确的窗口应去掉 ROWS BETWEEN ... 子句，默认窗口范围就是从数据集起始到当前行，这样才能让EMA聚合函数正确累积计算。

3. 窗口范围导致的计算偏差 #

结合错误的窗口范围，自定义EMA聚合无法正确计算累积加权平均，进一步放大了结果偏差。

修正后的代码示例 #

修正EMA函数 #

CREATE OR REPLACE FUNCTION ema_func(previous numeric, close numeric, observations numeric)
  returns numeric
  language plpgsql as
$$
begin
  return case
         when previous is null then close
         else (close * (2/(observations + 1))) + (previous * (1 - (2/(observations + 1))))
         end;
end
$$;

修正MACD、信号线及柱状图计算 #

SELECT
    sub.bucket as time,
    sub.macd,
    ema(sub.macd, 9) OVER(ORDER BY sub.bucket) AS signal,
    sub.macd - ema(sub.macd, 9) OVER(ORDER BY sub.bucket) AS hist
FROM (
    SELECT
        bucket,
        ema(close, 12) OVER(ORDER BY bucket) - ema(close, 26) OVER(ORDER BY bucket) AS macd
    FROM candle_1_minute
    WHERE product_id = 'BTC-GBP'
    ORDER BY bucket
) sub
ORDER BY bucket;

额外验证建议 #

• 确认 candle_1_minute 中的K线数据（尤其是close价格）与参考平台的1分钟K线完全一致，不同平台的K线收盘价取值逻辑（比如是否取分钟内最后一笔成交价）可能存在差异。
• 确认参考平台的MACD计算逻辑确实基于收盘价的EMA，避免因计算基准不同导致的结果偏差。

内容的提问来源于stack exchange，提问作者puffin