MySql 스칼라 서브쿼리 개선 관련 질문입니다. 0 4 277

by 찡스쿄 [MySQL] mysql subquery 스칼라 쿼리개선 [2021.01.20 10:09:40]


result.PNG (21,953Bytes)

안녕하세요, 선배님들.

이전에 비슷한 내용으로 질문을 올렸던 적이 있는데요,

현재 쿼리는 다음과 같습니다.


		SELECT A.COS_ORD_CD, A.COS_QTY, A.COS_IRS_GB, A.COS_TAX_FG, A.COS_PAY, A.COS_ORD_LS_SQ, A.COS_APPRO_FG, A.COS_STRD_GB, A.COS_UNIT,
		IF(A.COS_IRS_GB='품목', (SELECT COS_CLS01_NM FROM COS_CLS02 B2 INNER JOIN COS_CLS01 B3 ON B3.COS_CLS01_CD = B2.COS_CLS01_CD WHERE B2.COS_CLS02_CD = B1.COS_CLS02_CD), 
		IF(A.COS_IRS_GB='원료', (SELECT COS_CLS01_NM FROM COS_CLS02 C2 INNER JOIN COS_CLS01 C3 ON C3.COS_CLS01_CD = C2.COS_CLS01_CD WHERE C2.COS_CLS02_CD = C1.COS_CLS02_CD), 
		IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS01_NM FROM COS_CLS02 D2 INNER JOIN COS_CLS01 D3 ON D3.COS_CLS01_CD = D2.COS_CLS01_CD WHERE D2.COS_CLS02_CD = D1.COS_CLS02_CD), ''
		))) AS COS_CLS01_NM,
	
		IF(A.COS_IRS_GB='품목', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI B2 INNER JOIN COS_CLS02 B3 ON B3.COS_CLS02_BI_CD = B2.COS_CLS02_BI_CD WHERE B3.COS_CLS02_CD = B1.COS_CLS02_CD), 
		IF(A.COS_IRS_GB='원료', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI C2 INNER JOIN COS_CLS02 C3 ON C3.COS_CLS02_BI_CD = C2.COS_CLS02_BI_CD WHERE C3.COS_CLS02_CD = C1.COS_CLS02_CD), 
		IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI D2 INNER JOIN COS_CLS02 D3 ON D3.COS_CLS02_BI_CD = D2.COS_CLS02_BI_CD WHERE D3.COS_CLS02_CD = D1.COS_CLS02_CD), ''
		))) AS COS_CLS02_BI_NM,

		IF(A.COS_IRS_GB='품목', (SELECT SUM(COS_QTY) FROM COS_ORD_LS B2 WHERE B2.COS_ITEM_CD = B1.COS_ITEM_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != B2.COS_ORD_LS_SQ), 
		IF(A.COS_IRS_GB='원료', (SELECT SUM(COS_QTY) FROM COS_ORD_LS C2 WHERE C2.COS_RAW_MTRL_CD = C1.COS_RAW_MTRL_CD AND COS_STRD_GB = 'N'  AND A.COS_ORD_LS_SQ != C2.COS_ORD_LS_SQ), 
		IF(A.COS_IRS_GB='부자재', (SELECT SUM(COS_QTY) FROM COS_ORD_LS D2 WHERE D2.COS_SUB_RC_CD = D1.COS_SUB_RC_CD AND COS_STRD_GB = 'N'  AND A.COS_ORD_LS_SQ != D2.COS_ORD_LS_SQ), ''
		))) AS COS_OO_QTY
		,
		(SELECT SUM(COS_QTY) FROM COS_WARE_LS WHERE COS_ORD_LS_SQ = A.COS_ORD_LS_SQ) AS cos_now_qty,
		C03.COS_CLS03_NM AS CLS_NAME -- LEFT OUTER JOIN으로 처리한 코드명
		FROM COS_ORD_LS A
		
		LEFT OUTER JOIN COS_ITEM B1 ON B1.COS_ITEM_CD = A.COS_ITEM_CD
		LEFT OUTER JOIN COS_RAW_MTRL C1 ON C1.COS_RAW_MTRL_CD = A.COS_RAW_MTRL_CD
		LEFT OUTER JOIN COS_SUB_RC D1 ON D1.COS_SUB_RC_CD = A.COS_SUB_RC_CD
		LEFT OUTER JOIN COS_CLS03 C03 ON C03.COS_CLS03_CD = (CASE A.COS_IRS_GB
																WHEN '품목' THEN B1.COS_CLS03_CD
																WHEN '원료' THEN C1.COS_CLS03_CD
																WHEN '부자재' THEN D1.COS_CLS03_CD
																ELSE '' END)
		WHERE A.COS_ORD_CD='ORD20201210111051'
		ORDER BY A.COS_ORD_LS_SQ DESC;
	

