0

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 most importantly the duration of the outage in seconds.

Each outage should be treated as a separate event, in other words, when ordered by time, the max(time) - min(time) of an uninterrupted sequence of inactive rows the should be considered the duration.

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?

  • I'm thinking you may need to stack CTEs using window functions two or three deep to manage this, the first layer finds the starts and ends of the runs the next numbers the runs and the last groups the data and fudges the result to 60 when count(*)=1 – Jasen Aug 10 '18 at 1:30
  • That makes sense, could you provide a sample query? – the_darkside Aug 10 '18 at 12:51

Your Answer

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

Browse other questions tagged or ask your own question.