---------行轉(zhuǎn)列-------------
-- 行轉(zhuǎn)列是指多行數(shù)據(jù)轉(zhuǎn)換為一個(gè)列的字段

-- 1、準(zhǔn)備emp.txt數(shù)據(jù)
-- 需求：將序號(hào)相同的人放到一列
/*
20      SMITH
30      ALLEN
30      WARD
20      JOINS
30      MARTIN
10      CLARK
20      SCOTT
10      KING
30      TURNER
20      ADAMS
30      JAMES
20      FORD
10      MILLER
*/

create table emp
(
    deptno int comment '部門編號(hào)',
    ename  string comment '雇員姓名'
)
    row format delimited fields terminated by '\t';

load data local inpath '/export/data/emp.txt' overwrite into table emp;

select *
from emp;

-- 行轉(zhuǎn)列
-- collect_list(不去重)/collect_set(去重)
-- 該函數(shù)也是一個(gè)聚合函數(shù)，將同一組中的字段值存放到一個(gè)數(shù)組中（返回一個(gè)數(shù)組）
select deptno,
       collect_list(ename) enames
from emp
group by deptno;

-- concat_ws的作用
/**
  concat_ws(sep, str1, str2, ...):以分隔符拼接每個(gè)字符串
  sep:分隔符
  str1, str2, ...:要拼接的字符串

  也可以傳入一個(gè)字符串?dāng)?shù)組
  concat_ws(sep, 字符串?dāng)?shù)組引用)
 */
select concat_ws('|', 'aa', 'bb', 'cc');
-- select concat_ws("|", 數(shù)組);

-- concat_ws遍歷數(shù)組，每遍歷一個(gè)元素就進(jìn)行字符串拼接，分隔符是 |
select deptno,
       concat_ws('|', collect_list(ename)) as enames
from emp
group by deptno;

-- 向原表中添加10號(hào)部門重復(fù)的員工名
insert into emp
values (10, 'KING');

-- 去除重復(fù)元素 collect_set
select deptno,
       concat_ws('|', collect_set(ename)) as enames
from emp
group by deptno;


------------------列轉(zhuǎn)行--------------------
-- 1、準(zhǔn)備數(shù)據(jù)emp2.txt
-- 將每個(gè)人拆成單獨(dú)一行
/**
10  CLARK|KING|MILLER
20  SMITH|JONES|SCOTT|ADAMS|FORD
30  ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES
 */
create table emp2
(
    deptno int comment '部門編號(hào)',
    enames array<string> comment '部門雇員姓名集合'
)
    row format delimited fields terminated by '\t'
        collection items terminated by '|';

load data local inpath '/export/data/emp2.txt' overwrite into table emp2;

select *
from emp2;

/**
    explode(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。
    explode(ARRAY)  數(shù)組的每個(gè)元素生成一行
    explode(MAP)    map中每個(gè)key-value對(duì)，生成一行，key為一列，value為一列
 */
-- 使用explode函數(shù)進(jìn)行炸裂操作
select explode(enames)
from emp2;

/**
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用于和split, explode等UDTF一起使用，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對(duì)拆分后的數(shù)據(jù)進(jìn)行聚合。
 */
-- 需要使用LATERAL VIEW側(cè)視圖和explode配合進(jìn)行分析
/*
    emp2表和explode生成的表進(jìn)行join，判斷explode的每一行是否包含在emp2表的數(shù)組中
    如果在數(shù)組中，則join成功，否則失敗
    tmp_tbz:是explode生成的中間臨時(shí)表的別名
    as name:輸出列的別名
 */
select deptno, name
from emp2 lateral view explode(enames) tmp_tb as name;


-- map列轉(zhuǎn)行
/*
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26
*/
create table relation_map
(
    id      int comment 'id',
    name    string comment '姓名',
    members map<string,string>comment '家庭成員',
    age     int comment '年齡'
)
    row format delimited fields terminated by ','
        collection items terminated by '#'
        map keys terminated by ":";

load data local inpath '/export/data/map.txt' overwrite into table relation_map;

select *
from relation_map;

select explode(members)
from relation_map;

select id,
       name,
       age,
       tmp_tb.relation,
       tmp_tb.relation_name
from relation_map
         lateral view explode(members) tmp_tb as relation, relation_name;