Добавить первичный ключ в таблицу на основе отметки времени

Question

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

FooTable:
    foo1    foo2   timestamp
     1       1      1
     2       2      1
     2       1      2

В настоящее время нет ограничений первичного ключа для таблицы.

Вопрос:

В Oracle, как лучше всего сделать foo1 первичным ключом таблицы? Предположим, что:

1. Никакие две строки не будут иметь одинаковую метку времени и значение foo1
2. Строка с самой последней отметкой времени имеет приоритет (другие строки с таким же значением foo1 должны быть удалены).

Таким образом, вот искомая структура таблицы после запроса:

FooTable:
    foo1(pk)  foo2   timestamp
     1         1      1
     2         1      2

Примечание. Основная проблема заключается в удалении старых повторяющихся строк. После этого следующий запрос можно использовать для настройки первичного ключа, не опасаясь дубликатов:

alter table FooTable modify foo1 primary key;

Answer 1

Имейте в виду, что вы уничтожаете данные здесь. Убедитесь, что у вас есть хорошая резервная копия базы данных.

Я не работаю с Oracle, но думаю, что это достаточно универсально. Я не знаю, разрешает ли Oracle псевдонимы таблиц в предложении DELETE или нет, так что вам, возможно, придется скорректировать это:

DELETE FT1
FROM FooTable FT1
WHERE EXISTS (
    SELECT *
    FROM FooTable FT2
    WHERE
        FT2.foo1 = FT1.foo1 AND
        FT2.timestamp > FT1.timestamp
)

Answer 2

Если комбинация foo1 и timestamp уникальна, вы можете создать составной ключ, а затем создать представление, в котором будет показана только максимальная временная метка.

Это позволит вам сохранить ваши данные.

Answer 3

DELETE FROM foo_table WHERE EXISTS 
  (    
       WITH q0 AS (SELECT foo1 AS f, timestamp AS ts, row_number() AS rn 
         FROM foo_table OVER (PARTITION BY foo1 ORDER by timestamp DESC) )
       SELECT 1 FROM q0 WHERE q0.f=foo1 AND q0.timestamp=ts AND rn>1
  );

Некоторые другие версии SQL позволяют JOIN стиль для коррелированного подзапроса.