최근일자의 정보들 가져오기? 0 4 4,789

by 동동동 [SQL Query] Oracle 쿼리 [2022.08.09 12:05:03]


안녕하세요...표시해야 하는 항목이 고정일 경우 해당 정보들의 가장 최근 일자의 정보로 표시해야 하는데,

가져오는 방식에 대해 고민이 좀 되서 문의 드립니다.

 

/* 1번 방법 */
WITH TMP_A AS (
          SELECT '001' AS DVSN, '과일' AS DVSN_NM FROM DUAL
UNION ALL SELECT '002' AS DVSN, '야채' AS DVSN_NM FROM DUAL
)
, TMP_B AS (
          SELECT '001' AS DVSN, '001' AS COD, '수박' AS COD_NM, '20220710' AS IN_DY, 8000 AS PRIC FROM DUAL
UNION ALL SELECT '001' AS DVSN, '001' AS COD, '수박' AS COD_NM, '20220810' AS IN_DY, 5000 AS PRIC FROM DUAL
UNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220605' AS IN_DY, 1000 AS PRIC FROM DUAL
UNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220710' AS IN_DY, 1500 AS PRIC FROM DUAL
UNION ALL SELECT '002' AS DVSN, '003' AS COD, '배추' AS COD_NM, '20220510' AS IN_DY, 3000 AS PRIC FROM DUAL
UNION ALL SELECT '002' AS DVSN, '003' AS COD, '배추' AS COD_NM, '20220710' AS IN_DY, 4000 AS PRIC FROM DUAL
)
SELECT DVSN
     , MAX(DVSN_NM) DVSN_NM
     , MAX(COD_NM1) COD_NM1
     , MAX(IN_DY1) IN_DY1
     , MAX(PRIC1) PRIC1
     , MAX(COD_NM2) COD_NM2
     , MAX(IN_DY2) IN_DY2
     , MAX(PRIC2) PRIC2
 FROM (
SELECT A.DVSN
     , A.DVSN_NM
     , DECODE(COD, '001', IN_DY, '') IN_DY1
     , DECODE(COD, '001', COD, '') COD1
     , DECODE(COD, '001', COD_NM, '') COD_NM1     
     , DECODE(COD, '001', PRIC, '') PRIC1
     , DECODE(COD, '002', IN_DY, '') IN_DY2
     , DECODE(COD, '002', COD, '') COD2
     , DECODE(COD, '002', COD_NM, '') COD_NM2     
     , DECODE(COD, '002', PRIC, '') PRIC2
  FROM TMP_A A 
     , (SELECT * FROM (SELECT DVSN, COD, COD_NM, IN_DY, PRIC
             , ROW_NUMBER() OVER(PARTITION BY DVSN, COD ORDER BY DVSN, COD, IN_DY DESC) RN
          FROM TMP_B
         WHERE DVSN = '001'
           AND COD IN ('001', '002')
        )
        WHERE RN = 1 
     ) B
 WHERE A.DVSN = '001'
   AND B.DVSN = A.DVSN
   AND B.COD IN ('001', '002')
   )
 GROUP BY DVSN;
 
 
/* 2번 방법 */ 
WITH TMP_A AS (
          SELECT '001' AS DVSN, '과일' AS DVSN_NM FROM DUAL
UNION ALL SELECT '002' AS DVSN, '야채' AS DVSN_NM FROM DUAL
)
, TMP_B AS (
          SELECT '001' AS DVSN, '001' AS COD, '수박' AS COD_NM, '20220710' AS IN_DY, 8000 AS PRIC FROM DUAL
UNION ALL SELECT '001' AS DVSN, '001' AS COD, '수박' AS COD_NM, '20220810' AS IN_DY, 5000 AS PRIC FROM DUAL
UNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220605' AS IN_DY, 1000 AS PRIC FROM DUAL
UNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220710' AS IN_DY, 1500 AS PRIC FROM DUAL
UNION ALL SELECT '002' AS DVSN, '003' AS COD, '배추' AS COD_NM, '20220510' AS IN_DY, 3000 AS PRIC FROM DUAL
UNION ALL SELECT '002' AS DVSN, '003' AS COD, '배추' AS COD_NM, '20220710' AS IN_DY, 4000 AS PRIC FROM DUAL
)
SELECT DVSN
     , DVSN_NM
     , REGEXP_SUBSTR(CD1, '[^;]+', 1, 1) IN_DY1
     , REGEXP_SUBSTR(CD1, '[^;]+', 1, 2) COD1
     , REGEXP_SUBSTR(CD1, '[^;]+', 1, 3) COD_NM1
     , REGEXP_SUBSTR(CD1, '[^;]+', 1, 4) PRIC1
     , REGEXP_SUBSTR(CD2, '[^;]+', 1, 1) IN_DY2
     , REGEXP_SUBSTR(CD2, '[^;]+', 1, 2) COD2
     , REGEXP_SUBSTR(CD2, '[^;]+', 1, 3) COD_NM2
     , REGEXP_SUBSTR(CD2, '[^;]+', 1, 4) PRIC2   
  FROM (
SELECT A.DVSN
     , A.DVSN_NM
     , (SELECT MAX(IN_DY||';'||COD||';'||COD_NM||';'||PRIC)
          FROM TMP_B
         WHERE DVSN = A.DVSN
           AND COD = '001') CD1           
     , (SELECT MAX(IN_DY||';'||COD||';'||COD_NM||';'||PRIC)
          FROM TMP_B
         WHERE DVSN = A.DVSN
           AND COD = '002') CD2  
  FROM TMP_A A 
 WHERE A.DVSN = '001'
 );

 

