如何在Hive中利用外部查询列关联子查询跨表获取聚合结果

我懂你碰到的麻烦了——Hive对关联子查询的支持确实有限，你原来那种在FROM子句里引用外部表字段的写法肯定跑不通。别急，咱们换个更符合Hive特性的思路：用表连接+分组聚合就能搞定这个需求。

核心思路 #

先把账户交易表（acc）和汇率表（currency）做关联，过滤出每个交易对应的有效汇率数据，再按交易维度分组求和。具体逻辑：

• 关联条件1：货币类型必须匹配（acc.currency = currency.curr_type）
• 关联条件2：汇率日期必须落在交易的起始日期（last_trans_date）和结束日期（trans_date）之间
• 最后按每笔交易的唯一标识分组，对匹配到的汇率求和

完整SQL代码 #

SELECT 
    A.account,
    A.balance,
    A.trans_date AS d2,
    A.last_trans_date AS d1,
    A.currency,
    SUM(B.rate) AS exchng_rt
FROM 
    acc A
JOIN 
    currency B 
    ON A.currency = B.curr_type 
    AND B.banking_date BETWEEN A.last_trans_date AND A.trans_date
GROUP BY 
    A.account,
    A.balance,
    A.trans_date,
    A.last_trans_date,
    A.currency;

补充说明 #

1. GROUP BY的注意事项：Hive要求SELECT中的非聚合字段必须全部出现在GROUP BY里，所以上面的分组字段要和SELECT里除了SUM(B.rate)之外的字段保持一致。
2. 处理无匹配汇率的情况：如果有些交易在日期区间内没有对应的汇率数据，上面的JOIN会直接过滤掉这些交易。要是想保留所有交易记录，把JOIN改成LEFT JOIN，并用COALESCE把求和的NULL值替换成0：

SELECT 
    A.account,
    A.balance,
    A.trans_date AS d2,
    A.last_trans_date AS d1,
    A.currency,
    COALESCE(SUM(B.rate), 0) AS exchng_rt
FROM 
    acc A
LEFT JOIN 
    currency B 
    ON A.currency = B.curr_type 
    AND B.banking_date BETWEEN A.last_trans_date AND A.trans_date
GROUP BY 
    A.account,
    A.balance,
    A.trans_date,
    A.last_trans_date,
    A.currency;

用这个写法，就能完美实现你要的“针对每个账户交易，计算对应日期区间内货币汇率总和”的需求啦。

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