SQLite技术问询：计算各distinct token最新两条calc_eta的差值

嘿，我来帮你搞定这个查询需求！你的目标是为每个唯一的token，找出其_id最大的两条记录，计算它们calc_eta的差值（最新记录减倒数第二条），我们可以用SQLite的窗口函数来简洁高效地实现，比你原来嵌套多层子查询的方式清晰得多。

需求回顾 #

你要得到的结果应该是这样的：

iqmC.3aHMBGbl|-7
TFbintkHMBw4H|797

最优解法：用窗口函数实现 #

SQLite 3.25及以上版本支持窗口函数，这里我们用两种方式实现，都能完美解决问题：

方法1：使用LAG()函数（最简洁）

WITH token_sorted AS (
    SELECT 
        token,
        calc_eta,
        -- 获取同一token下，按_id降序的前一条记录的calc_eta
        LAG(calc_eta) OVER (PARTITION BY token ORDER BY _id DESC) AS prev_calc_eta,
        -- 为每个token内的记录按_id降序编号，1是最新的
        ROW_NUMBER() OVER (PARTITION BY token ORDER BY _id DESC) AS rn
    FROM DATA
)
-- 只取每个token的最新记录，计算当前calc_eta与前一条的差值
SELECT token, calc_eta - prev_calc_eta AS delay
FROM token_sorted
WHERE rn = 1;

方法2：使用ROW_NUMBER()分组筛选

如果你更习惯显式筛选两条记录，也可以用这个方式：

WITH ranked_records AS (
    SELECT 
        token,
        calc_eta,
        ROW_NUMBER() OVER (PARTITION BY token ORDER BY _id DESC) AS rn
    FROM DATA
)
SELECT 
    token,
    -- 最新记录的calc_eta减去倒数第二条的calc_eta
    (SELECT calc_eta FROM ranked_records WHERE token = rr.token AND rn = 1) -
    (SELECT calc_eta FROM ranked_records WHERE token = rr.token AND rn = 2) AS delay
FROM ranked_records rr
WHERE rn <= 2
GROUP BY token;

代码解释 #

• CTE部分：PARTITION BY token把数据按token分成独立的组，ORDER BY _id DESC让每个组内的记录从最新（最大_id）到最旧排序。
• LAG()函数：自动帮我们获取同一组中前一条（也就是倒数第二条）记录的calc_eta值，省去了额外的嵌套查询。
• ROW_NUMBER()：为每个组内的记录分配序号，rn=1是最新记录，rn=2是倒数第二条，我们只需要基于这两个序号的值计算差值即可。

测试结果 #

把你的测试数据代入这两个查询，都会得到完全符合预期的结果：

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