hive行转列

hive中如何进行行转列呢？

我们先创建一个表，并初始化一些数据进去

1
2
3
4
5
6
7
8
CREATE TABLEtest(
      name STRING COMMENT '姓名',
      subject STRING COMMENT '主题',
      score INT COMMENT '分数'

  ) COMMENT '测试表' ;

INSERT INTO test values('张三','语文',100),('李四','语文',90),('张三','数学',80),('李四','数学',80);

可以看到现在表test中的数据是这样的

现在我们用name进行分组，然后把subject的语文、数学变成列，再将score变成语文、数学列的值。

1
2
3
4
5
SELECT name
,concat_ws('-',collect_list(if(subject = '语文',CAST(score as STRING),null))) as `语文`
,concat_ws('-',collect_list(if(subject = '数学',CAST(score as STRING),null))) as `数学`
FROM test
GROUP BY name

再看一下效果

其中涉及到几个函数

concat_ws

concat_ws函数需要传入2个参数，第一个参数为拼接字符串，第二个参数为需要拼接的列名集合，需要注意的是，如果集合中存在null则会被忽略,利用这一点我们可以把多行的同一列数据拼接成一个字段，形成列转行。

IF

if 函数用来判断条件然后返回内容，第一个参数是条件，第二个参数是条件true时返回的内容，第三个参数是条件false时返回的内容。配合concat_ws函数忽略null的特性，可以用来指定返回那一行的值，例如

1
if(subject = '语文',CAST(score as STRING),null)

则只会返回语文那一行的数据

CAST

这是一个转换函数，因为concat_ws后的列需要一个string类型，所以将score从int转为string