I am new to psql window functions and would like to perform a query on test_table in order to find out every instance of inactive outages per mobile_id, country, and brand and the duration of the outage in seconds.
Each outage should be treated as a separate event, in other words, when ordered by time, the appearance of an active row should reset the count.
If there is only a single standalone "inactive" record, there cannot be a difftime and these cases should be assigned a default of 60.
For example:
> test_table
mobile_id state country brand time
1 1a inactive dk nokia 2018-08-09 19:01:53
2 1a inactive dk nokia 2018-08-09 18:51:39
3 1a active dk nokia 2018-08-09 18:42:10
4 1a inactive dk nokia 2018-08-09 18:31:23
5 1a inactive dk nokia 2018-08-09 18:21:27
6 2a active dk apple 2018-08-09 18:12:08
7 2a active dk apple 2018-08-09 18:01:45
8 2a active dk apple 2018-08-09 17:51:29
9 2a active dk apple 2018-08-09 17:41:27
10 2a inactive dk apple 2018-08-09 17:31:32
11 3a active de nokia 2018-08-09 17:21:34
12 3a active de nokia 2018-08-09 17:11:48
13 3a active de nokia 2018-08-09 17:01:46
14 3a active de nokia 2018-08-09 16:51:31
15 3a active de nokia 2018-08-09 16:41:34
Querying the test_table would produce a result like this:
> outages
id country brand diff_time
1 1 dk nokia 614
2 2 dk nokia 596
3 3 dk apple 60
How would one structure such a query?
You can identify the groups of inactives by taking the sum of the actives before them. The rest is basically then aggregation:
select mobile_id, brand, country,
least(extract(epoch from max(time) - min(time)), 60) as diff
from (select t.*,
count(*) filter (where state = 'active') over (partition by mobile_id, brand, country order by time) as grp
from t
) t
group by mobile_id, brand, country, grp;