[DB] ROWNUM 사용 시 주의점/활용법 | 민민의 하드디스크 - 티스토리

1. 테이블 구조 및 로직

마스터테이블 : 디테일테이블  => 1:N 구조

마스터 테이블 구조

컬럼명	설명
SN	순번(PK), 사용자 식별용
REQ_DT	신청 날짜
FG_CD	구분 코드 ('01' - 신청, '02' - 변경, '03' - 삭제)
CREATE_DT	데이터 생성 일자

디테일 테이블 구조

컬럼명	설명
SN	순번(PK), 사용자 식별용
START_DT	시작 날짜 (신청, 변경, 삭제의 시작일)
END_DT	종료 날짜 (신청, 변경, 삭제의 종료일)
FG_CD	구분 코드 ('01' - 신청, '02' - 변경, '03' - 삭제)
CREATE_DT	데이터 생성 일자

1.  마스터테이블에 신청한 사용자A에 대한 데이터가 쌓임 (KEY 값 1개)
2.  디테일테이블에서 사용자A가 신청/변경/삭제 등 비즈니스로직 일어나면 계속 ROW 쌓임 (1:N)

 

원하는 결과

SN	REQ_DT	신청시작	신청종료	변경시작	변경종료	삭제시작	삭제종료	CREATE_DT
1001	2024-03-07	2024-03-08	2024-03-15	2024-03-16	2024-03-20	2024-03-21	2024-03-22	2024-03-07

문제, 디테일테이블에 START_DT / END_DT 일자는 두개의 컬럼 밖에 없는데  위 결과처럼 신청/변경/삭제에 대한 시작일자, 종료일자 6개의 컬럼을 조회해줘야함.

조건1. 데이터 생성일자 최신

조건2. 한 개의 ROW로 사용자의 디테일 정보 모두 출력해야 됨.

 

2. 조인 및 GROUP BY로 짰을 때 

SELECT 
    m.SN, 
    m.REQ_DT, 
    MAX(CASE WHEN d.FG_CD = '01' THEN d.START_DT END) AS 신청시작,
    MAX(CASE WHEN d.FG_CD = '01' THEN d.END_DT END) AS 신청종료,
    MAX(CASE WHEN d.FG_CD = '02' THEN d.START_DT END) AS 변경시작,
    MAX(CASE WHEN d.FG_CD = '02' THEN d.END_DT END) AS 변경종료,
    MAX(CASE WHEN d.FG_CD = '03' THEN d.START_DT END) AS 삭제시작,
    MAX(CASE WHEN d.FG_CD = '03' THEN d.END_DT END) AS 삭제종료,
    m.CREATE_DT
FROM 마스터테이블 m
JOIN 디테일테이블 d 
    ON m.SN = d.SN 
    AND m.FG_CD = d.FG_CD
WHERE m.SN = '1001'
GROUP BY m.SN, m.REQ_DT, m.CREATE_DT

만약 SN (KEY값 '1001') 이런식으로 구현할 수 있는데, 위 쿼리는 마스터와 디테일 컬럼이 많아질수록 GROUP BY 컬럼이 많아지고 너무 지저분해졌다.

그래서 인라인뷰로 필요한 데이터 가져오게끔 ALIAS 주고 조인하는 과정에서 ROWNUM을 사용하게 됐는데,,,

문제가 발생함.

 

3. 잘못 짠 쿼리

SELECT 
    m.SN,
    m.REQ_DT,
    m.CREATE_DT,
    A1.START_DT AS 신청시작,
    A1.END_DT AS 신청종료,
    A2.START_DT AS 변경시작,
    A2.END_DT AS 변경종료,
    A3.START_DT AS 삭제시작,
    A3.END_DT AS 삭제종료
FROM 
    마스터테이블 m
LEFT JOIN 
    (SELECT SN, START_DT, END_DT
     FROM 디테일테이블 
     WHERE FG_CD = '01' AND ROWNUM = 1) A1  -- 신청 정보
    ON m.SN = A1.SN
LEFT JOIN 
    (SELECT SN, START_DT, END_DT
     FROM 디테일테이블 
     WHERE FG_CD = '02' AND ROWNUM = 1) A2  -- 변경 정보
    ON m.SN = A2.SN
LEFT JOIN 
    (SELECT SN, START_DT, END_DT
     FROM 디테일테이블 
     WHERE FG_CD = '03' AND ROWNUM = 1) A3  -- 삭제 정보
    ON m.SN = A3.SN
WHERE 
    m.SN = '1001';

 

