Обнаружение изменений в наборе данных в mysql

ProgramBox

Обнаружение изменений в наборе данных в mysql

Post author:admin
Запись опубликована:9 мая, 2023
Post category:Вопросы по программированию

#mysql #sql

Вопрос:

У меня есть таблица, в которой упорядочены по времени значения:

 id
date
value

value часто одно и то же для сотен записей одновременно, и я хотел бы иметь возможность определять, когда value происходят изменения. Другими словами, я хотел бы знать, когда d / dx (производная от данных) не равна 0!

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

Вот пример таблицы:

 id | date             | value
1  | 2011-04-05 12:00 | 33
2  | 2011-04-06 12:00 | 39
3  | 2011-04-07 12:00 | 39
...
72 | 2011-05-16 12:00 | 39
73 | 2011-05-17 12:00 | 37
74 | 2011-05-18 12:00 | 33
75 | 2011-05-19 12:00 | 33
...

Я ищу запрос, который мог бы возвращать строки, в которых изменяются значения:

 id | date             | value
1  | 2011-04-05 12:00 | 33
2  | 2011-04-06 12:00 | 39
73 | 2011-05-17 12:00 | 37
74 | 2011-05-18 12:00 | 33

Нет необходимости включать в результат первую строку, поскольку таблица с идентичными значениями вернет нулевые строки (т. Е. Изменений нет).

1. Вы имеете в виду, что хотите увидеть, где он меняется по мере выполнения итерации по нему? С какого языка вы это читаете? Или вы хотите это на обычном языке sql?

2. Данные довольно большие, поэтому я бы не хотел перебирать каждую запись. Вместо этого я бы в конечном итоге хотел получить список строк, соответствующих изменениям. Я обновлю вопрос, чтобы сделать это более понятным.

3. Есть ли что-нибудь отличное в значениях, которые мы могли бы использовать для отделения одного от другого?

4. Это просто точки данных. Единственная реальная различимая информация заключается в том, что они упорядочены по времени и могут находиться на постоянном расстоянии друг от друга, а могут и не находиться.

Ответ №1:

 SELECT t.id, t.date, t.value, if( (

SELECT td.value AS last_value
FROM tab td
WHERE td.date < t.date
ORDER BY td.date DESC
LIMIT 1
) <> t.value, 'changed', 'unchanged' ) AS cstatus
FROM tab t order by date

Не очень эффективный запрос, медленный на больших наборах, но делает свое дело.
Добавление счетчика, допустим, num_repeated в качестве другого столбца, обновляемого при вставке, было бы лучшим решением.

1. Спасибо за ответ! Это действительно медленно … 3 секунды всего для 2500 записей. Мне придется воспользоваться вашим предложением добавить еще один столбец для обозначения изменений. Как вы думаете, версия с внутренним соединением будет работать лучше?

2. Возможно, однако я не могу найти какой-либо простой запрос для выполнения той же работы с join. Но 3 секунды для такого набора — это очень долго, есть ли у вас какой-нибудь индекс, покрывающий столбец даты? Вы также могли бы попробовать использовать id вместо дат, но это сработало бы, только если более высокий id означает более высокую (более свежую) дату для всех записей.

3. Таблица, с которой я тестирую, содержит более 1 миллиона строк, но в этом FK всего ~ 2500 записей. У меня действительно есть индекс в столбце date, и вывод EXPLAIN показывает, что он используется (во всяком случае, для подзапроса). Некоторые запросы, которые я обнаружил, сканировали всю таблицу из всех 1 миллиона строк, несмотря на то, что я указал условие FK! id Опция, использующая id /- 1, была бы полезна, если бы я мог гарантировать порядок вставки (чего я не могу). Я полагаю, что использование порядкового номера с индексом обеспечит сопоставимую эффективность.

4. Кстати, я добавил порядковый номер, основанный на времени записи даты. Это дает мне отношение / — и значительно упрощает реализацию этой функции (а также некоторых других).

Ответ №2:

эээ, использовать group by ?

 select min(id), min(date), value
from ...
group by value
order by id asc