Как суммировать новую и последнюю вставленную запись с тем же идентификатором и вставить результат в новую запись

Мне кажется, что подходящий способ сделать это с помощью триггера BEFORE INSERT ВСТАВКИ. Такой триггер может выглядеть так:

 delimiter // create trigger avg_calc before insert on consum for each row begin declare lastOdo int; -- variable to hold the last odometer reading select km into lastOdo -- store the last reading here from consum where carid = NEW.carid -- for the carid we are inserting order by `datetime` desc -- get the last one by date limit 1; set NEW.average = (NEW.km - lastOdo) / NEW.li; -- update the average we're about to insert end// delimiter ; 

Это автоматически автоматически усреднит последние две записи на автомобиль каждый раз, когда для этого автомобиля будет вставлена ​​новая запись.

демо здесь

Подход

У вас есть две ошибки в вашем подходе, что представляет собой сложность.

1. Любая колонка, которая может быть выведена, например, AVERAGE, не должна храниться.

   Если он хранится, он представляет собой дублирующийся столбец … который приводит к обновлению аномалии, как вы переживаете. Точка нормализации – это устранение дублирования данных и, таким образом, устранение аномалий обновления. Он также устраняет сложный код, такой как этот, а также триггеры и т. Д.

   Вычислять SUM (), AVG () и т. Д. Только в наборе результатов «на лету».

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

   • у вас есть образ мышления.

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

   Идентификатор – это указатель физической записи, это ничего не значит, пользователь не должен его видеть. Но вы (и другие) дали ему смысл.

   Что приклеивает вас к физической структуре файла, а не к логической структуре данных. Это, в свою очередь, усложняет ваш код.

   Поэтому, не давая вам скорректированную команду CREATE TABLE , оставляя ваш как есть, давайте притвориться, что идентификатор и AVERAGE не существуют в файле.

Третий элемент, не связанный с подходом, кажется, что из приведенного рисунка 10.58 вы хотите использовать километры на литр, тогда как арифметика, которую вы указали (литры на 100 км), составит 9.44. Если вы действительно хотите какого-то среднего, вам лучше разобраться в элементах.

Решение

  (Code obsolete due to revision) 

Пересмотренный вопрос

Я пытался получить данные, которые вы дали, в то время как вопрос оставался путанным (обратите внимание на комментарии к этому вопросу). Поскольку вы пересмотрели свой вопрос, требование теперь становится ясным. Теперь кажется, что вы хотите (a) литров на 100 км [все еще не «средний»], и (б) общий показатель для каждой записи [своего рода общая сумма]. В этом случае используйте этот код.

Приведенные выше примечания остаются в силе и применимы.

  SELECT CARID, DATETIME, KM, LI, LPCK = ( LI_TOT / ( ( KM_LAST-KM_FIRST / 100 ) ) -- not stored FROM ( -- create a Derived Table with KM_FIRST SELECT CARID, DATETIME, -- not stored KM_FIRST = ( SELECT MIN( KM ) -- get the first KM for car FROM CONSUM WHERE CARID = C.CARID ), KM_LAST = ( SELECT MAX( KM ) -- get the last KM for car FROM CONSUM WHERE CARID = C.CARID ), KM, -- KM for this row LI, -- LI for this row LI_TOT = ( SELECT SUM( LI ) -- get the total LI for car FROM CONSUM WHERE CARID = C.CARID AND KM != ( -- exclude first LI for car SELECT MIN( KM ) -- get the first KM for car FROM CONSUM WHERE CARID = C.CARID ) ) FROM CONSUM C ) AS CONSUM_EXT ORDER BY CARID, DATETIME 

Заметьте, что я манипулирую данными, и только данные, никакие физические поля, нам не нужно заботиться о физических аспектах файла. Литры на 100 км (то, что вы вызываете СРЕДНЕГО) не сохраняется, и там предотвращается аномалия обновления. Общий показатель для каждой записи рассчитывается «на лету» только во время отображения.

Это также устраняет проблему с вашей /first entry .

Конечно, CARID также не имеет смысла для пользователя.

Пожалуйста, не стесняйтесь комментировать или задавать вопросы и т. Д.

Жесткое хранение

Существует множество проблем с хранением значения, которое может быть получено. Это жесткое кодирование на уровне хранения данных. Конечно, вы можете использовать триггер, чтобы облегчить боль, но он все равно не сработает, потому что (а) принцип нарушен и (б) он нарушает существующие инженерные принципы. Например. что происходит, когда LI для одной строки вводится неправильно (например, 700.17) и впоследствии исправляется (например, 70.17)? Все последующие строки для этого автомобиля теперь некорректны и должны быть пересчитаны и обновлены. Итак, теперь вам нужен триггер Update, а также триггер Insert. Раковые соединения сами по себе.

Понятие «аномалия обновления», запрет на хранение ценностей, которые могут быть получены, было с нами с 1970 года по уважительной причине. Мы избегаем их, по уважительной причине.

Следующий запрос получает последний идентификатор для каждого автомобиля:

 select c.*, (select c2.id from consum c2 where c2.carid = c.carid and c2.id < c.id order by c2.id desc limit 1 ) as last_id from consum c; 

Затем, для информации, которую вы хотите, вы можете присоединиться к таблице, чтобы получить полную запись, а затем выполнить расчет:

 select c.ID, c.CARID, c.LI, c.KM, c.DATETIME, c.li / (c.km - cprev.km) / 100) as avg from (select c.*, (select c2.id from consum c2 where c2.carid = c.carid and c2.id < c.id order by c2.id desc limit 1 ) as last_id from consum c ) c left join consum cprev on c.last_id = cprev.id; 

