Hive Join - на основе нескольких условий

Question

В одной из моих таблиц, которые содержат столбцы даты и события.

select * from event

это возвращается,

date event
2016-03-20 Launch
2016-03-20 delete
2016-03-20 Launch
2016-03-20 launch
2016-03-19 delete
2016-03-19 stop

Я хочу, чтобы результат, как использование группы по дате

date | count(Luanch) | count(delete) | count(stop)

Answer 1

select      date
           ,count(case when event = 'Launch' then 1 end) as count_Launch
           ,count(case when event = 'delete' then 1 end) as count_delete
           ,count(case when event = 'stop'   then 1 end) as count_stop

from        event

group by    date
;

---

    date    | count_Launch | count_delete | count_stop
------------+--------------+--------------+-------------
 2016-03-19 |      0       |      1       |    1
 2016-03-20 |      2       |      1       |    0