Query for repeating sequences in SQLite

Question

I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria.

consider this example (http://sqlfiddle.com/#!5/dbdb0/1)

schema + data:

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');

Basic query:

select * from eventLog
order by id asc

result:

I've highlighted some sequences: if I look at repeating sequences in "details" the result should be the ids: [1,2][5,6] and if I look at the "type" column the result should be [[1,2][5,6]],[[3,4][6,7]].

Is there a way to query for repeating sequences without external programing - using SQL only?

Answer 1

You can use OLAP or window functions. Example for details, you can add a derived column for type as well:

 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'
  ;

If you want a more generic solution, you can add a table that contains consecutive pairs that you are interested in, and use that in your query

If you don't care for which details that are consecutive, merely that they occur several times:

select id, type, details, lead_id, lead_details
from (  
  select id, type, details, lead_id, lead_details
     , count(1) over (partition by details, lead_details) cnt
  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
) as tt
where cnt > 1;

You can even look for repetitions of various length, here 2 and 3:

select id, type, details, lead_id, lead_details
from (  
  select id, type, details, lead_id, lead_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;

db<>fiddle