join - 使用Hive有效地将同一表连接到不同列

我正在尝试尽可能有效地解决这个问题。

假设我有一个表，ids，其中有一个包含整数列表的单列，我们称之为“ ids”：

ids
0
1
2
3
4
5
6

我还有另一个表，items，其中有几列整数：

item1    item2    item3    item4
5        2        2        4
9        2        1        19
0        25       9        2

我想做的是从table2中选择所有列，但是如果值不在“ ids”表中，则我希望为NULL。所以我的结果看起来像这样：

item1    item2    item3    item4
5        2        2        4
NULL     2        1        NULL
0        NULL     NULL     2

我可以通过从项目中选择并加入每个列的ID来做到这一点：

SELECT CASE WHEN ids1.id IS NOT NULL THEN items.item1 ELSE NULL,
       CASE WHEN ids2.id IS NOT NULL THEN items.item2 ELSE NULL,
       CASE WHEN ids3.id IS NOT NULL THEN items.item3 ELSE NULL,
       CASE WHEN ids4.id IS NOT NULL THEN items.item4 ELSE NULL,
FROM items
LEFT OUTER JOIN ids ids1 ON (ids1.id = items.item1),
LEFT OUTER JOIN ids ids2 ON (ids2.id = items.item2),
LEFT OUTER JOIN ids ids3 ON (ids3.id = items.item3),
LEFT OUTER JOIN ids ids4 ON (ids4.id = items.item4)

不幸的是，当这些表变大（数以百万计的行）时，必须将其连接到同一张表4次是很麻烦的。有没有更快或更有效的方法来做到这一点？

谢谢！

最佳答案

这是一个示例，它通过对其进行规范化，左连接以对id进行测试，最后将其向后旋转来进行循环。

create table ids ( id int );
insert ids values(0),(1),(2),(3),(4),(5),(6);
create table items (itemid int, item1 int, item2 int, item3 int, item4 int);
insert items select
1,5,2,2,4 union all select
2,9,2,1,19 union all select
3,0,25,9,2;

select *
from (
    select u.itemid,ids.id,u.item
    from items
    unpivot (id for item in (item1,item2,item3,item4)) u
    left join ids on ids.id = u.id
) x
pivot (max(id) for item in (item1,item2,item3,item4)) p

关于join - 使用Hive有效地将同一表连接到不同列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12738414/