Look for the values within the interval after a specific value to give a pass-fail result-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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