#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