Member Avatar for logicaweb

I'm trying to make average consumption of fuel in my db. But I do not know how to do it.
This is an little explanation:
I have 2 tables:

Tabe CONSUM:
ID   CARID   LI        KM          DATETIME
------------------------------------------------
6     9     70.17   174857   2015-02-10 10:58:51
5     5     51.00   154785   2015-02-09 19:11:19
4     8     99.44   485627   2015-02-09 18:45:48
3     9     47.78   174114   2015-02-09 17:21:32
2     8     24.74   484175   2015-02-07 12:28:37
1     5     89.65   154201   2015-02-02 22:01:14
...

Table CARS
CARID  avglasttwo 
-----------------
5        8.73
8        6.84
9        10.58  
...

*Data as an example.

I need to make the sum of last 2 KM rows in table COSUM with same CAREID something like this (exemple for CARID 9):
km 174857 - km 174114 = 743 and then to use last inserted LI 70.17 (for CARID 9), after that sum 70.17 / (743 / 100) and insert it in table CARS avglasttwo with card ID 9. And to do that for all CARIDs.
Also after each new input it is necessary to refresh avglasttwo.

I currently do this manually and takes me a lot of time.

Edited by pritaeas because: Moved to PHP
  • 3 Contributors
  • 3 Replies
  • 262 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by kalxas

Dani AI

Generated

Summary and practical options (answers build on ’s description and ’s testing suggestion).

Goal: for each CARID take the two most recent CONSUM rows, compute delta_km = last_km − prev_km, then avglasttwo = last_li / (delta_km / 100). The result can be stored in CARS.avglasttwo or as a snapshot column on CONSUM. Two robust SQL approaches and a trigger pattern are shown below; use DATETIME ordering if inserts can arrive out of sequence and ensure an index on (CARID, DATETIME) or (CARID, ID).

MySQL 8+ (window functions — concise and fast)

-- produce per-car value
SELECT CARID,
       ROUND(lastLI / NULLIF((lastKM - prevKM) / 100.0, 0), 2) AS avglasttwo
FROM (
  SELECT CARID, LI AS lastLI, KM AS lastKM,
         LAG(KM) OVER (PARTITION BY CARID ORDER BY DATETIME DESC, ID DESC) AS prevKM,
         ROW_NUMBER() OVER (PARTITION BY CARID ORDER BY DATETIME DESC, ID DESC) AS rn
  FROM CONSUM
) t
WHERE rn = 1 AND prevKM IS NOT NULL;

To persist to CARS, wrap that as a derived table and JOIN in an UPDATE.

Older MySQL (5.7 and earlier — self-join / correlated-subquery)

UPDATE CARS c
JOIN (
  SELECT t.CARID,
         ROUND(t.LI / NULLIF((t.KM - s.KM) / 100.0, 0), 2) AS avglasttwo
  FROM CONSUM t
  JOIN (SELECT CARID, MAX(ID) AS lastID FROM CONSUM GROUP BY CARID) m
    ON t.CARID = m.CARID AND t.ID = m.lastID
  JOIN CONSUM s
    ON s.CARID = t.CARID
   AND s.ID = (SELECT MAX(ID) FROM CONSUM WHERE CARID = t.CARID AND ID < t.ID)
  WHERE s.KM IS NOT NULL AND t.KM > s.KM
) u ON c.CARID = u.CARID
SET c.avglasttwo = u.avglasttwo;

Notes: this avoids GROUP_CONCAT fragility and uses MAX(ID) to find the last two rows. Use NULLIF to avoid division-by-zero.

Trigger / application patterns

  • For a DB-side snapshot on the new CONSUM row, use a BEFORE INSERT trigger that sets NEW.avglasttwo by selecting the most recent KM for that CARID (don’t UPDATE CONSUM from a CONSUM trigger — that’s disallowed).
  • For updating CARS immediately after a fill-up, an AFTER INSERT trigger that computes the same value and updates CARS is simple and atomic for single-row inserts. For bulk loads, prefer a batch UPDATE run from the application.

Cautions and best practice

  • Check for delta_km > 0 and exclude or NULL the result otherwise.
  • Ensure proper numeric types (DECIMAL) and ROUNDing to desired precision.
  • Add an index on CARID+(DATETIME or ID) to keep these queries fast.
  • As suggested, test on a small dump/SQLFiddle before applying to production.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

Can you provide a small SQL dump to test with? Or put it on SQLFiddle.

Edited by pritaeas
Member Avatar for logicaweb

I would like to make php function which would work as I gave an explanation.
After insert in table CONSUM update column avglasttwo from table CARS with same carid. OR even better solution to add new column avglasttwo in table CONSUM and after insert in table CONSUM make sum and insert as avglasttwo in same table/row.

This is how it can be done with sql:

SELECT c.CARID, MAX(c.lastLI) / (MAX(c.KM)-MIN(c.KM)) AS diff
FROM (
    SELECT CONSUM.*, lastTwo.lastLI
    FROM CONSUM
    JOIN 
        (SELECT CARID, 
        SUBSTRING_INDEX(GROUP_CONCAT(LI ORDER BY ID DESC), ',', 1) AS lastLI,
        SUBSTRING_INDEX(GROUP_CONCAT(ID ORDER BY ID DESC), ',', 2) AS twoLatest 
        FROM CONSUM 
        GROUP BY CARID) lastTwo ON CONSUM.CARID=lastTwo.CARID 
            AND FIND_IN_SET(CONSUM.ID, lastTwo.twoLatest)>0) c
GROUP BY c.CARID
Member Avatar for kalxas

try to post in the php section

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.