mysql - 在更新期间模拟 MySQL 中的 LAG

Question

昨天 interesting question有人询问哪个需要使用 LAG 更新 MySQL 表。考虑以下输入表(左)和所需的输出(右):

**INPUT**                                 **OUTPUT**
ID  TestDate    PerformanceStatus (PS)    ID  TestDate    PS  PreviousPerformanceStatus
1   15/03/2016  0                         1   15/03/2016  0   0
1   01/04/2016  2                         1   01/04/2016  2   0
1   05/05/2016  1                         1   05/05/2016  1   2
1   07/06/2016  1                         1   07/06/2016  1   1
2   15/03/2016  0                         2   15/03/2016  0   1
2   01/04/2016  2                         2   01/04/2016  2   0
2   05/05/2016  1                         2   05/05/2016  1   2
2   07/06/2016  3                         2   07/06/2016  3   1
2   23/08/2016  1                         2   23/08/2016  1   3

换句话说，目标是将之前记录中存在的值分配给 PreviousPerformanceStatus，按 ID 排序，然后按 TestDate 排序>.

@spencer7593 给出的已接受答案使用了相关子查询。然而，我首先想到的是使用用户变量。我是这样回答的:

SET @lag = 0;
UPDATE yourTable
SET PreviousPerformanceStatus = @lag,
    @lag:=PerformanceStatus
ORDER BY ID, TestDate

有人告诉我这个答案不稳定，但我想知道是否有人可以解释为什么会出现问题，在这种情况下会发生什么，最后我们可以做什么来使用此处的用户变量用于模拟 LAG。

据我了解，以下 SELECT 查询根本不会有问题:

SELECT PerformanceStatus,
       @lag AS PreviousPerformanceStatus,
       @lag:=PerformanceStatus
FROM yourTable
ORDER BY ID, TestDate

但是，在执行更新时，还需要考虑其他注意事项。

Answer 1

我认为您不能在更新语句中设置变量。这是我的推理-鉴于此

drop table if exists t;

create table t (ID int, TestDate date,   PerformanceStatus int, previousperformancestatus int);
insert into t values
(1 ,  '2016-03-15' , 0, null),                         
(1 ,  '2016-04-01' , 2, null),                         
(1 ,  '2016-05-05' , 1, null),                         
(1 ,  '2016-06-07' , 1, null),                         
(2 ,  '2016-03-15' , 0, null),                         
(2 ,  '2016-04-01' , 2, null),                         
(2 ,  '2016-05-05' , 1, null),                         
(2 ,  '2016-06-07' , 3, null),                         
(2 ,  '2016-08-23' , 1, null)
;

此代码失败

MariaDB [sandbox]> SET @lag = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [sandbox]> UPDATE T
    -> SET previousPerformanceStatus = @lag ,
    ->     @lag:=PerformanceStatus
    -> ORDER BY ID, TestDate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@lag:=PerformanceStatus
ORDER BY ID, TestDate' at line 3

注释掉@lag:=PerformanceStatus这段代码运行

MariaDB [sandbox]> SET @lag = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [sandbox]> UPDATE T
    -> SET previousPerformanceStatus = @lag
    -> #,@lag:=PerformanceStatus
    -> ORDER BY ID, TestDate;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 9  Changed: 0  Warnings: 0

因为代码至少运行没有错误并且手册 https://dev.mysql.com/doc/refman/5.7/en/update.html声明“SET 子句指示要修改哪些列”我对此的看法是，您无法在更新语句中设置变量，因此无法使用此方法模拟滞后。