[Oracle] Query : 각 회사에 달성건수에 해당하는 지급금액을 계산

예전에 같이 일하던 실장님의 도움 요청으로 잠깐. 건드려 본 Query.
각 회사에 달성건수에 해당하는 지급금액을 계산하는 것..

흠.. 일단, Table을 변경할 수 없어, 저런 Query가 나왔지만...
언뜻 보기에도.. TEST_BASE table을 두개로 나누어.. 사용하는게..
맞는 DB설계가 아닌지.. 하는 생각을 잠시 해보며..

더 좋은 Query가 있음 리플 달아주시길...
----------------------------------------------------------------------------

----------------------------------------------------------------------------
기준 Table
----------------------------------------------------------------------------
CREATE TABLE SYSTEM.TEST_BASE
(
  CO_ID        VARCHAR2(1 BYTE)                 NOT NULL,          /*회사코드*/
  PAYMENT_AMT  NUMBER,                                             /*지급액*/
  H_CNT        NUMBER,                                             /*목표건수*/
  S_DATE       VARCHAR2(8 BYTE)                 NOT NULL,          /*기준시작일*/
  E_DATE       VARCHAR2(8 BYTE)                 NOT NULL,          /*기준종료일*/
  S_CNT        NUMBER,                                             /*달성시작건수*/
  E_CNT        NUMBER,                                             /*달성종료건수*/
  RATE         NUMBER                                              /*지급율*/
)
TABLESPACE SYSTEM
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

ALTER TABLE SYSTEM.TEST_BASE ADD (
  CONSTRAINT PK_TEST_BASE
 PRIMARY KEY
 (CO_ID, S_DATE, E_DATE, S_CNT)
    USING INDEX
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
----------------------------------------------------------------------------
a 50000 100 20090701 20090715   1   89  90
a 50000 100 20090701 20090715  90   99 100
a 50000 100 20090701 20090715 100  199 150
a 50000 100 20090701 20090715 200 9999 300
a 30000  80 20090716 20090731   1   79  50
a 30000  80 20090716 20090731  80   89 100
a 30000  80 20090716 20090731  90   99 150
a 30000  80 20090716 20090731 100 9999 300
----------------------------------------------------------------------------

             
----------------------------------------------------------------------------
Data Table
----------------------------------------------------------------------------
CREATE TABLE TEST_DATA1
(
  co_id         varchar2(1) not null,          /*회사코드*/
  p_date        varchar2(8) not null,          /*달성일*/
  cnt           number                         /*달성건수*/
 
)
TABLESPACE SYSTEM
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

ALTER TABLE TEST_DATA1 ADD (
  CONSTRAINT PK_TEST_DATA1
 PRIMARY KEY
 (co_id, p_date)
    USING INDEX
    TABLESPACE SYSTEM
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
----------------------------------------------------------------------------
a 20090701 10
a 20090705 40
a 20090711 30
a 20090713 50
a 20090716 10
a 20090717 20
a 20090718  3
a 20090721 50
----------------------------------------------------------------------------

----------------------------------------------------------------------------
원하는 결과값
----------------------------------------------------------------------------
co_id 지급금액
a 105,000

----------------------------------------------------------------------------
Query
----------------------------------------------------------------------------
select ta.co_id, sum( payment_amt * ( ta.rate / 100 ) )
from   test_base ta,
       (
            select  a.co_id,
                    b.s_date,
                    b.e_date,
                    sum(( case when a.cnt >= s_cnt and a.cnt <= e_cnt then a.cnt else 0 end )) sum_cnt
            from    test_data1 a,
                    test_base b
            where   a.co_id = b.co_id
            and     a.p_date between b.s_date and b.e_date
            group by a.co_id,
                    b.s_date,
                    b.e_date
        ) td
where   ta.co_id = td.co_id
and     ta.s_date = td.s_date
and     ta.e_date = td.e_date
and     td.sum_cnt between ta.s_cnt and ta.e_cnt
group by ta.co_id