如何利用SQL窗口函数实现金融领域的移动加权平均成本计算？-SQL-PHP中文网

移动加权平均成本（MWAC）是每笔买入后按数量加权动态更新的持仓成本，即（累计总金额÷累计总数量）；它不同于普通窗口函数中的简单均价计算，因后者忽略数量权重，而MWAC必须同步累积数量与金额并相除，且需严格时间序与防零除处理。

如何利用sql窗口函数实现金融领域的移动加权平均成本计算？

什么是移动加权平均成本（MWAC）？它和普通窗口函数有什么区别？

移动加权平均成本不是简单对价格做 AVG()，而是每笔新成交后，用「累计总金额 / 累计总数量」动态更新持仓成本。关键在于：权重是每笔成交的「数量」，且必须按时间顺序逐笔累积计算——这决定了不能直接用 AVG(price) 或 AVG(price) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，因为那会忽略数量权重。

真实场景中，一笔买入 100 股 @¥10，再买 200 股 @¥12，成本不是 (10+12)/2=11，而是 (100×10 + 200×12) / (100+200) = 11.33。窗口函数必须同时累积 quantity 和 quantity * price，再做除法。

如何用 SUM() OVER 实现带数量权重的滚动成本？

核心思路：用两个并行的窗口聚合——一个累加数量，一个累加金额，再相除。注意必须确保排序字段（如 trade_time）严格单调，且同一时间戳有多笔时需加唯一次序列（如 trade_id），否则窗口边界可能错位。

SUM(quantity) OVER (ORDER BY trade_time, trade_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 得到截至当前笔的累计数量
SUM(quantity * price) OVER (ORDER BY trade_time, trade_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 得到累计金额
两者相除即为当前笔成交后的最新加权平均成本；结果建议用 DECIMAL(18,4) 避免浮点误差

示例（PostgreSQL/MySQL 8.0+/Snowflake）：

SELECT
  trade_id,
  trade_time,
  quantity,
  price,
  SUM(quantity * price) OVER w / NULLIF(SUM(quantity) OVER w, 0) AS mwac
FROM trades
WINDOW w AS (ORDER BY trade_time, trade_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

为什么用 NULLIF()？不处理会出什么错？

当某笔交易 quantity = 0（比如撤单、系统占位记录），或初始无成交时，分母可能为 0，导致除零错误或 NULL 溢出。直接写 SUM(quantity) OVER (...) = 0 判断再 CASE 是低效的——窗口函数不支持在聚合内嵌套条件聚合。正确做法是用 NULLIF(denominator, 0) 把 0 转成 NULL，让整除结果也为 NULL，语义清晰且性能无损。

镝数图表

一款AI数据处理工具，主要用于简单好用的数据可视化工具，适合需要提升相关任务效率的用户。

下载

另一个常见坑：如果表里混有卖出（quantity 为负），而你本意只算「买入持仓成本」，那必须先过滤 WHERE quantity > 0，否则累计数量可能归零甚至变负，MWAC 失去业务意义。

实时流式场景下，窗口函数还能用吗？

在 Flink SQL 或 Kafka Streams 中，SUM() OVER (ORDER BY ...) 本质依赖全量有序事件流，一旦发生乱序（比如延迟到达的早时间戳交易），历史 MWAC 值会被修正——这在回测可接受，但在实盘盯盘界面会导致成本数字跳变，用户困惑。此时应改用状态化计算（如 Flink 的 KeyedProcessFunction 维护 running_total_qty / running_total_amt），而非纯 SQL 窗口。

如果坚持用 SQL，至少加一层「最大允许延迟」保障：例如在 Flink 中定义 WATERMARK FOR trade_time AS trade_time - INTERVAL '5' SECOND，并把窗口改为 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 配合 watermark，避免无限等待。