데이터베이스 질문과 답변
안녕하세요~
스칼라서브쿼리로 누적값을 뽑아서 통계자료를 보고 있는 쿼리입니다.
그냥 ANSI쿼리 방식으로 조인하여 데이터를 추출하려고 했지만 잘 안되어 이렇게 문의합니다.
SELECT Y_NAME
, PART_NO
, NVL ( (
SELECT SUM (CNT)
FROM (
SELECT DECODE(D.PART_NO, '', T.PART_NO, D.PART_NO) PART_NO
, COUNT(*) CNT
FROM ITMS.TRBL_TRN_N T
, ITMS.TRBL_DEAL_TRN_N D
WHERE T.DEVICE = '011'
AND T.TRBL_RAMS = 'Y'
AND T.TRBL_RAS_DATE BETWEEN '20190101' AND '20190331'
AND T.TRBL_SEQ = D.TRBL_SEQ (+)
GROUP BY DECODE(D.PART_NO, '', T.PART_NO, D.PART_NO)
) B1
, ITMS.STD_LBS_MST B2
WHERE B2.PART_NO = B1.PART_NO (+)
AND B1.CNT > 0
START WITH B2.DLVR_CODE = '011'
AND B2.PART_NO = A.PART_NO
CONNECT BY PRIOR B2.PART_NO = B2.UPPR_PART_NO
), 0) X_CNT
FROM (
SELECT PART_NAME Y_NAME
, PART_NO
FROM ITMS.STD_LBS_MST
WHERE 1=1
AND LEVEL = 3
START WITH DLVR_CODE = '011'
AND PART_NO = '011'
CONNECT BY PRIOR PART_NO = UPPR_PART_NO
ORDER SIBLINGS BY PART_NO
) A
NVL 부분을 FROM절로 보내어 조인방식으로 다시 재구성 하고 싶습니다.
1. 계층 쿼리에서
- start with 에 있는 조건 dlvr_code = '011' 이
- connect by 조건에도 동일하게 들어가야 하는 건 아닌지?
2. 아우터 조인 후 cnt > 0 조건은
- 아우터 조인을 하고 조건을 주면 아우터 조인은 무의미합니다.
- 또한 COUNT(*) 가 0 이 나올 수는 없습니다. 따라서 cnt > 0 조건도 무의미합니다.
3. 스칼라서브쿼리를 아우터 조인으로 변경
- 일반적인 쿼리가 아니라 계층쿼리이고 하위계층 누적합 쿼리네요.
- 전체 노드에 대한 하위노드 누적합이라면?
- Start with 를 생략하여 모든 노드가 부모가 되게 한후
- connect_by_root(part_no) 로 그룹바이 하는 방법이 있습니다.
- 하위 계층 누적합 내용 참조 : http://gurubee.net/lecture/2250
- 다만 이 경우엔 모든 노드에 대해 구하는게 아닌 3레벨만 구하는 것이므로
- 조금 다른 방법으로 풀어봤습니다.
SELECT a.y_name
, a.part_no
, NVL(SUM(b.cnt), 0) x_cnt
FROM (SELECT LEVEL lv
, ROWNUM rn
, part_no c_part_no
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(part_no , '-'), '[^-]+', 1, 3) part_no
, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(part_name, '-'), '[^-]+', 1, 3) y_name
FROM itms.std_lbs_mst
WHERE 1=1
AND LEVEL >= 3
START WITH dlvr_code = '011'
AND part_no = '011'
CONNECT BY PRIOR part_no = uppr_part_no
AND PRIOR dlvr_code = dlvr_code -- 이 조건이 있어야 하는건 아닌지?
ORDER SIBLINGS BY part_no
) a
, (SELECT NVL(d.part_no, t.part_no) part_no
, COUNT(*) cnt
FROM itms.trbl_trn_n t
, itms.trbl_deal_trn_n d
WHERE t.device = '011'
AND t.trbl_rams = 'Y'
AND t.trbl_ras_date BETWEEN '20190101' AND '20190331'
AND t.trbl_seq = d.trbl_seq (+)
GROUP BY NVL(d.part_no, t.part_no)
) b
WHERE a.c_part_no = b.part_no(+)
GROUP BY a.part_no, a.y_name
ORDER BY MIN(a.rn)
;
마농님 감사합니다.
평소에도 개발 시 구루비에서 많은 도움을 받고 있는데 이렇게 또 도움을 받게 되어 정말 감사합니다.
마농님께서 답변해 주신 방법이 많은 도움이 되었습니다. 다시 한번 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.