Блог

У меня есть таблица «История» с примерно 300.000 строками, которая ежедневно заполняется новыми данными. Я хочу сохранить только последние две строки каждой комбинации refSchema / refId.

На самом деле я иду по этому пути:

Первый шаг:

 SELECT refSchema,refId FROM History GROUP BY refSchema,refId
  

С помощью этого оператора я получаю все комбинации (которых около 40.000).

Второй шаг:

Я запускаю foreach, который ищет существующие строки для приведенного выше запроса следующим образом:

 SELECT id
FROM History
WHERE refSchema = ? AND refId = ? AND state = 'done'
ORDER BY importedAt 
DESC LIMIT 2,2000
  

Пожалуйста, имейте в виду, что я хочу сохранить последние две строки в своей таблице, поэтому я ограничиваю 2,2000. Если я нахожу совпадающие строки, я помещаю идентификаторы в массив с именем idList .

Заключительный шаг

Таким образом я удаляю все идентификаторы из массива:

 DELETE FROM History WHERE id in ($idList)
  

Все это, похоже, не самая лучшая производительность, потому что я должен проверять каждую комбинацию с помощью дополнительного запроса. Есть ли способ иметь один оператор delete, который делает волшебство, чтобы избежать дополнительных запросов 40.000?

Редактировать Обновление: я использую AWS Aurora DB

Если вы используете MySQL 8 , то один концептуально простой способ продолжить здесь — использовать CTE для определения двух верхних строк для каждой группы, которые вы хотите сохранить. Затем удалите любую запись, пара схема / идентификатор которой не отображается в этом белом списке:

 WITH cte AS (
    SELECT refSchema, refId
    FROM
    (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY refSchema, refId ORDER BY importedAt DESC) rn
        FROM History
    ) t
    WHERE rn IN (1, 2)
)

DELETE
FROM History
WHERE (refSchema, refId) NOT IN (SELECT refSchema, refId FROM cte);
  

Если вы не можете использовать CTE, попробуйте встроить вышеупомянутый CTE:

 DELETE
FROM History
WHERE (refSchema, refId) NOT IN (
    SELECT refSchema, refId
    FROM
    (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY refSchema, refId ORDER BY importedAt DESC) rn
        FROM History
    ) t
    WHERE rn IN (1, 2)
);