时间戳

首页 > 解决方案 > 如何在 Hive 中加入具有多列的单个表？

sql - 如何在 Hive 中加入具有多列的单个表？

问题描述

我有 2 张表：一张有汇率（对 GBP），另一张有不同货币的各种金额列。我正在加入这两个表并以英镑显示所有金额值，但我得到很多空值。

> select * from xrates;
+--------------+--------------+--+
| xrates.curr  | xrates.rate  |
+--------------+--------------+--+
| GBP          | 1            |
| INR          | 89.74        |
| EUR          | 1.23         |
+--------------+--------------+--+

> select * from balances;
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+
| balances.acctbalance  | balances.acurr  | balances.interest  | balances.icurr  | balances.dividends  | balances.dcurr  |
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+
| 334.23                | GBP             | 1.2                | GBP             | 0                   | GBP             |
| 10000                 | INR             | 100                | EUR             | NULL                | GBP             |
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+

这是连接这两个表的查询

SELECT 
  acctbalance, acurr, acctbalance/rate as `AB to GBP`,
  interest, icurr, interest/rate as `Ints to GBP`,
  dividends, dcurr, dividends/rate as `Divnd to GBP`
FROM    
   balances           --table 1
LEFT JOIN 
   xrates             --table 2
  on acurr = curr     --account balance currency
 and icurr = curr     --interest currency
 and dcurr = curr     --dividend currency

+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
| acctbalance  | acurr  | ab to gbp  | interest  | icurr  | ints to gbp  | dividends  | dcurr  | divnd to gbp  |
+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
| 334.23       | GBP    | 334.23     | 1.2       | GBP    | 1.2          | 0          | GBP    | 0             |
| 10000        | INR    | NULL       | 100       | EUR    | NULL         | NULL       | GBP    | NULL          |
+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
(2nd row - converted to GBP columns - has nulls)

标签： sqlhivehiveql

解决方案

你又需要两个JOIN：

SELECT b.acctbalance, b.acurr, b.acctbalance / r1.rate as `AB to GBP`,
       b.interest, b.icurr, b.interest / r2.rate as `Ints to GBP`,
       b.dividends, b.dcurr, b.dividends / r3.rate as `Divnd to GBP`
FROM balances b LEFT JOIN 
     xrates r1           
     ON b.acurr = r1.curr LEFT JOIN
     xrates r2  
     ON b.icurr = r2.curr LEFT JOIN
     xrates r3 
     ON b.dcurr = r3.curr;

推荐阅读