Периоды разделения дат проверки непрерывны (и выявляются пробелы)
Я борюсь с аналогичной проблемой, как в этой теме: Периоды разделения дат проверки непрерывны
Может ли кто-нибудь помочь мне перевести ответ Qsebas на язык Presto? Поскольку это именно то, чего я хотел бы достичь.
Я не уверен, каким должен быть результат этой части:
CROSS APPLY Enumerate ( ABS(DATEDIFF(d, From_Date, To_Date))) AS NUMBERS
И как его заменить. Будет ли это ROW_NUMBER() в качестве одного столбца и ABS(DATE_DIFF()) во втором?
Мои образцы данных:
| Номерной знак | create_timestamp | delete_timestamp |
|---|---|---|
| АА-ААА | 2019-10-08 10:47:54 | \N |
| АА-ААА | 2021-01-22 12:37:21 | 2021-07-21 8:27:44 |
| АА-ААА | 2021-07-19 9:10:39 | \N |
| ВВ-ВВВ | 2016-04-15 8:38:59 | 2021-11-04 10:51:18 |
| ВВ-ВВВ | 2018-03-13 13:56:39 | 2021-10-07 08:21:07 |
| ВВ-ВВВ | 2021-12-23 12:42:31 | \N |
| CC-CCC | 2019-07-26 21:22:42 | 2021-12-17 18:21:37 |
| CC-CCC | 2021-11-05 11:08:13 | 2022-02-11 08:44:22 |
Я хотел бы получить что-то вроде этого:
| Номерной знак | create_timestamp | delete_timestamp |
|---|---|---|
| АА-ААА | 2019-10-08 10:47:54 | \N |
| ВВ-ВВВ | 2016-04-15 8:38:59 | 2021-10-07 08:21:07 |
| ВВ-ВВВ | 2021-12-23 12:42:31 | \N |
| CC-CCC | 2019-07-26 21:22:42 | 2022-02-11 08:44:22 |
Также может быть столбец count_ranges для подсчета количества диапазонов для каждого номерного знака. Для каждого номерного знака может быть много записей, и они могут перекрываться (поэтому сортировка по create_timestamp и сравнение построчно не работает). Идея состоит в том, чтобы сгруппировать транспортные средства (номерные знаки) в:
- 'Активный' (с момента первого create_timestamp до настоящего времени/end_date не было промежутка); license_plate AA-AAA в моем примере
- 'Реактивирован' (было по крайней мере на промежутке, но машина активна сейчас) - номерной знак BB-BBB
- «Удалено» (с момента первого create_timestamp и последнего delete_timestamp не было разрыва, но сейчас он не активен) — номерной знак CC-CCC
Я знаю, что может быть больше сценариев (например, «Повторно активировано — Удалено», «Повторно активировано — Повторно активировано», но пока подойдет такое разделение.
Если бы у меня была таблица, которую я хотел бы, я мог бы назначить их группам:
CASE WHEN count_ranges > 1 THEN 'Reactivated'
WHEN count_ranges = 1 AND delete_timestamp is null THEN 'Active'
WHEN count_ranges = 1 AND delete_timestamp is not null THEN 'Deleted'
END AS vehicle_status
Может быть, есть другой способ добиться этого?
PS. Указание точного параметра delete_timestamp в конце диапазона и create_timestamp в начале следующего диапазона для одного номерного знака было бы полезно, чтобы проверить, был ли номерной знак повторно активирован тем же пользователем или кем-то другим.
Спасибо!
Привет @SOS, я отредактировал и обновил свой пост, добавив больше информации.
Ответы 2
Используя примеры данных из связанного ответа, я предлагаю использовать подход с пробелами и островами - используйте lag для сравнения предыдущего to_date с текущим from_date, а затем используйте результат для скользящей суммы для формирования группировки:
-- sample data
WITH dataset (ref, from_date, to_date) AS (
VALUES ('A', date '1997-01-04', date '1998-01-04'),
('A', date '1998-01-04', date '1998-05-27'),
('A', date '1998-05-27', date '1999-01-04'),
('B', date '1997-01-04', date '1998-01-04'),
('B', date '1998-01-04', date '1998-07-26'),
('B', date '2012-01-04', date '2013-01-04')
)
-- query
select ref,
min(from_date) from_date,
max(to_date) to_date,
max(grp) over(partition by ref) + 1 count_ranges
from(
select ref,
from_date,
to_date,
sum(if (grp > 0, 1, 0)) over(partition by ref order by from_date, to_date) as grp
from (
select *,
coalesce(
date_diff('day', lag(to_date) over(partition by ref order by from_date, to_date), from_date),
0
) as grp
from dataset
)
)
group by ref, grp
Выход:
| ссылка | с даты | встретиться | count_ranges |
|---|---|---|---|
| А | 1997-01-04 | 1999-01-04 | 1 |
| Б | 1997-01-04 | 1998-07-26 | 2 |
| Б | 2012-01-04 | 2013-01-04 | 2 |
Это здорово, но я вижу здесь проблему (хотя, конечно, я могу ошибаться). Сравнивает ли он строку за строкой и «забывает» предыдущие временные метки? Потому что они могут пересекаться. create_timestamp1: 2019-10-08 10:47:54 delete_timestamp1: \N create_timestamp2: 22-01-2021 12:37:21 delete_timestamp2: 2021-07-11 8:27:44 create_timestamp3: 2021-07-19 9:10 :39 delete_timestamp3: \N Таким образом, существует разрыв между delete2 и create3, но запись 1 так и не была удалена. Транспортное средство должно считаться «активным», но оно будет «воскрешенным».
Для вычислений я бы также добавил Coalesce(delete_timestamp,now()), чтобы избавиться от записей '\N'. И запрос должен каким-то образом «запоминать» самую высокую предыдущую отметку времени при сравнении со следующей строкой.
@KamilDworzecki Я использовал образец данных из связанного вопроса и не видел обновления для вашего. Если это не сработает для вас, я попытаюсь нырнуть глубже завтра, если у меня будет время.
Как насчет добавления столбца moving_max в основную базу данных? Можно ли создать что-то вроде: Выберите Max (delete_timestamp) Over (Разделение по порядку номерного знака по create_timestamp) как moving_max ОТ транспортных средств, ГДЕ create_timestamp <= lead (create_timestamp) over (раздел по порядку номерного знака по create_timestamp, delete_timestamp) как moving_max Ваш запрос будет работа, если бы у меня была такая колонка, я надеюсь, вы понимаете. Извините, я сумбурный, просто думаю вслух перед сном ;)
@KamilDworzecki да, использование max над секционированием и упорядочением может помочь.
Спасибо @Guru Stron, вы указали мне правильное решение!
Я обновил ваш код, добавив один столбец в свою исходную базу данных:
select v.*,
max(delete_timestamp) over (partition by license_plate
order by create_timestamp, delete_timestamp ROWS UNBOUNDED PRECEDING)
as moving_max
from vehicles v
В конце я сравниваю следующую create_timestamp с предыдущей moving_max вместо предыдущей delete_timestamp
Еще не проверял вашу часть кода. Но если это работало раньше (не так, как мне хотелось бы, а так, как вы предполагали ;)) и моя обновленная база данных выглядит хорошо с новым столбцом (каждый номерной знак имеет свой moving_max, рассчитанный правильно), поэтому он не может работать неправильно.
Был рад помочь. Если вам подходит какой-либо ответ, отметьте его как принял один (галочка рядом с ответом) (включая ваш собственный). Также обратите внимание, что вы должны иметь возможность пропустить UNBOUNDED PRECEDING, потому что он используется По умолчанию: «Если кадр не указан, используется кадр по умолчанию RANGE UNBOUNDED PRECEDING»..
Я не знаком с presto, но, вероятно, было бы полезно, если бы вы опубликовали пример необработанных данных и ожидаемых результатов.