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

월별 년도별 통계 쿼리 질문드립니다.

by 물개 [SQL Query] [2013.07.19 10:32:10]

현재 데이터 값이
년월일|파트|건수 이렇게 들어가져 있습니다. 값이 없는 날은 0으로 처리 하고 싶습니다.
20130718|COM| 1
20130719|COM| 2
20130717|AST | 1
20130718|AST | 2

이런형태로 들어가져 있다고 가정합니다. ㅠ

-----------------------------------------
현재 아래처럼 작성해보니(월별, 년도별에 대해서 접근 시도중...)
SELECT NVL (taskpart, '기타') 업무,

occurdt 발생일,

COUNT (occurdt) OVER (PARTITION BY taskpart) 건수

FROM 테이블

WHERE occurdt BETWEEN SUBSTR ('20130709', 0, 6) || '01' AND SUBSTR ('20130709', 0, 6) || '31'

GROUP BY occurdt, taskpart

ORDER BY taskpart, occurdt;

결과가 아래와 같이 나타납니다.
ast | 20130718 | 1
ast | 20130719 | 2
com | 20130717 | 1
com | 20130718 | 2

보고 싶은 형태는 아래와 같습니다.

년도별
구분 |1|2|3|4|5|6|7|8|9|10|11|12|계
COM|0|0|0|0|0|0|0|0|1|0|0|10|10|21
AST |0|0|0|0|0|0|0|0|0|0|0|10|10|20
...

월별
구분 |1|2|3|4|5|6|7|8|9|10|11|12|...|30|31
COM|0|0|0|0|0|0|8|8|0| 0| 0| 0 |...| 0| 0
AST|0|0|0|0|0|0|8|8|0| 0| 0| 0 |...| 0| 0
...

이런 형태로 년도별과 월별 두 경우에 대해서
쿼리를 작성하고 싶습니다.

어떤 형태로 구성하면 좋은지 의견을 듣고 싶습니다.

읽어 주셔서 감사합니다.

by 아린 [2013.07.19 11:00:30]

테이블의 건수부분은 별도 컬럼이 있는건가요?
아니면, 년월일/파트 의 count 한 건가요.

by 물개 [2013.07.19 11:30:28]

년월일 / 파트 에 따른 count 입니다.

by DIIIN [2013.07.19 11:31:34]

테이블 모델을 말씀하시는건지 쿼리를 말씀하시는건지 모르겠네요.

by 물개 [2013.07.19 12:08:21]

쿼리 질문입니다;;

by 아린 [2013.07.19 11:35:32]

WITH t(occurdt, taskpart) AS(

SELECT '20130718', 'COM' FROM dual UNION ALL

SELECT '20130719', 'COM' FROM dual UNION ALL

SELECT '20130717', 'AST' FROM dual UNION ALL

SELECT '20130718', 'AST' FROM dual

)

-- 월별

SELECT taskpart

, COUNT(DECODE(SUBSTR(occurdt,7),'01',1)) d01

, COUNT(DECODE(SUBSTR(occurdt,7),'02',1)) d02

......

, COUNT(DECODE(SUBSTR(occurdt,7),'30',1)) d30

, COUNT(DECODE(SUBSTR(occurdt,7),'31',1)) d31

FROM t

WHERE occurdt LIKE '201307%'

GROUP BY taskpart

ORDER BY taskpart

-- 년도별

SELECT taskpart

, COUNT(DECODE(SUBSTR(occurdt,5,2),'01',1)) d01

, COUNT(DECODE(SUBSTR(occurdt,5,2),'02',1)) d02

......

, COUNT(DECODE(SUBSTR(occurdt,5,2),'11',1)) d11

, COUNT(DECODE(SUBSTR(occurdt,5,2),'12',1)) d12

FROM t

WHERE occurdt LIKE '2013%'

GROUP BY taskpart

ORDER BY taskpart

by 물개 [2013.07.19 12:14:28]

오우..간단하게 처리가 되는군요! 감사합니다 ㅎ

by 미카엘 [2013.07.19 11:37:04]