SELECT 절 마지막에 포함된 컬럼인 "CLS_NAME"은 이전에 마농(선생)님께서 가르쳐 주신 대로 변경을 하였는데요,

"COS_CLS01_NM", "COS_CLS02_BI_NM"과 같이 JOIN-WHERE가 포함된 서브쿼리나

"COS_OO_QTY", "COS_NOW_QTY"와 같이 집계 함수가 포함된 서브쿼리도 축약이 가능할까요?..

만약 가능하다면, 어떠한 형태로 처리할 수 있는지 궁금합니다..

인라인 서브쿼리와 제가 아는 여러 가지 조인을 이용해서 시도해 보았지만 원하는 결과가 나오지 않는 상황입니다...

선배님들의 소중한 조언 감사히 듣도록 하겠습니다.

답변 미리 감사드립니다. ^^

(현재 셀렉트되는 결과는 첨부파일에 추가하였습니다!)

 

 

by 마농 [2021.01.20 13:25:08]

cos_cls01_nm, cos_cls02_bi_nm 과 같이 join-where 가 포함된 서브쿼리는 쉽게 조인형태로 변경이 가능하지만
cos_oo_qty, cos_now_qty 와 같이 집계 함수가 포함된 서브쿼리는 축약이 까다롭습니다.
조인 후 집계 해야 할지? 집계후 조인해야 할지? 성능도 고려해야 하므로 생각할 게 많습니다.
cos_now_qty 의 경우엔 그래도 조건이 명확해 보이는데.
cos_oo_qty 의 경우엔 부정 조건도 사용되고 테이블도 메인과 동일한 테이블이네요?
이 항목이 어떤 의미로 사용된 것인지 불확실 하네요.
결과 이미지를 봐도 값이 NULL 인 듯 하고요.
조건이 정확하게 주어진 것이 맞는지?
동일 테이블인데? 메인 조건인 (cos_ord_cd = 'ORD20201210111051') 조건이 서브에는 없고
정확한 변환을 위해서는 각 테이블의 PK 와 서로간의 관계.
각 항목들의 의미 등을 알아야 합니다.


by 찡스쿄 [2021.01.20 14:21:01]

마농 선생님 답변 감사드립니다!

말씀하신 JOIN-WHERE를 이렇게 쿼리를 작성해 보았는데.. 결과는 올바르게 나오고 있습니다..

COS_CLS01_NM을 처리하는 기존의(주석 처리된) 스칼라 서브 쿼리가 올바른 형태로 변경된 게 맞을까요?..

