hive - 如何在 Hive 中分组集后 reshape 数据？

Question

我想聚合许多不同维度的列。我认为 GOUPING SETS 适合我的问题，但我无法弄清楚如何转换/ reshape GROUPING SETS 的结果表。

这是我使用 GROUPING SETS 的查询:

select date, dim1, dim2, dim3, sum(value) as sum_value
from table
grouping by date, dim1, dim2, dim3
grouping sets ((date, dim1), (date, dim2), (date, dim3))

查询将产生如下表:

date        dim1    dim2    dim3    sum_value
2017-01-01  A       NULL    NULL    [value_A]
2017-01-01  B       NULL    NULL    [value_B]
2017-01-01  NULL    C       NULL    [value_C]
2017-01-01  NULL    D       NULL    [value_D]
2017-01-01  NULL    NULL    E       [value_E]
2017-01-01  NULL    NULL    F       [value_F]

但我真正需要的是这样的表格:

date        dim     factor  sum_value
2017-01-01  dim1     A      [value_A]
2017-01-01  dim1     B      [value_B]
2017-01-01  dim2     C      [value_C]
2017-01-01  dim2     D      [value_D]
2017-01-01  dim3     E      [value_E]
2017-01-01  dim3     F      [value_F]

实际维度数远远超过 3，因此对查询进行硬编码并不是一个好主意。有没有办法通过分组集或其他聚合方法来 reshape 表格以获得所需的表格？

谢谢!

Answer 1

select    `date`
         ,elt(log2(GROUPING__ID - 1),'dim1','dim2','dim3')      as dim
         ,coalesce (dim1,dim2,dim3)                             as factor
         ,sum(value)                                            as sum_value

from      `table`

group by  `date`,dim1,dim2,dim3
          grouping sets ((`date`,dim1),(`date`,dim2),(`date`,dim3))