oracle 기간별 통계 쿼리

Question

oracle DB 통계 쿼리를 알려주세요

table

seq| date| service | service_value

1 | 20111101140101 | U0100 | 입력값

이런 형태의 테이블이고 date는 년 월 일 시 분 초(yyyymmddHHMMss) 구조에요

일자별 월별 년별 통계를 service 분류별로 통계를 소계, 총계로 내고 싶어요 도와주세요ㅜㅜ

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

추가

date는 varchar2에요'ㅡ';; 타입을 빼먹었네요;;

seq number

date varchar2

service varchar2

service_value varchar2 입니다(--)(__)(--)

#oracle 기간별 통계

Accepted Answer

일자,월,년별 통계네요.

조금만 바꿔서 생각해보시면 쉽게 해결됩니다.

단계1) 원시데이터 추출/가공

SELECT seq

      ,date

      ,service

      ,substr(date,1,4) as year_gbn

      ,substr(date,1,6) as month_gbn

      ,substr(date,1,8) as day_gbn

      ,service_value

FROM 테이블명

예상결과)

seq date service year_gbn month_gbn day_gbn service_value

1 20110101010101 U0100 2011 201101 20110101 1

2 20110201010101 U0100 2011 201102 20110201 3

3 20110501010101 U0100 2011 201105 20110501 4

4 20110601010101 U0100 2011 201106 20110601 5

5 20110701010101 U0100 2011 201107 20110701 2

6 20111001010101 U0100 2011 201110 20111001 1

7 20111101010101 U0100 2011 201111 20111101 2

8 20111102010101 U0100 2011 201111 20111102 5

9 20111103010101 U0100 2011 201111 20111103 6

10 20111104010101 U0100 2011 201111 20111104 3

… … … … … … …

예상결과를 보시면 기존의 정보로 3개의 기준정보를 생성하였습니다.

year_gbn, month_gbn, day_gbn입니다.

자세히 보시면 포함관계가 보이실겁니다.

그러면 이 3개의 값들을 가지고 통계질의를 만들면 되겠지요?

단계2) 통계질의 만들기

굉장히 많은 방법이 있습니다.

아주 오랜전부터 통용되어 온 COPY_T 방식의 테이블 복제부터

카테시안조인, ROLLUP 집계함수 등이 그것입니다.

또 각각의 방법을 활용하여 만들때에도 수많은 테크닉이 활용됩니다.

테이블 복제 방법)

SELECT service

      ,year_gbn

      ,month_gbn

      ,day_gbn

      ,service_value

      ,no

FROM (

       SELECT service

             ,substr(date,1,4) as year_gbn

           ,substr(date,1,6) as month_gbn

             ,substr(date,1,8) as day_gbn

             ,service_value

       FROM 테이블명

     ) a

    ,(

      SELECT rownum as no

       FROM DUAL

       WHERE level < 4

     ) b

결과는 어떨까요?
앞의 결과에 no가 추가되겠지요.
그런데 어떤가요...
똑같은 결과가 3번 반복도히겠지요.
바로 ( SELECT rownum as no FROM DUAL WHERE level < 4 ) b 이부분 때문입니다.
왜 똑같은 결과를 3개씩표현 하려고 했을까요?
바로 하나의 Record 정보를 년/월/일별로 불리하기 위해서 입니다.

그러면 no 기준으로 재정의해볼까요.
SELECT service
      ,decode( no , 1 , year_gbn
                  , 2 , month_gbn
                  , 3 , day_gbn ) as gubun
      ,service_value
FROM (
       SELECT service
             ,substr(date,1,4) as year_gbn
             ,substr(date,1,6) as month_gbn
             ,substr(date,1,8) as day_gbn
             ,service_value
       FROM 테이블명
     ) a
    ,( SELECT rownum as no FROM DUAL WHERE level < 4 ) b

예상결과)

service gubun service_value

U0100 2011 1

U0100 201101 1

U0100 20110101 1

U0100 2011 3

U0100 201102 3

U0100 20110201 3

U0100 2011 4

U0100 201105 4

U0100 20110501 4

U0100 2011 5

U0100 201106 5

U0100 20110601 5

… … …

새로운 gubun이란 이름으로 컬럼이 정의되었습니다.

붉은색 부분이 컬럼입니다. 그렇게 안보이시지요?

그래서 gubun이란 이름으로 alias 처리한 것입니다.

그럼 결과를 만들어 볼까요.

여기서 잠깐. 다시한번 말씀드리지만

decode( no , 1 , year_gbn
           , 2 , month_gbn
           , 3 , day_gbn )

이게 컬럼입니다.

SELECT service
      ,decode( no , 1 , year_gbn
                  , 2 , month_gbn
                  , 3 , day_gbn ) as gubun
      ,sum(service_value) as summary_servicce
FROM (
       SELECT service
             ,substr(date,1,4) as year_gbn
             ,substr(date,1,6) as month_gbn
             ,substr(date,1,8) as day_gbn
             ,service_value
       FROM 테이블명
     ) a
    ,( SELECT rownum as no FROM DUAL WHERE level < 4 ) b
GROUP BY service
        ,decode( no , 1 , year_gbn
                    , 2 , month_gbn
                    , 3 , day_gbn )

결과가 어떻게 나올까요.

service gubun service_value

U0100 2011 32

U0100 201101 1

U0100 201102 1

U0100 201105 3

