Fetch values only between two specifics ordered values in mariadb table-CodePudding

As a c#/python developper, i have one of my program which is inserting datas into a MariaDb 10.3.35.

Table has been created like this:

CREATE TABLE `securityevent` (
  `idsecurityevent` int(11) NOT NULL,
  `Datecre` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `Action` varchar(45) NOT NULL,
  PRIMARY KEY (`idsecurityevent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Data inside are :

    "idsecurityevent";"Datecre";"Action"
5237;"2022-10-03 14:50:00";"A"
5240;"2022-10-03 14:50:05";"A"
5242;"2022-10-03 14:50:10";"A"
5243;"2022-10-03 14:50:15";"EVENT1"
5244;"2022-10-03 14:50:20";"A"
5248;"2022-10-03 14:50:25";"A"
5252;"2022-10-03 14:50:30";"A"
5255;"2022-10-03 14:50:35";"A"
5257;"2022-10-03 14:50:40";"A"
5258;"2022-10-03 14:51:00";"D"
5259;"2022-10-03 14:51:20";"EVENT2"
5260;"2022-10-03 15:02:00";"A"
5261;"2022-10-03 15:02:10";"A"
5263;"2022-10-03 15:02:20";"A"
5264;"2022-10-03 15:02:30";"EVENT3"
5265;"2022-10-03 15:02:40";"D"

Action can be either Armed (A), Disarmed (D) and others values.

Note : Even if system is armed or disarmed, datas are still inserted.

My question:

I don't have my DBA around and i have to verify datas in the table.

To be as accurate as possible, I'd like to be able to select only events if system is armed. In this case, it should give me Event1 and Event3 but not Event2 because the system was not armed.

I tried to play around with lead .. over instructions, but i can't figure yet how to make it work. (pretty sure this is not gonna work)

SELECT Datecre, Action, next_disarm FROM (
SELECT Datecre, Action, lead(Action)over(order by idsecurityevent) as next_disarm
FROM securityevent) alias
#WHERE Action <> next_disarm
WHERE next_disarm not in ('A','D') and Action = 'A'

I would be grateful if someone could point me in the right direction.

Many thanks in advance.

Pierre

CodePudding user response：

This is a Gaps & Islands problem. You can use the traditional solution to assemble groups of rows according to your logic, and then retrieve the data accordingly.

For example:

select *
from (
  select x.*,
    max(case when action like 'E%' then action end) over(partition by g) as event,
    row_number() over(partition by g order by datecre desc) as rn
  from (
    select e.*,
      sum(case when action like 'E%' then 1 else 0 end) over(order by datecre) as g
    from securityevent e
  ) x
) y
where rn = 1 and event is not null

Result:

 idsecurityevent  datecre              action  g  event   rn 
 ---------------- -------------------- ------- -- ------- -- 
 5258             2022-10-03 14:51:00  D       1  EVENT1  1  
 5263             2022-10-03 15:02:20  A       2  EVENT2  1  
 5265             2022-10-03 15:02:40  D       3  EVENT3  1

See running example at db<>fiddle.

CodePudding user response：

you can use LEAD for capturing before or after states depending on your sorting criteria.

select * from ( 
SELECT Datecre, Action, lead(Action)over(order by idsecurityevent desc) before_state
    FROM securityevent ) a where a.action not in ('A','D') and a.before_state = 'A';