Hive / SQL -左联接和回退

Question

在Apache中，我必须使用我想要左加入的表--将所有数据保存在左边的数据中，并在可能的情况下从右表中添加数据。为此，我使用两个联接，因为连接基于两个字段( material_id和location_id)。这在两个传统的左联接中运行得很好：

SELECT 
   a.*, 
   b.*
FROM a
INNER JOIN (some more complex select) b
   ON a.material_id=b.material_id 
   AND a.location_id=b.location_id;

对于location_id数据库只包含两个不同的值，例如1和2.

我们现在有这样的要求:如果没有“完美匹配”，这意味着只有material_id可以加入，而对于b表中的location_id，没有material_id和location_id (例如material_id=100和location_id=1)的正确组合，连接应该“默认”或“回退”到location_id (例如material_id=001和location_id=2 )的其他可能值，反之亦然。这应该只适用于location_id。

我们已经调查了所有可能的答案，也有案例等等，但没有得逞。像这样的设置

...
ON a.material_id=b.material_id AND a.location_id=
CASE WHEN a.location_id = b.location_id THEN b.location_id ELSE ...;

我们试图或没有弄清楚如何真正地在蜂巢查询语言。

谢谢你的帮助!也许有人有个聪明的主意。

以下是一些示例数据：

Table a
| material_id | location_id | other_column_a |
| 100         | 1           | 45            |
| 101         | 1           | 45            |
| 103         | 1           | 45            |
| 103         | 2           | 45            |



Table b
| material_id | location_id | other_column_b |
| 100         | 1           | 66            |
| 102         | 1           | 76            |
| 103         | 2           | 88            |


Left - Join Table
| material_id | location_id | other_column_a | other_column_b
| 100         | 1           | 45            | 66
| 101         | 1           | 45            | NULL (mat. not in b)
| 103         | 1           | 45            | DEFAULT TO where location_id=2 (88)
| 103         | 2           | 45            | 88

PS:如前所述，here存在等在子查询中不起作用。

Answer 1

解决方案是在没有a.location_id = b.location_id的情况下离开join，并按首选项顺序对所有行进行编号。然后用row_number进行过滤。在连接下面的代码中，首先将重复行，因为所有匹配的material_id都将被连接，然后row_number()函数将1分配给a.location_id = b.location_id的行，2分配给a.location_id <> b.location_id存在的行(如果存在的话)，还有1行(如果不存在a.location_id = b.location_id )。b.location_id在row_number()函数中添加到order by中，因此在没有精确匹配的情况下，它将“更喜欢”具有较低b.location_id的行。我希望你已经领会了这个想法。

select * from 
(
SELECT 
   a.*, 
   b.*,
   row_number() over(partition by material_id 
                     order by CASE WHEN a.location_id = b.location_id THEN 1 ELSE 2 END, b.location_id ) as rn
FROM a
LEFT JOIN (some more complex select) b
   ON a.material_id=b.material_id 
)s 
where rn=1
;

Answer 2

也许这对未来的一些人有帮助：

我们也想出了一种不同的方法。

首先，我们创建另一个表来计算表b中的平均值，该表基于所有的material_id (!)位置。

其次，在join表中我们创建了三列: c1 -- material_id和location_id匹配的值(由表a与表b的左连接得到)。如果没有完全匹配，则此列为空。

c2 -表中的值，我们从这个material_id的平均值(回退)表中写入数字(不管位置)

c3 -“实际值”列，在该列中，我们使用case语句来确定当第1列为NULL时(材料和位置没有完全匹配)，然后使用来自第2列的值(对材料的所有其他位置的平均值)进行进一步的计算。