U0100 201106 3

U0100 201107 4

U0100 201110 4

U0100 201111 16

U0100 20110601 5

U0100 20110101 1

U0100 20110201 3

U0100 20110501 4

U0100 20110601 5

U0100 20110701 2

U0100 20111001 1

U0100 20111101 2

U0100 20111102 5

U0100 20111103 6

U0100 20111104 3

물론 데이터를 첨에 정의한 것만으로 가정하고 숫자를 표현하였습니다.

실제로는 더 많겠지요.

조금 응용하면 다음도 가능하겠지요?

service | 년도 | 1월 | 2월 | 3월 | 4월 | 5월 | 6월 | 7월 | 8월 | 9월 | 10월 | 11월 | 12월

그러면 더 쉬운 방법도 있겠지요?

ROLLUP 집계함수)

SELECT service
      ,year_gbn
      ,month_gbn
      ,day_gbn
      ,sum(service_value) as summary_servicce
FROM (
       SELECT service
             ,substr(date,1,4) as year_gbn
             ,substr(date,1,6) as month_gbn
             ,substr(date,1,8) as day_gbn
             ,service_value
       FROM 테이블명
     ) a
GROUP BY ROLLUP(service,year_gbn,month_gbn,day_gbn)

이러면 어떻게 나올까요.

계층모델로 생각하시면 됩니다.

즉, service > year_gbn > month_gbn > day_gbn

즉 day_gbn은 그대로 sum 그리고 그합은 다음상위 계층인 month_gbn으로 sum

이런식은 처리됩니다.

바로 첨에 짰던 쿼리를 오라클이 대신 수행한다고 보시면 됩니다.

Answer

그룹함수 ROLLUP 이나 GROUPING SETS 을 사용하시면 됩니다.

서비스 분류별로 일자별 소계 합계를 구하는 쿼리 샘플입니다.

질의에 맞게 수정해보세요.

--GROUPING SETS

SELECT CASE
          WHEN grp01 = 3 THEN '총계'
          WHEN grp01 = 1 THEN ''
          ELSE service
       END AS service,
       CASE
          WHEN grp01 = 1 THEN '소계'
          ELSE dt
       END AS dt,
       service_value
FROM   (SELECT   GROUPING_ID(service, dt) AS grp01,
                 GROUPING_ID(service) AS grp02,
                 service,
                 dt,
                 SUM(service_value) AS service_value
        FROM     (SELECT service,
                         TO_CHAR(dt, 'yyyy-mm-dd') AS dt,
                         service_value
                  FROM   (SELECT SYSDATE AS dt,
'001' AS service,
1 AS service_value
FROM DUAL
UNION ALL
SELECT SYSDATE AS dt,
'002' AS service,
1 AS service_value
FROM DUAL
UNION ALL
SELECT SYSDATE - 1 AS dt,
'001' AS service,
2 AS service_value
FROM DUAL
UNION ALL
SELECT SYSDATE - 1 AS dt,
'002' AS service,
5 AS service_value
FROM DUAL) ) a
        GROUP BY GROUPING SETS( (service, dt),(service), () ) ) a

--ROLLUP

SELECT CASE
          WHEN service IS NULL
       AND    dt IS NULL THEN '총계'
          ELSE service
       END AS service,
       CASE
          WHEN service IS NOT NULL
       AND    dt IS NULL THEN '소계'
          ELSE dt
       END AS dt,
       service_value
FROM   (SELECT   service,
                 dt,
                 SUM(service_value) AS service_value
        FROM     (SELECT service,
                         TO_CHAR(dt, 'yyyy-mm-dd') AS dt,
                         service_value
                  FROM   (SELECT SYSDATE AS dt,
'001' AS service,
1 AS service_value
FROM DUAL
UNION ALL
SELECT SYSDATE AS dt,
'002' AS service,
1 AS service_value
FROM DUAL
UNION ALL
SELECT SYSDATE - 1 AS dt,
'001' AS service,
2 AS service_value
FROM DUAL
UNION ALL
SELECT SYSDATE - 1 AS dt,
'002' AS service,
5 AS service_value
FROM DUAL) )
        GROUP BY ROLLUP(service, dt) ) a

oracle 기간별 통계 쿼리

oracle 기간별 통계 쿼리

IT전문가를 위한 액셋스 소개

IT전문가를 위한 액세스(엑세스-access)

seq	date	service	year_gbn	month_gbn	day_gbn	service_value
1	20110101010101	U0100	2011	201101	20110101	1
2	20110201010101	U0100	2011	201102	20110201	3
3	20110501010101	U0100	2011	201105	20110501	4
4	20110601010101	U0100	2011	201106	20110601	5
5	20110701010101	U0100	2011	201107	20110701	2
6	20111001010101	U0100	2011	201110	20111001	1
7	20111101010101	U0100	2011	201111	20111101	2
8	20111102010101	U0100	2011	201111	20111102	5
9	20111103010101	U0100	2011	201111	20111103	6
10	20111104010101	U0100	2011	201111	20111104	3
…	…	…	…	…	…	…

service	gubun	service_value
U0100	2011	1
U0100	201101	1
U0100	20110101	1
U0100	2011	3
U0100	201102	3
U0100	20110201	3
U0100	2011	4
U0100	201105	4
U0100	20110501	4
U0100	2011	5
U0100	201106	5
U0100	20110601	5
…	…	…