Блог

Проблема

У меня есть начальная таблица, которая выглядит следующим образом:

Я пытаюсь написать SQL-запрос, который запишет эти данные в таблицу аналогичного формата, но с дополнительными украшениями, указывающими, когда срок действия записей истек и какие записи активны. Результаты будут выглядеть следующим образом:

 identifier                       | loaddate   | loadenddate | activeflag | symbol
723a90699e99ec9e00216910910384bd | 2020-04-01 | 2020-04-07  | 0          | DXB
723a90699e99ec9e00216910910384bd | 2020-04-08 | 2999-12-31  | 1          | DXB CL
 

Обратите внимание, что существует 1000 разных идентификаторов, некоторые с одним, двумя, тремя разными символами на разных таймфреймах.

Требования

1. Каждый раз, когда идентификатор отображается в первый раз, он должен быть создан в окончательной таблице с сегодняшней датой в качестве даты загрузки и 2999-12-31 loadenddate и activeflag=1
2. Когда этот идентификатор отображается на второй день, добавляйте строку только в том случае, если символ изменился. Если это так, «истек срок действия» предыдущей строки, установив для loadenddate предыдущей строки значение loaddate для этой новой строки — 1 день и activeflag = 0
3. Запрос sql (или запросы) также должны иметь возможность перезапускаться в исходной таблице каждый день в будущем, чтобы они правильно обрабатывали существующие данные в целевой таблице, а также пустую целевую таблицу (начальный запуск)

Что у меня есть до сих пор

Для начальной загрузки (а не дублирования) у меня есть следующий SQL:

 INSERT INTO finaltable(
listinghashkey
symbol,
loaddate,
loadenddate,
activeflag
)
SELECT
s.listinghashkey
s.symbol,
MAX(s.loaddate),
'2999-12-31 00:00:00.0',
1
FROM
startingtable s
LEFT JOIN finaltable f ON s.listinghashkey = f.listinghashkey
WHERE (f.listinghashkey IS NULL)
GROUP BY s.listinghashkey, s.symbol
 

Преобразовать ваш исходный формат в новый формат довольно просто, поскольку проблема с пробелами и островами:

 select identifier, symbol, min(loaddate),
       nullif(max(loaddate), max_loaddate)
from (select s.*,
             max(loaddate) over () as max_loaddate,
             row_number() over (partition by identifier order by loaddate) as seqnum,
             row_number() over (partition by identifier, symbol order by loaddate) as seqnum_2
      from startingtable
     ) s
group by identifier, symbol, (seqnum - seqnum_2);
 

Это исключает флаг «активен» и произвольную будущую дату. Он просто использует NULL для бесконечного будущего. (Вы можете легко настроить логику для своей версии; с этим просто проще работать.)

Если у вас есть эта таблица, и вы хотите добавить следующую дату загрузки, то вы можете создать следующую версию полностью, используя union all . Идея состоит в том, чтобы разделить обработку на четыре этапа:

• Исторические записи, которые уже закрыты, поэтому новые данные не могут повлиять на них.
• Записи, в которых новые данные согласуются с существующими данными, поэтому ничего не меняется.
• Записи, которых нет в новых данных, поэтому существующую запись необходимо закрыть.
• Новые записи.

SQL выглядит следующим образом:

 -- all completed records
select ft.identifier, ft.symbol, ft.loaddate, ft.loadenddate
from finaltable ft
where loadenddate is not null
union all
-- Basically copy over records where the new data is consistent
select ft.identifer, ft.symbol, ft.loaddate, ft.loadenddate
from finaltable ft join
     oneload ol
     on ft.identifier = ol.identifier and
        ft.symbol = ol.symbol
where ft.loadenddate is null
union all
-- close records that are not in the new batch
select ft.identifer, ft.symbol, ft.loaddate, m.loaddate - interval '1 day'
from finaltable ft cross join
     (select max(loaddate) as loaddate
      from oneload
     ) m left join
     oneload ol
     on ft.identifier = ol.identifier and
        ft.symbol = ol.symbol
where ft.loadenddate is null
-- finally add new records
select ol.identifer, ol.symbol, ol.loaddate, null
from oneload ol left join
     finaltable ft
     on ft.identifier = ol.identifier and
        ft.symbol = ol.symbol and
        ft.loadenddate is null
where ft.identifier is null;
 

Я предпочитаю делать это как select операцию /replace, а не как серию insert update шагов / (или merge , возможно, с использованием). Но это основная идея.