[Oracle] ROW_NUMBER를 이용한 RANKING처리

-- 여러 가구(APT_ROOM)에서 거래하는 은행(BANK_CD)들 중에서 가장 많이 거래하는 상위 4개의 은행을 순서(좌에서 우로)대로 나열하고 
-- 해당은행의 계좌를 출력해라.
WITH T_DUMMY AS
(
 SELECT '0901' AS APT_NO, '0101' AS APT_ROOM, '01' AS BANK_CD, '12345' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0101' AS APT_ROOM, '02' AS BANK_CD, '83636' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0102' AS APT_ROOM, '02' AS BANK_CD, '82228' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0103' AS APT_ROOM, '02' AS BANK_CD, '82727' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0103' AS APT_ROOM, '03' AS BANK_CD, '38338' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0103' AS APT_ROOM, '04' AS BANK_CD, '44758' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0104' AS APT_ROOM, '01' AS BANK_CD, '13338' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0104' AS APT_ROOM, '02' AS BANK_CD, '89988' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0104' AS APT_ROOM, '03' AS BANK_CD, '32113' AS ACNT_NO FROM DUAL UNION ALL
 SELECT '0901' AS APT_NO, '0104' AS APT_ROOM, '05' AS BANK_CD, '55879' AS ACNT_NO FROM DUAL
)
SELECT A.APT_NO, A.APT_ROOM
     , MIN(DECODE(Z.BANK_CD1, A.BANK_CD, A.ACNT_NO, NULL)) AS ACNT_NO1
     , MIN(DECODE(Z.BANK_CD2, A.BANK_CD, A.ACNT_NO, NULL)) AS ACNT_NO2
     , MIN(DECODE(Z.BANK_CD3, A.BANK_CD, A.ACNT_NO, NULL)) AS ACNT_NO3
     , MIN(DECODE(Z.BANK_CD4, A.BANK_CD, A.ACNT_NO, NULL)) AS ACNT_NO4
     , MIN(Z.BANK_CD1) AS BANK_CD1
     , MIN(Z.BANK_CD2) AS BANK_CD2
     , MIN(Z.BANK_CD3) AS BANK_CD3
     , MIN(Z.BANK_CD4) AS BANK_CD4
  FROM (
        SELECT MIN(DECODE(RNUM, 1, BANK_CD, NULL)) AS BANK_CD1
             , MIN(DECODE(RNUM, 2, BANK_CD, NULL)) AS BANK_CD2
             , MIN(DECODE(RNUM, 3, BANK_CD, NULL)) AS BANK_CD3
             , MIN(DECODE(RNUM, 4, BANK_CD, NULL)) AS BANK_CD4
          FROM (
                SELECT BANK_CD, COUNT(*) AS CNT
                     , ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC, BANK_CD) AS RNUM
                  FROM T_DUMMY
                GROUP BY BANK_CD
               )
         WHERE RNUM <= 4
       ) Z
     , T_DUMMY A
 WHERE 1 = 1
GROUP BY A.APT_NO, A.APT_ROOM
ORDER BY 2, 3