Как обрабатывать строки с изменениями статуса и без них в таблице истории
У меня есть таблица в SQL Server, которая отслеживает состояние различных идентификаторов с течением времени. Таблица включает в себя как исторические, так и текущие данные, и мне нужно написать запрос, который будет возвращать правильный статус и диапазоны дат для каждого идентификатора, независимо от того, изменился статус или нет.
Структура таблицы следующая:
CREATE TABLE #history_Card_status (
HistoryData BIT,
StatusUpdateDate DATETIME,
CreateDate DATETIME,
Id BIGINT,
Status NVARCHAR(50)
);
INSERT INTO #history_Card_status (HistoryData, StatusUpdateDate, CreateDate, Id, Status)
VALUES
-- Current Data
(0, NULL, '2023-06-13 05:07:38.700', 222, 'Open'),
(0, NULL, '2021-07-16 00:44:46.740', 111, 'Closed'),
-- Historical Data
(1, '2024-08-20 21:10:57.093', '2021-07-16 00:44:46.740', 111, 'Closed'),
(1, '2024-07-05 13:22:04.220', '2021-07-16 00:44:46.740', 111, 'Closed'),
(1, '2024-07-05 13:13:02.133', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-07-02 03:01:12.467', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-06-24 02:12:00.773', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-06-24 02:12:00.687', '2021-07-16 00:44:46.740', 111, 'Inactive/Block'),
(1, '2024-06-24 02:00:46.040', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-06-24 02:00:44.303', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-04-14 11:57:21.133', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-04-14 11:52:09.073', '2021-07-16 00:44:46.740', 111, 'Open'),
(1, '2024-07-01 04:28:08.213', '2023-06-13 05:07:38.700', 222, 'Open'),
(1, '2024-07-01 03:39:54.607', '2023-06-13 05:07:38.700', 222, 'Open'),
(1, '2024-04-24 11:18:59.380', '2023-06-13 05:07:38.700', 222, 'Open'),
(1, '2024-04-24 11:18:59.227', '2023-06-13 05:07:38.700', 222, 'Open');
Эта таблица содержит как исторические записи (отмеченные HistoryData = 1), так и текущие записи (отмеченные HistoryData = 0). Столбец StatusUpdateDate заполняется только для исторических записей, а столбец CreateDate присутствует во всех записях.
Проблема
Мне нужно создать запрос, который возвращает статус и соответствующие диапазоны дат для каждого идентификатора. Запрос должен обрабатывать как случаи изменения статуса, так и случаи, когда он не меняется. Ожидаемый результат должен выглядеть так:
StatusUpdateDate StatusTillDate Id Status
----------------------- ---------------------- ------ -----------
2024-07-05 13:13:02.133 2024-08-24 08:49:31.233 111 Closed
2024-06-24 02:00:46.040 2024-07-05 13:13:02.133 111 Inactive/Block
2021-07-16 00:44:46.740 2024-06-24 02:00:46.040 111 Open
2023-06-13 05:07:38.700 2024-08-24 08:49:31.233 222 Open
Моя попытка
Я начал с запроса, который работает в случаях изменения статуса:
WITH Step1 AS (
SELECT
StatusUpdateDate = ISNULL(StatusUpdateDate, ISNULL(history.CreateDate, '1970-01-01 00:00:00.000')),
Id,
Status,
lag_status = LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC),
ChangeFlag = CASE
WHEN Status <> LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC)
THEN 1 ELSE 0 END
FROM #history_Card_status AS history
)
SELECT
StatusUpdateDate,
StatusTillDate = ISNULL(LAG(StatusUpdateDate) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), DATEADD(DAY, 2, GETDATE())),
Id,
Status = ISNULL(LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), lag_status)
FROM Step1
WHERE ChangeFlag = 1;
Этот запрос хорошо работает для идентификаторов, статус которых изменился, но я вижу только идентификаторы с изменениями, например:
StatusUpdateDate StatusTillDate Id Status
----------------------- ---------------------- ------ -----------
2024-07-05 13:13:02.133 2024-08-24 06:22:39.003 111 Closed
2024-06-24 02:00:46.040 2024-07-05 13:13:02.133 111 Inactive/Block
2021-07-16 00:44:46.740 2024-06-24 02:00:46.040 111 Open
Затем я попытался обработать строки, в которых изменений не произошло:
WITH Step1 AS (
SELECT
StatusUpdateDate = ISNULL(StatusUpdateDate, ISNULL(history.CreateDate, '1970-01-01 00:00:00.000')),
Id,
Status,
lag_status = LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC),
ChangeFlag = CASE
WHEN Status <> LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC)
THEN 1 ELSE 0 END,
rownum = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC)
FROM #history_Card_status AS history
)
SELECT
StatusUpdateDate,
StatusTillDate = ISNULL(LAG(StatusUpdateDate) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), DATEADD(DAY, 2, GETDATE())),
Id,
ChangeFlag,
Status = ISNULL(LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC), lag_status),
rownum
FROM Step1
WHERE ChangeFlag = 1
OR (ChangeFlag = 0 AND rownum = 1);
Эта попытка, однако, дает неутешительные результаты, такие как:
StatusUpdateDate StatusTillDate Id ChangeFlag Status
----------------------- ---------------------- ------ ----------- -----------
2024-08-20 21:10:57.093 2024-08-24 16:44:22.340 111 0 NULL
2024-07-05 13:13:02.133 2024-08-20 21:10:57.093 111 1 Closed
2024-06-24 02:00:46.040 2024-07-05 13:13:02.133 111 1 Inactive/Block
2021-07-16 00:44:46.740 2024-06-24 02:00:46.040 111 1 Open
2024-07-01 04:28:08.213 2024-08-24 16:44:22.340 222 0 NULL
Что я ищу
Я ищу способ вернуть правильный статус и диапазоны дат для каждого идентификатора, в том числе без каких-либо изменений статуса, как показано в ожидаемых результатах.
Выглядит как-то пусто и островно.
Если вы ожидаете, что ChangeFlag будет 1 для исходных записей истории, то вы, вероятно, захотите обработать случай null с помощью чего-то вроде CASE WHEN Status <> ISNULL(LAG(Status) OVER (PARTITION BY Id ORDER BY StatusUpdateDate DESC) , N'') THEN 1 ELSE 0 END
@TheImpaler Посмотрите на DATEADD(DAY, 2, GETDATE())) во второй части функции isnull внутри StatusTillDate во внешнем запросе.
Ответы 2
Сначала отмечаем строки («Пробелы и острова»), статус которых меняется (Flag New Group fngr=1).
Порядок строк по StatusUpdateDate.
Однако, поскольку для строк HistoryData StatusUpdateDate имеет значение null, и мы хотим переместить эти строки в конец порядка вывода, мы используемorder by HistoryData desc, StatusUpdateDate. Вывод подзапроса показан ниже в текстеДля отфильтрованных строк (fgrn=1) мы берем StatusUpdateDate (или CreateDate для первой строки)
и StatusTillDate в качестве ведущей даты вfilteredстроках (или DATEADD(DAY, 2, GETDATE()) для последней строки).
См. пример
select Id,Status
,case when prevStatus='New' then CreateDate
else StatusUpdateDate
end StatusUpdateDate
,lead(StatusUpdateDate,1,DATEADD(DAY, 2, GETDATE()))
over(partition by Id order by HistoryData desc,StatusUpdateDate) StatusTillDate
from(
select *
,lag(Status,1,'New')over(partition by Id order by HistoryData desc,StatusUpdateDate) prevStatus
,case when Status<>lag(Status,1,'New')over(partition by Id order by HistoryData desc,StatusUpdateDate)
then 1
else 0
end fngr
from history_Card_status
)t
where fngr=1
После фильтра с fngr=1 (флажок «Новая группа статуса») мы получаем
Обновление 1. Интересный момент со StatusUpdateDate и StatusTillDate. Если сравните желаемый результат с ОП, приведите ответ @siggemannen и мой результат.
Например
'2024-06-24 02:00:46.040' - это последняя точка, где статус установлен ='Открыто'
«2024-06-24 02:12:00.687» — это момент, когда статус изменился с «Открыто» на «Неактивно/Блокировано».
Open ? Inactive/Block ? Close
^-----------^------^------------------^--------------^------------
| | | | |
Open Open Inactive/Block Inactive/Block Close
У меня вопрос - где находится точка StatusTillDate?
Для справки. Вывод подзапроса
Объяснение (например, см. другой ответ) значительно улучшит ваш ответ.
@ValNik Спасибо за ответ! У меня есть одна вещь, которая нуждается в улучшении: мне нужна максимальная дата для каждого статуса, чтобы соответствовать минимальной дате следующего статуса, например: Максимальное обновление статуса (Открыто) == Минимальное обновление статуса (Неактивно/Блокировать) 2024-06-24 02:00:46.040 == 2024-06-24 02:00:46.040 Максимальное обновление статуса (Неактивно/Блокировано) == Минимальное обновление статуса (Закрыто) 05.07.2024 13:13:02.133 == 05.07.2024 13:13:02.133 Не могли бы вы предложить решение этой проблемы?
Это выглядит как стандартные пробелы и острова, с небольшими морщинками, вроде даты начала:
SELECT
MIN(CASE WHEN groupflag = 0 THEN CreateDate ELSE StatusUpdateDate END) AS minDate
, MAX(ISNULL(StatusUpdateDate, DATEADD(DAY, 2, GETDATE()))) AS maxDate
, ID, status, groupFlag
FROM (
SELECT *
, SUM(flag) OVER(partition BY ID ORDER BY historydata DESC, statusupdatedate rows BETWEEN unbounded preceding AND CURRENT row) AS groupFlag
FROM (
SELECT
CASE WHEN status <> lag(status) OVER(partition BY id ORDER BY historydata DESC, statusupdatedate) THEN 1 ELSE 0 END AS flag
, *
FROM #history_Card_status h
) x
) x
GROUP BY groupflag, id, status
ORDER BY id, groupflag DESC
Обычно я использую CASE WHEN something <> lag(something) then 1 else 0 end, который отвечает за первую строку.
Затем мы суммируем вышеуказанный флаг, чтобы создать группировку. Для повышения производительности я использую ROWS BETWEEN, в противном случае по умолчанию используется RANGE, что работает немного медленнее.
Наконец, я беру CreateDate для первой строки, чтобы создать дату привязки, и StatusUpdateDate для остальных.
Выход:
Спасибо за ваш ответ! У меня есть одна вещь, которая нуждается в улучшении: мне нужна максимальная дата для каждого статуса, чтобы соответствовать минимальной дате следующего статуса, например: Максимальное обновление статуса (Открыто) == Минимальное обновление статуса (Неактивно/Блокировать) 2024-06-24 02:00:46.040 == 2024-06-24 02:00:46.040 Максимальное обновление статуса (Неактивно/Блокировано) == Минимальное обновление статуса (Закрыто) 05.07.2024 13:13:02.133 == 05.07.2024 13:13:02.133 Не могли бы вы предложить решение этой проблемы?
Вы можете использовать функцию Lead, чтобы получить минимальную дату следующей группы. Или я что-то упускаю?
Спасибо за предложение! Не могли бы вы показать мне, как использовать функцию LEAD для выравнивания дат? Я не уверен, где это вписывается в ваш запрос.
Я внес небольшую корректировку в ваш запрос: ^ Показывает, что никаких изменений не было сделано... SELECT MIN(CASE WHEN groupFlag = 0 THEN CreateDate ELSE StatusUpdateDatemin END) AS minDate, ...^ FROM ( SELECT *, LAG(StatusUpdateDate) OVER (PARTITION BY id ORDER BY HistoryData DESC, StatusUpdateDate) AS StatusUpdateDatemin, ...^ Идея состоит в том, что когда группа завершена, MIN во внешнем запросе выглядит на одну строку выше, поэтому для минимального значения группы 1 проверяется max группы 0. Пожалуйста, дайте мне знать, правильный ли этот подход, или если у вас есть какие-либо предложения!
Да, это тоже должно сработать. Я подумывал сделать другой уровень подзапроса и взять там LEAD следующей даты, но твой тоже звучит хорошо
Ваш ответ очень помог! Большое Вам спасибо!
Откуда берется дата
2024-08-24для ID = 222?