join - 使用 Hive 有效地连接不同列上的同一个表

Question

我正在努力尽可能高效地解决这个问题。

假设我有一个表 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

我可以通过从 items 中选择并加入每个列的 ids 来做到这一点:

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 次会很麻烦。有没有更快或更有效的方法来做到这一点？

谢谢!

Answer 1

这是一个通过规范化循环的示例，左连接以测试 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