데이터베이스 질문과 답변
CKRE_ID 별, 년도별로 해서 상태값을 추출하고자 합니다.
추가적으로 동일한 CKRE_ID에서 가장 최근의 년도 , 년도별 가장 마지막의 상태값을 리스트화 하기위해서
아래와 같이 쿼리를 작성하였는데요..
원하던 결과값은 나오는데 뭔가 복잡해 보입니다.
같은 테이블을 3번이나 참조하는.. (약간 무식해보이는.. 정규화가 안된기분요)
해당 쿼리를 좀더 심플하고 차후에 데이터가 많이질때를 생각해서 튜닝하고자 하는데
더 좋은 방법이 있으시면 알려주세요...
Oracle 10g (10.2) 입니다.
WCHK_SULT 테이블에 대한 데이터 구조 및 쿼리 결과형태는 이미지로 첨부하였습니다.
작성한 쿼리
SELECT RA.CKRE_ID,
RA.CKRE_YY,
MAX(CASE WHEN CKRE_MM = '1' THEN RC.CDNM END) AS MM1,
MAX(CASE WHEN CKRE_MM = '2' THEN RC.CDNM END) AS MM2,
MAX(CASE WHEN CKRE_MM = '3' THEN RC.CDNM END) AS MM3,
MAX(CASE WHEN CKRE_MM = '4' THEN RC.CDNM END) AS MM4,
MAX(CASE WHEN CKRE_MM = '5' THEN RC.CDNM END) AS MM5,
MAX(CASE WHEN CKRE_MM = '6' THEN RC.CDNM END) AS MM6,
MAX(CASE WHEN CKRE_MM = '7' THEN RC.CDNM END) AS MM7,
MAX(CASE WHEN CKRE_MM = '8' THEN RC.CDNM END) AS MM8,
MAX(CASE WHEN CKRE_MM = '9' THEN RC.CDNM END) AS MM9,
MAX(CASE WHEN CKRE_MM = '10' THEN RC.CDNM END) AS MM10,
MAX(CASE WHEN CKRE_MM = '11' THEN RC.CDNM END) AS MM11,
MAX(CASE WHEN CKRE_MM = '12' THEN RC.CDNM END) AS MM12,
RM.CKRE_YY_MX ,
(
SELECT CKRE_STATE
FROM
(
SELECT RANK() OVER (PARTITION BY CKRE_ID, CKRE_YY ORDER BY CKRE_ID, CKRE_YY, CKRE_MM DESC) RANKS, A.CKRE_STATE, A.CKRE_ID, A.CKRE_YY
FROM WCHK_SULT A
) SS
WHERE SS.RANKS = 1
AND SS.CKRE_ID = RA.CKRE_ID
AND SS.CKRE_YY = RA.CKRE_YY
) CKRE_STATE_MX
FROM WCHK_SULT RA, (SELECT * FROM CODE_TT WHERE CM_CDTP = 'WSC') RC,
(SELECT CKRE_ID, MAX(CKRE_YY) AS CKRE_YY_MX FROM WCHK_SULT GROUP BY CKRE_ID) RM
WHERE RA.CKRE_ID = RM.CKRE_ID
AND RA.CKRE_STATE = RC.CM_CDVL(+)
GROUP BY RA.CKRE_ID, RA.CKRE_YY, RM.CKRE_YY_MX
ORDER BY RA.CKRE_ID, RA.CKRE_YY
SELECT ckre_id
, ckre_yy
, MAX ( CASE WHEN ckre_mm = '1' THEN b.cdnm END ) AS mm1
, MAX ( CASE WHEN ckre_mm = '2' THEN b.cdnm END ) AS mm2
, MAX ( CASE WHEN ckre_mm = '3' THEN b.cdnm END ) AS mm3
, MAX ( CASE WHEN ckre_mm = '4' THEN b.cdnm END ) AS mm4
, MAX ( CASE WHEN ckre_mm = '5' THEN b.cdnm END ) AS mm5
, MAX ( CASE WHEN ckre_mm = '6' THEN b.cdnm END ) AS mm6
, MAX ( CASE WHEN ckre_mm = '7' THEN b.cdnm END ) AS mm7
, MAX ( CASE WHEN ckre_mm = '8' THEN b.cdnm END ) AS mm8
, MAX ( CASE WHEN ckre_mm = '9' THEN b.cdnm END ) AS mm9
, MAX ( CASE WHEN ckre_mm = '10' THEN b.cdnm END ) AS mm10
, MAX ( CASE WHEN ckre_mm = '11' THEN b.cdnm END ) AS mm11
, MAX ( CASE WHEN ckre_mm = '12' THEN b.cdnm END ) AS mm12
, MAX ( ckre_yy ) OVER ( PARTITION BY ckre_id ) ckre_yy_max
, MAX ( ckre_state ) KEEP ( DENSE_RANK FIRST ORDER BY ckre_id, ckre_yy, ckre_mm DESC ) ckre_state_max
FROM t
, code_tt b
WHERE a.care_state = b.cm_cdvl (+)
AND b.cm_cdtp (+) = 'WSC'
GROUP BY ckre_id
, ckre_yy
ORDER BY ckre_id
, ckre_yy
-- ckre_mm 의 타입이 좋질 않네요... -- 숫자로 하던가 2자리 고정 문자로 하던가 해야 하는데 가변길이 문자네요??? SELECT ra.ckre_id , ra.ckre_yy , MIN(DECODE(ra.ckre_mm, '1', rc.cdnm)) AS mm1 , MIN(DECODE(ra.ckre_mm, '2', rc.cdnm)) AS mm2 , MIN(DECODE(ra.ckre_mm, '3', rc.cdnm)) AS mm3 , MIN(DECODE(ra.ckre_mm, '4', rc.cdnm)) AS mm4 , MIN(DECODE(ra.ckre_mm, '5', rc.cdnm)) AS mm5 , MIN(DECODE(ra.ckre_mm, '6', rc.cdnm)) AS mm6 , MIN(DECODE(ra.ckre_mm, '7', rc.cdnm)) AS mm7 , MIN(DECODE(ra.ckre_mm, '8', rc.cdnm)) AS mm8 , MIN(DECODE(ra.ckre_mm, '9', rc.cdnm)) AS mm9 , MIN(DECODE(ra.ckre_mm, '10', rc.cdnm)) AS mm10 , MIN(DECODE(ra.ckre_mm, '11', rc.cdnm)) AS mm11 , MIN(DECODE(ra.ckre_mm, '12', rc.cdnm)) AS mm12 , MAX(ra.ckre_yy) OVER(PARTITION BY ra.ckre_id) AS ckre_yy_mx , MAX(ra.ckre_state) KEEP(DENSE_RANK LAST ORDER BY TO_NUMBER(ra.ckre_mm)) AS ckre_state_mx FROM wchk_sult ra , code_tt rc WHERE ra.ckre_state = rc.cm_cdvl(+) AND rc.cm_cdtp(+) = 'WSC' GROUP BY ra.ckre_id, ra.ckre_yy ORDER BY ra.ckre_id, ra.ckre_yy ;
감사합니다!!
) 버튼을 클릭하여 작성 하시면 됩니다.