行列转换

列转行：collect_set(),collect_list()

两者常与group by一起使用，将分组中的某列转为一个数组返回
不同的是collect_list不去重，collect_set去重

示例Table：

colA	colB
1	A
1	B
1	A
2	C
3	A
3	D

• collect_set() 去重

  select colA,
  collect_set(colB) as set B 
  from Table
  group by colA 

  Result：

  colA	setB
  1	[‘A’,’B’]
  2	[‘C’]
  3	[‘A’,’D’]

• collect_list() 不去重

  select colA,
  collect_list(colB) as listB
  form Table 
  group by colA 

  Result：

  colA	listB
  1	[‘A’,’B’,’A’]
  2	[‘C’]
  3	[‘A’,’D’]

行转列：explode(array)

数组中的每个元素生成一行
select中无法产生其他列

示例T1：

colA	colB
1	[‘A’,’B’,’A’]
2	[‘C’]
3	[‘A’,’D’]

示例T2：

colA	colB
1	A,B,A
2	C
3	A,D

select explode(colB) as colB 
from T1;
select explode(split(colB,',')) as colB 
from T2;

Result：

colB
A
B
A
C
A
D

行转列：lateral view

常与explode一起使用

示例T1：

colA	colB
1	[‘A’,’B’,’A’]
2	[‘C’]
3	[‘A’,’D’]

select colA,tmpB 
from T1 
lateral view explode(colB) tmp as tmpB 

Result：

colA	tmp B
1	A
1	B
1	A
2	C
3	A
3	D

示例T2：

colA	colB
[1,4]	A,B,A
[2,5,6]	C
[3]	A,D

select tmpA,tmpB
from T2 
lateral view explode(colA) tmp1 as tmpA 
lateral view explode(colB) tmp2 as tmpB

Result：

tmpA	tmpB
1	A
1	B
1	A
4	A
4	B
4	A
2	C
5	C
6	C
3	A
3	D

综合示例Table：

os	uv
1	123
2	345
3	5
4	9
5	10

-- 统计各操作系统以及总的登录用户数
select
  os_type,
  count(distinct uid) as login_uv
from
  Table lateral view explode(
    case
      when os = '1' then array('ios', 'all')
      when os = '2' then array('android', 'all')
      else array('other', 'all')
    end
  ) t as os_type
group by
  os_type
order BY
  login_uv desc

Result：

os_type	login_uv
all	492
android	345
ios	123
other	24