Hive SQL中如何基于SELECT子查询结果设置IF条件字段

报错原因 #

Spark SQL（你当前使用的Hive执行引擎为Spark）不支持在SELECT子句的IF判断条件中直接嵌套IN子查询，该类关联匹配逻辑需通过表关联实现。

推荐实现方案 #

方案1：LEFT JOIN关联判断（通用兼容写法） #

先对t1表符合条件的deviceid去重，再和t2表左关联，通过判断关联字段是否非空实现原逻辑：

SELECT
    t2.request_id,
    t2.charge_click_cnt,
    IF(t1.deviceid IS NOT NULL, 'shop_user', 'non_shop_user') AS shop_user
FROM t2
LEFT JOIN (
    -- 去重避免关联后数据膨胀
    SELECT DISTINCT deviceid
    FROM t1
    WHERE dt BETWEEN '20210908' AND '20210915'
) t1 ON t2.uuid = t1.deviceid

方案2：小表场景性能优化 #

如果t1筛选后的数据集很小，可以加广播提示优化关联性能，避免shuffle：

SELECT /*+ MAPJOIN(t1) */
    t2.request_id,
    t2.charge_click_cnt,
    IF(t1.deviceid IS NOT NULL, 'shop_user', 'non_shop_user') AS shop_user
FROM t2
LEFT JOIN (
    SELECT DISTINCT deviceid
    FROM t1
    WHERE dt BETWEEN '20210908' AND '20210915'
) t1 ON t2.uuid = t1.deviceid

注意事项 #

• 必须对t1子查询结果加DISTINCT去重，否则t1中同一个deviceid存在多条匹配记录时，会导致t2的对应行重复，结果数据量异常
• 若t2的uuid字段存在空值，空值会自动归类为non_shop_user，如果有特殊判断需求可在IF中补充额外条件

内容的提问来源于stack exchange，提问作者Bowen Peng