列轉(zhuǎn)行
| id | data |
|---|---|
| 1 | a1_a2_a3 |
| 2 | b |
數(shù)據(jù)分析時(shí)需要行中的數(shù)據(jù)拆成多列，則需要用到：
1叽奥，lateral view
2奖蔓，explode
原始表table1數(shù)據(jù)如下：
| id | data |
|---|---|
| 1 | a1_a2_a3 |
| 2 | b |
| id | rd |
|---|---|
| 1 | a1 |
| 1 | a2 |
| 1 | a3 |
| 2 | b |
以上數(shù)據(jù)需要處理成
| id | rd |
|---|---|
| 1 | a1 |
| 1 | a2 |
| 1 | a3 |
| 2 | b |
就需要列轉(zhuǎn)行
select id, rd from table1 lateral view explode(split(data,'_')) datas as rd
行轉(zhuǎn)列
| id | rd |
|---|---|
| 1 | a1 |
| 1 | a2 |
| 1 | a3 |
| 2 | b |
如果需要把
| id | rd |
|---|---|
| 1 | a1 |
| 1 | a2 |
| 1 | a3 |
| 2 | b |
| id | data |
|---|---|
| 1 | a1_a2_a3 |
| 2 | b |
變成
| id | data |
|---|---|
| 1 | a1_a2_a3 |
| 2 | b |
select id, concat_ws(',',collect_set(rd)) as data
from table1
group by id;
