Заменять значения для каждого идентификатора и каждого месяца в IBM DB2 SQL?

#sql #db2

Вопрос:

У меня в IBM DB2 около нескольких миллионов записей в таблице с 4 столбцами: yeas, month, personal_number (2 миллиона), 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 
;

Вот начальная таблица:

ГОД	МЕСЯЦ	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

Вот результат (это не очень хороший результат):

ГОД	МЕСЯЦ	PERSONAL_NUMBER	PERSONAL_ID
2020	4	AA	1
2020	4	AA	1
2020	4	AA	1
2020	4	AA	1
2020	4	BB	3
2020	4	BB	3
2020	4	BB	3
2020	4	BB	3

Вот то, что мне нужно:

ГОД	МЕСЯЦ	PERSONAL_NUMBER	PERSONAL_ID
2020	1	AA	1
2020	2	AA	1
2020	3	AA	1
2020	4	AA	1
2020	1	BB	3
2020	2	BB	3
2020	3	BB	3
2020	4	BB	3

Большое вам спасибо

Ответ №1:

Попробуйте это:

 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              |

1. Спасибо @Mark Barinstein. Это решение! Еще раз спасибо. Я просто переставляю часть: РАЗДЕЛ ПО ПОРЯДКУ PERSONAL_NUMBER ПО ГОДУ, МЕСЯЦУ DESC) PERSONAL_ID_NEW ИЗ T, так как у меня тоже был бы 2021 год в один момент

2. Я пытался проголосовать за 1, но, думаю, у меня нет на это прав? Srry, я здесь новичок, если я могу как-то проголосовать за этот ответ, пожалуйста, напишите мне.

3. Если ваша цель — получить PERSONAL_ID данные за последний месяц, затем используйте DESC упорядочение для обоих ORDER BY столбцов: PARTITION BY PERSONAL_NUMBER ORDER BY YEAR DESC, MONTH DESC) .

4. Спасибо, Марк. Вот и все.

Ответ №2:

Я не уверен, правильно ли я понял, что вы хотели, но попробуйте это:

 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

1. Спасибо @Turophile, но это не нормально, поскольку в вашем решении есть несколько PERSONAL_ID, и мне нужен только один из прошлого месяца, чтобы заполнить все предыдущие месяцы.