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

Question

[Дмитрий Плотников]

Не сильно хорошо знаю я MySQL и поэтому требуется помощь...

Есть небольшая таблица в базе MySQL из двух колонок:
- Total (int, постоянно увеличивается) -
- 1h (int, разница между новым в предыдущим Total)

Задача - одним вызовом к базе вставить в базу новое значение Total, а в колонку 1h вставить разницу между новым значением Total и предыдущим Total.

Comments

[Ivan Ustûžanin]

в общем случае никак, т.к. в SQL нет понятия порядка строк для таблиц

однако, если предыдущесть определяется по максимальному total, то по идее должно сработать что-то типа

INSERT INTO table SET total = :total, 1h = total - (SELECT max(total) FROM table)

но оно скорее всего сломается при параллельных запросах

[Дмитрий Плотников]

Ivan Ustûžanin, фокус в том, что SELECT max(total) выбирает имеено новое вставленное значение total, а не предыдущее.

[Ипатьев]

оно же ещё не вставлено

[Ипатьев]

и непонятно зачем вообще хранить разницу.

[Дмитрий Плотников]

Ипатьев, я просто взял и попробовал

[Дмитрий Плотников]

Ипатьев, для совместимости со старым форматом данных

[Rsa97]

Дмитрий Плотников, Попробуйте поменять порядок, но это уже шаманство

INSERT INTO table SET 1h = :total - (SELECT max(total) FROM table), total = :total

[Ипатьев]

Rsa97, не следует идти на поводу у заведомо бессмысленных утверждений

[Дмитрий Плотников]

Rsa97, реально шаманство (это всегда так кажется, когда не знаешь чего то).

Итого: если в первом варианта получалось, что в качестве max() было вставляемое значение и разница получалась 0,
то при перемене месте total и 1h такое ощущение, что max() выдает 0, и 1h получается равным вставляемому total.. Бред. Видимо проще за пару запросов к БД делать - сначала предыдущее значение total получить, а потом уже вставлять новые

[Ivan Ustûžanin]

Дмитрий Плотников, хоть и странная задача, но появилась идея

INSERT INTO table SET total = :total, 1h = total - (SELECT max(table.total) FROM table)

подозреваю оно брало total как значение из вставляемой строки, а так должно именно max из таблицы посчитать

[Akina]

Задача - одним вызовом к базе вставить в базу новое значение Total, а в колонку 1h вставить разницу между новым значением Total и предыдущим Total.

Дерьмовая задача.
То, что вы хотите поиметь, называется "переопределённые данные". И в общем случае является bad practice.
Нужна дельта - считайте в запросе.

PS. И неплохо бы хотя бы указать версию MySQL..

[Ипатьев]

интересно, а мне одному эти запросы выдают ошибку You can't specify target table 'test' for update in FROM clause?

[Дмитрий Плотников]

Ипатьев, у меня тоже самое. Такое ощущение что MySQL считает, что это нельзя делать из других таблиц, и поэтому имя таблицы указывать вообщен не нужно.

MySQL у меня 8.0.40

[Ипатьев]

OMFG
чувак выполнял совсем другой, бессмысленный запрос

[Ипатьев]

Rsa97, к вопросу о том, что не надо доверять дурацким заявлениям. и писать на их основе дурацкий код

[Ипатьев]

Ivan Ustûžanin, к вопросу о том, что свои запросы надо проверять

[Ипатьев]

Дмитрий Плотников, милок. ну если ты таблицу не указал, то откуда у тебя значение-то браться будет?

[Руслан Федосеев]

триггера зачем придумали?
вставляем новое total

BEFORE insert триггер - считаем разницу с предыдущим total и записываем в соответствующее поле

[Ипатьев]

Руслан Федосеев, не очень понятно, зачем здесь триггер, если можно сделать запросом

[Adamos]

Руслан Федосеев, я тоже предположил триггер... и только потом понял, что здесь не обновление строки, а вставка новой, и разница - не с ее предыдущим значением, а с максимумом таблицы до вставки (то есть другой строкой).

