使用SQLite查找查询中行的差异 [英] Finding the difference in rows in query using SQLite

问题描述

我有一个 SQLite 表，其中包含各种产品的价格.这是一个快照表，因此包含5分钟间隔的价格.我想编写一个查询，该查询将返回每个项目的一行到另一行的价格差异.

I have an SQLite table that contains prices for various products. It's a snapshot table, so it contains the prices on 5 minute intervals. I would like to write a query that would return the difference in price from one row to the next on each item.

列为id(汽车公司)，record_id(产品ID)，价格(该时间点的价格)，时间(距纪元仅几秒钟)

The columns are id (auto inc), record_id (id of the product), price (price at that point in time), time (just seconds since epoch)

我正在尝试返回差异"列，其中包含间隔之间的差异值.

I'm trying to return a 'difference' column that contains a value of difference between intervals.

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是否可能?

Is it possible with SQLite?

第二，是否有可能-是否可以将其限制为最后5条左右的记录?

Secondly, should it be possible - is there a way to limit it to the last 5 or so records?

非常感谢您的帮助.

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

Just wanted to add a few things. I've found ways to do so in other databases, but I'm using XULRunner, thus SQLite. Which is why I'm working with it instead.

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

The secondary question may need clarifying, I'm looking to order by the time and take and analyze the last 5 records. It's an issue I can tackle separately if need be.

这是一个MySQL 解决方案， .这是我要采用的方法，但最关键的是如果表包含序列列但有间隔，请对其重新编号.如果表不包含此类列，请添加一个".通过设计，此场景存在差距，因为有很多记录会立即更新，而不会按顺序排列.

Here's a MySQL solution, kind of. It's the approach I'm heading towards, but the deal breaker is "If the table contains a sequence column but there are gaps, renumber it. If the table contains no such column, add one". By design this scenario has gaps as there is many records updated at once and won't be in order.

推荐答案

我不知道SQLite是否存在某些限制，但是您可以尝试在Sql Server中运行的以下语句.

I do not know if there are some limitations in SQLite, but you can try the following statements that are working in Sql Server.

如果时差恒定(您声明为5分钟)，则可以编写:

If the time difference is constant (you state that it is 5 minutes), you can write:

SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A 
    LEFT OUTER JOIN Table1 B ON A.record_id = B.record_id AND A.time - B.time = 5

否则

SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A 
    LEFT OUTER JOIN Table1 B ON B.record_id = A.record_id 
         AND B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)

无连接的语句如下

SELECT id, record_id, price, time,
    (SELECT A.price - B.price
        FROM Table1 as B
        WHERE B.record_id = A.record_id AND
            B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)) AS difference
FROM Table1 as A 

我希望其中之一能为您提供帮助.

I hope that one of them will help you.

这篇关于使用SQLite查找查询中行的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！