다음과 같이 과일이라는 구분에 수박과 참외만 고정으로 최근정보를 칼럼으로 표시하는데요..

실제 TMP_B는 Row수가 상당히 많이 증가되고 있는 테이블입니다..

어떤 방식이 더 좋은건지 더 좋은 방식이 있는지 조언 부탁드립니다...감사합니다..꾸벅..

by 마농 [2022.08.09 14:11:43]
-- 1번 방식 좀 더 간결하게 : 불필요 인라인뷰 제거
SELECT a.dvsn
     , a.dvsn_nm
     , MIN(DECODE(b.cod, '001', b.cod_nm)) cod_nm1
     , MIN(DECODE(b.cod, '001', b.in_dy )) in_dy1
     , MIN(DECODE(b.cod, '001', b.pric  )) pric1
     , MIN(DECODE(b.cod, '002', b.cod_nm)) cod_nm2
     , MIN(DECODE(b.cod, '002', b.in_dy )) in_dy2
     , MIN(DECODE(b.cod, '002', b.pric  )) pric2
  FROM tmp_a a
     , (SELECT dvsn, cod, cod_nm, in_dy, pric
             , ROW_NUMBER() OVER(PARTITION BY dvsn, cod ORDER BY in_dy DESC) rn
          FROM tmp_b b
         WHERE dvsn = '001'
           AND cod IN ('001', '002')
        ) b
 WHERE a.dvsn = b.dvsn
   AND b.rn = 1
 GROUP BY a.dvsn, a.dvsn_nm
;

-- 2번 방식 비효율 개선 : 테이블 여러번 읽기 및 정규식 사용 제거
SELECT a.dvsn
     , a.dvsn_nm 
     , MAX(DECODE(b.cod, '001', b.cod_nm))            cod_nm1
     , MAX(DECODE(b.cod, '001', b.in_dy ))            in_dy1
     , SUBSTR(
       MAX(DECODE(b.cod, '001', b.in_dy||b.pric)), 9) pric1
     , MAX(DECODE(b.cod, '002', b.cod_nm))            cod_nm2
     , MAX(DECODE(b.cod, '002', b.in_dy ))            in_dy2
     , SUBSTR(
       MAX(DECODE(b.cod, '002', b.in_dy||b.pric)), 9) pric2
  FROM tmp_a a
     , tmp_b b
 WHERE a.dvsn = '001'
   AND a.dvsn = b.dvsn
   AND b.cod IN ('001', '002')
 GROUP BY a.dvsn, a.dvsn_nm
;

-- 3. MAX() KEEP() 이용 방법
SELECT a.dvsn
     , a.dvsn_nm
     , MAX(DECODE(b.cod, '001', b.cod_nm)) cod_nm1
     , MAX(DECODE(b.cod, '001', b.in_dy )) in_dy1
     , MAX(DECODE(b.cod, '001', b.pric  )) pric1
     , MAX(DECODE(b.cod, '002', b.cod_nm)) cod_nm2
     , MAX(DECODE(b.cod, '002', b.in_dy )) in_dy2
     , MAX(DECODE(b.cod, '002', b.pric  )) pric2
  FROM tmp_a a
     , (SELECT dvsn
             , cod
             , cod_nm
             , MAX(in_dy) in_dy
             , MAX(pric) KEEP(DENSE_RANK LAST ORDER BY in_dy) pric
          FROM tmp_b b
         WHERE dvsn = '001'
           AND cod IN ('001', '002')
         GROUP BY dvsn, cod, cod_nm
        ) b
 WHERE a.dvsn = b.dvsn
 GROUP BY a.dvsn, a.dvsn_nm
;

 


by 동동동 [2022.08.10 09:20:12]

마농님 설명 감사드립니다...

Select절에 가져오는 칼럼들이 많아서 최대한 Group By를 안쓰려고 2번 방식으로 하려고 했는데..다시 생각해 봐야 겠네요..

늘 최근정보를 표시하려고 할때마다 고민이 많이 되네요..ㅠㅠ

 

답변과 설명 감사드립니다..^^


by 마농 [2022.08.10 09:28:12]

최신정보 표시는
ROW_NUMBER 나 KEEP 이용하시면 됩니다.
일자를 맨앞에 붙여서 잘라쓰는 방안도 있습니다.(과거 분석함수 없던 시절 요긴한 방법)
항목이 적은 경우에는 KEEP 를 이용하는게 편리하고.
항목이 많은 경우에는 ROW_NUMBER 를 이용하는게 편리합니다.


by 동동동 [2022.08.10 11:46:25]

옙..답변 감사드립니다..^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입