sql - 使用触发器计算SQLite中行之间的差异

Question

给定这样的表结构：

ID|Measurement|Diff|Date

其中，ID和Date是复合主键，并且Date列进一步索引了行。

我想使用触发器（在insert or replace into之后）为表计算Diff列。 Diff列仅记录同一Measurement的两个相邻日期之间的ID值差异。

在SQLite中执行此操作的最佳方法是什么？由于表很大，即1M +行，因此性能在这里至关重要。

Answer 1

用于计算值的查询应如下所示：

update structure
    set new.diff = new.measurement - (select s.measurement
                                      from structure s
                                      where date < new.date
                                      order by date desc
                                      limit 1)
    where id = new.id;

update应使用主键索引快速识别行。子查询应使用 date索引快速找到上一行。因此，这应该具有合理的性能。