-- 월별
WITH TMP AS (
SELECT '20120318' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20120419' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20120517' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20120118' PART_DATE, 'AST' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130719' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130717' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'AST' PART, 2 CNT FROM DUAL)
SELECT PART,
   YYYY "년도",
   COUNT(DECODE(MM, '01', CNT)) "1월",
   COUNT(DECODE(MM, '02', CNT)) "2월",
   COUNT(DECODE(MM, '03', CNT)) "3월",
   COUNT(DECODE(MM, '04', CNT)) "4월",
   COUNT(DECODE(MM, '05', CNT)) "5월",
   COUNT(DECODE(MM, '06', CNT)) "6월",
   COUNT(DECODE(MM, '07', CNT)) "7월",
   COUNT(DECODE(MM, '08', CNT)) "8월",
   COUNT(DECODE(MM, '09', CNT)) "9월",
   COUNT(DECODE(MM, '10', CNT)) "10월",
   COUNT(DECODE(MM, '11', CNT)) "11월",
   COUNT(DECODE(MM, '12', CNT)) "12월"
FROM
   (SELECT SUBSTR(PART_DATE, 1,4) YYYY,
   SUBSTR(PART_DATE, 5,2) MM,
   PART,
   CNT
FROM TMP
WHERE PART_DATE BETWEEN :START_DATE AND :END_DATE)
GROUP BY YYYY, PART

-- 일별
WITH TMP AS (
SELECT '20120318' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20120419' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20120517' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20120118' PART_DATE, 'AST' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130719' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130717' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'AST' PART, 2 CNT FROM DUAL)
SELECT PART,
   YYYY "년도",
   MM "월",
   COUNT(DECODE(DD, '01', CNT)) "1일",
   COUNT(DECODE(DD, '02', CNT)) "2일",
   COUNT(DECODE(DD, '03', CNT)) "3일",
   COUNT(DECODE(DD, '04', CNT)) "4일",
   COUNT(DECODE(DD, '05', CNT)) "5일",
   COUNT(DECODE(DD, '06', CNT)) "6일",
   COUNT(DECODE(DD, '07', CNT)) "7일",
   COUNT(DECODE(DD, '08', CNT)) "8일",
   COUNT(DECODE(DD, '09', CNT)) "9일",
   COUNT(DECODE(DD, '10', CNT)) "10일",
   COUNT(DECODE(DD, '11', CNT)) "11일",
   COUNT(DECODE(DD, '12', CNT)) "12일",
   COUNT(DECODE(DD, '13', CNT)) "13일",
   COUNT(DECODE(DD, '14', CNT)) "14일",
   COUNT(DECODE(DD, '15', CNT)) "15일",
   COUNT(DECODE(DD, '16', CNT)) "16일",
   COUNT(DECODE(DD, '17', CNT)) "17일",
   COUNT(DECODE(DD, '18', CNT)) "18일",
   COUNT(DECODE(DD, '19', CNT)) "19일",
   COUNT(DECODE(DD, '20', CNT)) "20일",
   COUNT(DECODE(DD, '21', CNT)) "21일",
   COUNT(DECODE(DD, '22', CNT)) "22일",
   COUNT(DECODE(DD, '23', CNT)) "23일",
   COUNT(DECODE(DD, '24', CNT)) "24일",
   COUNT(DECODE(DD, '25', CNT)) "25일",
   COUNT(DECODE(DD, '26', CNT)) "26일",
   COUNT(DECODE(DD, '27', CNT)) "27일",
   COUNT(DECODE(DD, '28', CNT)) "28일",
   COUNT(DECODE(DD, '29', CNT)) "29일",
   COUNT(DECODE(DD, '30', CNT)) "30일",
   COUNT(DECODE(DD, '31', CNT)) "31일"
FROM
   (SELECT SUBSTR(PART_DATE, 1,4) YYYY,
   SUBSTR(PART_DATE, 5,2) MM,
   SUBSTR(PART_DATE, 7,2) DD,
   PART,
   CNT
FROM TMP
WHERE PART_DATE BETWEEN :START_DATE AND :END_DATE)
GROUP BY YYYY, MM, PART

count를 sum으로 바꾸면 월별 일별 카운트 나옵니다.

by 물개 [2013.07.19 12:17:11]

헐...이런일이;;; 년월까지 정리해주시다니; 깔끔하게 잘 해결 되었습니다.
월별에 관한 '계' 컬럼이 빠졌네요 ㅠ 어찌 처리할 수 있을까요;
감사합니다~~

by 아린 [2013.07.19 13:38:09]

SELECT
........
, COUNT(DECODE(SUBSTR(occurdt,7),'30',1)) d30

, COUNT(DECODE(SUBSTR(occurdt,7),'31',1)) d31

, COUNT(occurdt) 계

by 물개 [2013.07.19 15:47:10]

아린님 답변 감사드립니다 !!!

by 미카엘 [2013.07.19 14:20:21]

-- 월별
WITH TMP AS (
SELECT '20130318' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130419' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130517' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130118' PART_DATE, 'AST' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130719' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130717' PART_DATE, 'AST' PART, 5 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'AST' PART, 2 CNT FROM DUAL)
SELECT PART,
   YYYY "년도",
   NVL(SUM(DECODE(MM, '01', CNT)), 0) "1월",
   NVL(SUM(DECODE(MM, '02', CNT)), 0) "2월",
   NVL(SUM(DECODE(MM, '03', CNT)), 0) "3월",
   NVL(SUM(DECODE(MM, '04', CNT)), 0) "4월",
   NVL(SUM(DECODE(MM, '05', CNT)), 0) "5월",
   NVL(SUM(DECODE(MM, '06', CNT)), 0) "6월",
   NVL(SUM(DECODE(MM, '07', CNT)), 0) "7월",
   NVL(SUM(DECODE(MM, '08', CNT)), 0) "8월",
   NVL(SUM(DECODE(MM, '09', CNT)), 0) "9월",
   NVL(SUM(DECODE(MM, '10', CNT)), 0) "10월",
   NVL(SUM(DECODE(MM, '11', CNT)), 0) "11월",
   NVL(SUM(DECODE(MM, '12', CNT)), 0) "12월",
   NVL(SUM(CNT), 0) "계"
FROM
   (SELECT SUBSTR(PART_DATE, 1,4) YYYY,
   SUBSTR(PART_DATE, 5,2) MM,
   PART,
   CNT
FROM TMP
WHERE PART_DATE BETWEEN :START_DATE AND :END_DATE)
GROUP BY YYYY, PART

-- 일별
WITH TMP AS (
SELECT '20120318' PART_DATE, 'COM' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20120419' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20120517' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20120118' PART_DATE, 'AST' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'COM' PART, 7 CNT FROM DUAL UNION ALL
SELECT '20130719' PART_DATE, 'COM' PART, 2 CNT FROM DUAL UNION ALL
SELECT '20130717' PART_DATE, 'AST' PART, 1 CNT FROM DUAL UNION ALL
SELECT '20130718' PART_DATE, 'AST' PART, 3 CNT FROM DUAL)
SELECT PART,
   YYYY "년도",
   MM "월",
   NVL(SUM(DECODE(DD, '01', CNT)), 0) "1일",
   NVL(SUM(DECODE(DD, '02', CNT)), 0) "2일",
   NVL(SUM(DECODE(DD, '03', CNT)), 0) "3일",
   NVL(SUM(DECODE(DD, '04', CNT)), 0) "4일",
   NVL(SUM(DECODE(DD, '05', CNT)), 0) "5일",
   NVL(SUM(DECODE(DD, '06', CNT)), 0) "6일",
   NVL(SUM(DECODE(DD, '07', CNT)), 0) "7일",
   NVL(SUM(DECODE(DD, '08', CNT)), 0) "8일",
   NVL(SUM(DECODE(DD, '09', CNT)), 0) "9일",
   NVL(SUM(DECODE(DD, '10', CNT)), 0) "10일",
   NVL(SUM(DECODE(DD, '11', CNT)), 0) "11일",
   NVL(SUM(DECODE(DD, '12', CNT)), 0) "12일",
   NVL(SUM(DECODE(DD, '13', CNT)), 0) "13일",
   NVL(SUM(DECODE(DD, '14', CNT)), 0) "14일",
   NVL(SUM(DECODE(DD, '15', CNT)), 0) "15일",
   NVL(SUM(DECODE(DD, '16', CNT)), 0) "16일",
   NVL(SUM(DECODE(DD, '17', CNT)), 0) "17일",
   NVL(SUM(DECODE(DD, '18', CNT)), 0) "18일",
   NVL(SUM(DECODE(DD, '19', CNT)), 0) "19일",
   NVL(SUM(DECODE(DD, '20', CNT)), 0) "20일",
   NVL(SUM(DECODE(DD, '21', CNT)), 0) "21일",
   NVL(SUM(DECODE(DD, '22', CNT)), 0) "22일",
   NVL(SUM(DECODE(DD, '23', CNT)), 0) "23일",
   NVL(SUM(DECODE(DD, '24', CNT)), 0) "24일",
   NVL(SUM(DECODE(DD, '25', CNT)), 0) "25일",
   NVL(SUM(DECODE(DD, '26', CNT)), 0) "26일",
   NVL(SUM(DECODE(DD, '27', CNT)), 0) "27일",
   NVL(SUM(DECODE(DD, '28', CNT)), 0) "28일",
   NVL(SUM(DECODE(DD, '29', CNT)), 0) "29일",
   NVL(SUM(DECODE(DD, '30', CNT)), 0) "30일",
   NVL(SUM(DECODE(DD, '31', CNT)), 0) "31일",
   NVL(SUM(CNT), 0) "계"
FROM
   (SELECT SUBSTR(PART_DATE, 1,4) YYYY,
   SUBSTR(PART_DATE, 5,2) MM,
   SUBSTR(PART_DATE, 7,2) DD,
   PART,
   CNT
FROM TMP
WHERE PART_DATE BETWEEN :START_DATE AND :END_DATE)
GROUP BY YYYY, MM, PART

by 물개 [2013.07.19 15:47:34]

미카엘님 답변 감사합니다!!

댓글등록

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

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

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

데이터베이스 질문과 답변