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 41600 게시물 읽기
No. 41600
월별 통계쿼리 질문드립니다
작성자
k62511(k62511)
작성일
2018-07-20 08:54ⓒ
2018-07-20 08:57ⓜ
조회수
4,151

기준월을 지정하면 그달부터 전년도 동월까지 1년치의 통계를 뽑는 쿼리를 작성하고있습니다.

아래 쿼리로 작성중인데요


 SELECT    

   CASE

        WHEN COL01 IN ('01001', '01003') THEN '2번'

        WHEN COL01 IN ('02001') THEN '1번'

        WHEN COL01 IN ('02002', '02003') THEN '3번'

        ELSE '4번'

    END

    COL01,

    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(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM'), count(*)) AS val01,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM'), count(*)) AS val02,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-10), 'YYYYMM'), count(*)) AS val03,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-9), 'YYYYMM'), count(*)) AS val04,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -8), 'YYYYMM'), count(*)) AS val05,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -7), 'YYYYMM'), count(*)) AS val06,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -6), 'YYYYMM'), count(*)) AS val07,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -5), 'YYYYMM'), count(*)) AS val08,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -4), 'YYYYMM'), count(*)) AS val09,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -3), 'YYYYMM'), count(*)) AS val10,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -2), 'YYYYMM'), count(*)) AS val11,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), -1), 'YYYYMM'), count(*)) AS val12,

    DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'), 0), 'YYYYMM'), count(*)) AS val13

FROM  TESTTBL

WHERE SUBSTR(COL02,1,6) >= TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM')

AND SUBSTR(COL02,1,6) <= '201801'

GROUP BY  

    CASE

        WHEN COL01 IN ('01001', '01003') THEN '2번'

        WHEN COL01 IN ('02001') THEN '1번'

        WHEN COL01 IN ('02002', '02003') THEN '3번'

        ELSE '4번'

    END, SUBSTR(COL02,1,6)

order by COL01

 

쿼리실행시 아래같이 나오는데 1달에 1row씩 4가지패턴으로 52row가 출력되는데요..

 

 

1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 5854                        
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801   5691                      
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801     8140                    
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801       9354                  
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801         11365                
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801           15816              
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801             16038            
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801               16055          
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                 9435        
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                   4649      
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                     7293    
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                       8998  
1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                         10104
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 1319                        
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801   1308                      
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801     1539                    
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801       1739                  
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801         2083                
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801           2337              
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801             2382            
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801               2260          
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                 1792        
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                   1189      
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                     1438    
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                       1329  
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                         1387
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 4501                        
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801   5234                      
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801     6994                    
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801       7283                  
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801         7716                
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801           8148              
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801             8009            
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801               8075          
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                 7162        
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                   4740      
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                     5169    
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                       5373  
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                         4496
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 5092                        
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801   5564                      
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801     5451                    
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801       4542                  
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801         5780                
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801           5395              
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801             4781            
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801               4495          
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                 3999        
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                   3224      
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                     5239    
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                       4978  
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801                         4481

 

 

아래형식으로 공백을 제거하여 위쪽으로 몰아서 출력시키고 싶은데 당최 감이오질않습니다..

어떻게 해야 위로 몰수있을까요..

 

 

1번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 5854 5691 8140 9354 11365 15816 16038 16055 9435 4649 7293 8998 10104
2번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 1319 1308 1539 1739 2083 2337 2382 2260 1792 1189 1438 1329 1387
3번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 4501 5234 6994 7283 7716 8148 8009 8075 7162 4740 5169 5373 4496
4번 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 5092 5564 5451 4542 5780 5395 4781 4495 3999 3224 5239 4978 4481

 

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

아래 표에 보시면

COL01 번별로 하나의 값이 존재함으로 해당 값을 MAX로 하면 결과값만 올라옵니다.. 

 

  MAX(TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM') ) AS mm01,

  MAX(TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM')) AS mm02,

 

MAX(DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-12), 'YYYYMM'), count(*))) AS val01,

MAX(DECODE(SUBSTR(COL02,1,6), TO_CHAR(ADD_MONTHS(TO_DATE('201801','YYYYMM'),-11), 'YYYYMM'), count(*))) AS val02,

...

 

GROUP BY 

CASE

        WHEN COL01 IN ('01001', '01003') THEN '2번'

        WHEN COL01 IN ('02001') THEN '1번'

        WHEN COL01 IN ('02002', '02003') THEN '3번'

        ELSE '4번'

    END

정준철(jjclove)님이 2018-07-22 18:38에 작성한 댓글입니다.
이 댓글은 2018-07-22 18:40에 마지막으로 수정되었습니다.

