Блог

Главная - Вопросы по программированию - MySQL — обновить строку с разницей в столбце между текущей и предыдущей строкой

MySQL — обновить строку с разницей в столбце между текущей и предыдущей строкой

#mysql

#mysql

Вопрос:

У меня есть таблица data_temp со следующей структурой:

 CREATE TABLE `data_temp` (
  `id` bigint(20) NOT NULL,
  `deviceid` int(11) NOT NULL,
  `registeraddress` int(11) NOT NULL,
  `value` decimal(20,9) NOT NULL,
  `time` datetime NOT NULL,
  `value_diff` decimal(20,9) NOT NULL DEFAULT '0.000000000',
  `time_diff` bigint(20) NOT NULL DEFAULT '0'
)

идентификатор уникален, но не непрерывен.
Записи уникальны для deviceid / registeraddress / time.
Вставляются записи, которые содержат значения для id, deviceid, registeraddress, value и time.
Я хочу вычислить value_diff и time_diff, которые представляют собой разницу в значении между вставленной записью и предыдущей записью, соответствующей deviceid и registeraddress, и разницу во времени в секундах.

В таблице уже есть много записей, все без вычисленных значений value_diff и time_diff, поэтому я хочу обновить существующие данные в таблице с помощью этих значений.

Вот некоторые примеры данных:

 id,  deviceid, registeraddress, value,time    
11102116190949258, 101, 856, 34.000000000, 2011-10-21 16:19:00
11102116240432553, 101, 856, 38.000000000, 2011-10-21 16:24:00
11102116290249672, 101, 856, 44.000000000, 2011-10-21 16:29:00
11102116340121243, 101, 856, 54.000000000, 2011-10-21 16:34:00
11102116390025291, 101, 856, 57.000000000, 2011-10-21 16:39:00
11102116440032519, 101, 856, 63.000000000, 2011-10-21 16:44:00

Я могу выполнить следующий выбор в таблице, в которой будут перечислены нужные мне данные:

 SELECT 
    r_B.deviceid, 
    r_B.registeraddress,
    r_B.value,
    r_B.time, 
    COALESCE(TIME_TO_SEC(TIMEDIFF(r_B.time,r_A.time)), 0) AS time_diff,
    COALESCE(r_B.value-r_A.value, 0) AS value_diff
FROM data_temp r_B
    LEFT OUTER JOIN data_temp r_A
    ON r_B.deviceid = r_A.deviceid 
        AND r_B.registeraddress = r_A.registeraddress 
        AND r_B.time > r_A.time
WHERE NOT EXISTS 
    (
        SELECT r_C.time 
        FROM data_temp r_C
        WHERE r_C.deviceid = r_C.deviceid 
            AND r_C.registeraddress = r_C.registeraddress 
            AND r_C.time > r_A.time 
            AND r_C.time < r_B.time
    )
ORDER BY r_B.deviceid, r_B.registeraddress, r_B.time

Результат:

 id,  deviceid, registeraddress, value, time, value_diff, time_diff
11102116190949258, 101, 856, 34.000000000, 2011-10-21 16:19:00, 0,   0.000000000
11102116240432553, 101, 856, 38.000000000, 2011-10-21 16:24:00, 300, 4.000000000
11102116290249672, 101, 856, 44.000000000, 2011-10-21 16:29:00, 300, 6.000000000
11102116340121243, 101, 856, 54.000000000, 2011-10-21 16:34:00, 300, 10.000000000
11102116390025291, 101, 856, 57.000000000, 2011-10-21 16:39:00, 300, 3.000000000
11102116440032519, 101, 856, 63.000000000, 2011-10-21 16:44:00, 300, 6.000000000

Я уже пару дней пытаюсь разобраться, как написать соответствующее обновление. Я попробовал следующее:

 UPDATE data_temp r_B
    LEFT OUTER JOIN data_temp r_A
    ON r_B.deviceid = r_A.deviceid 
        AND r_B.registeraddress=r_A.registeraddress 
        AND r_B.time > r_A.time
SET r_B.time_diff = COALESCE(TIME_TO_SEC(TIMEDIFF(r_B.time,r_A.time)), 0),
    r_B.value_diff = COALESCE(r_B.value-r_A.value, 0)
WHERE NOT EXISTS 
    (
        SELECT r_C.time 
        FROM data_temp r_C 
        WHERE r_C.deviceid = r_B.deviceid 
            AND r_C.registeraddress = r_B.registeraddress 
            AND r_C.time > r_A.time 
            AND r_C.time < r_B.time
    )

Но, конечно, получите немного разочаровывающую ошибку MySQL:

 Error Code: 1093
You can't specify target table 'r_B' for update in FROM clause

Проблема здесь в предложении WHERE в конце. Обновление работает без предложения, но дает неверный результат (все записи обновляются с разницей между текущей и самой старой записью, а не с предыдущей записью). Все остальное в инструкции работает, кроме предложения WHERE .

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

Комментарии:

1. Вы можете выбрать старые данные во временную таблицу, а затем выполнить обновление, связанное с этой таблицей.

2. Спасибо, Майкл — проблема не в соединении, а в предложении WHERE . Я обновил вопрос, чтобы отразить это. приветствия.

3. Конечно, я понимаю. Но если вы создадите новую таблицу с соответствующими данными, вы можете присоединиться к этой статической таблице без WHERE предложения, и вы не столкнетесь с той же ошибкой.

4. ХОРОШО — должен признать, я не вижу, как это будет работать. Как вы создаете новую таблицу и ссылаетесь на все это в инструкции update, а не используете предложение WHERE? Мой SQL, вероятно, немного ограничен, поэтому хотелось бы знать, как это можно сделать.

5. Вам понадобятся два отдельных оператора. Сначала CREATE TEMPORARY TABLE new_data SELECT… , где SELECT генерируется результирующий набор с deviceid и столбцами, которые вы хотите обновить. Затем UPDATE data_temp dt JOIN new_data nd USING(deviceid,registeraddress) SET td.time_diff=nd.time_diff, td.value_diff=nd.value_diff .