hive常见行列转换示例

1. 列转行

举例

select * from xy_temp.leon_test;
+------+--------+--+
| uid  |  tag   |
+------+--------+--+
| 1    | 1,2,3  |
+------+--------+--+
1 row selected (0.818 seconds)

列转行

> select 
>     uid,
>     tag,
>     tag_new
> from xy_temp.leon_test
> lateral view explode(split(tag, ',')) num as tag_new
> where uid=1;
+------+--------+----------+--+
| uid  |  tag   | tag_new  |
+------+--------+----------+--+
| 1    | 1,2,3  | 1        |
| 1    | 1,2,3  | 2        |
| 1    | 1,2,3  | 3        |
+------+--------+----------+--+
3 rows selected (26.399 seconds)

 2. 行转列

举例

> select * from xy_temp.leon_test where uid=2;
+------+------+--+
| uid  | tag  |
+------+------+--+
| 2    | 11   |
| 2    | 1    |
| 2    | 111  |
| 2    | 111  |
+------+------+--+

行转列——不去重

> select concat_ws('|', collect_list(tag)) from xy_temp.leon_test where uid=2;
+----------------------------------+--+
| concat_ws(|, collect_list(tag))  |
+----------------------------------+--+
| 11|1|111|111                     |
+----------------------------------+--+
1 row selected (4.355 seconds) 

行转列——去重

 > select concat_ws('|', collect_set(tag)) from xy_temp.leon_test where uid=2;
+---------------------------------+--+
| concat_ws(|, collect_set(tag))  |
+---------------------------------+--+
| 111|1|11                        |
+---------------------------------+--+
1 row selected (4.009 seconds)