hive - Hive 上的多个左外连接

Question

在 Hive 中，我有两个表，如下所示：

SELECT * FROM p_test; OK p_test.id p_test.age 01 1 02 2 01 10 02 11 Time taken: 0.07 seconds, Fetched: 4 row(s)

SELECT * FROM p_test2; OK p_test2.id p_test2.height 02 172 01 170 Time taken: 0.053 seconds, Fetched: 2 row(s)

我应该得到 p_test 表中同一用户之间的年龄差异。因此，我通过row_number函数运行 HiveQL ，如下所示：

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(partition by id order by age asc) rn FROM p_test) t1 LEFT JOIN (SELECT *, ROW_NUMBER() OVER(partition by id order by age asc) rn FROM p_test) t2 ON t2.id=t1.id AND t1.rn=(t2.rn+1) LEFT JOIN (SELECT * FROM p_test2) t_2 ON t_2.id = t1.id;

结果是：

t1.id t1.age t1.rn t2.id t2.age t2.rn t_2.id t_2.height 01 1 1 NULL NULL NULL 01 170 01 10 2 01 1 1 01 170 02 11 1 NULL NULL NULL 02 172 02 2 2 02 11 1 02 172 Time taken: 60.773 seconds, Fetched: 4 row(s)

到目前为止一切都很好。但是，如果我将上面显示的左连接表t1和表t2的条件移动到最后一行，如下所示：

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(partition by id order by age asc) rn FROM p_test) t1 LEFT JOIN (SELECT *, ROW_NUMBER() OVER(partition by id order by age asc) rn FROM p_test) t2 LEFT JOIN (SELECT * FROM p_test2) t_2 ON t_2.id = t1.id AND t2.id=t1.id AND t1.rn=(t2.rn+1);

我得到了意想不到的结果，如下所示：

t1.id t1.age t1.rn t2.id t2.age t2.rn t_2.id t_2.height 01 1 1 01 1 1 NULL NULL 01 1 1 01 10 2 NULL NULL 01 1 1 02 11 1 NULL NULL 01 1 1 02 2 2 NULL NULL 01 10 2 01 1 1 01 170 01 10 2 01 10 2 NULL NULL 01 10 2 02 11 1 NULL NULL 01 10 2 02 2 2 NULL NULL 02 11 1 01 1 1 NULL NULL 02 11 1 01 10 2 NULL NULL 02 11 1 02 11 1 NULL NULL 02 11 1 02 2 2 NULL NULL 02 2 2 01 1 1 NULL NULL 02 2 2 01 10 2 NULL NULL 02 2 2 02 11 1 02 172 02 2 2 02 2 2 NULL NULL

看来我移到最后一行的条件不再起作用了。它困扰了我很长时间。希望我能听到任何有价值的答案，感谢任何提前为我提供答案的人。

标签： hivehiveql

Answer 1

在没有 ON 条件的 t2 的第二个查询LEFT JOIN中转换为CROSS JOIN. 这就是为什么你有重复。对于 id=01，您最初在子查询 t1 中有两行，在 t2 中有 2 行，此 CROSS 连接为您提供 2x2=4 行。

并且 ON 条件有效，但它仅适用于带有 t_2 子查询的最后一个 LEFT 连接，仅检查此条件以确定在最后一个连接中加入哪些行，而不是所有连接，它不影响第一个 CROSS JOIN (LEFT JOIN 没有 ON 条件）。

每个连接都应该有它自己的 ON 条件，交叉连接除外。

另请参阅有关没有 ON 条件行为的连接的答案：https ://stackoverflow.com/a/46843832/2700344

顺便说一句，您可以在没有 t2 join 的情况下使用滞后或领先分析函数来计算按年龄排序的值。

像这样：

lag(height) over(partition by id order by age)-- 获取之前的高度