꿈꾸는 개발자, DBA 커뮤니티 구루비

순차적 집계

by Oracle10g [2017.10.17 16:42:59]

안녕하십니까!! 구루비회원분들!!

한가지 질문을 드리고 싶어서 글 올립니다 ㅠㅠ

바로 질문드리겠습니다.

순차적 집계를 하고싶은데..

WITH T AS (
          SELECT '홍길동' KORNM, '01' WEEK , 76 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '02' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '03' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '04' WEEK , 82 TM FROM DUAL --83.5   (V)
UNION ALL SELECT '홍길동' KORNM, '05' WEEK , 65 TM FROM DUAL --80.75  (V)
UNION ALL SELECT '홍길동' KORNM, '06' WEEK , 33 TM FROM DUAL --67
UNION ALL SELECT '홍길동' KORNM, '07' WEEK , 70 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '08' WEEK , 80 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '09' WEEK , 88 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '10' WEEK , 51 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '11' WEEK , 77 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '12' WEEK , 99 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '13' WEEK , 74 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '14' WEEK , 75 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '15' WEEK , 80 TM FROM DUAL
)
SELECT T.*
  FROM T;

1~3주차 패스
4주차부터 4주간의 합계의 평균값 TM의 값이 80이상인 WEEK를 체크하고 싶습니다..

4주차 (76+88+88+82)/4 = 83.5 로 초과했으므로 'V' 표시
5주차 (88+88+82+65)/4 = 80.75 로 초과했으므로 'V' 표시
6주차 (88+82+65+33)/4 = 67로 미만값이므로 null

어떤방법을 써서 집계할 수 있을까요?

by jkson [2017.10.17 16:52:16]

WITH T AS (
          SELECT '홍길동' KORNM, '01' WEEK , 76 TMM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '02' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '03' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '04' WEEK , 82 TM FROM DUAL --83.5   (V)
UNION ALL SELECT '홍길동' KORNM, '05' WEEK , 65 TM FROM DUAL --80.75  (V)
UNION ALL SELECT '홍길동' KORNM, '06' WEEK , 33 TM FROM DUAL --67
UNION ALL SELECT '홍길동' KORNM, '07' WEEK , 70 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '08' WEEK , 80 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '09' WEEK , 88 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '10' WEEK , 51 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '11' WEEK , 77 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '12' WEEK , 99 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '13' WEEK , 74 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '14' WEEK , 75 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '15' WEEK , 80 TM FROM DUAL
)
SELECT KORNM
     , WEEK
     , TMM
     , CASE WHEN TO_NUMBER(WEEK) < 4 THEN NULL
       ELSE CASE WHEN AVG(TMM) OVER (PARTITION BY KORNM ORDER BY WEEK ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) > 80 THEN 'V' END
       END FG
  FROM T;

by Oracle10g [2017.10.19 13:29:31]

원하던 답이었습니다!

감사합니다 잭슨님!! 좋은하루되세요 ㅎㅎ

by 우리집아찌 [2017.10.17 17:01:40]

WITH T AS (
          SELECT '홍길동' KORNM, '01' WEEK , 76 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '02' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '03' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '04' WEEK , 82 TM FROM DUAL --83.5   (V)
UNION ALL SELECT '홍길동' KORNM, '05' WEEK , 65 TM FROM DUAL --80.75  (V)
UNION ALL SELECT '홍길동' KORNM, '06' WEEK , 33 TM FROM DUAL --67
UNION ALL SELECT '홍길동' KORNM, '07' WEEK , 70 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '08' WEEK , 80 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '09' WEEK , 88 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '10' WEEK , 51 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '11' WEEK , 77 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '12' WEEK , 99 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '13' WEEK , 74 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '14' WEEK , 75 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '15' WEEK , 80 TM FROM DUAL
)
SELECT T.*
     , AVG(TM) OVER(ORDER BY WEEK ROWS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) V1
     , CASE WHEN  AVG(TM) OVER(ORDER BY WEEK ROWS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) >= 80 THEN 'V' END V2
           
  FROM T;

by Oracle10g [2017.10.19 13:30:15]

아찌님 정말정말 감사합니다 ㅎㅎ

많은 도움이 되었습니다!! 감사합니다

좋은하루 되세요!!

by 마농 [2017.10.17 17:47:29]

SELECT kornm, week, tm
     , CASE WHEN week >= '04' THEN x END x
     , CASE WHEN week >= '04' AND x >= 80 THEN 'V' END v
  FROM (SELECT kornm, week, tm
             , AVG(tm) OVER(PARTITION BY kornm ORDER BY week ROWS 3 PRECEDING) x
          FROM t
        )
;

by Oracle10g [2017.10.19 13:30:36]

감사합니다!!마농님!

분석함수에도 여러 종류가 있군요 ㅠㅠ

더 공부해 보도록 하겠습니다.

좋은하루 되십시요!!

by 고수가되고싶어요 [2017.12.12 17:00:42]

WITH T AS (
          SELECT '홍길동' KORNM, '01' WEEK , 76 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '02' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '03' WEEK , 88 TM FROM DUAL -- NULL
UNION ALL SELECT '홍길동' KORNM, '04' WEEK , 82 TM FROM DUAL --83.5   (V)
UNION ALL SELECT '홍길동' KORNM, '05' WEEK , 65 TM FROM DUAL --80.75  (V)
UNION ALL SELECT '홍길동' KORNM, '06' WEEK , 33 TM FROM DUAL --67
UNION ALL SELECT '홍길동' KORNM, '07' WEEK , 70 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '08' WEEK , 80 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '09' WEEK , 88 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '10' WEEK , 51 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '11' WEEK , 77 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '12' WEEK , 99 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '13' WEEK , 74 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '14' WEEK , 75 TM FROM DUAL
UNION ALL SELECT '홍길동' KORNM, '15' WEEK , 80 TM FROM DUAL
)
SELECT KORNM
	  ,WEEK
      ,TM 
      ,CASE WHEN SUM(TM) OVER(PARTITION BY KORNM ORDER BY WEEK
      				ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ) /4 >= 80 THEN 'V' END CK_80
FROM T

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변