添加缺少的每月行 [英] Add missing monthly rows

问题描述

例如，我想列出请求中两个日期之间的缺失日期

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

我的数据：

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

我希望请求返回

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

我希望从执行之日起过去24个月

i want the last 24 months from the date of execution

我在日期上做了类似的操作，但不是YEAR MONTH

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;

我将Cassandra数据库与Apache Hive连接器一起使用

I use Cassandra database with Apache Hive connector

有人可以帮我吗？

推荐答案

date_range 子查询生成24个月（请调整是否要设置24个月以外的时间）从当前日期算起。左键将其与数据集连接起来，请参见此演示代码中的注释：

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;

结果：

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

使用表代替your_data子查询。如有必要，添加订单。

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

这篇关于添加缺少的每月行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！