行转列,列转行 炸裂 lateral view explode(category)

  

 

 

行转列 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;