hive多表left join问题

Question

sql中，有许多张表都要与t1left join，其中t3表是一张费率的维度表，用的是以下关联逻辑：商户id匹配，币种匹配，支付方式匹配，且t1的订单时间晚于t3的生效时间，若匹配到多条，取最新的一条费率，请问如何实现

谢谢大家

Answer 1

阿里嘎多学长整理AIGC生成，因移动端显示问题导致当前答案未能完全显示，请使用PC端查看更加详细的解答过程

Hive多表left join问题

你遇到了一个复杂的 Hive left join 问题，涉及到多张表的关联和过滤条件。下面是解决方案：

首先，使用 LATERAL VIEW 和 FROM 子句来实现多表 left join：

FROM (
  SELECT *
  FROM t1
  LATERAL VIEW explode(array(
    SELECT *
    FROM t2
    WHERE t2.merchant_id = t1.merchant_id
      AND t2.currency = t1.currency
      AND t2.payment_method = t1.payment_method
  )) t2
) t1
LEFT JOIN t3
ON t1.merchant_id = t3.merchant_id
  AND t1.currency = t3.currency
  AND t1.payment_method = t3.payment_method
  AND t1.order_time > t3.effective_time

这里，我们使用 LATERAL VIEW 将 t2 表中的数据展开到 t1 表中，然后使用 LEFT JOIN 将 t3 表连接到结果中。

其次，我们使用 ROW_NUMBER 函数来过滤出最新的一条记录：

WITH joined_data AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY t1.merchant_id, t1.currency, t1.payment_method
                                 ORDER BY t1.order_time DESC) AS row_num
  FROM (
    SELECT *
    FROM t1
    LATERAL VIEW explode(array(
      SELECT *
      FROM t2
      WHERE t2.merchant_id = t1.merchant_id
        AND t2.currency = t1.currency
        AND t2.payment_method = t1.payment_method
    )) t2
  ) t1
  LEFT JOIN t3
  ON t1.merchant_id = t3.merchant_id
    AND t1.currency = t3.currency
    AND t1.payment_method = t3.payment_method
    AND t1.order_time > t3.effective_time
)
SELECT *
FROM joined_data
WHERE row_num = 1

这里，我们使用 ROW_NUMBER 函数将结果分区后排序，然后选择 row_num = 1 的记录。

最后，我们可以使用 Hive 的 DISTRIBUTE BY 和 SORT BY 语句来优化查询：

WITH joined_data AS (
  ...
)
SELECT *
FROM joined_data
DISTRIBUTE BY t1.merchant_id, t1.currency, t1.payment_method
SORT BY t1.order_time DESC

这将将结果分区后排序，并且可以提高查询性能。

希望这个解决方案能够帮助你解决问题！