logo标签列表

如何将具有相同ID的新插入项和上一个插入项求和，并将结果插入到新条目中

phpmysql
3
我是一个有用的助手，可以为您翻译文本。
我正在尝试使用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函数，该函数将为每个具有相同CAREID的新条目和上一个KM条目进行求和，例如CARID 9的情况如下：

  • 新条目KM 174857 - 上一个条目KM 174114 = 743
  • 新条目LI 70.17（对于CARID 9），在此总和中，70.17 /（743/100）
  • 将结果插入为新条目AVERAGE。

我花了很多时间来尝试使其工作，但简单地说，我从未接近过。

你是想替换 CARID=9 的 /first entry，还是获取 CARID=9 和 ID=6 的 10.58？ - Sean
不，我写第一条目的原因是在没有先前条目的情况下无法计算平均消耗。我正在尝试为每个新条目获取平均值。 - Aleksa
所以您想计算CARID=9和ID=6的10.58，对于其他CARID值的8.73和6.84也是一样吗？您是想从所有行中得到总平均值，还是只需要最后/前一行？ - Sean
是的。当我添加一个新条目时（这将是新ID，同时我手动编写其他输入），我希望自动计算新的平均值。 - Aleksa
1
@Alex。（a）感谢您澄清问题并更正示例数字。我已经修改了我的答案，并提供了修改后的代码，以回答您的修订问题。（b）我已经解释了触发器为什么不起作用。 - PerformanceDBA
显示剩余2条评论
5个回答
5

方法

你的方法存在两个错误，这会增加复杂性。

  1. 任何可以派生出来的列，比如说你的AVERAGE，不应该被存储。

    如果它被存储了，就构成了一个重复的列......这会导致更新异常，就像你正在经历的那样。规范化的目的是消除数据重复，从而消除更新异常。它还可以消除这样的复杂代码，以及触发器等。

    只在结果集中动态计算SUM()，AVG()等。

  2. 使用ID列，这基本上意味着你有一个记录归档系统，而不是关系数据库。不用枚举它引起的许多问题（我已经在别处讨论过了），在这里只是指出问题：

    • 你有一个ID的思维方式。

    ID是一个物理记录指针，它不能提供关系数据库所需的行唯一性。

    ID是一个物理记录指针，它没有实际意义，用户不应该看到它。但是你（和其他人）已经为它赋予了意义。

    这将把你固定在文件的物理结构上，而不是数据的逻辑结构上。这反过来又使你的代码变得复杂。

    因此，假设ID和AVERAGE不存在于文件中，而不是给出一个已更正的CREATE TABLE命令，保留原样。

第三个问题与方法无关，从给定的数字10.58看来，你想要每升公里数，而你详细说明的算术（每100公里的升数）将产生9.44。如果你确实想要某种平均值，最好先弄清楚元素。

解决方案

    (Code obsolete due to revision)

修改后的问题

我试着获取你给出的数字，但是问题仍然很混乱（请注意相关评论）。由于您已经进行了修订，要求现在变得清晰明确。现在似乎您想要每100公里的升数[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公里的升数（你称之为AVERAGE）未被存储，从而避免了更新异常。每个记录的总体数字是“即时计算”的，仅在显示时才会进行计算。这也消除了您的/first entry问题。当然，对于用户来说，CARID也是没有意义的。请随意评论或提问等等。
硬编码存储值存在许多问题。这是在数据存储级别上进行硬编码。当然，你可以使用触发器来缓解痛苦，但它仍然行不通，因为（a）原则被打破，（b）它违反了现有的工程原则。例如，当单个行的LI输入不正确（例如700.17），并随后更正（例如70.17）时，会发生什么？该车的所有后续行现在都不正确，必须重新计算和更新。因此，现在您需要一个Update触发器以及一个Insert触发器。癌症加剧了自身。
自1970年以来，更新异常的概念，禁止存储可以派生的值，一直与我们同在，出于很好的原因。我们避免它们，出于很好的原因。
感谢您详细的回复，我学到了很多。您说得完全正确，首先我没有很好地阐述问题，其次概念也不够清晰。我是一个初学者，所以选择了一开始看起来能给出结果的答案，尽管它并不是完整解决我的问题，正如您指出的那样，存在许多缺陷。为了成功应用您写的所有内容，我还有很多要学习的。非常感谢您花费时间回答我的问题。 - Aleksa
1
@Alex. (a) 谢谢你的诚实。 (b) 非常欢迎。这并不是一种损失，因为其他人会阅读我的答案并学习。我对被选择或受欢迎没有依恋之情。 (c) 你可以自由地取消选择一个答案，然后选择另一个答案。投票也是如此，但有一些限制。 (d) 如果你这样做，那将是一个激励，让我花些时间扩展这个答案，提供链接等。其他一些答案也很令人震惊。这应该被指出，并解释原因。 - PerformanceDBA
2
@PerformanceDBA。存储派生（重复，计算，...）值就像在数据存储级别上进行“硬编码”-很好的观点。看起来我们大多数人都讨厌一直进行硬编码，但对派生数据并不那么关注。 - dzhu
2

在我看来，实现这个功能的最恰当方式是使用“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 ;

每次为该车插入新条目时，它将自动平均计算该车的最后两个条目。
演示请参见：此处。
非常感谢您详细的回复。完美地运作。 - Aleksa
1
以下查询获取每辆汽车的最后一个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;
1

我无论如何都会发布。

  • 使用数组返回的最后2行（New entry & last entry）。 因此，我可以使用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();
 }
?>

结果：

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

0

您的 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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接