Find all rows in between a set in PostgreSQL

Question

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        | ....

Not being a great database expert, I would loop on the results and add them up, with a counter. When I hit 100, then I have the total and the number of values read. There is probably something that can be done in pure SQL... I think. — Nic3500, Oct 10 '19 at 01:03

Erwin Brandstetter · Accepted Answer · 2019-10-10T22:42:43.060

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:

@LaurenzAlbe: "last" in the sense of "Last Xmas, I gave you my heart, but the very next day ...". Using the unambiguous "preceding" now. Though "Preceding Christmas ..." really doesn't cut it. :) — Erwin Brandstetter, Oct 10 '19 at 02:45
This is great, how can i search just for the most recent fillup instead of all of them? — RonEskinder, Oct 10 '19 at 19:35
Recent as in the last time it went from 20 to 100 for example, because this will be running every day and i just want the "last" fillup and not all of them all the time — RonEskinder, Oct 10 '19 at 20:50

Laurenz Albe · Answer 2 · 2019-10-10T02:47:58.807

SELECT min(id) AS first_id,
       max(id) AS last_id,
       deviceid
FROM (SELECT id, deviceid, fuel,
             count(*) FILTER (WHERE refilled)
                OVER (PARTITION BY deviceid ORDER BY id DESC) AS filling
      FROM (SELECT id, deviceid, fuel,
                   fuel < lead(luel, 1, 0)
                             OVER (PARTITION BY deviceid ORDER BY id) AS before_fill
            FROM tc_fuel
           ) AS refill
     ) AS fills
WHERE filling = 1
GROUP BY deviceid;

First, I mark the entry before a tank filling.

Then, I label the fillings by counting these marks: 0 will be the current filling, 1 the previous filling, and so on.

Finally, I get the first and the last id of the last filling before the current one.

Throw a WHERE deviceid = 19 into the innermost query to get only data for a single device.

Find all rows in between a set in PostgreSQL

2 Answers2

Only get last tank for given device