I have a system that writes its log into the Postgres database. And I want to analyse data after a specific event to tell how this system works.
So status 9
, in my case, acts like a trigger. After the trigger I need to go through the statuses within the counter
interval. Interval
equals 50
:
counter > trigger_counter AND counter <= trigger_counter + 50
Having values <= 9
within the interval means FAIL
. And PASS
if there are no such values
For a better understanding, here is a part of my database:
date_time | status | counter
----------------------------+--------+---------
...
2022-08-11 02:34:55.005362 | 9 | 10450 --| interval = 50
2022-08-11 02:36:15.034973 | 650 | 10452 |
2022-08-11 02:36:23.478429 | 8 | 10459 | value <= 9 -> FAIL
2022-08-11 02:36:25.594843 | 600 | 10461 --|
2022-08-11 02:42:38.348111 | 650 | 10580
...
2022-08-11 05:15:56.151411 | 9 | 3419 --|
2022-08-11 05:16:34.072692 | 325 | 3419 |
2022-08-11 05:16:38.304485 | 692 | 3456 --| all values > 9 -> PASS
2022-08-11 05:17:50.266063 | 692 | 3676
...
2022-08-13 13:28:28.400286 | 9 | 13175 --|
2022-08-13 13:34:53.718093 | 325 | 13175 |
2022-08-13 13:36:49.562367 | 692 | 13199 |
2022-08-13 13:36:51.679191 | 9 | 13199 | FAIL --|
2022-08-13 13:37:27.490148 | 650 | 13200 --| |
2022-08-13 14:12:35.345867 | 650 | 13247 ------------| PASS
2022-08-13 14:22:18.601711 | 650 | 13356
...
Result should look like this:
date_time | result
----------------------------+--------
2022-08-11 02:34:55.005362 | FAIL
2022-08-11 05:15:56.151411 | PASS
2022-08-13 13:28:28.400286 | FAIL
2022-08-13 13:36:51.679191 | PASS
I can imagine how to do this using for/while loops in C++ or Python. But is it possible to get this result in PostgreSQL?
2
Answers
Easily. I originally thought this looked like a job for the
lead()
windowing function, but sample your table twice and join the one to the other and you’re good.You may use
EXISTS
operator within a case expression as the following:See a demo.