0

I have a table of car ids - id and their battery level - battery, which is collected at ten-minute intervals.

My goal is the following output: total count of unique car ids whose batteries experienced any battery gain per day. That could mean that at any time over the course of the day where a car's battery level was higher than the previous timestamp. In other words, where the value of battery - the previous value of battery is positive. Records with NA values under battery should be skipped.

I have started with a query but I am unsure how to only select unique id's whose battery levels rose. Any recommendations would be appreciated!

SELECT count(distinct id), TO_CHAR(date_trunc('day', (time::timestamp) AT TIME ZONE 'EST'), 'YYYY-MM-DD') AS day FROM test_db .... 
GROUP by day 
ORDER by day

Here is a sample of the data of one car :

  id|                           time| battery
 54 | 2017-12-12 09:50:04.402775+00 |     100
 54 | 2017-12-12 09:40:04.618926+00 |     100
 54 | 2017-12-12 09:30:04.11399+00  |     100
 54 | 2017-12-12 09:20:03.906716+00 |     100
 54 | 2017-12-12 09:10:03.955133+00 |     100
 54 | 2017-12-12 09:00:04.678508+00 |     100
 54 | 2017-12-12 08:50:03.733471+00 |     100
 54 | 2017-12-12 08:40:03.65688+00  |     100
 54 | 2017-12-12 08:30:04.260608+00 |     100
 54 | 2017-12-12 08:20:03.98387+00  |     100
 54 | 2017-12-12 08:10:04.164129+00 |      98
 54 | 2017-12-12 08:00:04.597976+00 |      98
 54 | 2017-12-12 07:50:04.501231+00 |      98
 54 | 2017-12-12 07:40:04.441531+00 |      98
 54 | 2017-12-12 07:30:04.310876+00 |      98
 54 | 2017-12-12 07:20:04.317241+00 |      98
 54 | 2017-12-12 07:10:03.856432+00 |      67
 54 | 2017-12-12 07:00:03.628862+00 |      67
 54 | 2017-12-12 06:50:03.868495+00 |      67
 54 | 2017-12-12 06:40:04.490324+00 |      67
 54 | 2017-12-12 06:30:03.83739+00  |      67
 54 | 2017-12-12 06:20:03.817014+00 |      67
 54 | 2017-12-12 06:10:04.081174+00 |      29
 54 | 2017-12-12 06:00:04.178765+00 |      29





    data_type         
--------------------------
 integer
 timestamp with time zone
 integer
1
  • Battery can't be NA because it's an int. Do you mean where battery IS NULL it should be skipped?
    – indiri
    Dec 12 '17 at 23:41
1

Window functions , for instance LAG should do the trick:

SELECT run_date, count(distinct car_id) 
  FROM 
  (
   SELECT id as car_id, date_trunc('day', (time::timestamp) AT TIME ZONE 'EST') as run_date, 
   battery - LAG(battery,1) OVER (partition by id,date_trunc('day', (time::timestamp) AT TIME ZONE 'EST')
     order by time) as diff
   from test_db
  )a 
  WHERE diff >0
  GROUP BY run_date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.