hive-----行列互换

新建一张这样的表：

CREATE TABLE users (

  name string,

  game string,

  score double

);

insert into users values('us1','game1',2.0 ) ,

('us1','game2',3.0 ) ,

('us1','game3',5.0 ) ,

('us2','game2',4.0 ) ,

('us2','game3',3.0 ) ,

('us2','game4',7.0 ) ,

('us3','game4',3.0 ) ,

('us3','game5',5.0 ) ,

('us3','game6',4.0 );

现在要转换成下面这样：

执行语句：

 

#行转列

SELECT  name

        ,MAX( CASE WHEN game='game1' THEN score else 0 END) AS game1

        ,MAX( CASE WHEN game='game2' THEN score else 0 END) AS game2

        ,MAX( CASE WHEN game='game3' THEN score else 0 END) AS game3

        ,MAX( CASE WHEN game='game4' THEN score else 0 END) AS game4

        ,MAX( CASE WHEN game='game5' THEN score else 0 END) AS game5

        ,MAX( CASE WHEN game='game6' THEN score else 0 END) AS game6

FROM    users

GROUP BY name

;

 

下面有三种列转行的形式：

 

第一种：运用了collect_set()函数，可以的到一个数组

Sql如下：

select name,collect_set(game)

from users

group by name;

 

第二种：运用了collect_set()和concat_ws()函数,可以得到一个字符串

select name,concat_ws('|',collect_set(game))

from users

group by name;

 

第三种:直接把数组中的每个数据取出来当成一列

这里我还排序了，使数组中的数据是按score从大到小的顺序排的

 

select t.name,top3[0],top3[1],top3[2]

from

(

   select t.name,collect_set(t.game) top3

   from

   (

    select name,game,score,

    row_number() over(distribute by name sort by score desc)

    from users

   )t

group by t.name

)t;