SELECT A.COS_ORD_CD, A.COS_QTY, A.COS_IRS_GB, A.COS_TAX_FG, A.COS_PAY, A.COS_ORD_LS_SQ, A.COS_APPRO_FG, A.COS_STRD_GB, A.COS_UNIT,
			/*IF(A.COS_IRS_GB='품목', (SELECT COS_CLS01_NM FROM COS_CLS02 B2 INNER JOIN COS_CLS01 B3 ON B3.COS_CLS01_CD = B2.COS_CLS01_CD WHERE B2.COS_CLS02_CD = B1.COS_CLS02_CD), 
			IF(A.COS_IRS_GB='원료', (SELECT COS_CLS01_NM FROM COS_CLS02 C2 INNER JOIN COS_CLS01 C3 ON C3.COS_CLS01_CD = C2.COS_CLS01_CD WHERE C2.COS_CLS02_CD = C1.COS_CLS02_CD), 
			IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS01_NM FROM COS_CLS02 D2 INNER JOIN COS_CLS01 D3 ON D3.COS_CLS01_CD = D2.COS_CLS01_CD WHERE D2.COS_CLS02_CD = D1.COS_CLS02_CD), ''
			))) AS COS_CLS01_NM,*/
			
			CD.COS_CLS01_NM, -- 바로 앞 라인에 주석처리한 COS_CLS01_NM을 FROM 절에서 JOIN으로 변경하여 조회(CASE 문 이용)

			IF(A.COS_IRS_GB='품목', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI B2 INNER JOIN COS_CLS02 B3 ON B3.COS_CLS02_BI_CD = B2.COS_CLS02_BI_CD WHERE B3.COS_CLS02_CD = B1.COS_CLS02_CD), 
			IF(A.COS_IRS_GB='원료', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI C2 INNER JOIN COS_CLS02 C3 ON C3.COS_CLS02_BI_CD = C2.COS_CLS02_BI_CD WHERE C3.COS_CLS02_CD = C1.COS_CLS02_CD), 
			IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI D2 INNER JOIN COS_CLS02 D3 ON D3.COS_CLS02_BI_CD = D2.COS_CLS02_BI_CD WHERE D3.COS_CLS02_CD = D1.COS_CLS02_CD), ''
			))) AS COS_CLS02_BI_NM,
			 
			IF(A.COS_IRS_GB='품목', (SELECT SUM(COS_QTY) FROM COS_ORD_LS B2 WHERE B2.COS_ITEM_CD = B1.COS_ITEM_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != B2.COS_ORD_LS_SQ), 
			IF(A.COS_IRS_GB='원료', (SELECT SUM(COS_QTY) FROM COS_ORD_LS C2 WHERE C2.COS_RAW_MTRL_CD = C1.COS_RAW_MTRL_CD AND COS_STRD_GB = 'N'  AND A.COS_ORD_LS_SQ != C2.COS_ORD_LS_SQ), 
			IF(A.COS_IRS_GB='부자재', (SELECT SUM(COS_QTY) FROM COS_ORD_LS D2 WHERE D2.COS_SUB_RC_CD = D1.COS_SUB_RC_CD AND COS_STRD_GB = 'N'  AND A.COS_ORD_LS_SQ != D2.COS_ORD_LS_SQ), ''
			))) AS COS_OO_QTY
			,
			(SELECT SUM(COS_QTY) FROM COS_WARE_LS WHERE COS_ORD_LS_SQ = A.COS_ORD_LS_SQ) AS cos_now_qty,
			C03.COS_CLS03_NM AS CLS_NAME -- LEFT OUTER JOIN으로 처리한 코드명
			FROM COS_ORD_LS A
			 
			LEFT OUTER JOIN COS_ITEM B1 ON B1.COS_ITEM_CD = A.COS_ITEM_CD
			LEFT OUTER JOIN COS_RAW_MTRL C1 ON C1.COS_RAW_MTRL_CD = A.COS_RAW_MTRL_CD
			LEFT OUTER JOIN COS_SUB_RC D1 ON D1.COS_SUB_RC_CD = A.COS_SUB_RC_CD
			LEFT OUTER JOIN COS_CLS03 C03 ON C03.COS_CLS03_CD = (CASE A.COS_IRS_GB
			                                                        WHEN '품목' THEN B1.COS_CLS03_CD
			                                                        WHEN '원료' THEN C1.COS_CLS03_CD
			                                                        WHEN '부자재' THEN D1.COS_CLS03_CD
			                                                        ELSE '' END)
			/* COS_CLS01_NM 스칼라 서브쿼리를 다음과 같은 형태의 조인으로 변경 */
			LEFT OUTER JOIN (
				SELECT C01.COS_CLS01_NM, C02.COS_CLS02_CD
				FROM COS_CLS02 AS C02
				INNER JOIN COS_CLS01 C01 ON C01.COS_CLS01_CD = C02.COS_CLS01_CD
			)
			AS CD ON CD.COS_CLS02_CD = (CASE A.COS_IRS_GB
											WHEN '품목' THEN B1.COS_CLS02_CD
											WHEN '원료' THEN C1.COS_CLS02_CD
											WHEN '부자재' THEN D1.COS_CLS02_CD
											ELSE '' END)
			/* =================================================== */
			WHERE A.COS_ORD_CD='ORD20201210111051'
			ORDER BY A.COS_ORD_LS_SQ DESC

 


