Hive 笔记四 行 「=」 列

行 <=> 列
行->列
使用 case when；group by + sum
      
sql

select id,
 sum(case when course="java" then 1 else 0 end) as java, 
 sum(case when course="hadoop" then 1 else 0 end) as hadoop, 
 sum(case when course="hive" then 1 else 0 end) as hive, 
 sum(case when course="hbase" then 1 else 0 end) as hbase, 
 sum(case when course="spark" then 1 else 0 end) as spark, 
 sum(case when course="flink" then 1 else 0 end) as flink, 
 sum(case when course="kafka" then 1 else 0 end) as kafka 
from rowline1 group by id;

行->列  多行一列
数据

a,b,2
a,b,1
a,b,3
c,d,6
c,d,8
c,d,8

实现效果:
 

id1 id2 flag 
a    b   2|1|3 
c    d   6|8

表结构:

create table rowline2( id1 string, id2 string, flag int ) row format delimited fields terminated by ',';

导入数据

load data local inpath '/root/data/data2.dat' into table rowline2;

实现步骤:
1.第一步 将元素聚拢
2.第二步 将元素连接在一起
1）collect_set(去重集合)   collect_list(不去重集合)   sort_array(排序去重集合)  按照实现效果选择第一个集合

select id1, id2, collect_set(flag) flag from rowline2 group by id1, id2;

id1     id2     flag
a       b       [2,1,3]
c       d       [6,8]


select id1, id2, collect_list(flag) flag from rowline2 group by id1, id2;

id1     id2     flag
a       b       [2,1,3]
c       d       [6,8,8]

select id1, id2, sort_array(collect_set(flag)) flag from rowline2 group by id1, id2;

id1     id2     flag
a       b       [1,2,3]
c       d       [6,8]

2）元素连接在一起

select id1, id2, concat_ws("|", collect_set(cast(flag as string))) flag from rowline2 group by id1, id2;

id1     id2     flag
a       b       2|1|3
c       d       6|8

函数说明:
concat_ws

concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.
Example:
  > SELECT concat_ws('.', 'www', array('facebook', 'com')) FROM src LIMIT 1;
  'www.facebook.com'

concat

concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data  bin1, bin2, ... binN
Returns NULL if any argument is NULL.
Example:
  > SELECT concat('abc', 'def') FROM src LIMIT 1;
  'abcdef'

列->行  一列多行

create table rowline3 as select id1, id2, 
concat_ws("|", collect_set(cast (flag as string))) flag 
from rowline2 group by id1, id2;

rowline3.id1    rowline3.id2    rowline3.flag
a       b       2|1|3
c       d       6|8

select id1, id2, newflag 
from rowline3 
lateral view explode(split(flag, "\\|")) t1 as newflag;

id1     id2     newflag
a       b       2
a       b       1
a       b       3
c       d       6
c       d       8

行->列 多行多列
case when + sum + group by
行->列 多行一列
collect_set、collect_list、concat_ws、sort_array
列->行  一列多行
explode + lateral view