行转列 CONCAT concat(name,":",age)拼接符返回输入字符串连接后的结果 concat_ws(",")给个参数,后面每个都有,
collect_list不去重
collect_set 去重
列转行
select
concat(name,":"blood_type)
from
v_hangzhuanlie
+------+-------------+----------+
| name|constellation|blood_type|
+------+-------------+----------+
|孙悟空| 白羊座| A|
| 娜娜| 射手座| A|
| 宋宋| 白羊座| B|
|猪八戒| 白羊座| A|
| 凤姐| 射手座| A|
+------+-------------+----------+
concat(id,":"gender) 结果是列式
+---------------------------+
|concat(name, :, blood_type)|
+---------------------------+
| 孙悟空:A|
| 娜娜:A|
| 宋宋:B|
| 猪八戒:A|
| 凤姐:A|
+---------------------------+
concat_ws(传入一个参数如:(","))
concat_ws(",",name,blood_type)
+------------------------------+
|concat_ws(,, name, blood_type)|
+------------------------------+
| 孙悟空,A|
| 娜娜,A|
| 宋宋,B|
| 猪八戒,A|
| 凤姐,A|
+------------------------------+
----------
COLLECT_SET 聚合操作.将一列组成数组 去除重复的,collect_list不去重
collect_set(name)
------------------------------
| name|constellation|blood_type|
SELECT
concat(constellation,",",blood_type),
concat_ws("|",COLLECT_SET(name))
FROM
v_hangzhuanlie
GROUP BY blood_type,constellation
+------------------------------------+-------------------------------+
|concat(constellation, ,, blood_type)|concat_ws(|, collect_set(name))|
+------------------------------------+-------------------------------+
| 白羊座,A| 猪八戒|孙悟空|
| 白羊座,B| 宋宋|
| 射手座,A| 娜娜|凤姐|
+------------------------------------+-------------------------------+
------------------------------------------------------------------------------------------
列转行
+-------------+-----------------------+
| movie| category|
+-------------+-----------------------+
| 《疑犯追踪》| 悬疑 动作 科幻剧情|
|《Lie to me》|悬疑 警匪 动作 心理剧情|
| 《战狼2》| 战争 动作 灾难|
+-------------+-----------------------+
explode将数据变为一行一行
lateral view ==开窗让别的字段能对应,movin,如过explode(category)name就有很多列,而movie只有三个
movie_info lateral view explode(category) table_tmp as category_name;
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;