汇率表缺失日期时动态均值计算的SQL问题排查

问题本质 #

你的SQL返回相同均值，大概率是因为计算均值时没有按每个缺失日期所属的月份分组，而是计算了全局所有月份的汇总均值，导致所有缺失日期复用同一个值。

两种可行修正方案 #

方案1：预计算月度均值（性能优先） #

通过CTE先算出每个月的有效汇率均值，再关联业务表，优先用当日汇率，无数据时取对应月份的均值：

WITH monthly_fx_avg AS (
    -- 预计算每个月的EUR转BRL汇率均值
    SELECT
        DATE_TRUNC('month', snapshot_date) AS fx_month,
        AVG(conversion_rate) AS monthly_avg_rate
    FROM Dim_Fx_Snapshot
    WHERE currency_from = 'EUR'
      AND currency_to = 'BRL'
    GROUP BY DATE_TRUNC('month', snapshot_date)
)
SELECT
    ps.kpi_date,
    ps.euro_amount,
    -- 优先用当日汇率，无数据则取当月均值
    COALESCE(fx.conversion_rate, mfx.monthly_avg_rate) AS applied_rate,
    ps.euro_amount * COALESCE(fx.conversion_rate, mfx.monthly_avg_rate) AS brl_kpi_amount
FROM fd_player_summary ps
-- 关联当日汇率
LEFT JOIN Dim_Fx_Snapshot fx
    ON ps.kpi_date = fx.snapshot_date
    AND fx.currency_from = 'EUR'
    AND fx.currency_to = 'BRL'
-- 关联对应月份的均值
LEFT JOIN monthly_fx_avg mfx
    ON DATE_TRUNC('month', ps.kpi_date) = mfx.fx_month
ORDER BY ps.kpi_date;

方案2：子查询动态计算均值（简洁优先） #

无需预计算，直接在SELECT中通过子查询动态获取当前日期所属月份的均值：

SELECT
    ps.kpi_date,
    ps.euro_amount,
    COALESCE(
        fx.conversion_rate,
        (SELECT AVG(conversion_rate)
         FROM Dim_Fx_Snapshot fx_avg
         WHERE DATE_TRUNC('month', fx_avg.snapshot_date) = DATE_TRUNC('month', ps.kpi_date)
           AND fx_avg.currency_from = 'EUR'
           AND fx_avg.currency_to = 'BRL')
    ) AS applied_rate,
    ps.euro_amount * COALESCE(
        fx.conversion_rate,
        (SELECT AVG(conversion_rate)
         FROM Dim_Fx_Snapshot fx_avg
         WHERE DATE_TRUNC('month', fx_avg.snapshot_date) = DATE_TRUNC('month', ps.kpi_date)
           AND fx_avg.currency_from = 'EUR'
           AND fx_avg.currency_to = 'BRL')
    ) AS brl_kpi_amount
FROM fd_player_summary ps
LEFT JOIN Dim_Fx_Snapshot fx
    ON ps.kpi_date = fx.snapshot_date
    AND fx.currency_from = 'EUR'
    AND fx.currency_to = 'BRL'
ORDER BY ps.kpi_date;

核心修正逻辑 #

• 强制均值计算与当前业务日期的所属月份绑定，而非全局统一计算
• 用COALESCE确保当日有汇率时优先使用原始值，仅缺失时才触发均值计算
• 方案1适合大数据量场景，预计算能减少重复计算；方案2代码更紧凑，适合小数据集

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