by 마농 [2021.01.20 14:41:01]
SELECT a.cos_ord_cd
     , a.cos_qty
     , a.cos_irs_gb
     , a.cos_tax_fg
     , a.cos_pay
     , a.cos_ord_ls_sq
     , a.cos_appro_fg
     , a.cos_strd_gb
     , a.cos_unit
     , c01.cos_cls01_nm
     , c2b.cos_cls02_bi_nm
     , (SELECT SUM(b.cos_qty)
          FROM cos_ord_ls b
         WHERE b.cos_strd_gb    = 'N'
           AND b.cos_ord_ls_sq != a.cos_ord_ls_sq
           AND CASE WHEN a.cos_irs_gb = '품목'   AND b.cos_item_cd     = b1.cos_item_cd     THEN 1
                    WHEN a.cos_irs_gb = '원료'   AND b.cos_raw_mtrl_cd = c1.cos_raw_mtrl_cd THEN 1
                    WHEN a.cos_irs_gb = '부자재' AND b.cos_sub_rc_cd   = d1.cos_sub_rc_cd   THEN 1
                END = 1
        ) cos_oo_qty
     , (SELECT SUM(cos_qty) FROM cos_ware_ls WHERE cos_ord_ls_sq = a.cos_ord_ls_sq) cos_now_qty
     , c03.cos_cls03_nm AS cls_name
  FROM cos_ord_ls a
  LEFT JOIN cos_item     b1  ON a.cos_item_cd     = b1.cos_item_cd
  LEFT JOIN cos_raw_mtrl c1  ON a.cos_raw_mtrl_cd = c1.cos_raw_mtrl_cd
  LEFT JOIN cos_sub_rc   d1  ON a.cos_sub_rc_cd   = d1.cos_sub_rc_cd
  LEFT JOIN cos_cls03    c03 ON c03.cos_cls03_cd  = (CASE a.cos_irs_gb
                                                     WHEN '품목'   THEN b1.cos_cls03_cd
                                                     WHEN '원료'   THEN c1.cos_cls03_cd
                                                     WHEN '부자재' THEN d1.cos_cls03_cd
                                                     ELSE '' END)
  LEFT JOIN cos_cls02    c02 ON c02.cos_cls02_cd  = (CASE a.cos_irs_gb
                                                     WHEN '품목'   THEN b1.cos_cls02_cd
                                                     WHEN '원료'   THEN c1.cos_cls02_cd
                                                     WHEN '부자재' THEN d1.cos_cls02_cd
                                                     ELSE '' END)
  LEFT JOIN cos_cls01    c01 ON c02.cos_cls01_cd  = c01.cos_cls01_cd
  LEFT JOIN cos_cls02_bi c2b ON c02.cos_cls01_cd  = c2b.cos_cls02_bi_cd
 WHERE a.cos_ord_cd = 'ORD20201210111051'
 ORDER BY a.cos_ord_ls_sq DESC
;

 


by 찡스쿄 [2021.01.20 15:02:40]

마농 선생님, 답변 감사드립니다. ^^

앞에서 말씀하신 의미가 명확하지 않은 컬럼은 우선은 제외해야 할 듯합니다 ^^..

오늘도 도움 주셔서 정말 감사드립니다.

선생님 덕분에 하루 하루 쿼리 실력이 늘고있는 것을 느끼게 됩니다. (- 꾸벅 -)

매번 정말 진심으로 감사드립니다.

좋은 한 주 보내시길 바라요. ^^

 

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