Oracle转Hive：解决子查询仅允许单字段选择的查询改写问题

需求：按parent_dr_sid分组，获取每组内version#最大的最新记录（DR_SID为主键，parent_dr_sid关联同表旧记录，仅通过查询实现，无法更新表）。原Oracle查询在Hive中执行报错SubQuery can contain only 1 item in Select List，需改写为Hive兼容语句。

可本地测试的EMP表结构及示例数据 #

建表语句 #

CREATE TABLE "EMP" 
(    
    "DR_SID" NUMBER, 
    "DR_NAME" VARCHAR2(50 BYTE) COLLATE "USING_NLS_COMP", 
    "ACTIVE_FLAG" VARCHAR2(1 BYTE) COLLATE "USING_NLS_COMP", 
    "LAST_UPDATED_TIME" TIMESTAMP (6), 
    "DATA_SOURCE" VARCHAR2(100 BYTE) COLLATE "USING_NLS_COMP", 
    "ROW_LIMIT" VARCHAR2(50 BYTE) COLLATE "USING_NLS_COMP", 
    "VERSION#" NUMBER, 
    "PARENT_DR_SID" NUMBER
) ;

插入示例数据 #

REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (DR_SID,DR_NAME,LAST_UPDATED_TIME,VERSION#,PARENT_DR_SID) values (1,'this should not come1',to_timestamp('18-APR-20 05.05.52.425734000 AM','DD-MON-RR HH.MI.SSXFF AM'),1,1);
Insert into EMP (DR_SID,DR_NAME,LAST_UPDATED_TIME,VERSION#,PARENT_DR_SID) values (2,'come',to_timestamp('19-SEP-20 07.18.56.271199000 AM','DD-MON-RR HH.MI.SSXFF AM'),1,2);
Insert into EMP (DR_SID,DR_NAME,LAST_UPDATED_TIME,VERSION#,PARENT_DR_SID) values (3,'come123',to_timestamp('13-FEB-21 05.05.51.645956000 AM','DD-MON-RR HH.MI.SSXFF AM'),1,3);
Insert into EMP (DR_SID,DR_NAME,LAST_UPDATED_TIME,VERSION#,PARENT_DR_SID) values (4,'come456',to_timestamp('13-FEB-21 05.05.51.951505000 AM','DD-MON-RR HH.MI.SSXFF AM'),1,4);
Insert into EMP (DR_SID,DR_NAME,LAST_UPDATED_TIME,VERSION#,PARENT_DR_SID) values (5,'this should not come2',to_timestamp('18-APR-20 05.05.52.425734000 AM','DD-MON-RR HH.MI.SSXFF AM'),2,1);
Insert into EMP (DR_SID,DR_NAME,LAST_UPDATED_TIME,VERSION#,PARENT_DR_SID) values (6,'this should COME',to_timestamp('18-APR-20 05.05.52.425734000 AM','DD-MON-RR HH.MI.SSXFF AM'),3,1);

全表查询验证 #

SELECT DR_SID, DR_NAME, LAST_UPDATED_TIME, VERSION#, PARENT_DR_SID FROM emp ;

原Oracle查询及报错 #

原Oracle查询语句 #

SELECT DR_SID, DR_NAME, LAST_UPDATED_TIME, VERSION#, PARENT_DR_SID FROM emp t
where (version#,parent_dr_sid)
in (select max(version#),parent_dr_sid from emp group by parent_dr_sid)
;

报错信息 #

SubQuery can contain only 1 item in Select List
原因：Hive不支持Oracle这种多列元组匹配的IN语法，子查询仅允许返回单列。

Hive兼容的改写方案 #

方案1：JOIN关联分组最大版本记录 #

通过子查询先计算每个parent_dr_sid对应的最大version#，再与原表关联筛选目标记录：

SELECT t.DR_SID, t.DR_NAME, t.LAST_UPDATED_TIME, t.VERSION#, t.PARENT_DR_SID
FROM emp t
JOIN (
    SELECT PARENT_DR_SID, MAX(VERSION#) AS max_version
    FROM emp
    GROUP BY PARENT_DR_SID
) m ON t.PARENT_DR_SID = m.PARENT_DR_SID AND t.VERSION# = m.max_version;

方案2：窗口函数实现（推荐） #

使用ROW_NUMBER()窗口函数按分组排序后取第一条，逻辑更清晰，且支持复杂排序规则：

SELECT DR_SID, DR_NAME, LAST_UPDATED_TIME, VERSION#, PARENT_DR_SID
FROM (
    SELECT 
        DR_SID, DR_NAME, LAST_UPDATED_TIME, VERSION#, PARENT_DR_SID,
        -- 按parent_dr_sid分组，组内按version#降序排序，生成行号
        ROW_NUMBER() OVER(PARTITION BY PARENT_DR_SID ORDER BY VERSION# DESC) AS rn
    FROM emp
) t
WHERE rn = 1;

• 若同一分组内存在多个相同最大版本的记录，需保留所有这类记录时，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

期望输出结果 #

DR_SID, DR_NAME, LAST_UPDATED_TIME, VERSION#, PARENT_DR_SID
1   this should not come1   18-APR-20 05.05.52.425734000 AM 1   1
2   come    19-SEP-20 07.18.56.271199000 AM 1   2
3   come123 13-FEB-21 05.05.51.645956000 AM 1   3
4   come456 13-FEB-21 05.05.51.951505000 AM 1   4
6   this should COME    18-APR-20 05.05.52.425734000 AM 3   1

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