Add missing monthly rows

Question

I would like to list the missing date between two dates in a request for example

My data :

YEAR_MONTH  | AMOUNT    
202001  |  500    
202001  |  600    
201912  |  100    
201910  |  200
201910  |  100     
201909  |  400
201601  | 5000

I want the request to return

201912  |  100    
201911  |    0    
201910  |  300
201909  |  400     
201908  |    0
201907  |    0
201906  |    0
....    |    0
201712  |    0

i want the last 24 months from the date of execution

I did something similar with the dates but not YEAR MONTH yyyyMM

select date_sub(s.date_order ,nvl(d.i,0)) as date_order, case when d.i > 0 then 0 else s.amount end as amount
from
(--find previous date
select date_order, amount, 
        lag(date_order) over(order by date_order) prev_date,
        datediff(date_order,lag(date_order) over(order by date_order)) datdiff
from
( --aggregate
 select date_order, sum(amount) amount from your_data group by date_order )s
)s
--generate rows
lateral view outer posexplode(split(space(s.datdiff-1),' ')) d as i,x
order by date_order;

I use Cassandra database with Apache Hive connector

Can someone help me ?

Answer 1

date_range subquery generates 24 months (adjust if you want some other than 24 months range) back from current date. Left join it with your dataset, see comments in this demo code:

with date_range as 
(--this query generates months range, check it's output
select date_format(add_months(concat(date_format(current_date,'yyyy-MM'),'-01'),-s.i),'yyyyMM') as year_month 
  from ( select posexplode(split(space(24),' ')) as (i,x) ) s --24 months
),

your_data as (--use your table instead of this example
select stack(7,
202001, 500,    
202001, 600,    
201912, 100,    
201910, 200,
201910, 100,     
201909, 400,
201601,5000 -----this date is beyond 24 months, hence it is not in the output
) as (YEAR_MONTH, AMOUNT )
)

select d.year_month, sum(nvl(s.amount,0)) as amount --aggregate
  from date_range d 
       left join your_data s on d.year_month=s.year_month
  group by d.year_month;

Result:

d.year_month    amount
201801  0
201802  0
201803  0
201804  0
201805  0
201806  0
201807  0
201808  0
201809  0
201810  0
201811  0
201812  0
201901  0
201902  0
201903  0
201904  0
201905  0
201906  0
201907  0
201908  0
201909  400
201910  300
201911  0
201912  100
202001  1100

Use your table instead your_data subquery. Add order by if necessary.