database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41642 게시물 읽기
No. 41642
통계쿼리 총합, 평균을 구하고 싶습니다..
작성자
k62511(k62511)
작성일
2018-10-15 16:25:38
조회수
192

안녕하세요 통계쿼리를 작성중에있습니다.

아래 쿼리는 기준년월에서부터 지난해 동월까지의

월별 통계쿼리입니다.

여기까지는 어찌어찌 하였는데 여기에 추가로 평균값을 같이 구하고 싶습니다..

 

아래쿼리를 실행하면 이런형식으로 데이터가 조회가 되는데요

17.1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월 '18.1월 16766 17797 22124 22918 26944 31696 31210 30885 22388 13802 19139 20678 20468

 

위 결과에 평균값을 아래와 같이 출력시키고 싶습니다..

조언부탁드립니다..

합     17.1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월 '18.1월 16766 17797 22124 22918 26944 31696 31210 30885 22388 13802 19139 20678 20468

평균 17.1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월 '18.1월 22833 22833 22833 22833 22833 22833 22833 22833 22833 22833 22833 22833 22833

 

 

select 

 ''''||SUBSTR(mm01,3,2)||'.'||TO_NUMBER(SUBSTR(mm01,5,2))||'월' AS mm01,
 DECODE(SUBSTR(mm02,5,2),'01', ''''||SUBSTR(mm02,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm02,5,2))||'월' AS mm02,
 DECODE(SUBSTR(mm03,5,2),'01', ''''||SUBSTR(mm03,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm03,5,2))||'월' AS mm03,
 DECODE(SUBSTR(mm04,5,2),'01', ''''||SUBSTR(mm04,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm04,5,2))||'월' AS mm04,
 DECODE(SUBSTR(mm05,5,2),'01', ''''||SUBSTR(mm05,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm05,5,2))||'월' AS mm05,
 DECODE(SUBSTR(mm06,5,2),'01', ''''||SUBSTR(mm06,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm06,5,2))||'월' AS mm06,
 DECODE(SUBSTR(mm07,5,2),'01', ''''||SUBSTR(mm07,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm07,5,2))||'월' AS mm07,
 DECODE(SUBSTR(mm08,5,2),'01', ''''||SUBSTR(mm08,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm08,5,2))||'월' AS mm08,
 DECODE(SUBSTR(mm09,5,2),'01', ''''||SUBSTR(mm09,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm09,5,2))||'월' AS mm09,
 DECODE(SUBSTR(mm10,5,2),'01', ''''||SUBSTR(mm10,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm10,5,2))||'월' AS mm10,
 DECODE(SUBSTR(mm11,5,2),'01', ''''||SUBSTR(mm11,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm11,5,2))||'월' AS mm11,
 DECODE(SUBSTR(mm12,5,2),'01', ''''||SUBSTR(mm12,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm12,5,2))||'월' AS mm12,
 DECODE(SUBSTR(mm13,5,2),'01', ''''||SUBSTR(mm13,3,2)||'.', '')||TO_NUMBER(SUBSTR(mm13,5,2))||'월' AS mm13,
max(val01) as mm_val01,
max(val02) as mm_val02,
max(val03) as mm_val03,
max(val04) as mm_val04,
max(val05) as mm_val05,
max(val06) as mm_val06,
max(val07) as mm_val07,
max(val08) as mm_val08,
max(val09) as mm_val09,
max(val10) as mm_val10,
max(val11) as mm_val11,
max(val12) as mm_val12,
max(val13) as mm_val13
from(
SELECT 
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM') AS mm01,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM') AS mm02,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-10), 'YYYYMM') AS mm03,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-9), 'YYYYMM') AS mm04,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-8), 'YYYYMM') AS mm05,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-7), 'YYYYMM') AS mm06,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-6), 'YYYYMM') AS mm07,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-5), 'YYYYMM') AS mm08,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-4), 'YYYYMM') AS mm09,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-3), 'YYYYMM') AS mm10,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-2), 'YYYYMM') AS mm11,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-1), 'YYYYMM') AS mm12,
    TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), 0), 'YYYYMM') AS mm13,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM'), count(*)) AS val01,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM'), count(*)) AS val02,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-10), 'YYYYMM'), count(*)) AS val03,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -9), 'YYYYMM'), count(*)) AS val04,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -8), 'YYYYMM'), count(*)) AS val05,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -7), 'YYYYMM'), count(*)) AS val06,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -6), 'YYYYMM'), count(*)) AS val07,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -5), 'YYYYMM'), count(*)) AS val08,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -4), 'YYYYMM'), count(*)) AS val09,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -3), 'YYYYMM'), count(*)) AS val10,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -2), 'YYYYMM'), count(*)) AS val11,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -1), 'YYYYMM'), count(*)) AS val12,
    DECODE(SUBSTR(acc_ymd,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),  0), 'YYYYMM'), count(*)) AS val13
FROM  tbl1
WHERE SUBSTR(acc_ymd,1,6) >= TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM') AND SUBSTR(acc_ymd,1,6) <= '201801'
GROUP BY SUBSTR (acc_ymd, 1, 6)
order by SUBSTR (acc_ymd, 1, 6)
)
group by mm01,mm02,mm03,mm04,mm05,mm06,mm07,mm08,mm09,mm10,mm11,mm12,mm13

 

 

이 글에 대한 댓글이 총 6건 있습니다.

평균의 의미가 뭔가요?
  13개월치 월별 count 에 대한 전체 평균인가요?
  왜? 한줄 추가해서 똑같은 값을 13번이나 보여줘야 하죠?
  그냥 컬럼 하나 추가해서 한번만 보여주면 될 것 같은데요?

마농(manon94)님이 2018-10-16 14:01:21에 작성한 댓글입니다.

네 13개월치의 각각의 count값이 있고 그값들의 평균값을 구하려고 합니다..

이게 한줄을 더뿌려야하는 이유가 해당 조회값을 레포팅툴에 같이 연동하여 사용하려고 하다보니

한줄을 더 출력하는 방식으로해야 정상적으로 출력이 되어 한줄을 더 추가하려고 구성중입니다..

 

 

 

k62511(k62511)님이 2018-10-16 17:41:57에 작성한 댓글입니다.

WITH tbl1 AS
(
-- test sample --
SELECT '20161201' acc_ymd FROM dual
UNION ALL SELECT '20170101' FROM dual
UNION ALL SELECT '20170201' FROM dual
UNION ALL SELECT '20170301' FROM dual
UNION ALL SELECT '20170401' FROM dual
UNION ALL SELECT '20170501' FROM dual
UNION ALL SELECT '20170601' FROM dual
UNION ALL SELECT '20170701' FROM dual
UNION ALL SELECT '20170801' FROM dual
UNION ALL SELECT '20170901' FROM dual
UNION ALL SELECT '20171001' FROM dual
UNION ALL SELECT '20171101' FROM dual
UNION ALL SELECT '20171201' FROM dual
UNION ALL SELECT '20171201' FROM dual
UNION ALL SELECT '20171201' FROM dual
UNION ALL SELECT '20180101' FROM dual
UNION ALL SELECT '20180201' FROM dual
)
SELECT gb
     , MIN(DECODE(mm, 01, ym)) mm01
     , MIN(DECODE(mm, 02, ym)) mm02
     , MIN(DECODE(mm, 03, ym)) mm03
     , MIN(DECODE(mm, 04, ym)) mm04
     , MIN(DECODE(mm, 05, ym)) mm05
     , MIN(DECODE(mm, 06, ym)) mm06
     , MIN(DECODE(mm, 07, ym)) mm07
     , MIN(DECODE(mm, 08, ym)) mm08
     , MIN(DECODE(mm, 09, ym)) mm09
     , MIN(DECODE(mm, 10, ym)) mm10
     , MIN(DECODE(mm, 11, ym)) mm11
     , MIN(DECODE(mm, 12, ym)) mm12
     , MIN(DECODE(mm, 13, ym)) mm13
     , MIN(DECODE(mm, 01, vv)) vv01
     , MIN(DECODE(mm, 02, vv)) vv02
     , MIN(DECODE(mm, 03, vv)) vv03
     , MIN(DECODE(mm, 04, vv)) vv04
     , MIN(DECODE(mm, 05, vv)) vv05
     , MIN(DECODE(mm, 06, vv)) vv06
     , MIN(DECODE(mm, 07, vv)) vv07
     , MIN(DECODE(mm, 08, vv)) vv08
     , MIN(DECODE(mm, 09, vv)) vv09
     , MIN(DECODE(mm, 10, vv)) vv10
     , MIN(DECODE(mm, 11, vv)) vv11
     , MIN(DECODE(mm, 12, vv)) vv12
     , MIN(DECODE(mm, 13, vv)) vv13
  FROM (SELECT mm
             , CASE WHEN mm = 1 OR m = '1' THEN y END || m || '월' ym
             , COUNT(*) cnt
             , ROUND(AVG(COUNT(*)) OVER()) avg
          FROM (SELECT SUBSTR(acc_ymd, 3, 2) || '.' y
                     , LTRIM(SUBSTR(acc_ymd, 5, 2), '0') m
                     , 13 - MONTHS_BETWEEN(TO_DATE('201801', 'yyyymm')
                            , TO_DATE(SUBSTR(acc_ymd, 1, 6), 'yyyymm')) mm
                  FROM tbl1
                 WHERE acc_ymd >= ('201801' - 100) || '01'
                   AND acc_ymd <= '201801'         || '31'
                )
         GROUP BY mm, y, m
        )
 UNPIVOT (vv FOR gb IN (cnt AS '합', avg AS '평균'))
 GROUP BY gb
;

마농(manon94)님이 2018-10-16 20:48:32에 작성한 댓글입니다.

마농님 답변 감사드립니다.

그런데 oracle 10g라 pivot 기능이 없네요..

혹시 괜찮으시면 10g용으로 하나만 더 부탁드려도 될까요..? ㅠㅠ

 

k62511(k62511)님이 2018-10-18 10:10:58에 작성한 댓글입니다.

SELECT gb
     , MIN(DECODE(mm, 01, ym)) mm01
     , MIN(DECODE(mm, 02, ym)) mm02
     , MIN(DECODE(mm, 03, ym)) mm03
     , MIN(DECODE(mm, 04, ym)) mm04
     , MIN(DECODE(mm, 05, ym)) mm05
     , MIN(DECODE(mm, 06, ym)) mm06
     , MIN(DECODE(mm, 07, ym)) mm07
     , MIN(DECODE(mm, 08, ym)) mm08
     , MIN(DECODE(mm, 09, ym)) mm09
     , MIN(DECODE(mm, 10, ym)) mm10
     , MIN(DECODE(mm, 11, ym)) mm11
     , MIN(DECODE(mm, 12, ym)) mm12
     , MIN(DECODE(mm, 13, ym)) mm13
     , MIN(DECODE(mm, 01, vv)) vv01
     , MIN(DECODE(mm, 02, vv)) vv02
     , MIN(DECODE(mm, 03, vv)) vv03
     , MIN(DECODE(mm, 04, vv)) vv04
     , MIN(DECODE(mm, 05, vv)) vv05
     , MIN(DECODE(mm, 06, vv)) vv06
     , MIN(DECODE(mm, 07, vv)) vv07
     , MIN(DECODE(mm, 08, vv)) vv08
     , MIN(DECODE(mm, 09, vv)) vv09
     , MIN(DECODE(mm, 10, vv)) vv10
     , MIN(DECODE(mm, 11, vv)) vv11
     , MIN(DECODE(mm, 12, vv)) vv12
     , MIN(DECODE(mm, 13, vv)) vv13
  FROM (SELECT mm
             , CASE WHEN mm = 1 OR m = '1' THEN y END || m || '월' ym
             , DECODE(GROUPING(1), 0, '합', '평균') gb
             , DECODE(GROUPING(1)
               , 0, COUNT(*)
               , 1, ROUND(AVG(COUNT(*)) OVER())
               ) vv
          FROM (SELECT SUBSTR(acc_ymd, 3, 2) || '.' y
                     , LTRIM(SUBSTR(acc_ymd, 5, 2), '0') m
                     , 13 - MONTHS_BETWEEN(TO_DATE('201801', 'yyyymm')
                            , TO_DATE(SUBSTR(acc_ymd, 1, 6), 'yyyymm')) mm
                  FROM tbl1
                 WHERE acc_ymd >= ('201801' - 100) || '01'
                   AND acc_ymd <= '201801'         || '31'
                )
         GROUP BY mm, y, m, ROLLUP(1)
        )
 GROUP BY gb
;

마농(manon94)님이 2018-10-18 10:56:15에 작성한 댓글입니다.

감사합니다 마농님

덕분에 완성하였습니다!

확인이 늦었지만 너무너무 감사드립니다!

k62511(k62511)님이 2018-10-23 16:42:28에 작성한 댓글입니다.
이 댓글은 2018-10-23 16:42:53에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41645SE 와 EE 차이점 [1]
이상우
2018-10-23
121
41644동일 sql문장이 was에서 수행시 느려지는 이유가 있을까요? [2]
웹개발자
2018-10-23
107
416431분,10분,1시간,7일 데이터 select [1]
김민수
2018-10-22
115
41642통계쿼리 총합, 평균을 구하고 싶습니다.. [6]
k62511
2018-10-15
192
41641무결성 제약 조건 [1]
kim
2018-10-12
146
41640쿼리질문합니다 [1]
초보
2018-10-05
181
41639NULL값과 특정값을 같이 조회하고 싶습니다 [1]
노진규
2018-10-02
199
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.082초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다