Удаление старых записей с сохранением минимального количества записей на одного родителя

Мой вопрос похож на оператор sql для удаления записей старше XXX, если строк больше, чем YY, но этот вопрос просто имеет дело с одним родителем, я хочу удалить записи для всех родителей за один раз.

Рассмотрим эту таблицу:

CREATE TABLE Children
(
    ChildId int NOT NULL,
    ChildCreated datetime NOT NULL,
    ParentId int NOT NULL
)

Это могут быть любые отношения родитель-потомок, поэтому имена являются общими.

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

Я пробовал некоторые операторы с вложенными SELECT и GROUP BY, которые дали мне некоторые результаты, но ни один из них не дал мне правильный набор результатов.

Поскольку я использую SQL Server, я придумал следующее решение, которое отлично работает:

WITH CTE AS
(
  SELECT ROW_NUMBER() OVER (Partition BY ParentId ORDER BY ChildCreated DESC) 
  As RowNo, ChildCreated FROM Children
)

DELETE FROM CTE WHERE RowNo > 10
AND RevisionCreated < DATEADD(MONTH,-1,GetDate())

Общее табличное выражение группирует все дочерние элементы для каждого родителя вместе и добавляет непрерывный номер строки в зависимости от порядка создания. Самый новый дочерний элемент для каждого родителя имеет номер строки 1, десятый самый новый имеет 10. Таким образом, я могу просто удалить все записи с номером строки больше 10, если они также старше месяца.

Мой вопрос в том, что если мне придется делать то же самое в системе, где CTE не поддерживаются. Каково решение ANSI SQL-92 для этой проблемы?


delete-row sql-server sql-delete
person Peter Hahndorf    schedule 10.02.2012    source источник
comment
Насколько мне известно, CTE действительны в стандарте ANSI SQL 99. Возможно, вам нужно решение для базы данных, которое не поддерживает стандарт? (возможно, MySQL?)   -  person Mark Byers    schedule 10.02.2012
comment
@MarkByers Я не знал, что CTE есть в SQL-99, да, меня бы заинтересовало решение для систем, где CTE не поддерживаются. Я изменил вопрос, чтобы задать SQL-92.   -  person Peter Hahndorf    schedule 10.02.2012

Ответы (2)


arrow_upward
2
arrow_downward

Основываясь на других ответах и ​​относительной простоте моего запроса, я думаю, что, возможно, слишком упрощаю проблему, но я предполагаю, что, поскольку parentID не имеет значения NULL, он не ссылается на childID. В этом случае это может быть достигнуто так же просто, как показано ниже

DELETE  Children
FROM    Children a
WHERE   ChildCreated < DATEADD(MONTH, -1, GETDATE())
AND     (   SELECT  COUNT(*)    -- NUMBER OF NEWER CHILDREN WITH THE SAME PARENT
            FROM    Children b
            WHERE   a.ParentID = b.ParentID
            AND (   a.ChildCreated < b.ChildCreated
                OR  (a.ChildCreated = b.ChildCreated AND a.ChildID > b.ChildID)
                )
        ) >= 10

Хотя этот точный SQL может нуждаться в настройке в зависимости от СУБД, я не знаю ни одной СУБД, в которой этот принцип нельзя было бы применить.

person GarethD    schedule 10.02.2012
comment
Похоже, это не работает, потому что вы исключаете все дочерние элементы, созданные после даты отсечения, и смотрите только на старые. Подумайте о том, что возвращает ваш подзапрос, может быть, для самого старшего ребенка он возвращает 11, поэтому он соответствует требованиям, но для второго по возрасту он возвращает 10 (поскольку самый старый не моложе второго по старшинству), поэтому второй по старшинству не квалифицировать. - person Peter Hahndorf; 10.02.2012
comment
Я немного отредактировал ответ, изменив › на ›= после подзапроса, и сделал поправку на 2 дочерних элемента одного и того же родителя, имеющих одинаковую отметку времени. Я не совсем понимаю ваш комментарий, если только он не относится к ошибке ›=, которую я сделал после подзапроса, поэтому я не знаю, как улучшить свой ответ. Опубликованный мной запрос удалит дочерние элементы старше одного месяца ниже родительского, по крайней мере, с 10 новыми дочерними элементами. Насколько я могу судить, это был критерий, который вам нужен? Я собираюсь списать это на то, что я неправильно понял вопрос. - person GarethD; 10.02.2012
comment
Это работает, и все три запроса теперь удаляют один и тот же набор записей. Ваш даже работает для повторяющихся значений ChildCreated. Спасибо. - person Peter Hahndorf; 10.02.2012

arrow_upward
2
arrow_downward

Более чем вероятно снижение производительности, но следующее утверждение

  • добавляет rownumber, перезапуская для каждой группы, используя подвыборку для каждой записи по количеству дочерних элементов одной и той же группы.
  • возвращает таблицу в памяти, содержащую ChildId с заданным номером строки.
  • JOIN вернуться к исходному столу ChildId
  • добавляет предложение WHERE, где вы можете фильтровать заданные rownumber и/или дополнительные столбцы из Children.
  • использует результат в операторе DELETE FROM.

Выражение SQL

DELETE FROM Children
FROM    Children c
        INNER JOIN (
          SELECT  ChildId
                  , ( SELECT COUNT(*) + 1
                      FROM   Children rn
                      WHERE  rn.ChildCreated < Children.ChildCreated
                             AND rn.ChildId = Children.ParentId
                      ) AS rn           
          FROM    Children
        ) rn ON rn.ChildId = c.ChildId          
WHERE   rn.rn > 10
        AND ChildCreated < DATEADD(MONTH,-1,GetDate())
person Lieven Keersmaekers    schedule 10.02.2012
comment
Интересно, несколько моментов: 1. Синтаксис «FROM children FROM Children c» мне кажется неправильным, я думаю, вы можете удалить первую часть «FROM Children». 2. Вы сравниваете rn.ChildId с Children.ChildId, что предполагает, что идентификаторы находятся в последовательности создания, было бы безопаснее сравнивать rn.ChildCreated с Children.ChildCreated. 3. Почему COALESCE не должен всегда быть ParentId, который не может быть NULL? В противном случае он, кажется, работает, но действительно медленно. - person Peter Hahndorf; 10.02.2012
comment
Я проверил это на практике, у которой немного другие требования. Поле ChildCreated на самом деле является полем LastModified, поэтому я не могу полагаться на порядок ChildId и должен сравнивать поля LastModified, проблема в том, что у родителя могут быть два дочерних элемента с одинаковым значением в LastModified. Если 10-й и 11-й старшие дочерние элементы имеют одинаковое значение LastModified, ни один из них не включается. Но если у вас есть уникальные идентификаторы, это должно работать. - person Peter Hahndorf; 10.02.2012
comment
@PeterHahndorf - 1. На самом деле DELETE FROM Children FROM является допустимым синтаксисом. 2. Вы правы в сравнении дат ChildCreated. 3. Сразу на ‹g›. - person Lieven Keersmaekers; 10.02.2012
comment
извините, удаление ... в порядке, первое, что я сделал, это изменило DELETE на SELECT, а затем это недействительно, моя вина. Ваша текущая версия работает для уникальных значений «ChildCreated», за исключением того, что мне пришлось изменить «rn.ChildCreated ‹ Children.ChildCreated» на «rn.ChildCreated › Children.ChildCreated» - person Peter Hahndorf; 10.02.2012