Search code examples
sqliterowswindow-functionsoffsetsubtraction

Sqlite subtract different rows from different columns

Let's say that I have sql table like this:

id | val_1 | val_2
1  | 55    |  300
2  | 90    |  600
3  | 80    |  200
..

Now, I wan't to subtract 300-90, and next 600-80 and so on with offset of one row. Table can be odd count like this. Is there a chance to do this without loop and external functions? I use Python api for sqlite3.

Thanks in advance!

Solution

  • Depending on the output that you want you can use LEAD() window function:

    SELECT *, 
       val_2 - LEAD(val_1, 1, 0) OVER (ORDER BY id) AS difference
FROM tablename;

    or LAG() window function:

    SELECT *, 
       LAG(val_2, 1, 0) OVER (ORDER BY id) - val_1 AS difference
FROM tablename;

    See the demo.