DELETE FROM test t
WHERE EXISTS (SELECT 1
FROM (SELECT *
FROM (SELECT id,
TIME,
lag(id) over(ORDER BY TIME) p_id,
lag(TIME) over(ORDER BY TIME) p_time
FROM test)
WHERE substr(id, 1, 3) = substr(p_id, 1, 3) AND
(TIME - p_time) / 86400 <= 10) a
WHERE a.id = t.id OR
a.p_id = t.id);
--上面有点问题，这个
DELETE FROM test t
WHERE EXISTS (SELECT 1
FROM (SELECT a.id aid, b.id bid
FROM test a, test b
WHERE substr(a.id, 1, 3) = substr(b.id, 1, 3) AND
(b.time - a.time) BETWEEN 0 AND 1 / 8640)
WHERE aid NOT IN (SELECT aid
FROM (SELECT a.id aid, b.id bid
FROM test a, test b
WHERE substr(a.id, 1, 3) = substr(b.id, 1, 3) AND
(b.time - a.time) BETWEEN 0 AND 1 / 8640)
GROUP BY aid
HAVING COUNT(aid) = 1) AND
(t.id = aid OR t.id = bid));
