스칼라 서브쿼리 질문드립니다 0 9 703

by 털보 [SQL Query] [2020.03.26 17:00:35]


1.png (23,997Bytes)

SELECT
	SUBSTR(A.REG_DT, 1, 4) AS 연도
	,(
		SELECT 
			COUNT(B.ASET_NO)
		FROM 
			HJJS_ASET_MST B
		LEFT OUTER JOIN HJJS_ASET_DISPO_DTL C
			ON B.ASET_NO = C.ASET_NO	
		WHERE 
			SUBSTR(B.REG_DT, 1, 4) <= SUBSTR(A.REG_DT, 1, 4)
	) AS 점수
	,(
		SELECT 
			SUM(PURC_AMT)
		FROM 
			HJJS_ASET_MST B
		LEFT OUTER JOIN HJJS_ASET_DISPO_DTL C
			ON B.ASET_NO = C.ASET_NO	
		WHERE 
			SUBSTR(REG_DT, 1, 4) <= SUBSTR(A.REG_DT, 1, 4) 
	) AS 금액
FROM
	HJJS_ASET_MST A
GROUP BY
	SUBSTR(A.REG_DT, 1, 4)
ORDER BY 
	SUBSTR(A.REG_DT, 1, 4);

 

안녕하세요.

통계를 빼느라 위와 같은 쿼리를 짰습니다. 

A 테이블의 연도를 점수, 금액 서브쿼리에서 사용하는 상황입니다. 

 

질문에 필요없는 부분은 덜어낸 쿼리인데요 

실제로는 서브쿼리가 2개가 아니라 여러개고, 시간이 굉장히 오래 걸려서

이 서브쿼리들을 하나의 서브쿼리로 합치고 싶습니다. ( 서브쿼리들이 FROM ~ 아래로는 동일합니다 )

 

아래와 같이 with 절로 만들어서 사용해봤는데 결과값이 다르더라구요 ... ㅠ

WITH TEMP AS (SELECT 
	TO_CHAR(TO_CHAR(SYSDATE,'YYYY')-LEVEL+1) AS YEAR
FROM DUAL 
	CONNECT BY LEVEL <= ((TO_CHAR(SYSDATE,'YYYY')+1)- (SELECT MIN(SUBSTR(REG_DT, 1, 4)) FROM HJJS_ASET_MST))
),
TEMP2 AS (
	SELECT 
		B.YEAR AS 연도
		,COUNT(A.ASET_NO) AS 점수
		,SUM(PURC_AMT) AS 금액
	FROM 
		HJJS_ASET_MST A
	INNER JOIN TEMP B
		ON SUBSTR(A.REG_DT, 1, 4) = B.YEAR		
	LEFT OUTER JOIN HJJS_ASET_DISPO_DTL C
		ON A.ASET_NO = C.ASET_NO
	WHERE 
		 SUBSTR(A.REG_DT, 1, 4) <= B.YEAR
	GROUP BY 
		B.YEAR
	ORDER BY 
		B.YEAR
)
SELECT * FROM TEMP2;

 

첨부에 다른 결과값 첨부드립니다. 감사합니다

by 마농 [2020.03.26 17:15:25]

애초에 접근 방법이 잘못되었습니다.
서브쿼리가 필요 없습니다.
 

SELECT SUBSTR(a.reg_dt, 1, 4) 연도
     , COUNT(b.aset_no) 점수
     , SUM(b.purc_amt) 금액
  FROM hjjs_aset_mst a
  LEFT OUTER JOIN hjjs_aset_dispo_dtl b
    ON a.aset_no = b.aset_no
 GROUP BY SUBSTR(a.reg_dt, 1, 4)
 ORDER BY 연도
;

 


by 털보 [2020.03.26 17:29:53]

2번째 쿼리 (WITH 절을 사용한 쿼리)를 서브쿼리 없이 쓰면 아래와 같이 쓰는게 맞을 것 같아요 

아마 답변도 이 의도로 해주신 것 같습니다. 제가 질문을 애매하게 해서 죄송합니다 ㅠ

SELECT 
	SUBSTR(A.REG_DT, 1, 4) 연도
    ,COUNT(B.ASET_NO) 점수
    ,SUM(A.PURC_AMT) AS 금액
 FROM HJJS_ASET_MST A
 INNER JOIN HJJS_ASET_MST B
	ON A.ASET_NO = B.ASET_NO
 LEFT OUTER JOIN HJJS_ASET_DISPO_DTL C
	ON A.ASET_NO = C.ASET_NO		
WHERE SUBSTR(B.REG_DT, 1, 4) <= SUBSTR(A.REG_DT, 1, 4)
GROUP BY SUBSTR(A.REG_DT, 1, 4)
ORDER BY SUBSTR(A.REG_DT, 1, 4);

