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;
첨부에 다른 결과값 첨부드립니다. 감사합니다
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) 가 문제인것 같은데 해결을 못하겠습니다
아.. 누적값 구하는 건가요? 저는 단순 집계인 줄 알았네요.
결과가 다른 이유는 조건절을 잘못 줘서 누적이 안되서 그런거구요.
- 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 연도 ;
질문이 처음이라 여러번 죄송합니다
제가 덜어낸 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' 년도가 매번 해당 년으로 변경되어야 하고 변경될 때마다 계산을 새로 해야 했기 때문입니다. (줄어드는 경우도 있어요)
그래서 이제 이걸 연도별로 계산하면 되는데 저 연도 부분을 어떻게 하면 좋을까요?
네. 주의하겠습니다.
연도가 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 |
이해가 되실런지 모르겠습니다. 아는게 없어서 질문도 어렵다는걸 느낍니다. 번거롭게 해드려 죄송합니다. 감사합니다.
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 ;