试图在 SQLite 中滞后一列 [英] trying to a lag a column in SQLite

问题描述

我有一个表格，其中的行代表某个时间点的个人.我正在尝试使用另一列的滞后值 (status) 创建一列 (statusLag)，如下所示:

I have a table where rows represent individuals in a point in time. I am trying create a column (statusLag) with the lagged values of another column (status), like so:

workerID    timeVar     status      statusLag
---------------------------------------------
1           1           0           NULL
1           2           1           0
1           3           1           1
1           4           1           1
----------------------------------------------
2           3           1           NULL
2           4           0           1
2           5           1           0
2           6           0           1

我尝试了什么

执行以下操作失败(保持列不变)，我不明白为什么

What I have tried

Doing the following fails (leaves the column unchanged), and I can't understand why

ALTER TABLE myTable ADD COLUMN statusLag INTEGER;    

INSERT INTO myTable (statusLag)
    SELECT 
        (SELECT t2.status
            FROM myTable t2
            WHERE t2.workerID = t1.workerID AND
                  t2.timeVar < t1.timeVar
            ORDER BY t2.timeVar desc
            LIMIT 1)
    FROM myTable t1;

我几乎通过创建一个新表来实现我想要的:

I almost achieve what I want by creating a new table:

CREATE TABLE myOtherTable
    (   'workerID'  REAL NOT NULL,
        'timeVar'   INTEGER NOT NULL,
        'statusLag' INTEGER,
        PRIMARY KEY('workerID', 'timeVar')
    )

INSERT INTO myOtherTable
SELECT t1.workerID, t1.timeVar, 
    (SELECT t2.status
        FROM myTable t2
        WHERE t2.workerID = t1.workerID AND
              t2.timeVar < t1.timeVar
        ORDER BY t2.timeVar desc
        LIMIT 1)
FROM myTable t1;

为了实现我想要的，我需要将 myOtherTable 与 myTable 结合起来.但这对我来说看起来效率很低.

To achieve what I want, I would need to join myOtherTable with myTable. But this looks quite inefficient to me.

所以我的问题是:为什么我的第一次尝试没有成功?以及如何以更有效的方式实现我想要的?

So my questions are: Why didn't my first attempt work? And How do I achieve what I want in a more efficient way?

推荐答案

它不起作用，因为您插入了带有信息的新行，而不是更新现有行.也许这可以满足您的要求:

It didn't work because you inserted new rows with the information rather than updating existing rows. Perhaps this does what you want:

UPDATE myTable
    SET statusLag = (SELECT t2.status
                     FROM myTable t2
                     WHERE t2.workerID = myTable.workerID AND
                           t2.timeVar < myTable.timeVar
                     ORDER BY t2.timeVar desc
                     LIMIT 1
                    );

这篇关于试图在 SQLite 中滞后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！