그런데 통계상으론 첫번째 스칼라서브쿼리를 사용한 결과값이 맞습니다. 

첫번째 쿼리도 서브쿼리 없이 뺄 수 있을까요 ..? 

WHERE 절의 SUBSTR(B.REG_DT, 1, 4) <= SUBSTR(A.REG_DT, 1, 4) 가 문제인것 같은데 해결을 못하겠습니다


by 마농 [2020.03.26 17:40:42]

애초에 서브쿼리가 필요 없습니다.
같은 테이블을 두번 사용할 필요도 없습니다.
아래 쿼리 참고하세요.


by 마농 [2020.03.26 17:33:44]

아.. 누적값 구하는 건가요? 저는 단순 집계인 줄 알았네요.
결과가 다른 이유는 조건절을 잘못 줘서 누적이 안되서 그런거구요.
 - ON SUBSTR(A.REG_DT, 1, 4) = B.YEAR 에서 = 이 아니라 <= 이 되야죠.
 - WHERE SUBSTR(A.REG_DT, 1, 4) <= B.YEAR 이부분은 굳이 필요 없구요.
그런데 연도만 뽑아서 다시 조인하는 부분도 사실 불필요합니다.
쿼리가 전반적으로 안해도 될 작업을 억지로 하고 있습니다.

SELECT SUBSTR(a.reg_dt, 1, 4) 연도
     , COUNT(b.aset_no) 점수
     , SUM(b.purc_amt)  금액
     , SUM(COUNT(b.aset_no)) OVER(ORDER BY SUBSTR(a.reg_dt, 1, 4)) 누적_점수
     , SUM(SUM(b.purc_amt))  OVER(ORDER BY SUBSTR(a.reg_dt, 1, 4)) 누적_금액
  FROM hjjs_aset_mst a
  LEFT OUTER JOIN hjjs_aset_dispo_dtl b
    ON a.aset_no = b.aset_no
 GROUP BY SUBSTR(a.reg_dt, 1, 4)
 ORDER BY 연도
;

 


by 털보 [2020.03.26 19:08:29]

질문이 처음이라 여러번 죄송합니다

제가 덜어낸 WHERE 절에 질문에 필수적인 게 있었는데 빼먹었습니다. ㅠ 


SELECT 
	'2020' 연도,
    SUM(SUM(CASE WHEN SUBSTR(A.REG_DT, 1, 4) <= '2020' AND (A.PROC_DVCD = 'JS001006' OR SUBSTR(B.DISPO_DT, 1, 4) >= '2020') THEN 1 ELSE 0 END)) 점수,
    SUM(SUM(CASE WHEN SUBSTR(A.REG_DT, 1, 4) <= '2020' AND (A.PROC_DVCD = 'JS001006' OR SUBSTR(B.DISPO_DT, 1, 4) >= '2020') THEN A.SM_AMT ELSE 0 END))  금액
  FROM HJJS_ASET_MST A
  LEFT OUTER JOIN HJJS_ASET_DISPO_DTL B
    ON A.ASET_NO = B.ASET_NO
WHERE SUBSTR(A.OLD_PURC_ACCT_CD,1,1) != 'Z' 
 GROUP BY SUBSTR(A.REG_DT, 1, 4)
 ORDER BY SUBSTR(A.REG_DT, 1, 4);

조언해 주신걸로 통계하고 동일한 값을 뽑아냈습니다. (2020년의 값 하나입니다)

제 처음 생각에 스칼라서브쿼리를 썼던 이유는 단순 누적이 아니라 저 '2020' 년도가 매번 해당 년으로 변경되어야 하고 변경될 때마다 계산을 새로 해야 했기 때문입니다. (줄어드는 경우도 있어요)

그래서 이제 이걸 연도별로 계산하면 되는데 저 연도 부분을 어떻게 하면 좋을까요?

 


by 마농 [2020.03.27 08:16:47]

왜곡된 정보로 질문하시면 안됩니다.
마지막 올려주신 쿼리는 정확한가요?
다시 한번 검토해 보신 뒤 정확한 쿼리 올려주세요.
쿼리에 사용된 항목들로 샘플 정보 보여주세요.
원본 대비 결과표 형태의 예시.


by 털보 [2020.03.27 09:27:47]

네. 주의하겠습니다. 

연도가 197x년도부터 현재까지 이어지는데 아래와 같이 연도마다 계산을 새로 합니다.

그걸 저는 처음에 스칼라서브쿼리를 써서 한번 감싸고 바깥의 연도를 가져다가 계산하는 식으로 했는데, 

시간도 오래 걸리고 컬럼마다 중복된 부분이 많았습니다. 그래서 스칼라 서브쿼리를 쓰더라도 서브쿼리를 하나로 만들 수 없는지 처음에 질문드렸습니다.

 

