有条件删除重复记录

Question

我正在SQLite中存储一些实时数据。现在，我希望删除重复的记录以减少数据，并通过SQL命令将其时间范围扩大到20秒。

样本数据：

id     t        col1    col2  
-----------------------------
23  9:19:18     15      16   
24  9:19:20     10      11
25  9:19:20     10      11   
26  9:19:35     10      11   
27  9:19:45     10      11   
28  9:19:53     10      11   
29  9:19:58     14      13

逻辑:在上面的示例中，记录25-28在col1和col2字段中具有相同的值，因此它们是重复的。但是，由于保留一个记录(例如，记录25)和删除其他记录将导致时间框架(=后续数据之间的时间差)超过20，所以我不想删除所有的记录26-28。因此，在上面的示例中，将保留row=25，因为它不是与其上一行重复的。Row=26将保持不变，因为尽管它与上一行重复，但删除此行会导致时间范围超过20(19:45-19:20)。Row=27将被移除，满足这两个条件，row=28将被保留。

我可以将数据加载到C#数据表中，并在循环记录中将此逻辑应用于代码中，但与在数据库中运行相比，这是缓慢的。我不确定这是否可以在SQL中实现。任何帮助都将不胜感激。

编辑：我在row =25之前添加了另一行，以显示具有相同时间的行。小提琴在这里：链接

Answer 1

好的，这里有一个替代答案，它处理您所描述的重复记录场景，使用延迟和引导，最终结果也会变得非常简单！

delete from t1 where id in
(
with cte as (
select id, 
       lag(t, 1) over(partition by col1, col2 order by t) as prev_t,
       lead(t, 1) over(partition by col1, col2 order by t) as next_t
from t1
)
select id
from cte
where strftime('%H:%M:%S',next_t,'-20 seconds') < strftime('%H:%M:%S',prev_t)
)

在线演示这里

Answer 2

我相信这是你所追求的：

delete from t1 where id in
(
select ta.id 
from t1 as ta
join t1 as tb 
   on tb.t = (select max(t) from t1 where t < ta.t 
               and col1 = ta.col1 and col2 = ta.col2)
   and tb.col1 = ta.col1 and tb.col2 = ta.col2
join t1 as tc  
   on tc.t = (select min(t) from t1 where t > ta.t 
               and col1 = ta.col1 and col2 = ta.col2)
   and tc.col1 = ta.col1 and tc.col2 = ta.col2
where strftime('%H:%M:%S',tc.t,'-20 seconds') < strftime('%H:%M:%S',tb.t)
)

在线演示是这里，我在这里进行了几次迭代，以将其简化为上面的内容。基本上，您需要查看(前一行)和下一行()，以确定是否可以删除当前行，这只有在前一行和下一行时间相差不到20秒时才会发生，我理解您的要求。

注:你可能会达到同样的使用滞后和领先，但我将把这作为一个练习，其他人谁感兴趣！！

编辑:如果时间值不是唯一的，我已经在ta/tb和ta/tc联接中添加了附加条件，以包括col1和col2，并更新了小提琴。

Answer 3

我认为你可以做到以下几点：

1. 在SQL中创建一个结果集，该结果集添加按id排序的上一行(为此使用延迟函数(https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-lag/) )。
2. 使用CASE构造(https://www.sqlitetutorial.net/sqlite-case/)计算新列。该列可以是一个名为"keep“的布尔值，基本上是以以下方式计算的：
   • 如果上一行col1和col2值不是相同的=> true
   • 如果上一行col1和col2值相同，但时间差>20秒，则为=> true
   • 在其他情况下，=>为false

3. 对此查询进行筛选，只选择要保留的行(备存=真)。