我有一个名为“customer”的表，如下所示：

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列表示客户id。给定的客户在系统中可以有多个ID。记录1-4表示属于同一个客户的id，比如john。根据表格，约翰有12个身份证，他的最新身份证是999。记录5代表另一个顾客，比如说简。简有三个身份证，最后一个身份证是110。
row_num列的目的是获取最后一个customer.charlie值。其思想是使用第一个charlie值作为分区。基本上，目标是得到一个parent:many children Map。在这种情况下，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;

谢谢你的帮助。