데이터베이스 질문과 답변
안녕하세요...표시해야 하는 항목이 고정일 경우 해당 정보들의 가장 최근 일자의 정보로 표시해야 하는데,
가져오는 방식에 대해 고민이 좀 되서 문의 드립니다.
/* 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]
옙..답변 감사드립니다..^^