I have a table named tc_fuel that is receiving all the fuel related data from GPS vehicles, and I want to get the last “Tank Usage” to calculate MPG for a whole tank, but the readings when the tank is full (100) some times repeat after 2 or 3 or more rows, so I am left with 2 values of a 100 next to each other, I want to be able to get the last “fillup” starting and ending ids.
What I have so far:
SELECT
"tc_fuel".deviceid,
"tc_fuel"."id",
"tc_fuel".fuel,
"tc_fuel"."fuelUsed",
"tc_fuel"."fuelUsed"
FROM "tc_fuel"
WHERE fuel=100
AND deviceid=19
ORDER BY ID
DESC LIMIT 2
Then I go into PHP to check if the id difference its over 100 records to check that the fuel values are not next to each other, but this is doing some more work than it should I was wondering if is there a better way.
For example this vehicle started on a full tank and then drop to 6% tank and did a full tank fill, I want to be able to grab all the data of the last tank.
id | deviceId | fuel ------+-----------+------- 1 | 19 | 100 <-- This should be starting point 2 | 19 | 97 3 | 19 | 100 4 | 19 | 96 5 | 19 | 94 6 | 19 | .... (keeps dropping) 7 | 19 | 33 8 | 19 | 31 9 | 19 | 30 10 | 19 | .... 11 | 19 | 6 12 | 19 | 5 13 | 19 | 6 <-- This should be end point (will flag this id as processed) 14 | 19 | 100 <-- Starts all over again in next iteration of the php script 15 | 19 | 99 16 | 19 | 98 17 | 19 | 100 18 | 19 | 99 19 | 19 | 97 20 | 19 | 96 21 | 19 | ....
Advertisement
Answer
The definition of “fillup” is a bit vague. I’ll assume it’s a fillup when the fuel value rises by more than 50. Replace with a number of your choosing. And it seems like a new tank must start with fuel = 100 (though that’s an odd condition). I added that as comment – uncomment to activate:
SELECT *
FROM (
SELECT *, count(*) FILTER (WHERE fillup) OVER (PARTITION BY device_id ORDER BY id) AS tank
FROM (
SELECT *
, fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50
-- AND fuel = 100 -- additional condition(s)?
AS fillup
FROM tbl
) sub1
) sub2
WHERE device_id = 19
AND tank = 1;
db<>fiddle here
In the subquery sub1, compute the difference between the preceding fuel entry per device and the current one – using the window function lag(). Notably, I use the variant with 3 parameters, providing 0 as default for missing rows to cover the first row per partition. An increase of more than 50 indicates a new fillup.
In subquery sub2, count the number of fillups over time with another window function, thereby assigning a “tank” number to every row.
In the outer SELECT, pick your device and the number of the “tank” filling. Voilá.
If you move the condition WHERE device_id = 19 to the innermost suqbquery, you can drop the PARTITION clauses. Faster, less versatile.
About the FILTER clause:
Only get last tank for given device
According to your comment, defined by “last time tank was filled from 20 or below to 100”.
I assume that later points in time correspond to higher id values. (Be aware that there can be corner case complications with serial columns under concurrent write load.)
The simplest way: just reverse the order and count from the bottom:
SELECT *
FROM (
SELECT *, count(*) FILTER (WHERE fillup) OVER (ORDER BY id DESC) AS tank
FROM (
SELECT *, lag(fuel, 1, 0) OVER (ORDER BY id DESC) = 100
AND fuel <= 20 AS fillup
FROM tbl
WHERE device_id = 19
) sub1
) sub2
WHERE tank = 0
-- ORDER BY id -- optional to get result in ascending order
db<>fiddle here
For this, it’s probably faster to walk through rows procedurally as that only needs a single pass and can stop immediately after the first tank is found.
Support it with an index on tbl(device_id, id DESC).
Example code: