SQL:ANSI Выводит таблицу с идентификаторами, когда значения сначала перемещаются от 0 до любого положительного числа
У меня есть таблица с идентификаторами, датами и значениями. Я хотел бы получить каждый уникальный идентификатор (дату и значение) в первый раз, когда значение перемещается конкретно с 0 на любое положительное число.
ID DATE Value
1 2019-01-31 0
2 2019-02-27 0
3 2019-03-31 0
2 2019-01-31 5
1 2019-02-31 1
3 2019-04-31 5
2 2019-04-30 5
1 2019-05-31 10
3 2020-01-31 0
2 2020-02-28 3
1 2019-06-31 5
3 2020-04-30 5
Желаемый результат:
ID DATE Value
1 2019-02-31 1
2 2019-02-28 3
3 2019-04-31 5
Я пытаюсь сделать это в снежинке, не уверен, что это на что-то повлияет.
Спасибо за внимание! Я создал таблицу вручную, чтобы представить другую таблицу, которую я запускаю. В следующий раз буду осторожнее.
Ответы 1
Для этого можно использовать КВАЛИФИКАЦИЯ и ROW_NUMBER():
Если вам нужно первое ненулевое значение... но вы просили об этом..
SELECT *
FROM values
(1, '2019-01-31'::date,0 ),
(2, '2019-02-27'::date,0 ),
(3, '2019-03-31'::date,0 ),
(2, '2019-01-31'::date,5 ),
(1, '2019-02-28'::date,1 ),
(3, '2019-04-30'::date,5 ),
(2, '2019-04-30'::date,5 ),
(1, '2019-05-31'::date,10 ),
(3, '2020-01-31'::date,0 ),
(2, '2020-02-28'::date,3 ),
(1, '2019-06-30'::date,5 ),
(3, '2020-04-30'::date,5 )
t(ID , DATE , Value )
QUALIFY value > 0 AND row_number() over(partition by id, value > 0 order by date ) = 1;
ORDER BY 1,2
Уловка, которую следует отметить, заключается в том, что вы хотите исключить все значения, не превышающие 0, и также разделить row_number на это.
дает:
| Я БЫ | ДАТА | ЦЕННОСТЬ |
|---|---|---|
| 1 | 2019-02-28 | 1 |
| 2 | 2019-01-31 | 5 |
| 3 | 2019-04-30 | 5 |
возьми два:
Первый переход от 0 к ненулевому:
поэтому давайте просто упорядочим данные, чтобы мы говорили об одном и том же:
| Я БЫ | ДАТА | ЦЕННОСТЬ | в розыске |
|---|---|---|---|
| 1 | 2019-01-31 | 0 | |
| 1 | 2019-02-28 | 1 | это |
| 1 | 2019-05-31 | 10 | |
| 1 | 2019-06-30 | 5 | |
| 2 | 2019-01-31 | 5 | |
| 2 | 2019-02-27 | 0 | |
| 2 | 2019-04-30 | 5 | это |
| 2 | 2020-02-28 | 3 | |
| 3 | 2019-03-31 | 0 | |
| 3 | 2019-04-30 | 5 | это |
| 3 | 2020-01-31 | 0 | |
| 3 | 2020-04-30 | 5 |
это можно сделать с помощью двух вложенных QUALIFY:
SELECT * FROM (
SELECT *
FROM values
(1, '2019-01-31'::date,0 ),
(2, '2019-02-27'::date,0 ),
(3, '2019-03-31'::date,0 ),
(2, '2019-01-31'::date,5 ),
(1, '2019-02-28'::date,1 ),
(3, '2019-04-30'::date,5 ),
(2, '2019-04-30'::date,5 ),
(1, '2019-05-31'::date,10 ),
(3, '2020-01-31'::date,0 ),
(2, '2020-02-28'::date,3 ),
(1, '2019-06-30'::date,5 ),
(3, '2020-04-30'::date,5 )
t(ID , DATE , Value )
QUALIFY lag(value)over(partition by id order by date) = 0
)
QUALIFY row_number() over(partition by id order by date ) = 1
ORDER BY 1,2
дает:
| Я БЫ | ДАТА | ЦЕННОСТЬ |
|---|---|---|
| 1 | 2019-02-28 | 1 |
| 2 | 2019-04-30 | 5 |
| 3 | 2019-04-30 | 5 |
ANSI-SQL:
Если вам нужен ANSI SQL, вы должны использовать эту форму:
SELECT
b.ID,
b.DATE,
b.Value
FROM (
SELECT
a.ID,
a.DATE,
a.Value,
row_number() over(partition by a.id order by a.date ) as rn
FROM (
SELECT
ID,
DATE,
Value,
lag(value)over(partition by id order by date) as lag_val
FROM table_data
) AS a
WHERE a.lag_val = 0
) AS b
WHERE b.rn = 1
ORDER BY 1,2
Я склоняюсь к тому, что чище выражать желаемый результат в наименьшем коде, так что это наиболее выразительно для поставленной задачи.
Вау, это прекрасно работает. Впервые я вижу использование «Квалификации» и сейчас изучаю, как это работает. Большое спасибо!!
Обратите внимание, что квалифицирование — это специальное ключевое слово Snowflake, а не часть ANSI SQL. В других СУБД вам потребуется либо использовать CTE, либо дополнительный подзапрос и предложение WHERE для достижения того же результата.
Приятно знать, спасибо за наводку.
Snowflake, Databricks, Teradata и Oracle поддерживают Qualify.
некоторые из ваших дат недействительны, это должно быть исправлено.