Обновление Oracle с использованием функции задержки

Я пытаюсь использовать функцию lag для update эффективных дат начала в измерении SCD2. Я пытаюсь использовать подзапрос для самостоятельного присоединения к таблице на основе ПК. Обновление не будет обновляться на основе предыдущей даты окончания, а будет обновляться только до значения по умолчанию (которое само по себе). Когда я удаляю значение по умолчанию, я получаю сообщение об ошибке, потому что действующая дата начала не может быть нулевой. Когда я просто использую select, я получаю желаемый результат.

Любая помощь будет принята с благодарностью, я уверен, что это что-то простое!

update schema.d_account ac1
set effective_start_dt = (select lag(effective_end_dt, 1, effective_start_dt) over(partition by id order by effective_end_dt asc) 
                          from schema.d_account ac2
                          where ac1.account_dim_key = ac2.account_dim_key),
audit_last_update_dt = sysdate,
where id in '0013600000USKLqAAP'

Стол:

введите здесь описание изображения

Желаемые результаты:

введите здесь описание изображения


sql oracle lag
person gisr    schedule 23.11.2016    source источник
comment
Вы можете сообщить нам об ошибке, которую вы получаете? я не вижу проблемы с запросом здесь.   -  person Vamsi Prabhala    schedule 23.11.2016
comment
meta.stackoverflow.com/questions/285551/   -  person a_horse_with_no_name    schedule 23.11.2016

Ответы (1)


arrow_upward
0
arrow_downward

Может быть одна из этих причин, почему ваше обновление не работает. Я не знаю точной структуры вашей таблицы. Вы не предоставили образцы данных. Если ваш account_dim_key уникален для каждой строки, тогда select lag() ... from schema.d_account ac2 where ac1.account_dim_key = ac2.account_dim_key) вернет одну строку, и вы фактически обновите effective_start_dt до effective_start_dt (это значение по умолчанию для функции задержки)

Если ваш account_dim_key одинаков для всех этих строк, которые вы предоставили в качестве образца, тогда select lag() ... from schema.d_account ac2 where ac1.account_dim_key = ac2.account_dim_key) вернет несколько строк, и Oracle будет жаловаться, что ОБНОВЛЕНИЕ невозможно (есть конкретное сообщение об ошибке, я не помню точную формулировку.

Чтобы ваш запрос работал, вам нужно использовать другой подход:

update schema.d_account ac1
set effective_start_dt = (select prev_dt from
                          (select lag(effective_end_dt, 1, effective_start_dt) over(partition by id order by effective_end_dt asc) as prev_dt
                          , ROWID as rid
                          from schema.d_account ac2) a where a.rid = ROWID),
audit_last_update_dt = sysdate,
where id in '0013600000USKLqAAP'

Итак, в основном у вас есть подзапрос a со столбцом ROWID, в котором вы строите предыдущую дату. Для оператора UPDATE вы присоединяетесь к этому подзапросу по ROWID.

Примечание. Если ваш account_dim_key уникален для каждой строки, вы можете использовать его вместо ROWID: вы можете повысить производительность в зависимости от индексов, которые у вас есть для вашей таблицы.

ОБНОВЛЕНИЕ: приведенный выше запрос может привести к плохой производительности. Вам будет лучше с оператором MERGE ниже:

MERGE INTO (SELECT id, effective_start_dt, ROWID rid, audit_last_update_dt 
   FROM schema.d_account WHERE id in '0013600000USKLqAAP') ac1
USING (select lag(effective_end_dt, 1, effective_start_dt) over(partition by id order by effective_end_dt asc) as prev_dt
      , ROWID as rid
      from schema.d_account) ac2
ON (ac1.rid = ac2.rid) 
WHEN MATCHED THEN UPDATE SET ac1.effective_start_dt = ac2.prev_dt,
ac1.audit_last_update_dt = sysdate;
person cha    schedule 23.11.2016
comment
Сначала я заменил ROWID на acocunt_dim_key, так как это PK в таблице, но это повлияло на производительность (обновление 100 тыс. записей заняло более 1 часа), но когда я запускаю вышеуказанный запрос с использованием ROWID, я получаю сообщение об ошибке, что подзапрос возвращает более 1 строки. Как я могу использовать ROWID? - person gisr; 29.11.2016
comment
обновить с помощью ROWID update dexwhs.d_account_veeva ac1 set Effective_start_dt = (выбрать prev_dt из (выбрать отставание ( Effective_end_dt, 1, Effective_start_dt) over (partition by id order by Effective_start_dt, account_dim_key) as prev_dt , ROWID as rid from dexwhs.d_account_veeva ac2) a где a.rid = ROWID ), audit_last_update_dt = sysdate - person gisr; 29.11.2016
comment
обновить с помощью account_dim_key : обновить dexwhs.d_account_veeva ac1 set Effective_start_dt = (выбрать prev_dt из (выбрать отставание ( Effective_end_dt, 1, Effective_start_dt) over (разделить по порядку идентификаторов с помощью Effective_end_dt asc) как prev_dt , account_dim_key как избавиться от dexwhs.d_account_veeva ac2) a где a.rid = account_dim_key), audit_last_update_dt = sysdate - person gisr; 29.11.2016
comment
Я добавил другое утверждение с MERGE - person cha; 30.11.2016
comment
Спасибо за альтернативу. Однако я получаю сообщение об ошибке: не удается обновить действующую дату начала до нуля (это поле не может быть пустым) с измененным оператором слияния. Но когда я запускаю выбор, он дает мне ожидаемые результаты для каждого ключа dim записи/учетной записи (действительная дата окончания из предыдущей строки) - person gisr; 30.11.2016
comment
Я исправил это, переместив предложение WHERE в ac1. - person cha; 30.11.2016