在IBM DB2 SQL中替换每个ID和每个月的值?

Question

我在IBM DB2中有大约数百万条记录的表，其中有4列:yearas、month、personal_number(200万)、personal_id。关键是我想从这个表中获得相同的表，但是我想填充上个月(在所有以前的月份)的personal_id列值。关键是得到1-1:1个personal_id对应1个personal_number。

这是我的代码，但错误是，现在我有4个月(我需要1,2,3,4个月)

With CTE As
(
SELECT  
a.YEAR,
a.MONTH, 
a.PERSONAL_NUMBER, 
a.PERSONAL_ID, 

Row_Number() Over (Partition By a.YEAR, a.PERSONAL_NUMBER
Order By a.MONTH DESC) Rn
FROM
DWH.PA A
)
SELECT 
D.YEAR, D.MONTH, D.PERSONAL_NUMBER, D.PERSONAL_ID
FROM CTE D
RIGHT JOIN
(
SELECT
YEAR,
MONTH,
PERSONAL_NUMBER
FROM
DWH.PA
) B
ON (D.PERSONAL_NUMBER = B.PERSONAL_NUMBER)
WHERE D.Rn = 1 
;

起始表:

月tbody> <<tr>

年	PERSONAL_NUMBER	PERSONAL_ID
2020	1	AA	8
2020	2	AA	5
2020	3	AA	5
2020	4	AA	1
2020	1	BB	2
2020	2	BB	2
2020	3	BB	3
2020	4	BB	3

试试这个:

WITH T (YEAR, MONTH, PERSONAL_NUMBER, PERSONAL_ID) AS
(
VALUES
(2020, 1, 'AA', 8)
, (2020, 2, 'AA', 5)
, (2020, 3, 'AA', 5)
, (2020, 4, 'AA', 1)
, (2020, 1, 'BB', 2)
, (2020, 2, 'BB', 2)
, (2020, 3, 'BB', 3)
, (2020, 4, 'BB', 3)
)
SELECT 
T.*
, FIRST_VALUE(PERSONAL_ID) OVER 
(PARTITION BY YEAR, PERSONAL_NUMBER ORDER BY MONTH DESC) PERSONAL_ID_NEW
FROM T;

结果是:

|YEAR|MONTH|PERSONAL_NUMBER|PERSONAL_ID|PERSONAL_ID_NEW|
|----|-----|---------------|-----------|---------------|
|2020|4    |AA             |1          |1              |
|2020|3    |AA             |5          |1              |
|2020|2    |AA             |5          |1              |
|2020|1    |AA             |8          |1              |
|2020|4    |BB             |3          |3              |
|2020|3    |BB             |3          |3              |
|2020|2    |BB             |2          |3              |
|2020|1    |BB             |2          |3              |

我不确定我是否理解你想要的正确，但试试这个:

SELECT
YEAR,
MONTH,
PERSONAL_NUMBER,
(
SELECT PERSONAL_ID
FROM DWH.PA AS B 
WHERE A.YEAR = B.YEAR
AND A.PERSONAL_NUMBER = B.PERSONAL_NUMBER
ORDER BY MONTH DESC
FETCH FIRST 1 ROW ONLY
) AS PERSONAL_ID
FROM DWH.PA AS A