传播teradata中缺少的日期-选择查询

Question

我有一个如下所示的表：

my_date	item_idtbody>
2020-03-01	GMZS72429	2
2020-03-07	GMZS72429	2
2020-03-09	GMZS72429	1
2020-03-04	GMZS72425	1

这是Teradata的EXPAND ON语法的一个用例：

select 
   new_date
  ,item_id 
  ,case when my_date = new_date then sales else 0 end
from
 (
   select dt.*, begin(p2) as new_date
   from
    (
      select t.*
         -- create a period for expansion in the next step
        ,period(my_date, lead(my_date, 1, my_date+1)
                         over (partition by item_id
                               order by my_date)) as pd
      from vt as t
    ) as dt
   -- now create the missing dates
   expand on pd as p2
 ) as dt
 

一个简单的选项是使用Teradata的内置日期视图作为驱动程序：

select
coalesce(v.my_date,c.calendar_date),
item_id,
coalesce(v.sales,0)
from
sys_calendar.calendar c
left join your_table v
    on v.my_date = c.calendar_date
where
    c.calendar_date between (select min(my_date) from your_table ) and (select max(my_date) from your_table)
order by 1