MariaDB中无子查询实现汇率转换后的持仓平均买入价计算

需求回顾 #

• 运行环境：Ubuntu + 最新版MariaDB
• 核心业务逻辑：用户使用美元（USD）等货币购买股票，投资组合需以欧元（EUR）计价，需完成三项核心计算：
  1. 为每条交易记录匹配最接近且不晚于交易创建日期的汇率（无匹配时取更早的有效汇率）
  2. 计算欧元计价的持仓总市值：SUM(剩余持仓量 × 转换后价格)
  3. 计算平均买入价：总市值 / 剩余持仓量总和
• 现存问题：原查询通过子查询实现，性能不佳；曾出现平均买入价计算正确但持仓量统计翻倍的异常

窗口函数替代方案 #

步骤1：匹配最优汇率 #

利用ROW_NUMBER()窗口函数，为每条交易筛选出符合日期要求的最近汇率，避免子查询的嵌套性能损耗：

WITH matched_rates AS (
    SELECT
        t.id AS trade_id,
        t.symbol,
        t.remaining_quantity,
        t.price AS original_price,
        t.currency AS source_currency,
        t.created_at,
        r.rate,
        -- 按单条交易分组，汇率日期倒序排序，取最近的有效汇率
        ROW_NUMBER() OVER (
            PARTITION BY t.id
            ORDER BY r.date DESC
        ) AS rn
    FROM trades t
    LEFT JOIN exchange_rates r
        ON r.source_currency = t.currency
        AND r.target_currency = 'EUR'
        AND r.date <= t.created_at
)

步骤2：计算持仓总市值与平均买入价 #

基于匹配到的最优汇率，再次用聚合逻辑按股票代码分组，计算最终的持仓总量、总市值及平均买入价：

SELECT
    symbol,
    SUM(remaining_quantity) AS total_remaining_quantity,
    SUM(remaining_quantity * original_price * rate) AS total_eur_value,
    -- 处理持仓量为0的边界情况，避免除以0错误
    CASE WHEN SUM(remaining_quantity) > 0 
         THEN SUM(remaining_quantity * original_price * rate) / SUM(remaining_quantity)
         ELSE 0 END AS avg_buyin_eur
FROM matched_rates
WHERE rn = 1 -- 仅保留每条交易匹配到的最优汇率记录
GROUP BY symbol;

完整可执行SQL代码 #

WITH matched_rates AS (
    SELECT
        t.id AS trade_id,
        t.symbol,
        t.remaining_quantity,
        t.price AS original_price,
        t.currency AS source_currency,
        t.created_at,
        r.rate,
        ROW_NUMBER() OVER (
            PARTITION BY t.id
            ORDER BY r.date DESC
        ) AS rn
    FROM trades t
    LEFT JOIN exchange_rates r
        ON r.source_currency = t.currency
        AND r.target_currency = 'EUR'
        AND r.date <= t.created_at
)
SELECT
    symbol,
    SUM(remaining_quantity) AS total_remaining_quantity,
    SUM(remaining_quantity * original_price * rate) AS total_eur_value,
    CASE WHEN SUM(remaining_quantity) > 0 
         THEN SUM(remaining_quantity * original_price * rate) / SUM(remaining_quantity)
         ELSE 0 END AS avg_buyin_eur
FROM matched_rates
WHERE rn = 1
GROUP BY symbol;

关键优化点说明 #

1. 汇率匹配准确性：通过PARTITION BY t.id确保单条交易仅匹配一条最优汇率，ORDER BY r.date DESC保证取到最近的历史汇率，完全符合需求
2. 解决持仓量翻倍问题：WHERE rn = 1过滤掉每条交易的多余汇率匹配结果，确保单条交易仅被统计一次
3. 性能提升：窗口函数的执行效率远高于嵌套子查询，在交易记录、汇率数据量较大时优势明显
4. 边界容错：通过CASE语句处理持仓量为0的场景，避免出现除以0的运行错误

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