Запрос Oracle, чтобы найти наименьшее изменение перед новым изменением в данном наборе данных

Я пытаюсь обновить таблицу, используя ее собственные данные, и я изо всех сил пытаюсь придумать запрос для моего требования.

пожалуйста, смотрите данные ниже

KEY  VALID_DATE  STATUS    INACTIVE_DT 

1    7/2/2019    ACTIVE   
1    7/27/2018   INACTIVE
1    8/19/2018   INACTIVE
1    9/7/2018    ACTIVE
1    1/15/2019   INACTIVE
1    1/21/2019   INACTIVE 

2   3/31/2019    INACTIVE
2   4/1/2019     INACTIVE

3   7/2/2019     ACTIVE 
3   7/3/2019     INACTIVE
3   7/4/2019     INACTIVE
3   7/5/2019     ACTIVE
3   7/6/2019     INACTIVE

Мне нужно обновить поле inactive_dt в той же таблице, используя приведенные ниже условия.

1) ДАННЫЕ необходимо разделить/сгруппировать по столбцу KEY.

2) заполнять inactive_dt всякий раз, когда статус становится неактивным, используя поле valid_date

3) для последовательных неактивных дат сохранить первую неактивную дату.

4) если между двумя неактивными датами есть активный dt, то вторая неактивная дата должна получить последнее значение, а не первое значение

см. вывод ниже

KEY  VALID_DATE  STATUS        INACTIVE_DT 

    1    7/2/2019    ACTIVE    NULL
    1    7/27/2018   INACTIVE  7/27/2018
    1    8/19/2018   INACTIVE  7/27/2018
    1    9/7/2018    ACTIVE    NULL
    1    1/15/2019   INACTIVE  1/15/2019
    1    1/21/2019   INACTIVE  1/15/2019

    2   3/31/2019    INACTIVE  3/31/2019
    2   4/1/2019     INACTIVE  3/31/2019
    2   6/1/2019     ACTIVE    NULL

    3   7/2/2019     ACTIVE    NULL
    3   7/3/2019     INACTIVE  7/3/2019
    3   7/4/2019     INACTIVE  7/3/2019
    3   7/5/2019     ACTIVE    NULL
    3   7/6/2019     INACTIVE  7/6/2019

Не могли бы вы дать мне предложения для лучшего способа достичь этого.

Спасибо за помощь


oracle lag rank lead
person user1751356    schedule 16.08.2019    source источник
comment
Ваш пункт № 3 противоречит ожидаемому результату.   -  person Popeye    schedule 16.08.2019
comment
Извините, я отредактировал вывод сейчас. Спасибо.   -  person user1751356    schedule 16.08.2019

Ответы (1)


arrow_upward
3
arrow_downward

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

select key, valid_date, status, grp,
       case 
         when status = 'ACTIVE' then null
         else first_value(valid_date) over (partition by key, grp, status)
       end dt
  from (select t.*, sum(case status when 'ACTIVE' then 1 end) 
                        over (partition by key order by valid_date) grp from t)
  order by key, valid_date

демонстрация dbfiddle

person Ponder Stibbons    schedule 16.08.2019