无法使用LAG功能

Question

我需要更新一个表:

ID | START_DATE | response| FINAL_TREND
1    14-10-2021     4                       
1    15-10-2021     3                       
1    16-10-2021     2                       
1    17-10-2021     2                        
1    18-10-2021     3
1    19-10-2021     2

输出:

ID | START_DATE | response| FINAL_TREND
1    14-10-2021     4           NULL            
1    15-10-2021     3            4           
1    16-10-2021     2            3           
1    17-10-2021     2            2            
1    18-10-2021     3            2
1    19-10-2021     2            3    

所以，当运行代码时:

SELECT LAG(RESPONSE,1) OVER (ORDER BY START_DATE) AS NEW
FROM DUMMY_YC 

输出:

NULL
4
3
2
3
2
2

但是当在update AS中使用相同的代码时:

UPDATE DUMMY_YC A SET A.RESPONSE = (SELECT LAG(B.RESPONSE,1) OVER (ORDER BY B.START_DATE) AS NEW
FROM DUMMY_YC B WHERE B.START_DATE=A.START_DATE)

输出:

7 rows updated.

但是实际更新的值是

RESPONSE|
(null)
(null)
(null)
(null)
(null)
(null)
(null)

帮助将是感激的。

Answer 1

我选merge。

:

SQL>   SELECT *
2      FROM test
3  ORDER BY id, start_date;
ID START_DATE   RESPONSE FINAL_TREND
---------- ---------- ---------- -----------
1 14.10.2021          4           0
1 15.10.2021          3           0
1 16.10.2021          2           0
1 17.10.2021          2           0
1 18.10.2021          3           0
1 19.10.2021          2           0
6 rows selected.

合并:

SQL> MERGE INTO test a
2       USING (SELECT b.id,
3                     b.start_date,
4                     b.response,
5                     LAG (b.response, 1) OVER (ORDER BY b.start_date) AS final_trend
6                FROM test b) x
7          ON (    x.id = a.id
8              AND x.start_date = a.start_date)
9  WHEN MATCHED
10  THEN
11     UPDATE SET a.final_trend = x.final_trend;
6 rows merged.

:后

SQL>   SELECT *
2      FROM test
3  ORDER BY id, start_date;
ID START_DATE   RESPONSE FINAL_TREND
---------- ---------- ---------- -----------
1 14.10.2021          4
1 15.10.2021          3           4
1 16.10.2021          2           3
1 17.10.2021          2           2
1 18.10.2021          3           2
1 19.10.2021          2           3
6 rows selected.
SQL>