데이터베이스 질문과 답변

쿼리 질문드립니다. 0 9 3,656

by 커피요쿠르트d [SQL Query] 시간 날짜 등급 오라클 쿼리 LAG GROUP BY [2013.11.20 16:39:29]

수고하십니다. 

------------------------------------------------------------------------------------------------------------------------------
질문에 input_date 관련 내용이 없어.. 혼란이. ㅠㅠ
관련 내용 추가, 수정 하였습니다.

-------------------------------------------------------------------------------------------------------------------------------



쿼리 짜다가 잘 안되서 질문드립니다. 
프로시저로 하면 될거 같은데.. 쿼리로도 가능한지 궁금해서요..

코드, 코드들의 그룹, 각 코드의 등급, 코드별 포인트, 입력된 날짜 정보가 있습니다. 


?
1
2
3
4
5
6
7
8
9
10
11
12
WITH BASE AS (
SELECT '401' CODE , '1' CODE_GROUP , '3' GRADE, '10' POINT , '20120201' INPUT_DATE FROM DUAL UNION ALL
SELECT '402' CODE , '1' CODE_GROUP , '2' GRADE, '100' POINT , '20120301' INPUT_DATE FROM DUAL UNION ALL
SELECT '403' CODE , '1' CODE_GROUP , '1' GRADE, '1000' POINT , '20120401' INPUT_DATE FROM DUAL UNION ALL
SELECT '404' CODE , '1' CODE_GROUP , '1' GRADE, '1000' POINT , '20120501' INPUT_DATE FROM DUAL UNION ALL
SELECT '405' CODE , '1' CODE_GROUP , '1' GRADE, '1000' POINT , '20120601' INPUT_DATE FROM DUAL UNION ALL
SELECT '407' CODE , '2' CODE_GROUP , '3' GRADE, '10' POINT , '20120101' INPUT_DATE FROM DUAL UNION ALL
SELECT '408' CODE , '2' CODE_GROUP , '2' GRADE, '100' POINT , '20120201' INPUT_DATE FROM DUAL UNION ALL
SELECT '409' CODE , '2' CODE_GROUP , '1' GRADE, '1000' POINT , '20120301' INPUT_DATE FROM DUAL UNION ALL
SELECT '410' CODE , '3' CODE_GROUP , '1' GRADE, '1000' POINT , '20120201' INPUT_DATE FROM DUAL UNION ALL
SELECT '411' CODE , '3' CODE_GROUP , '2' GRADE, '100' POINT , '20120301' INPUT_DATE FROM DUAL
)SELECT * FROM BASE ;



위와 같은데요..
그룹별로 포인트 합산을 구할려고 합니다.

그룹별로 합산이므로 그룹내에서 포인트를 합산합니다.

동일한 등급이 여러건 있어도 1건만 인정합니다. 

등급이 향상되어야 점수에 포함합니다.  (GRADE = 1 이 가장 높은 등급입니다)

시간이 흐를 수록 등급이 향상되어야지만 합산에 포함합니다. INPUT_DATE 

등급이 오르락 내리락 하더라도 각 등급별로 한 번만 합산에 포함합니다.
(시간순서로 코드들을 나열 하였을 때 이전에 입력된 코드보다 등급이 올라간 경우의 코드만 포인트를 합산에 포함합니다.)

음.. 결론은
동일 코드그룹내의 각(1,2,3) 등급의 포인트를 합산한다.
시간이 흐를 수록 등급이 향상된 경우만 합산에 포함한다
입니다.

예를 들면 

- 2등급이 10개 있더라도 한번만 합산되어야 하구요.
- 1등급이 먼저 입력되었다면, 뒤에 2, 3 등급이 100개가 있어도 1등급 하나만 점수에 포함됩니다. 
- 시간 순서로 1-2-1-3-1-2  이렇게 등급이 입력되었다 하더라도.. 제일 먼저 1등급이 들어왔으므로 1등급 점수만 포함
- 시간 순서로 3-3-2-3-3  이렇게 등급이 입력되었다면 3등급 1회와  2등급으로의 향상 1회 이므로 2,3 등급 점수만 포함.

입니다. 


위와 같은 조건이 있습니다. 


