如何用SQL补全可通过交叉汇率计算的缺失汇率数据？

当然可以，针对你的场景，SQL完全能搞定汇率缺失值的补全，根据缺失值的依赖层级，有两种常用实现方式：

1. 单层级缺失：直接用COALESCE+计算式补全 #

如果缺失值只需要同一行里的其他两个关联汇率就能直接计算（比如你举的USD-JPY = CAD-USD * CAD-JPY这类场景），用COALESCE函数最直接——它会优先取非空的原值，原值为空时就用计算结果填充。

假设你的表名为exchange_rates，包含日期date、cad_jpy、cad_usd、usd_jpy等列，示例SQL如下：

SELECT
    date,
    COALESCE(cad_jpy, cad_usd * usd_jpy) AS cad_jpy,
    COALESCE(cad_usd, cad_jpy / usd_jpy) AS cad_usd,
    COALESCE(usd_jpy, cad_jpy / cad_usd) AS usd_jpy
    -- 其他8种汇率对按同样逻辑补充
FROM exchange_rates;

这种方式适合大部分基础场景，一行SQL就能完成直接可推导的缺失值补全。

2. 多层级缺失：用递归CTE迭代补全 #

如果存在某一行同时缺多个值，需要通过多轮推导（比如先补A，再用补好的A补B），就需要用**递归CTE（WITH RECURSIVE）**来迭代处理，直到所有缺失值都被补全。

示例代码（以5种汇率对为例，你可以扩展到10种）：

WITH RECURSIVE filled_rates AS (
    -- 初始步骤：先补全第一轮能直接计算的缺失值
    SELECT
        date,
        COALESCE(cad_jpy, cad_usd * usd_jpy) AS cad_jpy,
        COALESCE(cad_usd, cad_jpy / usd_jpy) AS cad_usd,
        COALESCE(usd_jpy, cad_jpy / cad_usd) AS usd_jpy,
        COALESCE(eur_usd, eur_jpy / usd_jpy) AS eur_usd,
        COALESCE(eur_jpy, eur_usd * usd_jpy) AS eur_jpy
    FROM exchange_rates

    UNION ALL

    -- 递归步骤：基于上一轮的结果，继续补全剩余缺失值
    SELECT
        date,
        COALESCE(cad_jpy, cad_usd * usd_jpy) AS cad_jpy,
        COALESCE(cad_usd, cad_jpy / usd_jpy) AS cad_usd,
        COALESCE(usd_jpy, cad_jpy / cad_usd) AS usd_jpy,
        COALESCE(eur_usd, eur_jpy / usd_jpy) AS eur_usd,
        COALESCE(eur_jpy, eur_usd * usd_jpy) AS eur_jpy
    FROM filled_rates
    -- 终止条件：当当前轮次还有缺失值时继续迭代，否则停止
    WHERE EXISTS (
        SELECT 1
        FROM filled_rates
        WHERE cad_jpy IS NULL OR cad_usd IS NULL OR usd_jpy IS NULL OR eur_usd IS NULL OR eur_jpy IS NULL
    )
    -- 额外加迭代次数限制，防止无限递归（比如最多10次）
    AND (SELECT COUNT(*) FROM filled_rates) <= 10
)
-- 取最终无缺失值的结果
SELECT DISTINCT *
FROM filled_rates
WHERE cad_jpy IS NOT NULL 
  AND cad_usd IS NOT NULL 
  AND usd_jpy IS NOT NULL 
  AND eur_usd IS NOT NULL 
  AND eur_jpy IS NOT NULL;

注意事项 #

• 精度控制：汇率计算建议用DECIMAL类型存储，避免用FLOAT/DOUBLE带来的精度丢失问题。
• 逻辑自洽：确保你的交叉汇率公式是正确且无矛盾的，比如不要出现A=B*C和B=A*C这种冲突逻辑。
• 终止条件：递归CTE一定要加终止条件，要么判断无缺失值，要么限制迭代次数，防止无限循环。

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