Battery %   time    charging
90          t1      yes
91          t2      yes
95          t3      no
89          t4      no
87          t5      no
80          t6      no
78          t7      yes
85          t8      yes
50          t9      no
40          t10     no
38          t11     no
20          t12     yes

I want to calculate battery depletion rate as : change in battery / time taken

This should be calculated for ALL the windows when charging is 'no' (sandwiched in between 2 "yes"), and then the average of those rates should be taken.

So, for this dataset it should be:

95 - 80 / t6 - t3 = rate 1

50 - 38 / t11 - t9 = rate 2

average rate = ( rate 1 rate 2 ) / 2

Please note there can be more than 2 windows of no's in the data

Here is my current code -

select ((max(battery_Percentage) - min (battery_Percentage)) / NULLIF(Extract(epoch FROM (max(time) - min(time))/3600),0)) as rate_of_battery_decline   
from table 
where 
table.charging = 'no'

but this is not taking into account windows of no's in between the yes's as I want. Please help.

CodePudding user response：

You have to separate the runs between the charging = 'yes' blocks:

with discharge_intervals as (
  select battery_pct, tstamp, 
         sum((charging = 'yes')::int) over (order by tstamp) as ival_number,
         charging = 'no' as keep
    from cstats
), interval_rates as (
  select ival_number, 
         (max(battery_pct) - min(battery_pct)) 
           / extract(epoch from max(tstamp) - min(tstamp)) as ival_rate
    from discharge_intervals
   where keep
   group by ival_number
)
select avg(ival_rate) 
  from interval_rates;

Fiddle Here