조건을 적용시킨 결과의 모습은 아래와 같습니다. 
?
1
2
3
4
5
WITH RESULT1 AS (
SELECT '1' CODE_GROUP , '1110' SUM_POINT FROM DUAL UNION ALL
SELECT '2' CODE_GROUP , '1110' SUM_POINT FROM DUAL UNION ALL
SELECT '3' CODE_GROUP , '1000' SUM_POINT FROM DUAL
)SELECT * FROM RESULT1 ;


CODE_GROUP  = 1 인 애들을 설명해보면
CODE 401 , 402, 403 은 기간이 지날수록 등급이 올라서 모두 점수를 합산
404,405는  이미 1등급인 403이 있으므로 점수에 포함되지 않음.
401,402,403의 합산인 1110 점이 됩니다.

CODE_GROUP  = 2 는
기본형입니다.  CODE 401 , 402, 403 와 동일합니다.
그래서 1110 점입니다.

CODE_GROUP  = 3 는 
CODE 410이  411보다 먼저 입력된 높은 등급 CODE이므로 410만 합산하여 
1000 점이 됩니다. 

그리하여 위 결과를 SUM 한 ..(1110 + 1110 + 1000) = 3220
아래와 같은 결과를 얻고 싶습니다. 

?
1
2
3
4
5
WITH RESULT_FINAL AS (
SELECT '1' CODE_GROUP , '1110' SUM_POINT FROM DUAL UNION ALL
SELECT '2' CODE_GROUP , '1110' SUM_POINT FROM DUAL UNION ALL
SELECT '3' CODE_GROUP , '1000' SUM_POINT FROM DUAL
)SELECT SUM(SUM_POINT) FROM RESULT_FINAL ;


1단계에서 2단계 자료를 만드는데 쉽지 않네요. 
하고자 하는 바를 조리있고 쉽게 설명하기도 쉽지 않네요. ㅠㅠ

LAG()나  LEAD()를 이용하면 할 수도 있겠다 싶어 해보았는데.. 잘 안되네요. 
날짜와 등급별 정렬을 만들기도 쉽지않고..

쿼리로는 안되겠다 싶어 프로시저로 할려고 하다가 혹시나 싶어 질문드립니다. 
혹시 쿼리로도 가능할까요?


그럼 도움 바랍니다. 

즐거운 오후 되시길...
by 아발란체 [2013.11.20 16:58:54]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--근데 위에 최종 결과가 3220인데, 3320이 정답 아닌가요?
--CODE_GROUP 3를 보면, 서로 다른 등급 GRADE (1, 2) 2개 있습니다. 2개 더하면 1100입니다.
--근데 중간 결과에 1000으로 되어 있네요.
--단서를 보면 1000이 맞다고 되어 있는데, 이게 code_group 기준으로 grade가 높은 등급이 있어
--2번째 등급을 취하지 않는다는 것인데 이런 기준으로 본다면 다른 code_group도 높은 1등급이 있으니
--2등급, 3등급 데이타를 취하지 않아야 할 것 같은데.... (난독증 + 머리나쁨... ㅠㅠ)
SELECT
 SUM(POINT) POINT
FROM (
 SELECT
 CODE_GROUP, GRADE, POINT
 FROM
 BASE
 GROUP BY
 CODE_GROUP, GRADE, POINT
);
by 아발란체 [2013.11.20 17:21:59]

아... 이해 했습니다.
날짜가 대한 언급이 없는 것 같아 아예 날짜를 배제 시켰는데
역추하면 INPUT_DATE 날짜로 차이가 발생하네요... 뉴ㅅ뉴)ㆀ
물론 아래 분들의 답을 보고 이해... OTL

by 커피요쿠르트d [2013.11.20 18:20:17]
아고.. 죄송합니다. 

설명이 누락되었네요;; 

예 말씀대로 시간 순서대로 등급들을 나열했을 때 등급이 향상 되었을 때만 합산을 해야하는 상황이었습니다. 

죄송해요..ㅠㅠ

난독증 환자에 머리나쁜 사람으로 만들어버린 주요인을 제공... 쿨럭.. 
말이 길어지니 이상하네요 ㅋ
감사합니다!!


얼떨결에 답변채택을 했네용;;

사과의 의미로.. 받아주....쿨럭..

