У меня есть таблица с именем клиента, которая выглядит так:
ID ALPHA BRAVO CHARLIE DATE
-------------------------------------------------
1 111 222 333 02/02/2019
2 333 444 555 11/11/2019
3 666 555 777 12/12/2019
4 777 888 999 05/05/2020
5 100 101 110 12/25/2020
и мне нужно получить следующий вывод:
ID ALPHA BRAVO CHARLIE DATE NEW_COL ROW_NUM
-----------------------------------------------------------------------
1 111 222 333 02/02/2019 333 4
2 333 444 555 11/11/2019 333 3
3 666 555 777 12/12/2019 333 2
4 777 888 999 05/05/2020 333 1
5 100 101 110 12/25/2020 010 1
Столбцы ALPHA, BRAVO и CHARLIE представляют идентификаторы клиентов. У данного клиента может быть несколько идентификаторов в системе. Записи 1-4 представляют идентификаторы, принадлежащие одному и тому же клиенту, скажем, Джону. Согласно таблице, у Джона 12 идентификаторов, и его последний идентификатор — 999. Запись 5 представляет другого клиента, скажем, Джейн. У Джейн три идентификатора, и ее последний идентификатор — 110.
Целью столбца ROW_NUM является получение последнего значения CUSTOMER.CHARLIE. Идея состоит в том, чтобы использовать первое значение CHARLIE в качестве раздела. По сути, цель состоит в том, чтобы получить отображение один родитель: много дочерних элементов. В этом случае ID 333 нужно привязать к 555, 777 и 999.
Вот DDL/DML:
CREATE TABLE CUSTOMER
(ID NUMBER(20) NOT NULL,
ALPHA NUMBER(20) NOT NULL,
BRAVO NUMBER(20) NOT NULL,
CHARLIE NUMBER(20) NOT NULL,
CREATEDDATE DATE
);
INSERT INTO CUSTOMER
VALUES
(1, 111, 222, 333, to_date('02-FEB-19','DD-MON-RR'));
INSERT INTO CUSTOMER
VALUES
(2, 333, 444, 555, to_date('11-NOV-19','DD-MON-RR'));
INSERT INTO CUSTOMER
VALUES
(3, 666, 555, 777, to_date('12-DEC-19','DD-MON-RR'));
INSERT INTO CUSTOMER
VALUES
(4, 777, 888, 999, to_date('05-MAY-20','DD-MON-RR'));
INSERT INTO CUSTOMER
VALUES
(5, 100, 101, 110, to_date('25-DEC-20','DD-MON-RR'));
COMMIT;
Я пробовал следующий запрос, но он не может правильно заполнить столбец раздела:
WITH
charlies
AS
(SELECT DISTINCT charlie
FROM customer),
mult_customers
AS
(SELECT c.*, c.charlie AS NEW_COL
FROM customer c
UNION
SELECT c.*,
CASE WHEN c.alpha = e.charlie THEN c.alpha ELSE c.bravo END AS NEW_COL
FROM customer c
JOIN charlies e ON e.charlie = c.alpha OR e.charlie = c.bravo),
ranked
AS
(SELECT mc.*,
ROW_NUMBER ()
OVER (PARTITION BY NEW_COL ORDER BY createddate DESC) AS row_num
FROM mult_customers mc)
SELECT *
FROM ranked
ORDER BY ID;
Спасибо за любую оказанную помощь.