SN	REQ_DT	CREATE_DT	신청시작	신청종료	변경시작	변경종료	삭제시작	삭제종료
1001	2024-03-07	2024-03-07	2024-03-08	2024-03-15	2024-03-16	2024-03-20	2024-03-08	2024-03-15

 

• 위의 쿼리에서 ROWNUM = 1을 사용한 방식은 각 구분 코드에 대해 한 개의 레코드만 가져오는데, ROWNUM = 1은 쿼리 실행 시 최초의 결과 하나만 선택되기 때문에, 각 구분 코드(신청/변경/삭제)에 대해 첫 번째 행만을 가져오게 됨.
• 디테일테이블의 여러 행 중에서 신청, 변경, 삭제에 해당하는 값이 여러 개가 있을 경우 첫 번째 행만 출력되므로 올바른 결과를 얻을 수 없음.

 

위와 같이 잘못된 결과가 출력된다. ROWNUM = 1을 사용하면 첫 번째 신청 정보와 첫 번째 변경 정보만 출력되고, 삭제 정보도 첫 번째 행만 나오게 돼서 실제로는 각 구분별로 모두 값을 출력해야 하므로, 위와 같은 결과는 정확하지 않았다.

 

4. 해결 방법

각 구분 코드에 대해 정확한 날짜 정보를 가져오기 위해서는 서브쿼리 안에서 먼저 ORDER BY로 정렬한 후 ROWNUM을 사용해야 하며, LEFT JOIN을 통해 각 구분 코드에 해당하는 정보를 정확하게 가져와야 한다.

수정쿼리

SELECT 
    m.SN,
    m.REQ_DT,
    m.CREATE_DT,
    A1.START_DT AS 신청시작,
    A1.END_DT AS 신청종료,
    A2.START_DT AS 변경시작,
    A2.END_DT AS 변경종료,
    A3.START_DT AS 삭제시작,
    A3.END_DT AS 삭제종료
FROM 
    마스터테이블 m
LEFT JOIN 
    (SELECT SN, START_DT, END_DT
     FROM (SELECT SN, START_DT, END_DT
           FROM 디테일테이블 
           WHERE FG_CD = '01'
           ORDER BY CREATE_DT DESC) 
     WHERE ROWNUM = 1) A1  -- 신청 정보
    ON m.SN = A1.SN
LEFT JOIN 
    (SELECT SN, START_DT, END_DT
     FROM (SELECT SN, START_DT, END_DT
           FROM 디테일테이블 
           WHERE FG_CD = '02'
           ORDER BY CREATE_DT DESC) 
     WHERE ROWNUM = 1) A2  -- 변경 정보
    ON m.SN = A2.SN
LEFT JOIN 
    (SELECT SN, START_DT, END_DT
     FROM (SELECT SN, START_DT, END_DT
           FROM 디테일테이블 
           WHERE FG_CD = '03'
           ORDER BY CREATE_DT DESC) 
     WHERE ROWNUM = 1) A3  -- 삭제 정보
    ON m.SN = A3.SN
WHERE 
    m.SN = '1001';

위처럼 각 구분 코드에 대해 정확한 날짜 정보를 가져오기 위해서는 서브쿼리 안에서 먼저 ORDER BY로 정렬한 후 ROWNUM을 사용해야 하며, LEFT JOIN을 통해 각 구분 코드에 해당하는 정보를 정확하게 가져와야 함.

이렇게 하면 원하는 결과처럼 CREATE_DT(데이터 생성일자) 최근 값에 대한 사용자의 데이터를 하나의 ROW로 출력할 수 있다.

SN	REQ_DT	신청시작	신청종료	변경시작	변경종료	삭제시작	삭제종료	CREATE_DT
1001	2024-03-07	2024-03-08	2024-03-15	2024-03-16	2024-03-20	2024-03-21	2024-03-22	2024-03-07

 

 

 

간단한 ROWNUM 예시

잘못된 쿼리

SELECT SN, START_DT, END_DT
FROM 디테일테이블
WHERE SN = '1001'
AND ROWNUM = 1;

해결 쿼리

SELECT SN, START_DT, END_DT
FROM (
    SELECT SN, START_DT, END_DT
    FROM 디테일테이블
    WHERE SN = '1001'
    ORDER BY CREATE_DT DESC
)
WHERE ROWNUM = 1;

결국 먼저 특정 조건으로 정렬을 해주고 사용해야 된다.

 

 

내용은 ROWNUM에 대한 내용이라,,, 쿼리 최적화나 속도면에서 좋은 쿼리인지는 모른다.... 다른 좋은 방법있으면 올려주세요!