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 ?

leftjoin · Accepted Answer · 2020-01-21T21:50:21.413

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.

edited Jan 21 '20 at 21:50

answered Jan 21 '20 at 21:16

leftjoin

36,950
8
57
116

What's `space(24)`? Can't find it in Spark SQL (neither in Scala's functions object nor in SQL mode)? – Jacek Laskowski Jan 22 '20 at 08:29
1

@JacekLaskowski This is Hive function, returns string of spaces with given length (24) – leftjoin Jan 22 '20 at 08:30
1

@JacekLaskowski in Scala/spark it should be more elegant ways of generating rows I believe. This is pure Hive sql – leftjoin Jan 22 '20 at 08:33
Does this `select posexplode(split(space(24),' ')) as (i,x)` generate 24 ids starting from 0? – Jacek Laskowski Jan 22 '20 at 09:01
@JacekLaskowski yes – leftjoin Jan 22 '20 at 09:09

LeroyFromBerlin · Answer 2 · 2020-01-21T18:36:47.823

So if I understand correctly you'd like to add all dates that are currently missing because it so happened that amount was 0 at these days.

You can use this:

select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) base_date from
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    having base_date between curdate() - interval 24 month and curdate();

This basically creates a list of dates between 1970 and 2200 (filtered for the ones you're interested in).

Idea is to select from this as a subquery and join with the table at hand (on the date field).

example of what this could look like: return empty rows for not existsting data

as for the date format (YEAR MONTH YYYYMM) you can run this:

DATE_FORMAT(your_date,'%Y%m')

Add missing monthly rows

2 Answers2

Linked