我在其中一个实现中有一个场景要在hive上完成,以便进行报告。我有一个表结构,目前看起来如下-
+------+------+----------+----------+-------+-------+--------+--------+
| Col1 | Col2 | M1_Today | M2_Today | M1_LW | M2_LW | M1_L2W | M2_L2W |
+------+------+----------+----------+-------+-------+--------+--------+
| A | A1 | 10 | 200 | 9 | 190 | 11 | 210 |
| A | A2 | 12 | 210 | 11 | 200 | 13 | 220 |
| B | B1 | 15 | 300 | 14 | 290 | 16 | 310 |
| B | B2 | 18 | 310 | 17 | 300 | 19 | 320 |
+------+------+----------+----------+-------+-------+--------+--------+
表中的列需要转换为如下所示-
+------+------+-------+----+-----+
| Col1 | Col2 | Col3 | M1 | M2 |
+------+------+-------+----+-----+
| A | A1 | Today | 10 | 200 |
| A | A1 | LW | 9 | 190 |
| A | A1 | L2W | 11 | 210 |
| A | A2 | Today | 12 | 210 |
| A | A2 | LW | 11 | 200 |
| A | A2 | L2W | 13 | 220 |
| B | B1 | Today | 15 | 300 |
| B | B1 | LW | 16 | 310 |
| B | B1 | L2W | 14 | 290 |
| B | B2 | Today | 18 | 310 |
| B | B2 | LW | 17 | 300 |
| B | B2 | L2W | 19 | 320 |
+------+------+-------+----+-----+
如何通过sql实现这一点。我正在使用hive作为我的数据存储。非常感谢您的帮助
1条答案
按热度按时间b1uwtaje1#
你可以用这个: