By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE eventLog
(
id integer primary key autoincrement,
type varchar(20),
details varchar(30)
);
✓
INSERT INTO eventLog
(type, details)
VALUES
('power', 'power on');
✓
INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning started');
✓
INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning finished');
✓
INSERT INTO eventLog
(type, details)
VALUES
('power', 'power off');
✓
INSERT INTO eventLog
(type, details)
VALUES
('power', 'power on');
✓
INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning started');
✓
INSERT INTO eventLog
(type, details)
VALUES
('power', 'power off');
✓
select *
from (
select id, type, details
, lead(id) over (order by id) as lead_id
, lead(details) over (order by id) as lead_details
from eventLog
) as t
where t.details = 'power on' and t.lead_details = 'cleaning started'
;
| id | type | details | lead_id | lead_details |
|---|---|---|---|---|
| 1 | power | power on | 2 | cleaning started |
| 5 | power | power on | 6 | cleaning started |
create table consecutive_detail_pairs
( fst varchar(30) not null
, snd varchar(30) not null
, primary key (fst, snd)
);
✓
insert into consecutive_detail_pairs
(fst, snd)
values ('power on', 'cleaning started');
✓
select *
from (
select id, type, details
, lead(id) over (order by id) as lead_id
, lead(details) over (order by id) as lead_details
from eventLog
) as t
join consecutive_detail_pairs c
on t.details = c.fst and t.lead_details = c.snd
;
| id | type | details | lead_id | lead_details | fst | snd |
|---|---|---|---|---|---|---|
| 1 | power | power on | 2 | cleaning started | power on | cleaning started |
| 5 | power | power on | 6 | cleaning started | power on | cleaning started |
select details, lead_details
from (
select id, type, details
, lead(id) over (order by id) as lead_id
, lead(details) over (order by id) as lead_details
from eventLog
)
group by details, lead_details
having count(1) > 1;
| details | lead_details |
|---|---|
| power on | cleaning started |
select id, type, details, lead_id, lead_details, lead2_details
from (
select id, type, details, lead_id, lead_details, lead2_details
, count(1) over (partition by details, lead_details) cnt
, count(1) over (partition by details, lead_details, lead2_details) cnt2
from (
select id, type, details
, lead(id) over (order by id) as lead_id
, lead(details) over (order by id) as lead_details
, lead(details,2) over (order by id) as lead2_details
from eventLog
) as t
) as tt
where cnt > 1
or cnt2 > 1;
| id | type | details | lead_id | lead_details | lead2_details |
|---|---|---|---|---|---|
| 1 | power | power on | 2 | cleaning started | cleaning finished |
| 5 | power | power on | 6 | cleaning started | power off |