SQL跨列计算：查找带中间货币的USD兑GBP汇率路径相关问题

背景说明 #

现有汇率表结构如下：

需求是查找所有仅经过1个中间货币的USD兑换GBP的汇率路径，以下是对应问题的解答：

1. 问题通用名称 #

这类问题属于固定步长有向图路径查找，在汇率场景下也常被称为三角套利路径匹配（当前需求是1个中间货币对应2跳路径，是三角套利的典型场景）。本质是把每个货币作为图的节点，货币兑换汇率作为有向边的权重，查找从起点USD到终点GBP、经过1个中间节点的所有有效路径。

2. PostgreSQL、ClickHouse实现方案 #

完全可以用原生SQL实现，不需要额外引入组件，可直接依托数据库自带的查询优化器完成计算。

核心实现逻辑

仅需要对汇率表做一次自关联，关联条件为第一张表的目标货币等于第二张表的源货币，再限定路径起点和终点即可：

SELECT 
  t1.currency1 AS start_currency,
  t1.currency2 AS intermediate_currency,
  t2.currency2 AS end_currency,
  ROUND(t1.price * t2.price, 4) AS calculated_exchange_rate,
  CONCAT(t1.currency1, '->', t1.currency2, '->', t2.currency2) AS path
FROM exchange_rate t1
INNER JOIN exchange_rate t2 
  ON t1.currency2 = t2.currency1
WHERE 
  t1.currency1 = 'usd' 
  AND t2.currency2 = 'gbp'
  -- 过滤掉直接兑换的无中间节点路径
  AND t1.currency1 <> t2.currency2;

PostgreSQL适配说明

上述SQL可直接在PostgreSQL中运行，若数据量较大，可给currency1、currency2字段创建联合索引大幅提升查询效率。如果后续需要查询包含2个及以上中间货币的更长路径，可使用PostgreSQL自带的递归CTE（WITH RECURSIVE语法）实现任意步长的路径遍历。

ClickHouse适配说明

ClickHouse完全支持上述自关联语法，对于大体积的汇率表，建议将currency1设为表的排序键，可明显提升关联查询效率。如果需要实现长路径遍历，ClickHouse从21.8版本开始也支持递归CTE语法，可满足需求。

样例数据运行结果

对给出的样例表，上述查询会返回一条有效路径：

• 路径：usd->eur->gbp，计算得到的汇率为 0.87 * 0.88 = 0.7656

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