Hive 行列转换

太过爱你忘了你带给我的痛 2024-04-17 16:11 192阅读 0赞

**一、列转行** (对某列拆分，一列拆多行)

使用函数：lateral view explode(split(column, ',')) num

eg: 如表：t\_row\_to\_column\_tmp 数据如下，对tag列进行拆分

![format_png][]

SQL代码：

select id,tag,tag\_new

from t\_row\_to\_column\_tmp

lateral view explode(split(tag, ',')) num as tag\_new

where id=212022894;

![format_png 1][]

**二、行转列 **(根据主键，进行多行合并一列)

使用函数：concat\_ws(',',collect\_set(column))

说明：collect\_list 不去重，collect\_set 去重。 column 的数据类型要求是 string

eg：如表：t\_column\_to\_row ，根据id，对tag\_new 进行合并

![format_png 2][]

SQL代码1：

select id,

concat\_ws(',',collect\_set(tag\_new)) as tag\_col

from t\_column\_to\_row

group by id;

![format_png 3][]

SQL代码2：

select id,

concat\_ws(',',collect\_list(tag\_new)) as tag\_col

from t\_column\_to\_row

group by id;

![format_png 4][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvNTY0NzYwLzIwMTYxMi81NjQ3NjAtMjAxNjEyMjExOTU3NTc4NzAtMTE4NDAwMzk0NS5wbmc?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvNTY0NzYwLzIwMTYxMi81NjQ3NjAtMjAxNjEyMjEyMDAwNTYxNTEtMTM1ODE1NDY2OS5wbmc?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvNTY0NzYwLzIwMTYxMi81NjQ3NjAtMjAxNjEyMjEyMDE4Mjk1NDItMTY3MjI5NzQxMC5wbmc?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvNTY0NzYwLzIwMTYxMi81NjQ3NjAtMjAxNjEyMjEyMDE5NTY0MDEtMTYxMzI4OTQ5Ni5wbmc?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvNTY0NzYwLzIwMTYxMi81NjQ3NjAtMjAxNjEyMjEyMDE5Mjc0MTctMTE3MjQ1MTI5OS5wbmc?x-oss-process=image/format,png