У меня есть база данных, содержащая несколько строк с одинаковыми значениями, но с другой меткой времени. Это произошло из-за ошибки, и теперь я хочу удалить неправильные значения. Слишком много значений для удаления вручную. В одном столбце подсчитывается число, которое сбрасывается ежедневно, поэтому также могут быть похожие строки с разными днями, поэтому я хочу удалять только похожие строки, которые были записаны в базу через час после первой записи. Все записи должны содержать одно и то же значение в столбцах «Режим» и «Значение».
Вот как выглядит моя таблица данных:
В этом случае я хотел бы использовать запрос для удаления строк, в которых идентификатор равен 2, 3, 5, 6 и 8.
Я уже пытался отфильтровать данные с помощью соединения, но не получил результата.
🤔 А знаете ли вы, что...
SQL может работать с множеством языковых расширений, таких как PL/SQL, T-SQL и другими.
Шаг 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
)
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);Вот обновленное решение на основе 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)
Следует признать, что это решение не будет напрямую «удалять» повторяющиеся строки из вашей таблицы, а вместо этого создаст новую таблицу, содержащую нужные строки. Вы можете вставить полученные строки в новую таблицу, соответствующим образом переименовать таблицы и в конечном итоге удалить старую. Это даст вам время и возможность проверить вновь созданную таблицу, прежде чем опубликовать ее для общего доступа.