sqlite - sqlite查询-选择所有早于X天的日期，而不是最新的Y天

Question

我的sqlite表具有以下格式（例如，所有都不为null且不是唯一的INTEGER）：

time        type data
1436268660  0    ...
1436268661  1    ...
1436268662  0    ...
1436268666  2    ...
1436268668  1    ...

有时，我需要删除每种类型的所有早于某个时间的行，但是即使每种类型的时间都早于特定时间，也需要从每种类型中保留5个最新的行。
换句话说，要保留每种类型的5条最新行，并且还保留所有比指定时间新的行（如果有5条以上），并删除其余行。

因此，如果指定的时间为X并且类型0比X更新20行，则对于类型0则不执行任何操作（所有内容都足够新）。
同样，如果指定的时间为X并且类型0的行都比X早5条，则不执行任何操作（其中不超过5行）。
但是，例如，如果有7个条目，并且其中至少2个条目早于X，则将删除最旧的2个条目。

到目前为止，我只有这个查询。但这是不正确的。当该类型多于5个时，它只会删除所有早于X的行。如果它们都比X大，则什么也没有。

DELETE FROM table WHERE rowid IN 
  (SELECT table.rowid FROM table JOIN
    (SELECT type FROM table GROUP BY type HAVING COUNT(*) > 5) 
  USING (type) WHERE TIME < 14362685399);

如您所见，情况有点复杂，因为我上面描述的“类型”实际上是多列的唯一组合（您可以用type1，type2，type3代替），但是我想这对于解决方案并不那么重要。
感谢您的任何帮助。

time        type0 type1 type2 data
1436268660  0     0     0     ...
1436268661  1     1     1     ...
1436268662  0     0     0     ...
1436268666  2     2     2     ...
1436268668  1     1     1     ...

编辑：基本上，我需要删除不在以下位置的所有行：（比X更新）UNION（每种类型有5个最新条目）。我只是不知道如何使用“每种类型的5个最新条目”来创建结果。

Answer 1

试试这个。我已将您的表重命名为t。

DELETE FROM t WHERE rowid IN(
  SELECT a.rowid FROM t a
  WHERE time < 14362685399
  AND a.rowid NOT IN (
    SELECT b.rowid FROM t b
    WHERE a.type = b.type
    ORDER BY b.time DESC
    LIMIT 5
  )
);

请注意，由于 correlated subquery会在每次需要时进行评估（对于表中的每个不同 type一次，或者甚至表中的每行一次），因此这在大数据上可能不是很有效。有关如何执行查询的信息）。

顺便说一句，在支持它的SQL变体中，使用窗口函数可能会更好地实现这一点。例如，在 Postgres中。