我正在尝试使用PHP和MYSQL自动计算燃料的平均消耗。但我不知道该怎么做。这是解释:
课程CONSUM:
ID CARID LI KM DATETIME AVERAGE
--------------------------------------------------------------
6 9 70.17 174857 2015-02-14 12:58:51 9.44
5 5 51.00 154785 2015-02-13 10:11:19 8.73
4 8 99.44 485627 2015-02-12 11:45:48 6.84
3 9 47.78 174114 2015-02-11 10:21:32 /first entry
2 8 24.74 484175 2015-02-10 10:28:37 /first entry
1 5 89.65 154201 2015-02-09 10:01:14 /first entry
*数据作为一个例子,我想看起来像什么。除了AVERAGE列,其他都可以,这就是为什么我在这里。
我试图使php函数,将使总和的新条目和最后一个KM条目具有相同的CAREID像这样的东西(例如CARID 9):
- <
- 新条目公里/strong> 174857 - 最后条目公里 174114 = 743
- 新条目LI 70.17(对于CARID 9),在此sum中为70.17/(743/100)
- insert result as New entry AVERAGE .
我花了很多时间试图让这个工作,但我从来没有接近。
方法
你的方法有两个错误,这导致了复杂性。
任何可以派生的列,例如AVERAGE,都应该存储而不是。
如果它被存储,它构成一个重复列…这会导致更新异常,正如你所经历的。规范化的要点是消除数据重复,从而消除更新异常。它还消除了像这样的复杂代码,以及触发器等。
计算SUM(), AVG()等,在结果集中仅,在飞行中
使用ID列,这基本上意味着你有一个记录归档系统,而不是关系数据库。不列举它导致的许多问题(我已经在其他地方做过了),只是在这里命名问题
- 你有一个本我的心态。
ID是一个物理记录指针,它不提供行唯一性,这是关系数据库所需要的。
ID是一个物理记录指针,它没有任何意义,用户不应该看到它。但你(和其他人)赋予了它意义。
将您粘到文件的物理结构上,而不是数据的逻辑结构上。这反过来又使你的代码变得复杂。
因此,不给您更正的
CREATE TABLE
命令,保留您的命令,让我们假装ID和AVERAGE在文件中不存在。
第三项,与方法无关,从给出的数字来看,10.58,你想要每升的公里数,而你详细的计算(每100公里的升数)将产生9.44。如果你想要某种平均数,你最好先找出元素。
解决方案
(Code obsolete due to revision)
修订问题
我试图得到你给的数字,而这个问题仍然很困惑(注意这方面的评论)。既然你有修订你的问题,现在的要求是明确的。现在看来,你想要的是(a)每百公里的公升数(仍然不是"平均值"),(b)每项记录的总体数字(一种跑步总数)。在这种情况下,使用以下代码。
以上说明仍然有效并适用。
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
对用户来说也是没有意义的。
请随时评论或提问等
硬存储
存储可派生的值有很多问题。这是数据存储级别的硬编码。当然,您可以使用触发器来缓解痛苦,但它仍然不起作用,因为(a)原则被打破,(b)它违反了现有的工程原则。如。当单行的LI输入错误时会发生什么情况(例如:700.17),并随后予以纠正(例如。70.17) ?这辆车的所有后续行现在都不正确,必须重新计算和更新。所以现在您需要一个Update触发器和一个Insert触发器。癌症自我合成。
Update Anomaly的概念,即禁止存储可派生的值,从1970年就有了,这是有充分理由的。我们有充分的理由避开它们。
在我看来,这样做的适当方法是使用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 ;
这将在每次为该车插入新条目时自动平均每辆车的最后两个条目。
demo here
下面的查询获取每辆车的最后id:
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();
}
?>
Result:
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 example - http://sqlfiddle.com/#!9/dce1d/1