SQL Server - удалить старые записи даты и времени, если дата появляется более одного раза

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

Что я имею:

    ID  insert_time
1   2020-04-26 04:23:04.923
2   2020-04-26 04:23:04.923
3   2020-04-26 04:23:04.923
1   2020-04-28 12:13:04.923
2   2020-04-28 12:13:04.923
3   2020-04-28 12:13:04.923
1   2020-04-28 13:13:04.923
2   2020-04-28 13:13:04.923
1   2020-04-29 10:16:03.876
2   2020-04-29 10:16:03.876
3   2020-04-29 10:16:03.876

Что я хочу:

ID  insert_time
1   2020-04-26 04:23:04.923
2   2020-04-26 04:23:04.923
3   2020-04-26 04:23:04.923
3   2020-04-28 12:13:04.923
1   2020-04-28 13:13:04.923
2   2020-04-28 13:13:04.923
1   2020-04-29 10:16:03.876
2   2020-04-29 10:16:03.876
3   2020-04-29 10:16:03.876

Текущий запрос, над которым я работаю:

SELECT * FROM my_table
WHERE insert_time
   IN (SELECT MIN(insert_time)
       FROM my_table GROUP BY [id])

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


sql sql-server duplicates datetime remove
person Kuris    schedule 23.11.2020    source источник
comment
ID не уникален? Как однозначно идентифицировать строку?   -  person Aaron Bertrand    schedule 23.11.2020
comment
@AaronBertrand Но ID кажется уникальным в данный день.   -  person Tim Biegeleisen    schedule 23.11.2020
comment
Вы хотите удалить другие строки или просто запретить их отображение в результатах? Связан ли ID каким-либо образом с insert_time (например, является ли самый высокий ID в данный день также последней строкой)?   -  person Aaron Bertrand    schedule 23.11.2020
comment
@ Тим, это неправда, в источнике есть несколько строк от 28 апреля с одним и тем же ID.   -  person Aaron Bertrand    schedule 23.11.2020
comment
@ Аарон, Тим прав, и идентификатор уникален для данного дня. Записи загружаются из CSV-файла и будут иметь несколько записей в разные дни. Чтобы уточнить мой вопрос, файл был загружен несколько раз в один и тот же день, но я хочу сохранить записи с более позднего времени приема в тот же день.   -  person Kuris    schedule 23.11.2020
comment
Исходные данные не совпадают (например, несколько строк для ID = 1, 28 апреля). В любом случае, в идеале, у вас также есть способ однозначно идентифицировать строку (и если ID не может этого сделать, это плохое имя). Я согласен, что это то, что вы хотите, но это не то, что у вас есть.   -  person Aaron Bertrand    schedule 23.11.2020
comment
@ Аарон, я не закончил предложение должным образом. Я должен был сказать: ID кажется уникальным в день в ожидаемом выводе.   -  person Tim Biegeleisen    schedule 23.11.2020

Ответы (3)


arrow_upward
0
arrow_downward

Вы можете использовать существующую логику здесь:

DELETE
FROM my_table t1
WHERE EXISTS (SELECT 1 FROM my_table t2
              WHERE t2.ID = t1.ID AND
                    CONVERT(date, t2.insert_time) =
                    CONVERT(date, t1.insert_time) AND
                    t2.insert_time > t1.insert_time);

На простом английском языке приведенный выше запрос предлагает удалить запись, для которой мы можем найти другую запись с тем же ID, той же датой, но позже отметкой времени.

person Tim Biegeleisen    schedule 23.11.2020
comment
Привет, Тим, спасибо за ответ, похоже, все работает без проблем. Я просто проверяю результат и вскоре приму ваш ответ. - person Kuris; 23.11.2020

arrow_upward
1
arrow_downward

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

;WITH cte AS 
(
  SELECT ID, insert_time, rn = ROW_NUMBER() OVER 
    (PARTITION BY ID, CONVERT(date, insert_time) 
     ORDER BY insert_time DESC)
  FROM dbo.source_table
)
SELECT ID, insert_time 
  FROM cte WHERE rn = 1
  ORDER BY insert_time, ID;
person Aaron Bertrand    schedule 23.11.2020

arrow_upward
0
arrow_downward

Ваш метод будет работать, если вы относитесь к нему как к коррелированному предложению:

SELECT t.*
FROM my_table t
WHERE t.insert_time = (SELECT MIN(t2.insert_time)
                       FROM my_table t2
                       WHERE t2.id = t.id AND
                             CONVERT(DATE, t2.insert_time) = CONVERT(DATE, t.insert_time)
                      );
person Gordon Linoff    schedule 23.11.2020