hive 行转列

行转列的常规做法是，group by+sum(if())【或count(if())】

建表:

CREATE TABLE table2 (year INT,month INT,amount DOUBLE
);INSERT INTO table2 (year, month, amount) VALUES(1991, 2, 1.2),(1991, 3, 1.3),(1991, 4, 1.4),(1992, 1, 2.1),(1992, 2, 2.2),(1992, 3, 2.3),(1992, 4, 2.4);

查询：
首先建立子查询

select *,if(month=1,amount,0) as a,if(month=2,amount,0) as b,if(month=3,amount,0) as c,if(month=4,amount,0) as d
from table2

然后分组求和一下：

select year,sum(a) as m1,sum(b)as m2,sum(c) as m3,sum(d) as m4
from
(
select *,if(month=1,amount,0) as a,if(month=2,amount,0) as b,if(month=3,amount,0) as c,if(month=4,amount,0) as d
from table2)t1group by year

****2.腾讯游戏

首先建表

CREATE TABLE table3 (ddate varchar(20),shengfu varchar(20)
);
insert INTO table3  values ('2015-05-09', "胜"),
('2015-05-09', "胜"),
('2015-05-09', "负"),
('2015-05-09', "负"),
('2015-05-10', "胜"),
('2015-05-10', "负"),
('2015-05-10', "负");

查询

select ddate,
SUM(case when shengfu = '胜' then 1 else 0 end) `胜`,
SUM(case when shengfu = '负' then 1 else 0 end) `负`
from table3
group by ddate