최대값과 해당해의 다른 값 가져오기

조건.
    아래와 같은 TABLE과 DATA가 있다.

    열 이름                        널?      유형
    ------------------------------ -------- ----
    YYMM_YM                        NOT NULL VARCHAR2(6)
    ITEM_CD                        NOT NULL VARCHAR2(20)
    BUDGET_CD                      NOT NULL VARCHAR2(8)
    PROD_AM                                 NUMBER

    년월   제품                 항목     금액      
    ------ -------------------- -------- ----------
    199711 C2-                  62099011  197557081
    199711 C2-                  62099101 1.9756E+13
    199711 C3-                  62099011  216227309
    199711 C3-                  62099101 2.1623E+13
    199711 MC4                  62099011  334920377
    199711 MC4                  62099101 3.3492E+13
    199712 C1                   62099011  528.04224
    199712 C1                   62099101   52804224
    199712 C2-                  62099011  1185.0948
    199712 C2-                  62099101  118509480
    199712 C3-                  62099011  2486.8656
    199712 C3-                  62099101  248686560
    199712 MC4                  62099011 3837.30696
    199712 MC4                  62099101  383730696
    199801 C1                   62099011  528.04224
    199801 C1                   62099101   52804224
    199801 C2-                  62099011  1185.0948
    199801 C2-                  62099101  118509480
    199801 C3-                  62099011  2486.8656
    199801 C3-                  62099101  248686560
    199801 MC4                  62099011 3837.30696
    199801 MC4                  62099101  383730696
    199802 C2-                  62099011  197557081
    199802 C2-                  62099101 1.9756E+13
    199802 C3-                  62099011  216227309
    199802 C3-                  62099101 2.1623E+13
    199802 MC4                  62099011  334920377
    199802 MC4                  62099101 3.3492E+13
    28 행이 선택되었습니다

    항목 은  62099011:매출수량 , 62099101:총원가 이다.

문제.
    제품별로 1998년 03월보다 작은 월의 12개월치 DATA를 이용해서
    1998년 03월 의 매출수량별 총원가를 구하기 위한
    제품별 단위당변동원가 와 고정원가를 구하라.

                              
    단위당변동원가 = (최고판매량의원가 - 최저판매량의원가)/
                     (최고판매량       - 최저판매량)          
    
    고정원가       = 최고판매량원가 - (단위당변동원가*최고판매량)

    과 같은 방식으로 구한다.
    매달 제품별로 매출수량은 '62099011' 항목으로 DATA가 들어오고
                  총원가는   '62099101' 항목으로 DATA가 들어온다.
    최고 판매량의 원가량 12개월중 해당제품의 판매량이 최고인 월의
    총원가를 의미한다.

생각.
    간단히 말해서 이전 12개월분의 DATA를 이용해 추정을 위한
    단위당 변동원가와 고정원가를 구하자는 문제다.
    엿보기1.먼저 월별/제품별로 판매량과 총원가를 분리하는 작업이 필요하다.
            월/제품/판매량/총원가 순으로 DATA가 나와야 한다.
    엿보기2.그 다음이
            제품
            1년치중 최소판매량
            최소판매월의총원가
            1년치중 최대판매량
            최대판매월의총원가
            의 정보를 추출해야한다.
    엿보기3.엿보기2까지 했으면 그정보를 식에 대입하면된다.

