동적 순위 부여

동적으로 순위를 부여하는 쿼리를 작성해보자. 선행 결과에 따라 후행 결과가 변경되기 때문에 쿼리로 작성하기 어려운 요건이다.

 

테스트를 위해 아래와 같이 테이블을 생성하자. 

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    bf VARCHAR2(1) -- 갱신전
  , af VARCHAR2(1) -- 갱신후
  , r1 NUMBER      -- 1순위
  , r2 NUMBER      -- 2순위
  , r3 NUMBER      -- 3순위
);

INSERT INTO t1 VALUES ('A', 'V', 0, 1, 1); -- A에서 V로 갱신된 건은 2순위와 3순위에 해당
INSERT INTO t1 VALUES ('A', 'W', 0, 1, 0);
INSERT INTO t1 VALUES ('A', 'X', 1, 1, 0);
INSERT INTO t1 VALUES ('B', 'W', 1, 1, 0);
INSERT INTO t1 VALUES ('B', 'X', 0, 0, 1);
INSERT INTO t1 VALUES ('C', 'X', 0, 1, 1);
INSERT INTO t1 VALUES ('C', 'Y', 0, 1, 0);
INSERT INTO t1 VALUES ('D', 'Y', 0, 0, 1);
INSERT INTO t1 VALUES ('D', 'Z', 0, 0, 1);
INSERT INTO t1 VALUES ('E', 'Z', 0, 1, 1);
INSERT INTO t1 VALUES ('E', 'V', 0, 1, 0);
COMMIT;

 

아래는 t1 테이블의 r# 열을 행으로 변환하고 rk, bf, af 순으로 정렬한 결과다. 순위를 부여하는 순서는 정렬 순서와 동일하며, 순위가 부여된 bf와 af는 다시 순위가 부여되지 않는다.

-- 2
SELECT b.lv AS rk, a.bf, a.af, ROW_NUMBER () OVER (ORDER BY b.lv, a.bf, a.af) AS rn
  FROM t1 a
     , (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 3) b
 WHERE DECODE (b.lv, 1, a.r1, 2, a.r2, 3, a.r3) = 1;

RK BF AF RN
-- -- -- --
 1 A  X   1 -- Y
 1 B  W   2 -- Y
 2 A  V   3 -- N (RN=1, A)
 2 A  W   4 -- N (RN=1, A)
 2 A  X   5 -- N (RN=1, A)
 2 B  W   6 -- N (RN=2, B)
 2 C  X   7 -- N (RN=1, X)
 2 C  Y   8 -- Y
 2 E  V   9 -- Y
 2 E  Z  10 -- N (RN=9, E)
 3 A  V  11 -- N (RN=1, A)
 3 B  X  12 -- N (RN=1, X)
 3 C  X  13 -- N (RN=1, X)
 3 D  Y  14 -- N (RN=8, Y)
 3 D  Z  15 -- Y
 3 E  Z  16 -- N (RN=9, E)

13 행이 선택되었습니다.

 

아래는 최종 결과다. bf와 af가 중복되지 않고 순차적으로 순위가 부여되어야 한다.

RK BF AF
-- -- --
 1 A  X
 1 B  W
 2 C  Y
 2 E  V
 3 D  Z

 

아래 쿼리는 Recursive Subquery Factoring을 사용했다. 행수가 많을수록 쿼리의 성능이 저하된다.

-- 3
WITH w1 AS (
SELECT b.lv AS rk, a.bf, a.af, ROW_NUMBER () OVER (ORDER BY b.lv, a.bf, a.af) AS rn
  FROM t1 a
     , (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 3) b
 WHERE DECODE (b.lv, 1, a.r1, 2, a.r2, 3, a.r3) = 1)
   , w2 (rk, bf, af, rn, ph) AS (
SELECT a.rk, a.bf, a.af, a.rn, NULL AS ph
  FROM w1 a
 WHERE a.rn = 1
UNION ALL
SELECT b.rk, b.bf, b.af, b.rn
     , CAST (CASE
                 WHEN (    NVL (INSTR (a.ph, a.bf || ','), 0) = 0
                       AND NVL (INSTR (a.ph, a.af || ','), 0) = 0)
                 THEN a.ph || a.bf || ',' || a.af || ','
                 ELSE a.ph
             END AS VARCHAR2 (4000)) AS ph
  FROM w2 a
     , w1 b
 WHERE a.rn = b.rn - 1)
SELECT a.*
  FROM (SELECT a.*
             , CASE
                   WHEN (    NVL (INSTR (a.ph, a.bf || ','), 0) = 0
                         AND NVL (INSTR (a.ph, a.af || ','), 0) = 0)
                   THEN 'Y'
                   ELSE 'N'
               END AS rs
          FROM w2 a) a
 WHERE a.rs = 'Y';

RK BF AF RN PH               RS
-- -- -- -- ---------------- --
 1 A  X   1                  Y
 1 B  W   3 A,X,             Y
 2 C  Y  15 A,X,B,W,         Y
 2 E  V  17 A,X,B,W,C,Y,     Y
 3 D  Z  29 A,X,B,W,C,Y,E,V, Y

5 행이 선택되었습니다.

 

아래는 동일한 논리를 PL/SQL로 작성한 패키지다. 쿼리보다 단순하고 이해하기 쉽다. 행수가 많더라도 성능 저하가 크지 않다.

-- 4
CREATE OR REPLACE PACKAGE pkg1
IS
    TYPE trc_ph IS TABLE OF NUMBER INDEX BY VARCHAR2(1);
    TYPE trc_t1 IS RECORD (rk NUMBER, bf VARCHAR2(1), af VARCHAR2(1));
    TYPE tnt_t1 IS TABLE OF trc_t1;

    FUNCTION fnc1 RETURN tnt_t1 PIPELINED;
END pkg1;
/

CREATE OR REPLACE PACKAGE BODY pkg1
IS
    FUNCTION fnc1
        RETURN tnt_t1 PIPELINED
    IS
        l_t1_rc    trc_t1;
        l_ph_rc    trc_ph;
        l_bf       VARCHAR2(1);
        l_af       VARCHAR2(1);
    BEGIN
        FOR c1 IN (SELECT   b.lv AS rk
                          , a.bf
                          , a.af
                          , ROW_NUMBER () OVER (ORDER BY b.lv, a.bf, a.af) AS rn
                       FROM t1 a
                          , (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 3) b
                      WHERE DECODE (b.lv, 1, a.r1, 2, a.r2, 3, a.r3) = 1
                   ORDER BY 1, 2, 3)
        LOOP
            BEGIN
                l_bf := l_ph_rc (c1.bf);
            EXCEPTION
                WHEN OTHERS THEN l_bf := 0;
            END;

            BEGIN
                l_af := l_ph_rc (c1.af);
            EXCEPTION
                WHEN OTHERS THEN l_af := 0;
            END;

            IF (l_bf = 0 AND l_af = 0)
            THEN
                l_ph_rc (c1.bf) := 1;
                l_ph_rc (c1.af) := 1;
                l_t1_rc.rk := c1.rk;
                l_t1_rc.bf := c1.bf;
                l_t1_rc.af := c1.af;
                PIPE ROW (l_t1_rc);
            END IF;
        END LOOP;
    END fnc1;
END pkg1;
/

 

아래는 패키지를 사용한 결과다.

-- 5
SELECT * FROM TABLE (pkg1.fnc1);

RK BF AF
-- -- --
 1 A  X
 1 B  W
 2 C  Y
 2 E  V
 3 D  Z

5 행이 선택되었습니다.