创建一个 SQLite 视图，其中一行依赖于前一行 [英] Create a SQLite view where a row depends on the previous row

问题描述

我想在 SQLite 中创建一个视图，其中一行中的字段取决于前一行中字段的值.我可以使用 LAG 分析函数在 Oracle 中执行此操作，但不确定如何在 SQLite 中执行此操作.

I'd like to create a view in SQLite where a field in one row depends on the value of a field in the previous row. I could do this in Oracle using the LAG analytic function, but not sure how to go about it in SQLite.

例如，如果我的表看起来像:

For example, if my table looked like:

ITEM        DAY           PRICE
apple       2011-01-07    1.25
orange      2011-01-02    1.00
apple       2011-01-01    1.00
orange      2011-01-03    2.00
apple       2011-01-08    1.00
apple       2011-01-10    1.50

我希望我的视图看起来像，WHERE item = 'apple':

I'd like my view to look like, with WHERE item = 'apple':

DAY           PRICE    CHANGE
2011-01-01    1.00     (null)
2011-01-07    1.25     0.25
2011-01-08    2.00     0.75
2011-01-10    1.50     -0.50

我正在寻找的查询的等价物会在 Oracle 中查找类似的内容(尚未尝试过，但我认为这是正确的):

The equivalent of the query I'm looking for would look in Oracle something like (haven't tried this, but I think this is correct):

SELECT day, price, 
       price - LAG( price, 1 ) OVER ( ORDER BY day ) AS change
  FROM mytable
 WHERE item = 'apple'

推荐答案

Oracle 等效项是正确的.从 SQLite 3.25.0 开始，您可以本地使用 LAG:

Oracle equivalent is correct. Starting from SQLite 3.25.0 you could use LAG natively:

WITH mytable(ITEM,DAY,PRICE) AS (
    VALUES
    ('apple',  CAST('20110107' AS DATE),    1.25),
    ('orange', CAST('20110102' AS DATE),    1.00),
    ('apple',  CAST('20110101' AS DATE),    1.00),
    ('orange', CAST('20110103' AS DATE),    2.00),
    ('apple',  CAST('20110108' AS DATE),    2.00),
    ('apple',  CAST('20110110' AS DATE),    1.50)
)
SELECT day, price, price-LAG(price) OVER (ORDER BY day) AS change
FROM mytable
WHERE item = 'apple'
ORDER BY DAY;

db<>小提琴演示

这篇关于创建一个 SQLite 视图，其中一行依赖于前一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！