○ 그룹바이에 연월이 들어 있어서 그래요.
  1. 그룹바이에서 연월을 빼시고
  2. DECODE(COUNT()) 를 COUNT(DECODE()) 로 바꾸셔야 합니다.
○ 쿼리에 비효율이 있네요.
  1. 조건절에서 컬럼가공하지 마세요.
    - 변경전 : SUBSTR(col02, 1, 6) <= '201801'
    - 변경후 : col02 <= '201801' || '31'
○ 쿼리를 간결하게
  1. 인라인뷰 활용 : 반복 구문 간결하게
  2. Months_Between 활용


SELECT gb
     , TO_CHAR(ADD_MONTHS(mm13, -12), 'yyyymm') mm01
     , TO_CHAR(ADD_MONTHS(mm13, -11), 'yyyymm') mm02
     , TO_CHAR(ADD_MONTHS(mm13, -10), 'yyyymm') mm03
     , TO_CHAR(ADD_MONTHS(mm13,  -9), 'yyyymm') mm04
     , TO_CHAR(ADD_MONTHS(mm13,  -8), 'yyyymm') mm05
     , TO_CHAR(ADD_MONTHS(mm13,  -7), 'yyyymm') mm06
     , TO_CHAR(ADD_MONTHS(mm13,  -6), 'yyyymm') mm07
     , TO_CHAR(ADD_MONTHS(mm13,  -5), 'yyyymm') mm08
     , TO_CHAR(ADD_MONTHS(mm13,  -4), 'yyyymm') mm09
     , TO_CHAR(ADD_MONTHS(mm13,  -3), 'yyyymm') mm10
     , TO_CHAR(ADD_MONTHS(mm13,  -2), 'yyyymm') mm11
     , TO_CHAR(ADD_MONTHS(mm13,  -1), 'yyyymm') mm12
     , TO_CHAR(ADD_MONTHS(mm13,   0), 'yyyymm') mm13
     , COUNT(DECODE(mm, 12, 1)) cnt01
     , COUNT(DECODE(mm, 11, 1)) cnt02
     , COUNT(DECODE(mm, 10, 1)) cnt03
     , COUNT(DECODE(mm,  9, 1)) cnt04
     , COUNT(DECODE(mm,  8, 1)) cnt05
     , COUNT(DECODE(mm,  7, 1)) cnt06
     , COUNT(DECODE(mm,  6, 1)) cnt07
     , COUNT(DECODE(mm,  5, 1)) cnt08
     , COUNT(DECODE(mm,  4, 1)) cnt09
     , COUNT(DECODE(mm,  3, 1)) cnt10
     , COUNT(DECODE(mm,  2, 1)) cnt11
     , COUNT(DECODE(mm,  1, 1)) cnt12
     , COUNT(DECODE(mm,  0, 1)) cnt13
  FROM (SELECT CASE
               WHEN COL01 IN ('01001', '01003') THEN '2번'
               WHEN COL01 IN ('02001'         ) THEN '1번'
               WHEN COL01 IN ('02002', '02003') THEN '3번'
               ELSE '4번' END AS gb
             , TO_DATE('201801', 'yyyymm') mm13
             , MONTHS_BETWEEN( TO_DATE('201801', 'yyyymm')
                             , TO_DATE(SUBSTR(col02, 1, 6), 'yyyymm')
                             ) mm
          FROM testtbl
         WHERE col02 >= TO_CHAR(
                        ADD_MONTHS(TO_DATE('201801', 'yyyymm'), -12)
                        , 'yyyymmdd')
           AND col02 <= '201801' || '31'
        )
 GROUP BY gb, mm13
 ORDER BY gb
;

마농(manon94)님이 2018-07-23 09:40에 작성한 댓글입니다.
이 댓글은 2018-07-23 09:43에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41607DBA 입문자입니다. 실행계획 관련 질문 부탁드리겠습니다.
DBA개발자
2018-07-29
3930
41603테이블 설계 모델링 관련 질문입니다.(유니크, PK 관련) [2]
조동건
2018-07-28
3967
41601날짜 yyyymmdd 문의 [1]
남제
2018-07-26
4135
41600월별 통계쿼리 질문드립니다 [2]
k62511
2018-07-20
4151
41597오라클 12C 설정하다가 질문드립니다. [2]
신승익
2018-07-17
4078
41596안녕하세요 쿼리 질문드려도될까요? [1]
lovepoem
2018-07-11
4847
41595쿼리 문의 드려요...ㅜㅜ [2]
왕초보
2018-07-10
4105
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다