즐거운 저녁 되셔요~!!
by 우리집아찌 [2013.11.20 17:09:15]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH BASE AS (
SELECT '401' CODE , '1' CODE_GROUP , '3' GRADE, '10' POINT , '20120201' INPUT_DATE FROM DUAL UNION ALL
SELECT '402' CODE , '1' CODE_GROUP , '2' GRADE, '100' POINT , '20120301' INPUT_DATE FROM DUAL UNION ALL
SELECT '403' CODE , '1' CODE_GROUP , '1' GRADE, '1000' POINT , '20120401' INPUT_DATE FROM DUAL UNION ALL
SELECT '404' CODE , '1' CODE_GROUP , '1' GRADE, '1000' POINT , '20120501' INPUT_DATE FROM DUAL UNION ALL
SELECT '405' CODE , '1' CODE_GROUP , '1' GRADE, '1000' POINT , '20120601' INPUT_DATE FROM DUAL UNION ALL
SELECT '407' CODE , '2' CODE_GROUP , '3' GRADE, '10' POINT , '20120101' INPUT_DATE FROM DUAL UNION ALL
SELECT '408' CODE , '2' CODE_GROUP , '2' GRADE, '100' POINT , '20120201' INPUT_DATE FROM DUAL UNION ALL
SELECT '409' CODE , '2' CODE_GROUP , '1' GRADE, '1000' POINT , '20120301' INPUT_DATE FROM DUAL UNION ALL
SELECT '410' CODE , '3' CODE_GROUP , '1' GRADE, '1000' POINT , '20120201' INPUT_DATE FROM DUAL UNION ALL
SELECT '411' CODE , '3' CODE_GROUP , '2' GRADE, '100' POINT , '20120301' INPUT_DATE FROM DUAL
)
  
SELECT CODE_GROUP , SUM(CASE WHEN GRADE <= MIN_GRADE THEN POINT END ) PT
 FROM
(SELECT DISTINCT MIN(GRADE) OVER(PARTITION BY CODE_GROUP ORDER BY CODE) MIN_GRADE
 ,CODE_GROUP , GRADE , POINT
 FROM BASE )
 GROUP BY ROLLUP (CODE_GROUP)
 ORDER BY CODE_GROUP
by 커피요쿠르트d [2013.11.20 18:45:47]
와우!!
MIN_GRADE를 만들어서 하는 방법 정말 놀랍습니다~!!!!

알려주신 코드에서
?
1
SELECT DISTINCT MIN(GRADE) OVER(PARTITION BY CODE_GROUP ORDER BY INPUT_DATE) MIN_GRADE

ORDER BY 부분만 INPUT_DATE로 바꿨습니다. 
제가 설명에 시간흐름제어를 안 적어놨습니다. 죄송합니다;; 


좋은 부분을 배우고 갑니다. 

즐거운 저녁 되시길 바랍니다.
by 마농 [2013.11.20 17:13:49]
?
1
2
3
4
5
6
7
8
SELECT SUM(point) sum_point
  FROM (SELECT grade
             , point
             , LAG(grade, 1, '9') OVER(PARTITION BY code_group ORDER BY input_date) lag_grade
          FROM base
        )
 WHERE grade < lag_grade
;
by 마농 [2013.11.20 17:21:32]
?
1
2
3
4
5
6
7
8
9
-- 상승 > 하락 > 상승을 반복하는 경우 등급향상 단 한번만 인정한다면 다음과 같이 --
SELECT SUM(MAX(point)) sum_point
  FROM (SELECT code_group, grade, point
             , LAG(grade, 1, '9') OVER(PARTITION BY code_group ORDER BY input_date) lag_grade
          FROM base
        )
 WHERE grade < lag_grade
 GROUP BY code_group, grade
;
by 커피요쿠르트d [2013.11.20 18:52:56]
정말 감사합니다. 

마농님께서 알려주신 쿼리를 보니 GROUP BY 부분을 제대로 못 다뤄서 헤메었던것 같습니다. 
또 배우고 가네요 
감사합니다. !!!

그럼 즐거운 저녁 되시길 바랍니다.     
by 커피요쿠르트d [2013.11.20 18:54:01]
윗 분들 모두 감사드립니다. 

참고로 본문의 내용을 약간 수정하였습니다. 
시간흐름 언급이 없어 혼란을 ;;; 쿨럭.. 

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