skip to Main Content

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


  1. 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.

    Select Trigger.ts, Trigger.counter,
         min(Case When logs.status<Trigger.status
            or logs.status=Trigger.status
                and Trigger.ts <> logs.date_time  
             Then 'FAIL' Else 'PASS' End) as Result
    From (Select date_time as ts, status, counter
          From mytable
          Where status=9
        ) Trigger Inner Join mytable logs
          On logs.counter between Trigger.counter.   
             and Trigger.counter + 50
    Group By Trigger.ts, Trigger.counter
    Order by Trigger.ts
    
    Login or Signup to reply.
  2. You may use EXISTS operator within a case expression as the following:

    SELECT date_time, 
           CASE 
             WHEN EXISTS (SELECT 1 FROM Table_Name D 
                          WHERE D.status<=9 AND 
                                D.counter <= T.counter+50 AND
                                D.counter > T.counter AND
                                D.date_time > T.date_time
                         )
             THEN 'FAIL'
             ELSE 'PASS'
           END AS result
    FROM Table_Name T
    WHERE status=9
    ORDER BY date_time
    

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search