Удаление повторяющихся строк в группе, упорядоченной по дате
Поскольку я сомневаюсь, что мой титул имеет очень большой смысл, я сделаю все возможное, чтобы объяснить, что я прошу. Мне нужно очистить таблицу аудита, которая отслеживает, когда состояние объекта было изменено. По той или иной причине создается несколько записей с новыми датами, в то время как состояние объекта остается неизменным. Мне нужно сохранить первую запись каждого изменения состояния, а затем удалить все последующие записи, в которых состояние будет одинаковым. О, и нет первичного ключа. Да! : |
Вот пример набора данных:
ObjectID ObjectState DateOfEntry 101144 1 2007-08-14 12:39:30.587 101144 1 2007-08-14 12:41:52.620 101144 1 2007-08-14 12:42:11.150 101144 1 2007-08-14 12:42:24.197 101144 3 2007-08-14 12:44:06.403 101144 3 2007-08-14 12:44:06.467 101144 3 2007-08-14 12:46:12.573 101144 3 2007-08-14 12:50:51.670 101144 3 2007-08-14 12:50:51.750 101144 3 2007-08-14 12:56:34.330 101144 4 2007-08-14 17:28:59.280 101144 3 2007-08-14 17:32:26.313 101144 3 2007-08-14 17:32:48.720 101144 3 2007-08-14 17:45:07.460 101144 3 2007-08-14 17:46:31.740 101144 3 2007-08-14 17:47:04.380 101144 3 2007-08-14 17:47:29.507 101144 3 2007-08-14 17:49:13.460 101144 3 2007-08-14 17:54:15.320 101144 3 2007-08-14 17:55:57.540 101144 3 2007-08-14 19:50:11.913 101144 3 2007-08-14 19:53:10.820 101144 3 2007-08-14 20:03:44.900 101144 3 2007-08-16 10:34:56.477 101144 3 2007-08-16 10:36:06.477 101144 3 2007-08-16 10:36:24.570 101144 3 2007-11-06 09:19:26.157 101144 3 2007-11-06 09:24:28.200 101144 4 2010-09-27 14:11:03.287 101144 4 2014-01-27 17:31:58.077 Результат конечной таблицы должен быть:
- Сбивать с толку экзамен t-sql о последовательности или uniqueidentifier
- Как создать хранимую процедуру с динамически созданным набором IN
- Получить поле даты на основе месяца и года
- Исключить функции из предложения where на сервере Sql
- Как узнать содержимое файла SQL Server * .ndf
ObjectID ObjectState DateOfEntry 101144 1 2007-08-14 12:39:30.587 101144 3 2007-08-14 12:44:06.403 101144 4 2007-08-14 17:28:59.280 101144 3 2007-08-14 17:32:26.313 101144 4 2010-09-27 14:11:03.287
Я попытался использовать RANK() но проблема в том, что я не могу просто сортировать ObjectState потому что значения ObjectState могут быть повторены не в порядке. Я должен заказать их с помощью DateOfEntry . Но если я делаю RANK() OVER(ORDER BY DateOfEntry) то я в основном получаю нумерацию строк.
Как я могу создать SQL-запрос, который позволит мне заказывать DateOfEntry но затем группировать ObjectState чтобы я мог удалить все строки в этой «группе состояний объекта», за исключением минимальной группы?
- Общий набор данных не поддерживается службами отчетов sql server 2012
- SQL Date Range - группа по шкале и мин. Макс.
- оператор обновления противоречит ограничениям внешнего ключа
- Перемещение данных SQL по столбцам
- Ошибка SQL Server 2012: параметры для функции X не были заданы
- Как извлечь данные из таблицы2, которая ближе всего к DATE FIELD таблицы1?
- SQL Server 2012, создающий определенный XML из таблицы
- Как получить историю изменения структуры таблицы в SQL Server
Короткий ответ:
; WITH Records AS ( SELECT ObjectId, ObjectState, DateOfEntry, ROW_NUMBER() OVER (PARTITION BY ObjectID ORDER BY DateOfEntry) AS RowNum FROM @Audits ) DELETE R2 FROM Records R1 INNER JOIN Records R2 ON R1.ObjectId = R2.ObjectId AND R1.ObjectState = R2.ObjectState AND R1.RowNum + 1 = R2.RowNum
Доказательство решения
DECLARE @Audits TABLE (ObjectID INT, ObjectState INT, DateOfEntry DATETIME) INSERT @Audits SELECT 101144,1,'2007-08-14 12:39:30.587' UNION ALL SELECT 101144,1,'2007-08-14 12:41:52.620' UNION ALL SELECT 101144,1,'2007-08-14 12:42:11.150' UNION ALL SELECT 101144,1,'2007-08-14 12:42:24.197' UNION ALL SELECT 101144,3,'2007-08-14 12:44:06.403' UNION ALL SELECT 101144,3,'2007-08-14 12:44:06.467' UNION ALL SELECT 101144,3,'2007-08-14 12:46:12.573' UNION ALL SELECT 101144,3,'2007-08-14 12:50:51.670' UNION ALL SELECT 101144,3,'2007-08-14 12:50:51.750' UNION ALL SELECT 101144,3,'2007-08-14 12:56:34.330' UNION ALL SELECT 101144,4,'2007-08-14 17:28:59.280' UNION ALL SELECT 101144,3,'2007-08-14 17:32:26.313' UNION ALL SELECT 101144,3,'2007-08-14 17:32:48.720' UNION ALL SELECT 101144,3,'2007-08-14 17:45:07.460' UNION ALL SELECT 101144,3,'2007-08-14 17:46:31.740' UNION ALL SELECT 101144,3,'2007-08-14 17:47:04.380' UNION ALL SELECT 101144,3,'2007-08-14 17:47:29.507' UNION ALL SELECT 101144,3,'2007-08-14 17:49:13.460' UNION ALL SELECT 101144,3,'2007-08-14 17:54:15.320' UNION ALL SELECT 101144,3,'2007-08-14 17:55:57.540' UNION ALL SELECT 101144,3,'2007-08-14 19:50:11.913' UNION ALL SELECT 101144,3,'2007-08-14 19:53:10.820' UNION ALL SELECT 101144,3,'2007-08-14 20:03:44.900' UNION ALL SELECT 101144,3,'2007-08-16 10:34:56.477' UNION ALL SELECT 101144,3,'2007-08-16 10:36:06.477' UNION ALL SELECT 101144,3,'2007-08-16 10:36:24.570' UNION ALL SELECT 101144,3,'2007-11-06 09:19:26.157' UNION ALL SELECT 101144,3,'2007-11-06 09:24:28.200' UNION ALL SELECT 101144,4,'2010-09-27 14:11:03.287' UNION ALL SELECT 101144,4,'2014-01-27 17:31:58.077' ; WITH Records AS ( SELECT ObjectId, ObjectState, DateOfEntry, ROW_NUMBER() OVER (PARTITION BY ObjectID ORDER BY DateOfEntry) AS RowNum FROM @Audits ) DELETE R2 FROM Records R1 INNER JOIN Records R2 ON R1.ObjectId = R2.ObjectId AND R1.ObjectState = R2.ObjectState AND R1.RowNum + 1 = R2.RowNum SELECT * FROM @Audits
Допускается выход
ObjectID ObjectState DateOfEntry ----------- ----------- ----------------------- 101144 1 2007-08-14 12:39:30.587 101144 3 2007-08-14 12:44:06.403 101144 4 2007-08-14 17:28:59.280 101144 3 2007-08-14 17:32:26.313 101144 4 2010-09-27 14:11:03.287
Если @table – ваша таблица, может быть, ниже sql поможет вам. Я предположил, что сортировка DateOfEntry.
DELETE B FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY DateOfEntry) [ROW] FROM @table) A LEFT JOIN (SELECT *,ROW_NUMBER() OVER(ORDER BY DateOfEntry) [ROW] FROM @table) B ON A.[Row] = B.[Row] - 1 AND ABS(a.ObjectState - b.ObjectState) = 0
Результат до и после для меня
Примечание. Пожалуйста, не учитывайте данные в столбце DateOfEntry. Я сохранил его для удобства.
Альтернативное решение, которое я, наконец, подумал об использовании LAG (), устранит CTE.
DELETE @Audits FROM @Audits a1 INNER JOIN (SELECT ObjectID, DateOfEntry FROM (SELECT ObjectID, DateOfEntry, ObjectState, LAG(ObjectState) OVER(PARTITION BY ObjectID ORDER BY DateOfEntry) AS [PreviousObjectState] FROM @Audits) AS Audits WHERE Audits.ObjectState = PreviousObjectState ) a2 ON a2.ObjectID = a1.ObjectID AND a2.DateOfEntry = a1.DateOfEntry SELECT * FROM @Audits
Длинная версия с доказательством (я дублировал набор данных с другим идентификатором, чтобы проверить раздел, работает как ожидалось)
DECLARE @Audits TABLE (ObjectID INT, ObjectState INT, DateOfEntry DATETIME) INSERT @Audits SELECT 101144,1,'2007-08-14 12:39:30.587' UNION ALL SELECT 101144,1,'2007-08-14 12:41:52.620' UNION ALL SELECT 101144,1,'2007-08-14 12:42:11.150' UNION ALL SELECT 101144,1,'2007-08-14 12:42:24.197' UNION ALL SELECT 101144,3,'2007-08-14 12:44:06.403' UNION ALL SELECT 101144,3,'2007-08-14 12:44:06.467' UNION ALL SELECT 101144,3,'2007-08-14 12:46:12.573' UNION ALL SELECT 101144,3,'2007-08-14 12:50:51.670' UNION ALL SELECT 101144,3,'2007-08-14 12:50:51.750' UNION ALL SELECT 101144,3,'2007-08-14 12:56:34.330' UNION ALL SELECT 101144,4,'2007-08-14 17:28:59.280' UNION ALL SELECT 101144,3,'2007-08-14 17:32:26.313' UNION ALL SELECT 101144,3,'2007-08-14 17:32:48.720' UNION ALL SELECT 101144,3,'2007-08-14 17:45:07.460' UNION ALL SELECT 101144,3,'2007-08-14 17:46:31.740' UNION ALL SELECT 101144,3,'2007-08-14 17:47:04.380' UNION ALL SELECT 101144,3,'2007-08-14 17:47:29.507' UNION ALL SELECT 101144,3,'2007-08-14 17:49:13.460' UNION ALL SELECT 101144,3,'2007-08-14 17:54:15.320' UNION ALL SELECT 101144,3,'2007-08-14 17:55:57.540' UNION ALL SELECT 101144,3,'2007-08-14 19:50:11.913' UNION ALL SELECT 101144,3,'2007-08-14 19:53:10.820' UNION ALL SELECT 101144,3,'2007-08-14 20:03:44.900' UNION ALL SELECT 101144,3,'2007-08-16 10:34:56.477' UNION ALL SELECT 101144,3,'2007-08-16 10:36:06.477' UNION ALL SELECT 101144,3,'2007-08-16 10:36:24.570' UNION ALL SELECT 101144,3,'2007-11-06 09:19:26.157' UNION ALL SELECT 101144,3,'2007-11-06 09:24:28.200' UNION ALL SELECT 101144,4,'2010-09-27 14:11:03.287' UNION ALL SELECT 101144,4,'2014-01-27 17:31:58.077' UNION ALL SELECT 101145,1,'2007-08-14 12:39:30.587' UNION ALL SELECT 101145,1,'2007-08-14 12:41:52.620' UNION ALL SELECT 101145,1,'2007-08-14 12:42:11.150' UNION ALL SELECT 101145,1,'2007-08-14 12:42:24.197' UNION ALL SELECT 101145,3,'2007-08-14 12:44:06.403' UNION ALL SELECT 101145,3,'2007-08-14 12:44:06.467' UNION ALL SELECT 101145,3,'2007-08-14 12:46:12.573' UNION ALL SELECT 101145,3,'2007-08-14 12:50:51.670' UNION ALL SELECT 101145,3,'2007-08-14 12:50:51.750' UNION ALL SELECT 101145,3,'2007-08-14 12:56:34.330' UNION ALL SELECT 101145,4,'2007-08-14 17:28:59.280' UNION ALL SELECT 101145,3,'2007-08-14 17:32:26.313' UNION ALL SELECT 101145,3,'2007-08-14 17:32:48.720' UNION ALL SELECT 101145,3,'2007-08-14 17:45:07.460' UNION ALL SELECT 101145,3,'2007-08-14 17:46:31.740' UNION ALL SELECT 101145,3,'2007-08-14 17:47:04.380' UNION ALL SELECT 101145,3,'2007-08-14 17:47:29.507' UNION ALL SELECT 101145,3,'2007-08-14 17:49:13.460' UNION ALL SELECT 101145,3,'2007-08-14 17:54:15.320' UNION ALL SELECT 101145,3,'2007-08-14 17:55:57.540' UNION ALL SELECT 101145,3,'2007-08-14 19:50:11.913' UNION ALL SELECT 101145,3,'2007-08-14 19:53:10.820' UNION ALL SELECT 101145,3,'2007-08-14 20:03:44.900' UNION ALL SELECT 101145,3,'2007-08-16 10:34:56.477' UNION ALL SELECT 101145,3,'2007-08-16 10:36:06.477' UNION ALL SELECT 101145,3,'2007-08-16 10:36:24.570' UNION ALL SELECT 101145,3,'2007-11-06 09:19:26.157' UNION ALL SELECT 101145,3,'2007-11-06 09:24:28.200' UNION ALL SELECT 101145,4,'2010-09-27 14:11:03.287' UNION ALL SELECT 101145,4,'2014-01-27 17:31:58.077' DELETE @Audits FROM @Audits a1 INNER JOIN (SELECT ObjectID, DateOfEntry FROM (SELECT ObjectID, DateOfEntry, ObjectState, LAG(ObjectState) OVER(PARTITION BY ObjectID ORDER BY DateOfEntry) AS [PreviousUserState] FROM @Audits) AS Audits WHERE Audits.ObjectState = PreviousUserState ) a2 ON a2.ObjectID = a1.ObjectID AND a2.DateOfEntry = a1.DateOfEntry SELECT * FROM @Audits
Допускается выход
ObjectID ObjectState DateOfEntry ----------- ----------- ----------------------- 101144 1 2007-08-14 12:39:30.587 101144 3 2007-08-14 12:44:06.403 101144 4 2007-08-14 17:28:59.280 101144 3 2007-08-14 17:32:26.313 101144 4 2010-09-27 14:11:03.287 101145 1 2007-08-14 12:39:30.587 101145 3 2007-08-14 12:44:06.403 101145 4 2007-08-14 17:28:59.280 101145 3 2007-08-14 17:32:26.313 101145 4 2010-09-27 14:11:03.287