데이터베이스 질문과 답변
안녕하세요...표시해야 하는 항목이 고정일 경우 해당 정보들의 가장 최근 일자의 정보로 표시해야 하는데,
가져오는 방식에 대해 고민이 좀 되서 문의 드립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | /* 1번 방법 */WITH TMP_A AS ( SELECT '001' AS DVSN, '과일' AS DVSN_NM FROM DUALUNION 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 DUALUNION ALL SELECT '001' AS DVSN, '001' AS COD, '수박' AS COD_NM, '20220810' AS IN_DY, 5000 AS PRIC FROM DUALUNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220605' AS IN_DY, 1000 AS PRIC FROM DUALUNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220710' AS IN_DY, 1500 AS PRIC FROM DUALUNION ALL SELECT '002' AS DVSN, '003' AS COD, '배추' AS COD_NM, '20220510' AS IN_DY, 3000 AS PRIC FROM DUALUNION 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 DUALUNION 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 DUALUNION ALL SELECT '001' AS DVSN, '001' AS COD, '수박' AS COD_NM, '20220810' AS IN_DY, 5000 AS PRIC FROM DUALUNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220605' AS IN_DY, 1000 AS PRIC FROM DUALUNION ALL SELECT '001' AS DVSN, '002' AS COD, '참외' AS COD_NM, '20220710' AS IN_DY, 1500 AS PRIC FROM DUALUNION ALL SELECT '002' AS DVSN, '003' AS COD, '배추' AS COD_NM, '20220510' AS IN_DY, 3000 AS PRIC FROM DUALUNION 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | -- 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(
) 버튼을 클릭하여 작성 하시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.