Find date gaps in a table

user_13380099 0
Viewed 129

I have an AWS Redshift table looking like this:

id, id_aw_sk, id_ai_sk, snapshot_date, update_timestamp
3278059021, 3197624, 173642, today-1, today
3278059021, 3197624, 173642, today-2, today-1
3278059021, 3197624, 173642, today-3, today-2
3278059021, 3197624, 173642, today-4, today-3
etc.
3278059021, 3224904, 173642, date in past -1, date in past

This table contains snapshot on every day, to see changes in some other columns. if there's a change id_aw_sk would be different than the previous one. What seems to be the issue is that I have some date gaps for some rows, accidently deleted rows. As i can't retrieve those, I would like to "create" them by finding gaps in dates.

I am not sure how to do this. Please, help?

I understand that i should firstly find the gaps and for each row i would use lead function to update values from current (known) rows.

e.g. i have dates for 3278059021 where id_aw_sk was 3224904, but i have gaps for dates between 16th March 2021 until 11th April 2021 for id_aw_sk 3197624. I know that all rows between those dates haven't changed. I only need to populated gaps with first known data (from 11th April) as rows from 16th March and later are the same even now.

I hope that I explained it okay :) Thanks upfront for your help.

amazon-redshift
2 Answers

I've found a gap like this:

select *, case when lag(snapshot_date) over (partition by site_id, site_aw_sk order by snapshot_date ASC)<>snapshot_date-1 
then 1 else 0 end as gap
from 
dwh.fact_site_ps where site_id=3278059021;

that way I can know where are the gaps but don't know how to populate them with data that's missing.

user_133800990

I've solved it. Step 1:

create table dwh_dev.tmp_fact_site_ps_gaps as 
select t.*,case when gap=1 then snapshot_date else null end as gap_start,
case when gap=1 then lead(snapshot_date) over (partition by site_id, site_aw_sk order by snapshot_date asc) else null end as gap_end
from
(select *, case when lead(snapshot_date) over (partition by site_id, site_aw_sk order by snapshot_date ASC)<>snapshot_date+1 
then 1 else 0 end as gap
from 
dwh.fact_site_ps)t

step 2:

select tfspg.*, tdr."date"
from dwh.tmp_date_range tdr
inner join (select * from dwh_dev.tmp_fact_site_ps_gaps where gap=1)tfspg 
on tdr."date" > tfspg.gap_start AND tdr."date" < tfspg.gap_end
where site_id=3278059021
order by site_id

dwh.tmp_date_range is only a table with all dates for the last 2 years.

user_133800990
Related