데이터베이스 질문과 답변

전월 데이터 합산 관련 해서 질문 드립니다. 0 5 639

by 아지태 [SQL Query] [2016.07.23 00:44:56]

안녕하세요. 쿼리 구현에 관해 궁금한 부분이 있어 질문드립니다.

위에서 AMT 컬럼까지가 기초데이터고 뒤에 CAL_AMT가 제가 원하는 결과값인데요.


SELECT  'B1210001' AS PSN_NO, '201401' AS CLYM,  1000 AS AMT  ,    0 AS CAL_AMT  FROM DUAL UNION ALL
SELECT  'B1210001' AS PSN_NO, '201402' AS CLYM, -3000 AS AMT , -3000 AS CAL_AMT  FROM DUAL UNION ALL
SELECT  'B1210001' AS PSN_NO, '201405' AS CLYM,  5000 AS AMT ,     0 AS CAL_AMT  FROM DUAL UNION ALL
SELECT  'B2010001' AS PSN_NO, '201407' AS CLYM, -4000 AS AMT , -4000 AS CAL_AMT  FROM DUAL UNION ALL   
SELECT  'C2710001' AS PSN_NO, '201402' AS CLYM, -5000 AS AMT , -5000 AS CAL_AMT  FROM DUAL UNION ALL
SELECT  'C2710001' AS PSN_NO, '201403' AS CLYM,  1000 AS AMT , -4000 AS CAL_AMT  FROM DUAL UNION ALL
SELECT  'C2710001' AS PSN_NO, '201404' AS CLYM,  7000 AS AMT ,     0 AS CAL_AMT  FROM DUAL

기본적으로 PSN_NO(개인번호) , CLYM(마감년월)별 누계를 구하려고 하는데,

단순하게 개인번호별 당월과 전월만 더하면 LAG함수를 쓰면 되지만 제가 구해야 하는 결과치는 조금 다릅니다.

1. 우선 그룹별 첫번째 데이터부터 AMT누계를 구합니다. 

   이때, 당월 + 전월(전월이 없으면 0)이  양수이면 0으로 바꿔야 합니다.

    예) B1210001 201401 1000 -->  1000 + 0 = 1000 --> 0

2. 두번째 데이터부터는 당월 AMT + 전월의 합산 결과 AMT를 더하고, 마찬가지로 양수면 0으로 바꿔줍니다.

    예) B1210001 201402 -3000 --> -3000 + 0 = -3000

3. 이 프로세스로 개인번호의 마감년월이 끝날때까지 누계를 구합니다. 

     (B1210001 201401 ~ 201407)

 

한방 쿼리로 해보려고 생각을 해봐도 답이 나오지를 않네요.

이런식으로 구하는 계산 데이터를 쿼리로 처리할수 있나요?  

질문 끝까지 봐주셔서 감사드립니다.

 

by jkson [2016.07.23 09:21:17] 
WITH T AS
(
SELECT  'B1210001' AS PSN_NO, '201401' AS CLYM,  1000 AS AMT FROM DUAL UNION ALL
SELECT  'B1210001' AS PSN_NO, '201402' AS CLYM, -3000 AS AMT FROM DUAL UNION ALL
SELECT  'B1210001' AS PSN_NO, '201405' AS CLYM,  5000 AS AMT FROM DUAL UNION ALL
SELECT  'B2010001' AS PSN_NO, '201407' AS CLYM, -4000 AS AMT FROM DUAL UNION ALL
SELECT  'C2710001' AS PSN_NO, '201402' AS CLYM, -5000 AS AMT FROM DUAL UNION ALL
SELECT  'C2710001' AS PSN_NO, '201403' AS CLYM,  1000 AS AMT FROM DUAL UNION ALL
SELECT  'C2710001' AS PSN_NO, '201404' AS CLYM,  7000 AS AMT FROM DUAL
)
,T1 AS
(SELECT PSN_NO, CLYM , AMT, ROW_NUMBER() OVER (PARTITION BY PSN_NO ORDER BY CLYM) SEQ 
   FROM T
)
,T2 (PSN_NO, CLYM, AMT, CAL_AMT, SEQ) AS
(
SELECT PSN_NO, CLYM, AMT, LEAST(AMT,0)  CAL_AMT , 1 SEQ
  FROM
    (
    SELECT PSN_NO, CLYM, AMT, ROW_NUMBER() OVER(PARTITION BY PSN_NO ORDER BY CLYM) RN FROM T
    )
 WHERE RN = 1
 UNION ALL
SELECT B.PSN_NO, B.CLYM, B.AMT, LEAST(A.CAL_AMT + B.AMT,0), A.SEQ + 1 
  FROM T2 A, T1 B
 WHERE A.PSN_NO = B.PSN_NO
   AND A.SEQ + 1 = B.SEQ
)
SELECT PSN_NO, CLYM, AMT, CAL_AMT
  FROM T2
ORDER BY PSN_NO, CLYM

 

by 마농 [2016.07.25 08:32:46]

인라인뷰 부분을 T1 으로 대체하면 더 깔끔하겠네요.

by 아지태 [2016.07.23 11:37:16]

감사합니다. 도움이 되었습니다. ^^

그런데 궁금한게 아래에서 T1은 위쪽에 있는 상위집합인데 조인이 되는군요. 

진짜 공부좀 해야겠네요 ㅠㅠ

저는 뷰가 (T1, T2) 만들어지는 과정이 각각의 뷰가 DEPTH가 깊은 부분부터 만들기 시작해서 두개가 다 생성된 뒤에야 서로를 참조 할 수 있는줄 알았는데 먼저 선언한 뷰가 있으면(T1), 다음 뷰(T2)를 생성할때 하위단에서 먼저 선언한 뷰(T1)를 참조할수 있는건가요?



 UNION ALL

SELECT B.PSN_NO, B.CLYM, B.AMT, LEAST(A.CAL_AMT + B.AMT,0), A.SEQ + 1 

  FROM T2 A, T1 B

 WHERE A.PSN_NO = B.PSN_NO

   AND A.SEQ + 1 = B.SEQ

 

 

 

by 마농 [2016.07.25 08:30:46]
by jkson [2016.07.25 08:53:32]

어렴풋이 모델문으로 될 것 같았는데 아직 사용이 익숙하지 않아서 쓸 때마다 헷갈리네요. 문법이 어려워요ㅠ

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