Возвращает самое последнее ненулевое значение до даты минус 1 год — Снежинка

#sql #snowflake-cloud-data-platform

Вопрос:

У меня есть таблица с идентификатором, датой, значением. Для каждой строки я хочу вернуть самое последнее ненулевое значение, где Дата

Я пробую варианты оконных функций с last_value или лагом и разделением по идентификатору, порядку по дате, с предложением ИГНОРИРОВАТЬ НУЛИ. Однако я не уверен, где включить условие, при котором дата

Примеры Данных:

ID Дата Ценность
1 01-19 января нулевой
1 02-19 января нулевой
1 03-19 января нулевой
1 04-19 января нулевой
1 05-19 января нулевой
1 01-20 января нулевой
1 02-20 января 20
1 03-20 января нулевой
1 04-20 января нулевой
1 05-20 января 30
1 01-21 января 20
1 02-21 января нулевой
1 03-21 января нулевой
1 04-21 января нулевой
1 05-21 января 30
2 01-19 января нулевой
2 02-19 января нулевой
2 03-19 января нулевой
2 04-19 января 30
2 05-19 января 30
2 01-20 января 25
2 02-20 января 20
2 03-20 января нулевой
2 04-20 января нулевой
2 05-20 января 30
2 01-21 января 20
2 02-21 января 15
2 03-21 января 15
2 04-21 января 15
2 05-21 января 30

Desired Results:

ID Date Value Most_Recent_Prior_Year_Date Most_Recent_Prior_Year_Value
1 01-Jan-19 NULL NULL NULL
1 02-Jan-19 NULL NULL NULL
1 03-Jan-19 NULL NULL NULL
1 04-Jan-19 NULL NULL NULL
1 05-Jan-19 NULL NULL NULL
1 01-Jan-20 NULL NULL NULL
1 02-Jan-20 20 NULL NULL
1 03-Jan-20 NULL NULL NULL
1 04-Jan-20 NULL NULL NULL
1 05-Jan-20 30 NULL NULL
1 01-Jan-21 20 NULL NULL
1 02-Jan-21 NULL 02-Jan-20 20
1 03-Jan-21 NULL 02-Jan-20 20
1 04-Jan-21 NULL 02-Jan-20 20
1 05-Jan-21 30 05-Jan-20 30
2 01-Jan-19 NULL NULL NULL
2 02-Jan-19 NULL NULL NULL
2 03-Jan-19 NULL NULL NULL
2 04-Jan-19 30 NULL NULL
2 05-Jan-19 30 NULL NULL
2 01-Jan-20 25 NULL NULL
2 02-20 января 20 нулевой нулевой
2 03-20 января нулевой нулевой нулевой
2 04-20 января нулевой 04-19 января 30
2 05-20 января 30 05-19 января 30
2 01-21 января 20 01-20 января 25
2 02-21 января 15 02-20 января 20
2 03-21 января 15 02-20 января 20
2 04-21 января 15 02-20 января 20
2 05-21 января 30 05-20 января 30

Ответ №1:

ЕСЛИ у вас есть только одна строка в день на идентификатор, вы можете использовать:

Единственное ЛЕВОЕ СОЕДИНЕНИЕ и ГДЕ/ГДЕ будет работать здесь:

 SELECT   a.ID,  a.Date,  a.Value  b.Data AS Most_Recent_Prior_Year_Date  b.Value AS Most_Recent_Prior_Year_Value FROM table AS a LEFT JOIN table AS b   on a.id = b.id AND DATEADD('year',-1,a.date) gt;= b.date QUALIFY ROW_NUMBER() OVER( PARTITION BY a.id, a.date ORDER BY b.date DESC NULLS LAST) = 1  

В противном случае вы захотите сначала выделить одно значение в день, например MAX, MIN, FIRST_VALE, а затем выполнить вышеуказанное над этим выводом.

Комментарии:

1. Спасибо, это в конечном итоге отлично сработало для меня!

Ответ №2:

Рассмотрим group by и две join s вместо оконных функций, например

 select Id, Date, Value from DataTable d1 inner join (  select Id, Date as Most_Recent_Prior_Year_Date, max(Value) as Most_Recent_Prior_Year_Value   -- max(Value) because there could be many values for that day  from DataTable d3  inner join (  select Id, max(Date) as MostRecentDate  from DataTable  where Date lt;= DATEADD(year, -1, CURRENT_DATE())  group by Id  ) d4 on d3.Id = d4.Id AND d3.Date = d4.MostRecentDate  group by Id, Date ) d2 on d1.Id = d2.Id