Назначьте время обновления при изменении определенного столбца в SQL
У меня есть таблица со значениями:
В настоящее время я использую столбец FIRST_VALUE для LastUpdatedDate, чтобы получить обновленную дату изменения PartnerProgramStatusDWKey. Однако, как вы можете видеть, в столбце даты указана старая дата последнего обновления, поскольку программа изменилась с 1 обратно на 2. Поэтому я хотел бы, чтобы всякий раз, когда происходит изменение статуса программы, независимо от того, какое значение, последнее дата обновления будет равна дате обновления. Вот желаемый результат:
(1) Существует ли столбец LastUpdatedDate, показанный в вашей первой таблице данных выше, в вашем исходном источнике данных или он просто показывает результат вашей текущей попытки? (2) Вам нужно запросить или обновить всю таблицу за одну операцию, или вам нужна логика для расчета и установки LastUpdatedDate при вставке новых строк?
Ответы 2
Мы могли бы подойти к этому как к проблеме пробелов и островов, где минимальное значение UpdatedTime на остров является желаемым значением.
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Original Id]
ORDER BY UpdatedTime, DWKey) rn1,
ROW_NUMBER() OVER (PARTITION BY [Original Id], PartnerProgramStatusDWKey
ORDER BY UpdatedTime, DWKey) rn2
FROM yourTable
)
SELECT DWKey, [Original Id], PartnerProgramStatusDWKey, UpdatedTime,
MIN(UpdatedTime) OVER (PARTITION BY [Original Id],
PartnerProgramStatusDWKey,
rn1 - rn2) AS LastUpdatedDate
FROM cte
ORDER BY [Original Id], UpdatedTime, DWKey;
Вышеупомянутая логика пробелов и островов зависит от наличия четко определенного порядка, но исходные данные имеют повторяющиеся значения UpdatedTime. Добавление DWKey в качестве второго значения ORDER BY может быть исправлением, если возрастающие значения подразумевают порядок. Скрипка.
@dr Да, это исправляет.
Меня это устраивает, спасибо :)
Один из вариантов — использовать выражение Case с аналитическими функциями в два этапа: первый (внутренний запрос LAG() Over()) определяет даты, когда значение PartnerProgramStatusDWKey изменяется, и второй (внешний запрос Max() Over()) заполняет даты, которые должны иметь новый LastUpdatedDate.
WITH -- S a m p l e D a t a :
tbl ( DWKey, Original_Id, PartnerProgramStatusDWKey, UpdatedTime, LastUpdatedDate ) AS
( Select 2614, 3584, 2, Cast('2023-11-10' as Date), Cast('2023-11-10' as Date) Union All
Select 3731, 3584, 2, Cast('2023-12-20' as Date), Cast('2023-11-10' as Date) Union All
Select 4436, 3584, 2, Cast('2024-01-02' as Date), Cast('2023-11-10' as Date) Union All
Select 4454, 3584, 1, Cast('2024-01-02' as Date), Cast('2024-01-02' as Date) Union All
Select 4888, 3584, 1, Cast('2024-01-09' as Date), Cast('2024-01-02' as Date) Union All
Select 5343, 3584, 1, Cast('2024-01-15' as Date), Cast('2024-01-02' as Date) Union All
Select 22600, 3584, 2, Cast('2024-08-16' as Date), Cast('2023-11-10' as Date) Union All
Select 22909, 3584, 2, Cast('2024-08-21' as Date), Cast('2023-11-10' as Date) Union All
Select 23264, 3584, 2, Cast('2024-08-27' as Date), Cast('2023-11-10' as Date)
)
-- S Q L :
SELECT a.DWKey, a.Original_Id, a.PartnerProgramStatusDWKey, a.UpdatedTime,
Case When PartnerProgramStatusDWKey =
Coalesce( LAG( PartnerProgramStatusDWKey)
Over(Partition By Original_id Order By UpdatedTime, DWKey),
PartnerProgramStatusDWKey
)
Then Coalesce( Max( New_LastUpdatedDate)
Over(Partition By Original_id Order By UpdatedTime, DWKey
Rows Between Unbounded Preceding And Current Row),
New_LastUpdatedDate
)
Else New_LastUpdatedDate
End as LastUpdatedDate
FROM ( Select t.*,
Case When PartnerProgramStatusDWKey !=
Coalesce( LAG( PartnerProgramStatusDWKey)
Over(Partition By Original_id Order By UpdatedTime, DWKey),
PartnerProgramStatusDWKey
)
Then UpdatedTime
Else LastUpdatedDate
End as New_LastUpdatedDate
From tbl t
) a
ORDER BY a.Original_Id, a.UpdatedTime, a.DWKey
/* R e s u l t :
DWKey Original_Id PartnerProgramStatusDWKey UpdatedTime LastUpdatedDate
------ ----------- ------------------------- ----------- ---------------
2614 3584 2 2023-11-10 2023-11-10
3731 3584 2 2023-12-20 2023-11-10
4436 3584 2 2024-01-02 2023-11-10
4454 3584 1 2024-01-02 2024-01-02
4888 3584 1 2024-01-09 2024-01-02
5343 3584 1 2024-01-15 2024-01-02
22600 3584 2 2024-08-16 2024-08-16
22909 3584 2 2024-08-21 2024-08-16
23264 3584 2 2024-08-27 2024-08-16 */
См. скрипку здесь.
Вышеупомянутое можно упростить, чтобы исключить ссылку на исходный ненадежный столбец LastUpdatedDate. Я не думаю, что в исходных данных ОП есть этот столбец. Посмотрите эту скрипку.
@TN Цитата нет. 1 из вопроса (первая строка) «У меня есть эта таблица со значениями:» в конце кавычки, за которой следует таблица с LastUpdatedDate в качестве последнего столбца.
@TN Столбец существует и имеет значение из первой строки, имеющее тот же PartnerProgramStatusDWKey. Логика OP гласит, что начальное значение остается до изменения значения PartnerProgramStatusDWKey (2, затем 1, затем снова 2)... Второе появление PartnerProgramStatusDWKey = 2 требует нового значения для LastUpdatedDate, которое должно быть «назначено» (выражение OP из заголовка) из UpdateTime...
Можете ли вы опубликовать свою попытку вместе с DDL+DML создать образец данных - это значительно облегчит кому-то попытку помочь вам.