HUE中编写SQL实现两表左连接按最近前置日期匹配的查询问题

需求规则 #

• 关联方式：Table1 左连接 Table2
• 关联条件：
  • 两表boat_code（船舶唯一编码）字段值相等
  • 对同一艘船，匹配Table2中早于Table1.Boat_service_date（船舶服务日期）的、最近的一条Boat_purchase/Ownership_change_date（船舶购置/权属变更日期）对应的记录

实现方案 #

Hive/Impala（HUE默认对接的查询引擎）不支持直接在非等值关联中直接取最近匹配记录，通过窗口函数先给权属表排序再关联的方式实现，逻辑稳定兼容性强。

可直接运行的SQL代码 #

SELECT
  t1.*,
  t2.`Boat_purchase/Ownership_change_date`,
  t2.owner -- 此处替换为Table2中你需要输出的其余字段即可
FROM Table1 t1
LEFT JOIN (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY boat_code
      ORDER BY `Boat_purchase/Ownership_change_date` DESC
    ) AS rn
  FROM Table2
) t2
  ON t1.boat_code = t2.boat_code
  AND t2.`Boat_purchase/Ownership_change_date` <= t1.Boat_service_date
  AND t2.rn = 1;

注意事项 #

• 如果你Table1中的船舶编码字段实际拼写为boad_code（描述中出现过该拼写笔误），将关联条件中的t1.boat_code替换为t1.boad_code即可
• 如果业务要求权属变更日期必须严格早于服务日期（不允许和服务日期为同一天），将关联条件中的<=替换为<
• 该写法无额外依赖，在HUE的Hive、Impala查询环境下均可直接运行

内容的提问来源于stack exchange，提问作者Amit Pandya