8

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.

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

Is it possible with SQLite?

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

I'd appreciate any help, thanks.

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.

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.

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.

Taryn
  • 242,637
  • 56
  • 362
  • 405

2 Answers2

11

Yes, it is a bit late, but for completeness. SQLite Release 3.25.0 in 2018 added support for window functions. And the above task can now be completed by using the LAG() and LEAD() functions.

Taken from: https://www.sqlitetutorial.net/sqlite-window-functions/

LAG Provides access to a row at a given physical offset that comes before the current row.

So using the sqlite3 command in Linux, the following should match the output listed in your question. The first 2 commands are only there to display proper output format.

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

I hope this will save new users some time.

Jeroen
  • 121
  • 1
  • 4
9

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

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

otherwise

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)

A statement without joins is the following

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.

Panos
  • 18,992
  • 6
  • 45
  • 54