主页 / dba / 问题 / 331585
Accepted
yossico
Asked: 2023-09-26 16:16:27 +0800 CST

SQLite 中重复序列的查询

  • 772

我有一个 SQLite DB，假设 id 可以被视为行的顺序，我想根据某些标准在表中查找重复序列。

考虑这个例子（http://sqlfiddle.com/#!5/dbdb0/1）

架构+数据：

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 eventLog
order by id asc

结果： 在此输入图像描述

我突出显示了一些序列：如果我查看“详细信息”中的重复序列，结果应该是 ids：[1,2][5,6]，如果我查看“类型”列，结果应该是 [[ 1,2][5,6]]，[[3,4][6,7]]。

有没有一种方法无需外部编程即可查询重复序列 - 仅使用 SQL？

subquery

1 个回答

  1. Best Answer

    您可以使用 OLAP 或窗口函数。有关详细信息，您还可以为类型添加派生列：

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

    如果您想要更通用的解决方案，您可以添加一个包含您感兴趣的连续对的表，并在查询中使用它

    如果您不关心哪些细节是连续的，只关心发生多次：

    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;

    您甚至可以查找不同长度的重复，这里是 2 和 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;

    数据库<>小提琴

    • 2

相关问题