Я все равно отправлю. Моя идея:

• Использование последних двух строк (новая запись и последняя запись) с использованием возврата массива. Поэтому я мог бы использовать count (array) – 1 & -2.

,

 <?php include("./inc.connect.php"); $Query = "SELECT id, km, li FROM consum WHERE cardid = 9"; $users = $db->query($Query); $array_res = $users->fetchAll(); $nb_rows = count($array_res); $diff_km = $array_res[($nb_rows - 1)]['km'] - $array_res[($nb_rows - 2)]['km']; $new_li = number_format(($array_res[($nb_rows - 1)]['li'] / ($diff_km * 0.01)),2); print "<pre>"; print_r($array_res); print "</pre>"; echo "diff km : " . $diff_km . " new_li : " . $new_li . "<br>"; $UpdateQuery = "UPDATE consum SET average = '$new_li' WHERE id = " . $array_res[($nb_rows - 1)]['id']; /* Begin a transaction, turning off autocommit */ try { $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->beginTransaction(); $sth = $db->exec($UpdateQuery); $db->commit(); } catch (Exception $e) { $db->rollBack(); echo "Failed: " . $e->getMessage(); } ?> в <?php include("./inc.connect.php"); $Query = "SELECT id, km, li FROM consum WHERE cardid = 9"; $users = $db->query($Query); $array_res = $users->fetchAll(); $nb_rows = count($array_res); $diff_km = $array_res[($nb_rows - 1)]['km'] - $array_res[($nb_rows - 2)]['km']; $new_li = number_format(($array_res[($nb_rows - 1)]['li'] / ($diff_km * 0.01)),2); print "<pre>"; print_r($array_res); print "</pre>"; echo "diff km : " . $diff_km . " new_li : " . $new_li . "<br>"; $UpdateQuery = "UPDATE consum SET average = '$new_li' WHERE id = " . $array_res[($nb_rows - 1)]['id']; /* Begin a transaction, turning off autocommit */ try { $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->beginTransaction(); $sth = $db->exec($UpdateQuery); $db->commit(); } catch (Exception $e) { $db->rollBack(); echo "Failed: " . $e->getMessage(); } ?> 

Результат:

 Array ( [0] => Array ( [id] => 3 [0] => 3 [km] => 174114 [1] => 174114 [li] => 47.78 [2] => 47.78 ) [1] => Array ( [id] => 6 [0] => 6 [km] => 174857 [1] => 174857 [li] => 70.17 [2] => 70.17 ) ) diff km : 743 new_li : 9.44 UPDATE consum SET average = '9.44' WHERE id = 6 

Я сделал математику – это правильно 70.17 / 7.43 = 9.44

Ваше AVERAGE где CARID=5 & CARID=8 не вычисляет то же самое, что и CARID=9 , поэтому мой пример точно не соответствует, но если вы пытаетесь это сделать на вставке, вы можете сделать что-то вроде

 INSERT INTO CONSUM SELECT 6, 9, 70.17, 174857, '2015-02-14 12:58:51', ROUND((174857-a.KM)/70.17, 2) FROM CONSUM a WHERE a.CARID = 9 ORDER BY ID DESC LIMIT 1; 

Пример sqlfiddle – http://sqlfiddle.com/#!9/dce1d/1