Calculate the sum of a column on weekly basis in hive

Question

I have a table say testTable in Hive(with data for 3 years) with the following columns:

retailers, order_total, order_total_qty, order_date

I have to create a new table with these columns:

'source_name' as source, sum(retailers), sum(order_total), sum(order_total_qty) 

for each week from the starting order_date.

I am stuck with this. How can I group following data in the way that it will sum up on weekly basis.

Answer 1

Use WEEKOFYEAR() function to calculate aggregation on weekly basis.

select 
  'source_name'           source, 
   sum(retailers)         sum_retailers, 
   sum(order_total)       sum_order_total, 
   sum(order_total_qty)   sum_order_total_qty,
   WEEKOFYEAR(order_date) week,
   year(order_date)       year
from testTable 
where order_date >= '2015-01-01' --start_date
group by WEEKOFYEAR(order_date), year(order_date)
order by year, week; --order if necessary