Hive行转列，列转行

下面举两个例子：

例一:

行转列

数据：

a b 1

a c 2

a b 3

c d 4

c d 5

c d 6

转化为：

a b 1,2,3

c d 4,5,6

 创表

Hive>create table test1 (col1 String,col2 String,col3 String) row format delimited fields terminated by ' ';

加载数据：

Hive>load data local inpath '/home/huangwei/test.txt' into table test1;

使用的函数说明：

concat_wa(string SEP,string array<String>) 函数返回字符串连接后的结果，SEP表示各个字符串直接的分割符

Collect_set(col)函数 将col字段进行去重，并合并成一个数组

实现方式：

Hive>select col1,col2,concat_ws(',',collect_set(col3)) from test1 group by col1,col2;

 

列转行

数据：

a b 1,2,3

c d 4,5,6

转化为：

a b 1

a c 2

a b 3

c d 4

c d 5

c d 6

使用到的函数

函数split(String str,String pat) 将字符串按照pat分割

函数explode(array) 将数组中的元素拆分为多行显示

select col1,col2,test.col4 from test1 lateral view explode(split(col3,',')) test as col4;

 

例二

行转列

数据：

张三,语文,80

李四,语文,89

王五,语文,75

张三,数学,90

李四,数学,88

王五,数学,79

张三,英语,93

李四,英语,87

王五,英语,85

转换为：

张三,80,90,93

李四,89,88,87

王五,75,79,85

创表：

create table test2 (name String,project String,score int) row format delimited fields terminated by ',';

加载数据：

load data local inpath '/home/huangwei/test.txt' into table test1;

函数：case when a then b else c end 如果a为true返回b如果a为false返回c

Max()去最大值

处理：

select name,max(case when subject='Chinese' then score else 0 end)as Chinese,max(case when subject='math' then score else 0 end) as math,max(case when subject='English' then score else 0 end) as English from test2 group by name;

Sql执行过程

看第一行 80是由CASE WHEN Subject='语文' THEN Score ELSE 0 END得出，其他的0分别是由CASE WHENSubject='数学' THEN Score ELSE 0 END、CASE WHEN Subject='英语' THEN Score ELSE 0 END、CASE WHEN Subject='生物' THEN Score ELSE 0 END得出，一次类推，得到的结果集为:

张三    80    0    0
李四    89    0    0
王五    75    0    0
张三    0    90    0
李四    0    88    0
王五    0    79    0
张三    0    0    93
李四    0    0    87
王五    0    0    85

下一步，聚合分组，最终完成任务。

列转行：

数据：

张三,80,90,93

李四,89,88,87

王五,75,79,85

实现方式：

select name,’Chinese’ as subject,’Chinese’ as score from test3

union

select name,’math’ as subject,’math’ as score from test3

union 

select name,’English’ as subject,’math’ as score from test3;