우선 테이블이 주차가 늘어날때마다 컬럼이 늘어나는 형태가 아니라 로우가 늘어나는 형태로 되어 있어야 합니다..

 

테이블 구성을 컬럼이

CODE, DESCT, 주차, 값, OVERDUE_값

CODE, DESCT, STCK

이렇게 구성되도록 두개로 나눴구요..

 

이를 바탕으로 주어진 식대로 계산 하자면..

 

WITH V1 AS

(

  SELECT '001' CODE, 'TEST1' DESCT , 40 WEEK, 120 VAL, 64869 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '001' CODE, 'TEST1' DESCT , 41 WEEK, 132 VAL, 136274 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '001' CODE, 'TEST1' DESCT , 42 WEEK, 155 VAL, 72485 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '001' CODE, 'TEST1' DESCT , 43 WEEK, 167 VAL, 60063 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '001' CODE, 'TEST1' DESCT , 44 WEEK, 189 VAL, NULL OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '002' CODE, 'TEST2' DESCT , 40 WEEK, 342 VAL, 34 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '002' CODE, 'TEST2' DESCT , 41 WEEK, 178 VAL, 17 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '002' CODE, 'TEST2' DESCT , 42 WEEK, 122 VAL, 58 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '002' CODE, 'TEST2' DESCT , 43 WEEK, 765 VAL, 24 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '002' CODE, 'TEST2' DESCT , 44 WEEK, 669 VAL, NULL OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '003' CODE, 'TEST3' DESCT , 40 WEEK, 334 VAL, 34 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '003' CODE, 'TEST3' DESCT , 41 WEEK, 987 VAL, 231 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '003' CODE, 'TEST3' DESCT , 42 WEEK, 765 VAL, 48 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '003' CODE, 'TEST3' DESCT , 43 WEEK, 343 VAL, 34 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '003' CODE, 'TEST3' DESCT , 44 WEEK, 232 VAL, NULL OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '004' CODE, 'TEST4' DESCT , 40 WEEK, 234 VAL, 34 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '004' CODE, 'TEST4' DESCT , 41 WEEK, 287 VAL, 244 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '004' CODE, 'TEST4' DESCT , 42 WEEK, 565 VAL, 38 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '004' CODE, 'TEST4' DESCT , 43 WEEK, 3403 VAL, 78 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '004' CODE, 'TEST4' DESCT , 44 WEEK, 1232 VAL, NULL OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '005' CODE, 'TEST5' DESCT , 40 WEEK, 304 VAL, 34 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '005' CODE, 'TEST5' DESCT , 41 WEEK, 907 VAL, 24 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '005' CODE, 'TEST5' DESCT , 42 WEEK, 705 VAL, 28 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '005' CODE, 'TEST5' DESCT , 43 WEEK, 393 VAL, 9 OVERDUE_VAL FROM DUAL

  UNION ALL

  SELECT '005' CODE, 'TEST5' DESCT , 44 WEEK, 5232 VAL, NULL OVERDUE_VAL FROM DUAL

)

, V2 AS

(

  SELECT '001' CODE, 'TEST1' DESCT, 230278 STCK FROM DUAL

  UNION ALL

  SELECT '002' CODE, 'TEST2' DESCT, 150 STCK FROM DUAL

  UNION ALL

  SELECT '003' CODE, 'TEST3' DESCT, 180 STCK FROM DUAL

  UNION ALL

  SELECT '004' CODE, 'TEST4' DESCT, 240 STCK FROM DUAL

  UNION ALL

  SELECT '005' CODE, 'TEST5' DESCT, 220 STCK FROM DUAL

)

SELECT CODE

     , DESCT

     , WEEK

     , STCK - RESUTL

FROM  (

        SELECT V1.CODE

             , V1.DESCT

             , V1.WEEK

             , V1.VAL

             , V1.OVERDUE_VAL

             , V2.STCK

             , SUM(V1.OVERDUE_VAL) OVER(PARTITION BY V1.CODE ORDER BY V1.WEEK) RESUTL

        FROM   V1

             , V2

        WHERE  V1.CODE = V2.CODE

        AND    V1.DESCT = V2.DESCT

        AND    V1.OVERDUE_VAL IS NOT NULL

      )

 

근데 질문 상단에 더하기, 빼기, 곱하기 등등 다른 연산을 한다고 하셨는데..

누적합을 가지고 연산을 하는거는 가능하지만 누적차, 누적곱 등을 구하는건 위와같은 방법으로 해결 안될 수 있습니다..

PL/SQL이나 로직단에서 풀어야 할듯..

 

매번 감사합니다..

그러면 차주가 고정(4주나, 8주나, 16주) 되어 있고, STCK 정보 가  한 row로 table에 저장 되어 있다면, 한 row로 표현이 가능 할까요? (계산 할수 있는 DATA가 정보 존재시)

code | desc  | w40 | w41| w42| w43| w44|  RESUTL|

이런 형태로는 불가능 할까요?

 

혹시 이걸 원하시는건지??

질문 내용이 이해가 잘 안가네요..

 

 SELECT CODE

정말 감사합니다..

진짜 저에게 햇살한조각 입니다.

다시 한번 감사드립니다..