해법.
    엿보기2 단계에서 제품별로 최소판매량과 최대판매량을 구하기위해서
    제품별로 GROUP BY를 한다.
    이때 총원가도 MIN,MAX 함수를 적용하게 되면 우리가 의도하는 값이 아니다.
    총판매량이 최대이고 최소라고해도 그원가마저 1년중 최소가되고 최대가
    되라는 보장은 없다.
    다시말해서 최대판매량의 월과 최소총원가의월은 다를 수 있고
    최대도 마찬가지란 말이다.
    우리가 원하는것은 최소판매량의 월에 있는 원가가 1년중 최소이든 아니든
    그 원가를 가져와야하고 최대도 마찬가지이다.
    단계1.먼저 월별/제품별로 판매량과 총원가를 분리하는 작업을 하자.

           SELECT YYMM_YM,
                  ITEM_CD,
                  ROUND(SUM(DECODE(BUDGET_CD,'62099011',PROD_AM))) Q,
                  ROUND(SUM(DECODE(BUDGET_CD,'62099101',PROD_AM))) C
           FROM   TEST17
           WHERE  YYMM_YM >=  TO_CHAR(TO_NUMBER(SUBSTR('199803',1,4)) - 1)
                                || SUBSTR('199803',5,2)
           AND    YYMM_YM <   '199803'
           GROUP BY
                  YYMM_YM,
                  ITEM_CD

          조건은 입력월을 기준으로 1년전부터 바로 전월의 DATA를
          월별/제품별로 가져오겠단얘기다.
          항목의 분리는 DECODE를 이용했다.
      
          DECODE로분류하면 다음과 같다.

           SELECT YYMM_YM 년월,
                  ITEM_CD 제품,
                  ROUND(DECODE(BUDGET_CD,'62099011',PROD_AM)) Q,
                  ROUND(DECODE(BUDGET_CD,'62099101',PROD_AM)) C
           FROM   TEST17
           WHERE  YYMM_YM >=  TO_CHAR(TO_NUMBER(SUBSTR('199803',1,4)) - 1)
                                || SUBSTR('199803',5,2)
           AND    YYMM_YM <   '199803'

          결과는

           년월   제품                 Q          C        
           ------ -------------------- ---------- ----------
           199711 C2-                   197557081          
           199711 C2-                             1.9756E+13
           199711 C3-                   216227309          
           199711 C3-                             2.1623E+13
           199711 MC4                   334920377          
           199711 MC4                             3.3492E+13
           199712 C1                          528          
           199712 C1                                52804224
         생략......

         과 같은 형태로 나온다.
         이것을 년월/제품별로 SUM하면
         문장은 처음과 같고 결과는 다음과 같다.

          년월   제품                 Q          C        
          ------ -------------------- ---------- ----------
          199711 C2-                   197557081 1.9756E+13
          199711 C3-                   216227309 2.1623E+13
          199711 MC4                   334920377 3.3492E+13
          199712 C1                          528   52804224
          199712 C2-                        1185  118509480
         생략......

    단계2.이제 여러월의 DATA중 최소판매량과 최대판매량의
          DATA를 제품별로 찾아보자.
          일년치를 제품별로 GROUP BY하고 그중에 MIN과 MAX값을
          가져오면된다.
          단 총원가는 수량이 최소,최대인 월의 DATA이어야 하므로
          입력가능한최대 총원가금액으로 총원가를 나눈 후
          그값을 수량에다 더한후 그 MIN과 MAX를 가져온다해도
          최소/최대 수량의 월DATA를 가져오는것은 변하지 않는다.
          그렇게 가져온 값에서 판매량을 빼면 총원가를 큰값으로 나눈
          소수점 아래 값만 남게된다.
          이값을 처음에 나눠주었던 충분히 큰값으로 다시곱하면
          판매량이 제외된 순수 초원가를 구할수 있게 된다.
          다음과 같다.
            (MIN(Q + C/1000000000000 ) - MIN(Q)) * 1000000000000  AS C1,
            (MAX(Q + C/1000000000000 ) - MAX(Q)) * 1000000000000  AS C2
            입력가능한 총원가를 1000000000000원 으로 간주했다.
          따라서 우리가 원하는 문장은 다음과 같다.

               SELECT ITEM_CD,
                      MIN(Q)                                               AS Q1,
                     (MIN(Q + C/1000000000000 ) - MIN(Q)) * 1000000000000  AS C1,
                      MAX(Q)                                               AS Q2,
                     (MAX(Q + C/1000000000000 ) - MAX(Q)) * 1000000000000  AS C2
               FROM (
                     SELECT YYMM_YM,
                            ITEM_CD,
                            ROUND(SUM(DECODE(BUDGET_CD,'62099011',PROD_AM))) Q,
                            ROUND(SUM(DECODE(BUDGET_CD,'62099101',PROD_AM))) C
                     FROM   TEST17
                     WHERE  YYMM_YM >=  TO_CHAR(TO_NUMBER(SUBSTR('199803',1,4)) - 1)
                                          || SUBSTR('199803',5,2)
                     AND    YYMM_YM <   '199803'
                     GROUP BY
                            YYMM_YM,
                            ITEM_CD
                    )
               GROUP BY
                     ITEM_CD

             결과는 아래와 같다.

              ITEM_CD              Q1         C1         Q2         C2        
              -------------------- ---------- ---------- ---------- ----------
              C1                          528   52804224        528   52804224
              C2-                        1185  118509480  197557081 1.9756E+13
              C3-                        2487  248686560  216227309 2.1623E+13
              MC4                        3837  383730696  334920377 3.3492E+13
    단계3. 이제 마지막으로 단위별변동원가 와 고정원가를 구하는 식에
           만들어 놓은 DATA를 대입하면된다.
           Q1:1년치중 최소판매량
           C1:최소판매월의총원가
           Q2:1년치중 최대판매량
           C2:최대판매월의총원가 와 같이 명칭을 부여했다.

           아래와 같이 식에 대입한다.
        
            SELECT ITEM_CD                                           AS 제품,
                   DECODE(Q2,Q1,NULL,ROUND((C2 - C1)/(Q2 - Q1)))     AS 단위당변동원가,
                   DECODE(Q2,Q1,NULL,ROUND(C2 - ((C2 - C1)/(Q2 - Q1)) * Q2))  AS 고정원가
            FROM  (위에서 만든 문장)
            
          따라서 최종문장은 다음처럼 된다.

            SELECT ITEM_CD                                          AS 제품,
                   DECODE(Q2,Q1,NULL,ROUND((C2 - C1)/(Q2 - Q1)))    AS 단위당변동원가,
                   DECODE(Q2,Q1,NULL,ROUND(C2 - ((C2 - C1)/(Q2 - Q1)) * Q2)) AS 고정원가
            FROM (
                  SELECT ITEM_CD,
                         MIN(Q)                                               AS Q1,
                        (MIN(Q + C/1000000000000 ) - MIN(Q)) * 1000000000000  AS C1,
                         MAX(Q)                                               AS Q2,
                        (MAX(Q + C/1000000000000 ) - MAX(Q)) * 1000000000000  AS C2
                  FROM (
                        SELECT YYMM_YM,
                               ITEM_CD,
                               ROUND(SUM(DECODE(BUDGET_CD,'62099011',PROD_AM))) Q,
                               ROUND(SUM(DECODE(BUDGET_CD,'62099101',PROD_AM))) C
                        FROM   TEST17
                        WHERE  YYMM_YM >=  TO_CHAR(TO_NUMBER(SUBSTR('199803',1,4)) - 1)
                                             || SUBSTR('199803',5,2)
                        AND    YYMM_YM <   '199803'
                        GROUP BY
                               YYMM_YM,
                               ITEM_CD
                       )
                  GROUP BY
                        ITEM_CD
                   )

         단,1년중 최소판매량이 같은 월이 있다면 총원가가 최소인 월의 DATA가
                  최대판매량이 같은 월이 있다면 총원가가 최대인 월의 DATA가
         읽혀온다.
  
         최종 결과는 아래와 같다.

          제품       단위당변동원가                 고정원가      
          ---------- ------------------------------ --------------
          C1                                                      
          C2-        100000                         9480          
          C3-        100000                         -13440        
          MC4        100000                         30697        

        문장중 DECODE(Q2,Q1,NULL... 의 부부분은 제수가 0 이 되는 경우를
        고려한 것이다.

뒷풀이.
    단위당변동원가*예상판매수량 + 고정원가 가 이번달의 추정원가가 된다.
    무엇이 핵심인지 생각해보자.
    응용의 범위는 끝이 없다.
    아이디어를 살리면 문제는 더욱 쉬워질 수도 있으리라.