行转列简单案例

最新推荐文章于 2023-09-27 22:08:42 发布

小刘新鲜事儿

最新推荐文章于 2023-09-27 22:08:42 发布

阅读量94

点赞数

分类专栏： Hive

爱小刘，爱生活

本文链接：https://blog.csdn.net/weixin_45775873/article/details/109539143

版权

Hive 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

数据：

person_info.txt
孙悟空  白羊座  A
大海    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A

建表

0: jdbc:hive2://hadoop:11240> create table person_info(name string,constellation string,blood_type string)
. . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . > fields terminated by '\t';
0: jdbc:hive2://hadoop:11240> load data local inpath '/home/xiaokang/hivedata/person_info.txt'
. . . . . . . . . . . . . . > into table person_info;
0: jdbc:hive2://hadoop:11240> select * from person_info;
+-------------------+----------------------------+-------------------------+
| person_info.name  | person_info.constellation  | person_info.blood_type  |
+-------------------+----------------------------+-------------------------+
| 孙悟空               | 白羊座                        | A                       |
| 大海                | 射手座                        | A                       |
| 宋宋                | 白羊座                        | B                       |
| 猪八戒               | 白羊座                        | A                       |
| 凤姐                | 射手座                        | A                       |
+-------------------+----------------------------+-------------------------+

concat

0: jdbc:hive2://hadoop:11240> select concat(constellation,",",blood_type) from person_info;
+--------+
|  _c0   |
+--------+
| 白羊座,A  |
| 射手座,A  |
| 白羊座,B  |
| 白羊座,A  |
| 射手座,A  |
+--------+

collect_set

0: jdbc:hive2://hadoop:11240> select collect_set(name) from person_info;
+-------------------------------+
|              _c0              |
+-------------------------------+
| ["孙悟空","大海","宋宋","猪八戒","凤姐"]  |
+-------------------------------+

concat_ws

0: jdbc:hive2://hadoop:11240> select concat_ws('|',collect_set(name)) from person_info;
+-------------------+
|        _c0        |
+-------------------+
| 孙悟空|大海|宋宋|猪八戒|凤姐  |
+-------------------+

要求：把星座和血型一样的人归到一起

0: jdbc:hive2://hadoop:11240> select t1.base,
. . . . . . . . . . . . . . > concat_ws('|',collect_set(t1.name)) as name
. . . . . . . . . . . . . . > from (select name,
. . . . . . . . . . . . . . > concat(constellation,",",blood_type) as base
. . . . . . . . . . . . . . > from person_info) as t1
. . . . . . . . . . . . . . > group by t1.base;
+----------+----------+
| t1.base  |   name   |
+----------+----------+
| 射手座,A    | 大海|凤姐    |
| 白羊座,A    | 孙悟空|猪八戒  |
| 白羊座,B    | 宋宋       |
+----------+----------+