如何在配置单元中取消激活表？

9gm1akwq 于 2021-05-29 发布在 Hadoop

关注(0)|答案(1)|浏览(228)

我的数据集如下：

item|location|week1|week2|week3|week4
_____________________________________
1000|10000000|1.2  |2.2  |3.2  |4.5  
1001|10000001|1.8  |2.5  |3.5  |4.1  
1002|10000002|9.3  |2.9  |3.7  |4.8

我希望数据如下所示：

item|location|week_name|week_value
__________________________________
1000|10000000|week1    |1.2       
1000|10000000|week2    |2.2       
1000|10000000|week3    |3.2
1000|10000000|week4    |4.5
1001|10000001|week1    |1.8
1001|10000001|week2    |2.5
1001|10000001|week3    |3.5
1001|10000001|week4    |4.1
1002|10000002|week1    |9.3
1002|10000002|week2    |2.9
1002|10000002|week3    |3.7
1002|10000002|week4    |4.8

告诉我有什么有效的方法/查询吗？

hadoop Hive hiveql

来源：https://stackoverflow.com/questions/46188691/how-to-unpivot-table-in-hive

1条答案

按热度按时间

x8diyxa71#

根据op回复更新我的评论（使用周号而不是周名）

select  item
       ,location
       ,pe.pos+1  as week_number
       ,pe.val    as week_value

from    mytable t
        lateral view    posexplode(array(week1,week2,week3,week4)) pe
;

+-------+-----------+--------------+-------------+
| item  | location  | week_number  | week_value  |
+-------+-----------+--------------+-------------+
| 1000  | 10000000  | 1            | 1.2         |
| 1000  | 10000000  | 2            | 2.2         |
| 1000  | 10000000  | 3            | 3.2         |
| 1000  | 10000000  | 4            | 4.5         |
| 1001  | 10000001  | 1            | 1.8         |
| 1001  | 10000001  | 2            | 2.5         |
| 1001  | 10000001  | 3            | 3.5         |
| 1001  | 10000001  | 4            | 4.1         |
| 1002  | 10000002  | 1            | 9.3         |
| 1002  | 10000002  | 2            | 2.9         |
| 1002  | 10000002  | 3            | 3.7         |
| 1002  | 10000002  | 4            | 4.8         |
+-------+-----------+--------------+-------------+

赞(0）回复(0）举报 2021-05-29

我来回答

如何在配置单元中取消激活表？

1条答案

相关问题

热门标签

最新问答