[Руслан Федосеев]

А какая разница?

[Дмитрий Плотников]

Ипатьев, ну ввиду того, что в INSERT таблица указана, во вложенном SELECT используется видимо эта же таблица. Хотя по мне так странно, что нельзя другую таблицу указать, вроде раньше можно было это делать.

[mxelgin]

Дмитрий Плотников, это задача триггера. и никак не запроса. Вы должны сохранить только число. Триггер сделает остальное.

[Ипатьев]

mxelgin, в триггере, внезапно, надо написать тот же запрос. который у автора не работает...

[Ипатьев]

Дмитрий Плотников, когда "видимо", то креститься надо.
И при чем здесь "другая" таблица если речь о той же самой?

[mxelgin]

Ипатьев,
в рамках одной транзакции

Задача - одним вызовом к базе вставить в базу новое значение Total

в запрос

а в колонку 1h вставить разницу между новым значением Total и предыдущим Total.

в триггер

[Ипатьев]

mxelgin, "чем лучше? чем армяне!"

[Akina]

Руслан Федосеев,

BEFORE insert триггер - считаем разницу с предыдущим total и записываем в соответствующее поле

А потом выяснится. что записи могут добавляться "в середину", удаляться и корректироваться.
Ну вот не для того триггеры существуют, не для того...

[Руслан Федосеев]

что значит в середину?

Я все таки не пойму. При добавлении записи - смотрим разницу между предыдущим total и текущим, и сохраняем ее...
по поводу редактирования и удаления записей ничего не говорилось в задаче )

не хотите триггер - не надо, уговаривайте все сломать и сделать правильно....

[Дмитрий Плотников]

Руслан Федосеев, меня вариант с триггером вполне устраивает. И наверное это единственно правильный вариант.

[Дмитрий Плотников]

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

Answer 1

[Fedoresko1]

Согласен с коллегами которые недоумевают насчет хранения разницы. Хранить её крайне вредно - это дупликация данных, которая вызывает дополнительные сложности с поддержанием консистентности. Разница легко вычисляется при чтении, именно так и нужно поступить.

Comments

[Дмитрий Плотников]

Я тоже с этим согласен. Но мне нужно настроить передачу данных во внешнюю систему, а там люди говорят - на не нужен накопительный итог, а только разницу за 1 час. Ладно, будем считать перед вставкой и вставлять уже значение.

[Ипатьев]

Дмитрий Плотников, вы издеваетесь что ли? При чем здесь ХРАНЕНИЕ разницы если речь про ПЕРЕДАЧУ?! Вам русским языком говорят, разница легко вычисляется при передаче, а вы в ответ продолжаете бубнить "мне нужно совсем другое, разница нужна при передаче! и поэтому буду считать её при записи".

[Akina]

это дупликация данных

Дупликация или дублирование? Второе гораздо ближе к происходящему.. Впрочем, оба термина в данном случае - не по делу. Здесь применим термин "переопределённые данные" (но не "переопределение!").

[Дмитрий Плотников]

Ипатьев, не надо меня уговаривать, я это все прекрасно понимаю. А вот заказчик не хочет это понять.

[Ипатьев]

Дмитрий Плотников, ну вы определитесь, речь про заказчика или про внешнюю систему :)

[Дмитрий Плотников]

А чего мне определяться ? Я в качестве подрядчика ставлю у заказчика новую систему. Для сохранения совместимости со старым форматом сбора данных с меня просят сделать такой вариант.

[Akina]

Дмитрий Плотников,

Я в качестве подрядчика ставлю у заказчика новую систему. Для сохранения совместимости со старым форматом сбора данных с меня просят сделать такой вариант.

CREATE TRIGGER tr_bi_tablename
BEFORE INSERT ON tablename
FOR EACH ROW
SET NEW.delta_column = NEW.meter_column - COALESCE(( SELECT meter_column 
                                                     FROM tablename
                                                     ORDER BY created_at DESC LIMIT 1), 0);