Início / dba / Perguntas / 331585
Accepted
yossico
Asked: 2023-09-26 16:16:27 +0800 CST

Consulta para sequências repetidas no SQLite

  • 772

Eu tenho um banco de dados SQLite, vamos supor que o id possa ser considerado como a ordem das linhas, quero encontrar sequências repetidas na tabela de acordo com determinados critérios.

considere este exemplo ( http://sqlfiddle.com/#!5/dbdb0/1 )

esquema + dados:

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

Consulta básica:

select * from eventLog
order by id asc

resultado: insira a descrição da imagem aqui

Destaquei algumas sequências: se eu olhar as sequências repetidas em "detalhes" o resultado deverá ser os ids: [1,2][5,6] e se eu olhar a coluna "tipo" o resultado deverá ser [[ 1,2][5,6]],[[3,4][6,7]].

Existe uma maneira de consultar sequências repetidas sem programação externa - usando apenas SQL?

subquery

1 respostas

  1. Best Answer

    Você pode usar funções OLAP ou de janela. Exemplo para detalhes, você também pode adicionar uma coluna derivada para o tipo:

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

    Se quiser uma solução mais genérica, você pode adicionar uma tabela que contenha pares consecutivos de seu interesse e usá-la em sua consulta

    Se você não se importa com quais detalhes são consecutivos, basta que ocorram várias vezes:

    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;

    Você pode até procurar repetições de vários comprimentos, aqui 2 e 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;

    banco de dados<> violino

    • 2

relate perguntas