MYSQL - Как избежать дублирования значения на основе уникального ключа и метки времени сервера

#mysql

Вопрос:

Я пытаюсь написать запрос для обновления повторяющегося значения на 0, если между двумя уникальными метками времени сервера разница составляет 15 секунд.

 id  D_Id             value serverTimeStamp
1   001               1    2016-10-05 23:37:02
2   002               1    2016-10-05 23:37:04
3   001               1    2016-10-05 23:52:24
4   002               1    2016-10-05 23:55:24
5   001               1    2016-10-06 00:29:09
6   002               1    2016-10-06 00:29:11
7   002               1    2016-10-06 01:56:51
8   001               1    2016-10-06 01:56:58

Ожидаемый результат :

 id  D_Id             value serverTimeStamp
1   001               1    2016-10-05 23:37:02
2   002               0    2016-10-05 23:37:04
3   001               1    2016-10-05 23:52:24
4   002               1    2016-10-05 23:55:24
5   001               1    2016-10-06 00:29:09
6   002               0    2016-10-06 00:29:11
7   002               1    2016-10-06 01:56:51
8   001               0    2016-10-06 01:56:58

как добиться этого с помощью запроса?

1. интересное имя 2-го столбца

2. «Группа» в контексте вашего желаемого запроса на самом деле представляет собой кластер записей, все из которых находятся в пределах 15 секунд друг от друга. Следовательно, я думаю, что это проблема пробелов и островов.

3. Проблема четко не определена. Дубликаты могут повлиять на последующие. Это потенциально каскадирует. Также это исправление. Что делать с новыми вставками, такими как принудительное исполнение в триггере, продвигаясь вперед.

4. Та неловкая ситуация, когда unique column нет unique вообще. Кстати, вы хотите обновить свою таблицу по ожидаемому результату?

5. @Drew Четко определено, является ли запрос для идентификации целевых записей частью инструкции update. Есть столбец с уникальным идентификатором, но это было бы настоящей головной болью. Я думаю, что даже Гордон может не попытаться это сделать.

Ответ №1:

Вы можете попробовать:

 UPDATE your_table YT
INNER JOIN 
(
    SELECT 
    id,
    D_Id,
    value,
    serverTimeStamp,
    @prevTimeStamp AS prevTimeStamp,
    @prevTimeStamp := serverTimeStamp
    FROM your_table
    CROSS JOIN (SELECT @prevTimeStamp := NULL) AS var
    ORDER BY serverTimeStamp
) AS x 
ON YT.id = x.id
SET YT.value = 0
WHERE TIMESTAMPDIFF(SECOND,x.prevTimeStamp,YT.serverTimeStamp) <= 15;

Сортируйте записи в порядке возрастания времени.

Сохраните значение метки времени сервера предыдущей строки в определяемой пользователем переменной.

Теперь создайте внутреннее соединение между вашей таблицей и отсортированной таблицей выше (с меткой времени предыдущей строки).

И, наконец, обновите value до нуля ( 0 ) только те записи, которые previousTimeStamp находятся в течение 15 нескольких секунд после serverTimeStamp