SQLite查询：获取最近的时间相近（100ms内）记录组

嘿，我明白你的困惑了——你不想用那种固定时间切片的桶划分，而是要找到最新的、满足“时间彼此相近”的记录组。这里先明确两种常见的“彼此相近”定义，然后分别给出SQLite的实现方案：

情况1：组内任意两个记录的时间差都不超过100ms #

这种情况下，最直接的思路是：以最新记录的时间为终点，往前取100ms范围内的所有记录——因为这个区间内的任意两个记录的时间差肯定不会超过100ms，而且这是最新的符合条件的组。

首先要把100毫秒转换成SQLite julianday的单位（julianday以天为单位）：
100ms = 100 / (1000*60*60*24) = 1/864000.0 天

对应的SQL代码：

-- 先获取最新记录的时间
WITH latest_time AS (
    SELECT MAX(created) AS max_created FROM your_table
)
SELECT *
FROM your_table, latest_time
WHERE created >= max_created - 1.0/864000.0
ORDER BY created DESC;

这个方案简单高效，而且完全符合“任意两个记录间隔<=100ms”的要求，和固定时间桶的区别是：它的窗口是动态以最新记录为基准的，而不是固定的时间切片。

情况2：从最新记录开始，相邻记录的间隔不超过100ms（允许组内非相邻记录间隔超过100ms） #

如果你的需求是“只要相邻两条记录的间隔<=100ms就包含，直到遇到第一个间隔超过100ms的记录为止”（比如组内最早和最晚的记录间隔可能超过100ms，但每相邻一对都符合），那可以用窗口函数+递归CTE来实现：

步骤1：先给记录按时间倒序编号，计算相邻记录的时间差 #

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY created DESC) AS rn,
        created - LAG(created) OVER (ORDER BY created DESC) AS time_diff
    FROM your_table
),
-- 步骤2：找到第一个相邻间隔超过100ms的位置
break_point AS (
    SELECT COALESCE(MIN(rn), (SELECT COUNT(*) FROM your_table)+1) AS first_break
    FROM ranked_records
    WHERE time_diff < -1.0/864000.0  -- 因为倒序，后一条比前一条早，所以差值为负，绝对值>100ms
)
-- 步骤3：取从第一条到断点前的所有记录
SELECT rr.*
FROM ranked_records rr, break_point bp
WHERE rr.rn < bp.first_break
ORDER BY created DESC;

解释一下：

• ranked_records给记录按时间从新到旧编号，同时计算每条记录和上一条（更新的）的时间差（因为倒序，所以旧记录的created更小，差值为负）。
• break_point找到第一个相邻间隔超过100ms的记录编号，如果所有相邻间隔都符合，就取总记录数+1，这样所有记录都会被选中。
• 最后取编号小于断点的所有记录，就是符合要求的组。

补充说明 #

• 注意SQLite的julianday是浮点数，计算时要确保用浮点除法（比如1.0/864000.0而不是1/864000，后者会被当作整数除法得到0）。
• 如果你的表有主键（比如id），可以在递归或窗口函数里用主键来避免重复或排序问题。

内容的提问来源于stack exchange，提问作者iffy