PSQL select max value per minute per id for multiple values a minute per id?

Question

Given a table that looks like this:

 sensor_id |          time          |  voltage
-----------+------------------------+------------
     12292 | 2009-12-01 00:50:04-07 |       2270
     12282 | 2009-12-01 00:50:04-07 |      93774
     12192 | 2009-12-01 00:50:04-07 |       9386
     12609 | 2009-12-01 00:50:05-07 |          0
     12566 | 2009-12-01 00:50:08-07 |          0
     12659 | 2009-12-01 00:50:19-07 |        540
     12660 | 2009-12-01 00:50:19-07 |        550
     12661 | 2009-12-01 00:50:19-07 |        510
     12656 | 2009-12-01 00:50:19-07 |      30240
     12657 | 2009-12-01 00:50:19-07 |      14930
     12658 | 2009-12-01 00:50:19-07 |      17420
     11820 | 2009-12-01 00:50:26-07 |       1.38
     11832 | 2009-12-01 00:50:28-07 |      1.359
     12768 | 2009-12-01 00:50:33-07 |     636588
     13192 | 2009-12-01 00:50:34-07 |      1.401
         .                        .            .
         .                        .            .
         .                        .            .

There are times when you will get records looking like:

     12292 | 2009-12-01 00:50:04-07 |       2270
     12282 | 2009-12-01 00:50:04-07 |      93774
     12192 | 2009-12-01 00:50:04-07 |       9386
     12609 | 2009-12-01 00:50:05-07 |          0
     12566 | 2009-12-01 00:50:08-07 |          0
     12659 | 2009-12-01 00:50:19-07 |        540     *
     12659 | 2009-12-01 00:50:45-07 |        541     *
     12660 | 2009-12-01 00:50:19-07 |        550
     12661 | 2009-12-01 00:50:19-07 |        510
     12656 | 2009-12-01 00:50:19-07 |      30240
     12657 | 2009-12-01 00:50:19-07 |      14930
     12658 | 2009-12-01 00:50:19-07 |      17420
     11820 | 2009-12-01 00:50:26-07 |       1.38     #
     11832 | 2009-12-01 00:50:28-07 |      1.359
     11820 | 2009-12-01 00:50:28-07 |        1.3     #
     12768 | 2009-12-01 00:50:33-07 |     636588
     13192 | 2009-12-01 00:50:34-07 |      1.401
         .                        .            .
         .                        .            .
         .                        .            .

Notice the stared and hashed lines. Each are readings taken in the same minute, but i need only one value per minute, preferably the max value. I tried:

select sensor_id, read_time, voltage 
from table 
where (sensor_id, read_time) 
in (select sensor_id, max(read_time) 
    from table 
    group by sensor_id);

This obviously doesn't work, but I think I'm on the right track?

---

For those interested the final code looks like:

select sensor_id, date_trunc('minute', read_time), max(voltage) 
from table 
group by sensor_id, date_trunc('minute', read_time) 
order by date_trunc('minute', read_time); 
 

Answer 1

You have to group By an expression that "defines" the one minute buckets you want the maximum values in:

select sensor_id, DateHourMinuteFunction(read_time), Max(voltage) 
from table 
Group By sensor_id, DateHourMinuteFunction(read_time)

Where DateHourMinuteFunction(read_time) is some function or Sql Expression in your database that will return an expression that is the same for any read_time in the same minute(i.e., it needs to strip off the seconds values)

Can your database convert a date time to a string? If so, then at a minimum write an expression that converts it to a string, formatted as Month day year, hour, minute, second, and then strip off the seconds part...

Assuming what you already have in the datetime column was a string, then just use substring on it...

select sensor_id, SubString(Cast(read_time as varChar(22)), 0, 16), Max(voltage) 
from table 
Group By sensor_id, SubString(Cast(read_time as varChar(22)), 0, 16)

Answer 2

I work with MS SQL, not Oracle for the most part, so I don't know all of the Oracle date functions off the top of my head. What you basically want to do though is join to a table that has your minutes broken down by a start and end time. It can either be generated on the fly as a kind of subquery or you can have a permanent table in your database with all of the ranges. The code then works out to something like this:

SELECT
     T.sensor_id,
     DR.start_time AS read_time_minute,
     MAX(T.voltage) AS voltage
FROM
     Date_Ranges DR
INNER JOIN My_Table T ON
     T.read_time BETWEEN DR.start_time AND DR.end_time
WHERE
     DR.start_time >= <report start time> AND
     DR.start_time < <report end time>

If you wanted to include times where no measurement was taken then change the INNER JOIN to a LEFT OUTER JOIN. The start and end times will look something like '2009-12-14 00:00:00', '2009-12-14 00:02:00'

Answer 3

Something like

SELECT sensor_id, date_trunc('minute',time), max(voltage)
FROM t
GROUP BY sensor_id, date_trunc('minute',time)

may be what you're looking for?