Snowflake/SQL:创建一个时间序列表，这样每个ID都是可见的，如果ID为空，它使用前一个值?(类似于shift

方式一:

• 首先我们从一些data
开始
• 则在我们感兴趣的
期间找到the_days然后我们找到每个id的data_start
• 然后我们将这些值连接在一起，并使用LAG和IGNORE NULLS OVER子句来查找"优先值";如果当前值没有通过NVL
显示

with data(Day, ID, Value) as (
select * from values
('2022-11-05'::date, 0, 'A'),
('2022-11-06'::date, 1, 'B'),
('2022-11-07'::date, 0, 'C')
), the_days as (
select 
row_number() over (order by null)-1 as rn
,dateadd('day', rn, from_day) as day
from (
select 
min(day) as from_day
,'2022-11-08' as to_day
,datediff('days', from_day, to_day) as days
from data
), table(generator(ROWCOUNT => 200))
qualify rn <= days
), data_starts as (
select 
id, 
min(day) as start_day
from data
group by 1
)
select 
td.day,
ds.id,
nvl(d.value, lag(d.value) ignore nulls over (partition by ds.id order by td.day)) as value
from data_starts as ds
join the_days as td 
on td.day >= ds.start_day
left join data as d
on ds.id = d.id and d.day = td.day
order by 1,2;

给: