如何在配置单元中反转表？
问题描述：
我有数据集象下面这样：如何在配置单元中反转表？
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
我要将数据UNPIVOT象下面这样：
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
告诉我任何有效的方法/查询办呢？
答
*更新根据对我的评论的回复OP（使用而不是week_name WEEK_NUMBER）
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 |
+-------+-----------+--------------+-------------+
有每星期使用名称的点（例如'week2'），而不是只一周数字（例如'2'）？ –
不，我已经将它转换为数字，感谢您的帮助:) –