Удалить все похожие строки, кроме первой
У меня есть база данных, содержащая несколько строк с одинаковыми значениями, но с другой меткой времени. Это произошло из-за ошибки, и теперь я хочу удалить неправильные значения. Слишком много значений для удаления вручную. В одном столбце подсчитывается число, которое сбрасывается ежедневно, поэтому также могут быть похожие строки с разными днями, поэтому я хочу удалять только похожие строки, которые были записаны в базу через час после первой записи. Все записи должны содержать одно и то же значение в столбцах «Режим» и «Значение».
Вот как выглядит моя таблица данных:
В этом случае я хотел бы использовать запрос для удаления строк, в которых идентификатор равен 2, 3, 5, 6 и 8.
Я уже пытался отфильтровать данные с помощью соединения, но не получил результата.
Какие СУБД вы используете? (Лучший ответ может быть специфичным для продукта.) Кроме того, функции даты и времени слишком часто зависят от продукта.
Хотя я могу легко закрыть это как подделку для SQL Server, и я не сомневаюсь, что у MySQL есть аналогичный дубликат.
Обратите внимание, что минимальный воспроизводимый пример также должен включать ожидаемый результат. Т.е. покажите нам содержимое таблицы после удаления.
Укажите логику, какие строки следует удалять, а какие нет. Я думаю, вы хотите сохранить только раннюю строку в час. Но я не должен гадать, вам должно быть ясно. Вы тоже пишите "В этом случае", а какие еще случаи?
почему в конечном результате должна появиться строка 9?
Логика, которую я описал в своем предыдущем комментарии, будет такой: dbfiddle.uk/nhLL-Jxw. Вам нужно будет заменить функцию DATEPART на другую, если вы используете другую СУБД. Но, как уже было сказано, вам неясно, нужна ли вам эта логика.
я бы, вероятно, создал новую таблицу только с хорошими строками, а затем заменил бы таблицы.
Угадывая провалы и острова, @trillion .
@user24151662 user24151662 Пожалуйста, проверьте, нужна ли вам логика, показанная в предоставленной мной скрипте. И отметьте свою СУБД.
В приведенном примере данных нет столбца «подсчитывается число, которое сбрасывается ежедневно». (Идентификатор не сбрасывается.) Предполагается ли, что вы (мы) создадите этот столбец?
@user24151662 user24151662 вы пробовали решение, которое я добавил ниже?
Ответы 3
Шаг 1. Используйте функцию
LAG(), чтобы перенести предыдущую временную метку в следующую строку для того же значения и режима.Шаг 2. Рассчитайте разницу между полем метки времени и предыдущей меткой времени из шага 2 и отфильтруйте результаты.
Шаг 3. Удалите строки из таблицы, используя
delete from.
LAG() использует предложение partition by для захвата сгруппированных значений, то есть режима и значения, а затем использует предложение order by для установки порядка, т.е. в вашем случае это поле метки времени
ПРИМЕЧАНИЕ. Поскольку я не уверен, какой SQL вы используете, функцию timestampdiff необходимо изменить в соответствии с вашим диалектом SQL. Для MYSQL эта функция должна работать
with main as (
select
id,
timestamp,
mode,
value,
lag(timestamp) over (partition by mode, value order by timestamp) as prev_timestamp
from
table
)
delete from table
where id in (
select id
from main
where
prev_timestamp is not null
and timestampdiff(hour, prev_timestamp, timestamp) <= 1
)
Кажется, это неправильно, потому что строка с идентификатором 9 не будет получена.
@trillion им нужно удалить аналогичную запись, кроме первой, а не только выбрать данные.
@JonasMetzler, я отредактировал решение
Обратите внимание, что ОП хочет УДАЛИТЬ.
Также сообщите ОП (и остальным из нас), для какой СУБД это предназначена. (timestampdiff — это функция, специфичная для продукта.)
@jarlh теперь должно быть в порядке, да, временная метка. Я не знаю, какой SQL использует человек.
@jarlh для MySQL, это должно работать
1- Создайте таблицу памяти.
2- Выберите идентификатор и идентификатор строки всех элементов, упорядоченных по метке времени и разделенных по режиму и значению. Это приведет к перезапуску подсчета идентификатора строки на основе изменений режима.
3- Удалить все идентификаторы в таблице памяти с rowId> 1.
CREATE TABLE test ENGINE=MEMORY
SELECT ROW_NUMBER() OVER (partition by mode,value) AS RowID,id
FROM YOUR_TABLE order by timestamp;
delete from YOUR_TABLE e where e.Id in (select t.Id
from test t where rowId > 1);О каких сотрудниках вы говорите?
Я просто привожу пример того, как это можно реализовать. Может быть, мне лучше изменить это, чтобы оно точно соответствовало именам таблиц в сообщении @Jonas Metzler.
строка 9 должна быть на выходе
Вот обновленное решение на основе SQL-сервера:
SELECT id,timestamp,mode,value FROM (
SELECT *,row_number() OVER (partition by value,mode,cast(timestamp as date) order by timestamp) rn
FROM #tmp
) tbl WHERE rn=1 ORDER BY id
(См. демо здесь: https://data.stackexchange.com/stackoverflow/query/1858218/filter-out-the-first-values-of-repeated-entries)
Следует признать, что это решение не будет напрямую «удалять» повторяющиеся строки из вашей таблицы, а вместо этого создаст новую таблицу, содержащую нужные строки. Вы можете вставить полученные строки в новую таблицу, соответствующим образом переименовать таблицы и в конечном итоге удалить старую. Это даст вам время и возможность проверить вновь созданную таблицу, прежде чем опубликовать ее для общего доступа.
строка 9 должна быть в конечном результате
Я исправил свой ответ, поэтому теперь будут сохраняться только первые записи для значений одного и того же mode и того же дня в timestamp.
Спасибо! Это то, что я искал, и это сработало так, как должно было
Я пошел дальше и удалил конфликтующие теги; пожалуйста отредактируйте свой вопрос, чтобы (пере) пометить фактическую (R) СУБД, которую вы используете. Почему пометка нескольких продуктов РСУБД делает мой вопрос неясным?