使用 SQLite 查找查询中行的差异

我有一个SQLite包含各种产品价格的表。它是一个快照表，因此包含 5 分钟间隔的价格。我想编写一个查询，该查询将返回每件商品从一行到下一行的价格差异。

这些列是 id（自动增量）、record_id（产品的 id）、价格（该时间点的价格）、时间（自纪元以来的秒数）

我试图返回一个“差异”列，其中包含间隔之间的差异值。

Given the following

id      record_id       price   time
1       apple001        36.00   ...
67      apple001        37.87   ...
765     apple001        45.82   ...
892     apple001        26.76   ...

I'd like it to return
id      record_id       price   time    difference
1       apple001        36.00   ...     0
67      apple001        37.87   ...     1.87
765     apple001        45.82   ...     7.95
892     apple001        26.76   ...     -19.06
  

用 SQLite 可以吗？

其次，是否有可能 - 有没有办法将其限制为最后 5 条左右的记录？

我将不胜感激任何帮助，谢谢。

只是想添加一些内容。我已经找到了在其他数据库中执行此操作的方法，但我使用的是 XULRunner，因此使用 SQLite。这就是为什么我要使用它。

第二个问题可能需要澄清，我希望按时间排序并获取并分析最后 5 条记录。如果需要的话，我可以单独解决这个问题。

这是一个 MySQLsolution http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-CHP-12-SECT-13.html， 有点儿。这是我正在采用的方法，但最重要的是“如果表包含序列列但有间隙，请对其重新编号。如果表不包含此类列，请添加一个”。根据设计，这种情况存在间隙，因为有许多记录同时更新并且不按顺序排列。

是的有点晚了，但为了完整性。SQLite 2018年版本3.25.0添加了对窗口函数的支持。现在可以使用以下命令完成上述任务LAG() 和 LEAD() https://www.sqlite.org/windowfunctions.html功能。

取自：https://www.sqlitetutorial.net/sqlite-window-functions/ https://www.sqlitetutorial.net/sqlite-window-functions/

LAG 提供对当前行之前给定物理偏移量处的行的访问。

因此，在 Linux 中使用 sqlite3 命令，以下内容应与您的问题中列出的输出匹配。前 2 个命令仅用于显示正确的输出格式。

sqlite> .mode columns
sqlite> .headers on
sqlite> CREATE TABLE data(id INT, record_id TEXT, price REAL);
sqlite> INSERT INTO data VALUES(1,"apple001",36.00);
sqlite> INSERT INTO data VALUES(67,"apple001",37.87);
sqlite> INSERT INTO data VALUES(765,"apple001",45.82);
sqlite> INSERT INTO data VALUES(892,"apple001",26.76);
sqlite> SELECT id, record_id, price, (price - LAG(price, 1) OVER (ORDER BY id)) AS difference FROM data;
id          record_id   price       difference
----------  ----------  ----------  ----------
1           apple001    36.0                  
67          apple001    37.87       1.87      
765         apple001    45.82       7.95      
892         apple001    26.76       -19.06

我希望这能为新用户节省一些时间。