hive> select * from info;
OK
2005-05-09 win
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
Time taken: 1.014 seconds, Fetched: 7 row(s)
hive> select `date` as d,result as r,count(result) as c from info group by `date`,result;
OK
2005-05-09 lose 2
2005-05-09 win 2
2005-05-10 lose 2
2005-05-10 win 1
Time taken: 7.557 seconds, Fetched: 4 row(s)
hive> select d,case when r='win' then c else 0 end as win,case when r='lose' theoup by `date`,result) t;
OK
2005-05-09 0 2
2005-05-09 2 0
2005-05-10 0 2
2005-05-10 1 0
Time taken: 3.656 seconds, Fetched: 4 row(s)
hive> select d,sum(case when r='win' then c else 0 end) as win,sum(case when r='lose' then c else 0 end) as lose from (select `date` as d,result as r,count(result) as c from info group by `date`,result) t group by d;
OK
2005-05-09 2 2
2005-05-10 1 2
Time taken: 1.825 seconds, Fetched: 2 row(s)
SQL如下
select d,sum(case when r='win' then c else 0 end) as win,sum(case when r='lose' then c else 0 end) as lose from (select `date` as d,result as r,count(result) as c from info group by `date`,result) t group by d;
