hive - SQL/HIVE - 如何从水平输出查询到垂直输出？

Question

我有一个查询返回两行所需的信息

SELECT src_file_dt, a, b ,c FROM my_table WHERE src_file_dt IN ('1531675040', '1531675169');

它将返回：

src_file_dt | a | b | c  
1531675040  | 2 | 6 | 9  
1531675169  | 8 | 2 | 0 

现在，我需要以下布局中的数据，我如何获得这样的输出：

fields | prev (1531675040) | curr (1531675169)
a      |   2               | 8
b      |   6               | 2
c      |   9               | 0

标签： hivehiveql

Answer 1

应该有更简单的方法来实现这一点。我已经使用explode函数构建结果并分别为上一列和下一列选择数据：

select t1.keys, t1.vals as prev, t2.vals as next from (
SELECT explode(map('a', a, 'b', b, 'c',c)) as (keys, vals)
FROM my_table 
WHERE src_file_dt = '1531675040'
) t1, 
(
SELECT explode(map('a', a, 'b', b, 'c',c)) as (keys, vals)
FROM my_table 
WHERE src_file_dt = '1531675169'
) t2
where t1.keys = t2.keys
;