AA 테이블이 원본 데이터 테이블로 렌덤하게 NULL이 발생 하도록 했습니다.
NULL을 만나면 다음 로우부터 채번을 다시 하도록 해 보았습니다.
뭐 해결 하셨다고 하니 필요없겠지만...
재미삼아 만들어 봤습니다..
WITH AA AS( SELECT A.NULL_CT , DECODE(B.NULL_CT,NULL,A.NULL_CT,NULL) CT FROM ( SELECT ROWNUM NULL_CT FROM DUAL CONNECT BY ROWNUM <= 202 ) A ,( SELECT round(dbms_random.value(1, 202)) NULL_CT FROM DUAL CONNECT BY ROWNUM <= 10 ) B WHERE A.NULL_CT = B.NULL_CT(+) ORDER BY A.NULL_CT ) ,BB AS( SELECT ROWNUM RN , NVL(LAG(NULL_CT) OVER(ORDER BY ROWNUM),1) F_CNT , NULL_CT E_CNT FROM AA WHERE CT IS NULL ) SELECT CT , DECODE(CT,NULL,NULL,RANK() OVER(PARTITION BY GP ORDER BY CT)) NEW_CT FROM ( SELECT DISTINCT AA.CT , CASE WHEN AA.NULL_CT > (SELECT MAX(E_CNT) FROM BB) THEN 9999999 WHEN AA.NULL_CT >= BB.F_CNT AND AA.NULL_CT <= BB.E_CNT THEN BB.RN END AS GP FROM AA , BB ) A1 WHERE A1.GP IS NOT NULL ;
WITH AA AS( SELECT A.NULL_CT , DECODE(B.NULL_CT,NULL,A.NULL_CT,NULL) CT FROM ( SELECT ROWNUM NULL_CT FROM DUAL CONNECT BY ROWNUM <= 20 ) A ,( SELECT round(dbms_random.value(1, 20)) NULL_CT FROM DUAL CONNECT BY ROWNUM <= 2 ) B WHERE A.NULL_CT = B.NULL_CT(+) ORDER BY A.NULL_CT ) SELECT CT , NVL2(CT, ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY NULL_CT), NULL) rn FROM ( SELECT NULL_CT , CT , NVL2(CT, SUM(DECODE(CT,NULL,1,0)) OVER(ORDER BY NULL_CT), NULL) GRP FROM AA ) ORDER BY NULL_CT ;