아래 쿼리로 해보니 원본=처음 스칼라 서브쿼리 결과값=아래 쿼리 결과값 으로 맞게 나옵니다. 

 

저 연도(문자열로 박혀있는 부분)는 A 테이블을 한번 더 조인해서 C.REG_DT 이렇게 비교하면 결과값이 다르더라구요. 연도로 더미테이블 만들어서 조인해봐도 다르구요. 그래서 일단 결과값 보여드리려고 UNION으로 묶어놨습니다.  

비교는 REG_DT와 한번, DISPO_DT와 한번 이렇게 두 번 합니다. 

 

WITH TEMP AS (
	SELECT A.ASET_NO 
		 , A.PRLS_CD
		 , SUBSTR(A.REG_DT, 1, 4) 	AS REG_DT
		 , A.SM_AMT
		 , A.PROC_DVCD
		 , SUBSTR(B.DISPO_DT, 1, 4) AS DISPO_DT
	  FROM HJJS_ASET_MST A
	  LEFT OUTER JOIN HJJS_ASET_DISPO_DTL B
	    ON A.ASET_NO = B.ASET_NO
	WHERE SUBSTR(A.OLD_PURC_ACCT_CD,1,1) != 'Z'
)
SELECT '2018' 																																	AS 연도
	 , SUM(COUNT(DISTINCT (CASE WHEN REG_DT <= '2018' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2018') THEN PRLS_CD ELSE NULL END))) OVER() 	AS 종수
	 , SUM(SUM(CASE WHEN REG_DT <= '2018' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2018') THEN 1 ELSE 0 END)) OVER() 						AS 점수
	 , SUM(SUM(CASE WHEN REG_DT <= '2018' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2018') THEN SM_AMT ELSE 0 END)) OVER() 					AS 금액
  FROM TEMP
UNION ALL
SELECT '2019' 																																	AS 연도
	 , SUM(COUNT(DISTINCT (CASE WHEN REG_DT <= '2019' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2019') THEN PRLS_CD ELSE NULL END))) OVER() 	AS 종수
	 , SUM(SUM(CASE WHEN REG_DT <= '2019' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2019') THEN 1 ELSE 0 END)) OVER() 						AS 점수
	 , SUM(SUM(CASE WHEN REG_DT <= '2019' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2019') THEN SM_AMT ELSE 0 END)) OVER() 					AS 금액
  FROM TEMP
UNION ALL
SELECT '2020' 																																	AS 연도
	 , SUM(COUNT(DISTINCT (CASE WHEN REG_DT <= '2020' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2020') THEN PRLS_CD ELSE NULL END))) OVER() 	AS 종수
	 , SUM(SUM(CASE WHEN REG_DT <= '2020' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2020') THEN 1 ELSE 0 END)) OVER() 						AS 점수
	 , SUM(SUM(CASE WHEN REG_DT <= '2020' AND (PROC_DVCD = 'JS001006' OR DISPO_DT >= '2020') THEN SM_AMT ELSE 0 END)) OVER() 					AS 금액
  FROM TEMP
;

 

연도 종수 점수 금액
2018 3909 44938 29540230932
2019 3552 38106 27472367906
2020 3552 38128 27503067899

 

이해가 되실런지 모르겠습니다. 아는게 없어서 질문도 어렵다는걸 느낍니다. 번거롭게 해드려 죄송합니다. 감사합니다. 


by 마농 [2020.03.27 14:36:59]
SELECT a.yyyy 연도
     , COUNT(DISTINCT b.prls_cd) 종수
     , COUNT(*)                  점수
     , SUM(b.sm_amt)             금액
  FROM (SELECT TO_CHAR(yyyy + LEVEL - 1) yyyy
        --FROM (SELECT SUBSTR(MIN(reg_dt), 1, 4) yyyy FROM hjjs_aset_mst WHERE old_purc_acct_cd LIKE 'Z%')
          FROM (SELECT 1976 yyyy FROM dual)
         CONNECT BY LEVEL <= TO_CHAR(sysdate, 'yyyy') - yyyy + 1
        ) a
 INNER JOIN hjjs_aset_mst b
    ON b.reg_dt <= a.yyyy || '1231'
  LEFT OUTER JOIN hjjs_aset_dispo_dtl c
    ON b.aset_no = c.aset_no
 WHERE b.old_purc_acct_cd LIKE 'Z%'
   AND (b.proc_dvcd = 'JS001006' OR c.dispo_dt > a.yyyy)
 GROUP BY a.yyyy
 ORDER BY a.yyyy
;

 


by 털보 [2020.03.30 11:34:09]

정말 감사합니다! 많이 배우고 갑니다

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