Блог

У меня есть таблица my_table в RedShift следующим образом:

 id, update_time, value
abc, 2019-10-01 05:05:05, 20
xyz, 2020-01-22 01:02:03, 40
abc, 2019-11-11 15:15:15, 40
jul, 2020-05-05 02:02:02, 55
  

Идентификатор abc состоит из двух строк, и я хотел бы сохранить в таблице только строки с последними update_time значениями, удалив остальные. Используя что-то вроде этого:

 CREATE TEMP TABLE foo as 
SELECT *, ROW_NUMBER OVER (PARTITION BY id ORDER BY update_time DESC) AS row_number FROM my_table
  

дает

 id, update_time, value, row_number
abc, 2019-10-01 05:05:05, 20, 2
xyz, 2020-01-22 01:02:03, 40, 1
abc, 2019-11-11 15:15:15, 40, 1
jul, 2020-05-05 02:02:02, 55, 1
  

Итак, теперь я могу идентифицировать последнюю строку как строку с номером row_number равным 1. Как бы я использовал это для удаления строк из исходной таблицы, т. Е. Из my_table ? Или есть лучший способ?

Используйте подзапрос и WHERE :

 CREATE TEMP TABLE foo as 
    SELECT t.*
    FROM (SELECT t.*,
                 ROW_NUMBER OVER (PARTITION BY id ORDER BY update_time DESC) AS row_number
          FROM my_table t
         ) t
    WHERE row_number = 1;
  

Редактировать:

Для a DELETE вы можете использовать using :

 delete from my_table
    using (select t.id, max(t.update_time) as max_ut
          from my_table t
          group by t.id
         ) tt
    where my_table.id = tt.id and
          my_table.update_time < tt.max_ut;
  

альтернативой ответу Гордона (который является гораздо более естественным способом выполнения действий, чем то, что я предлагаю ниже) является создание некоторого уникального идентификатора для строк вместо ваших идентификаторов, которые не являются уникальными в таблице. Итак, я думаю, что-то вроде этого должно сработать

 numbered as (
    select id   update_time id_1
        , row_number() over (partition by id order by update_time desc) as rn 
    from my_table
)
delete from my_table 
where id   update_time in (
    select id_1
    from numbered
    where rn > 1
)
  

Но это может привести к коллизиям, когда id и datetime совпадают в первой строке и нескольких следующих строках, в этом случае этот запрос удаляет все данные в коллизии (что также относится к ответу Гордона, но частично НЕ удаляет такие дубликаты)

Таким образом, вам необходимо проанализировать вашу таблицу на предмет таких столкновений

 select count(*) 
from (
    select id   update_time
        , row_number() over (partition by id   update_time) as rn 
    from my_table
) 
where rn > 1
  

если этот запрос ничего не возвращает, вы в безопасности и можете запустить запрос на удаление. Иначе вам нужно ввести какое-то другое поле в новый идентификатор, чтобы сделать его уникальным, что-то вроде id update_time value as id_1