@Ranjith M Try two options below. It will read your source table just once and will create 2 lines: create external table test_groupby
(sale_id string, salevalue double, datex string)
row format delimited fields terminated by ','
stored as textfile
location '/tmp/groupby'
;
select * from test_groupby;
select inline(
array(
named_struct('type', 'month', 'value', sum(case when substr(datex, 1,7) = '2015/12' then salevalue end)),
named_struct('type', 'year', 'value', sum(case when substr(datex, 1,4) = '2015' then salevalue end))
))
from test_groupby
;
create external table test_groupby
(sale_id string, salevalue double, datex string)
row format delimited fields terminated by ','
stored as textfile
location '/tmp/groupby'
;
select * from test_groupby;
drop table test_groupby_result;
create table test_groupby_result
(value double)
partitioned by (type string)
;
from test_groupby
insert into table test_groupby_result partition (type = 'month')
select sum(salevalue)
where substr(datex, 1,7) = '2015/12'
insert into table test_groupby_result partition (type = 'year')
select sum(salevalue)
where substr(datex, 1,4) = '2015'
;
select * from test_groupby_result;
... View more
