如何在不使用自定义 UDF 或左联接的情况下在 Hive 中执行关键操作?

Question

示例输入：

PARTY      |TYPE | TIMESTAMP
-------------------------------------
135761103  |AA   |2017-04-24 11:18:46.233  
135761103  |BB   |2017-04-24 11:19:18.436

期望输出：

PARTY |AA_TIMESTAMP|BB_TIMESTAMP   
--------------------------------
135761103  |2017-04-24 11:18:46.233 |2017-04-24 11:19:18.436

Answer 1

您可以使用CASE和GROUP BY组合来做到这一点。像这样：

SELECT
PARTY,
max(CASE WHEN TYPE = 'AA' THEN TIMESTAMP END) AS AA_TIMESTAMP,
max(CASE WHEN TYPE = 'BB' THEN TIMESTAMP END) AS BB_TIMESTAMP
FROM 
MY_TAB
GROUP BY
PARTY