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 41776 게시물 읽기
No. 41776
view 생성 도와주세요 ㅠㅠ_내용 추가 및 작성 쿼리 첨부
작성자
고라니(e3161)
작성일
2019-12-12 01:51ⓒ
2019-12-12 13:31ⓜ
조회수
2,904

 안녕하세요... VIEW 생성중.. 어려움에 봉착하여 문의 남겨봅니다...

TABLE 1

bu period_date attribute1 attribute2 attribute3 attribute4 attribute5 biz_plan
brake 2019-09 korea PT

A

 H MBS 140
brake 2019-09 korea PT A K MBS 100
brake 2019-09 korea PT B M MBS 100
brake 2019-09 korea PT B E MBS 100
brake 2019-09 korea PT C A MBS 150
brake 2019-09 korea PT A H EBS 40
brake 2019-09 korea PT A K EBS 250
brake 2019-09 korea PT B M EBS 40
brake 2019-09 korea PT B E EBS

120
brake 2019-09 korea PT C A EBS 140

TABLE2

bu period_date attribute1 attribute2 attribute3 attribute4 attribute5 current_plan
brake 2019-09 korea PT

A

 H MBS 140
brake 2019-09 korea PT A K MBS 100
brake 2019-09 korea PT B M MBS 100
brake 2019-09 korea PT B E MBS 100
brake 2019-09 korea PT C A MBS 150
brake 2019-09 korea PT A H EBS 40
brake 2019-09 korea PT A K EBS 250
brake 2019-09 korea PT B M EBS 40
brake 2019-09 korea PT B E EBS 120
brake 2019-09 korea PT C A EBS 140

TABLE3

 

bu YEAR period_date attribute1 attribute2 attribute3 attribute4 attribute5 act_plan
brake 2019 2019-09 korea PT

A

 H MBS 140
brake 2019 2019-09 korea PT A K MBS 100
brake 2019 2019-09 korea PT B M MBS 100
brake 2019 2019-09 korea PT B E MBS 100
brake 2019 2019-09 korea PT C A MBS 150
brake 2019 2019-09 korea PT A H EBS 40
brake 2019 2019-09 korea PT A K EBS 250
brake 2019 2019-09 korea PT B M EBS 40
brake 2019 2019-09 korea PT B E EBS 120
brake 2019 2019-09 korea PT C A EBS 140


3개 테이블을 이용해서 1년 년간 계획 데이터 및 누계데이터를 표현하는 통계 view를 만들려고합니다. 

A1 A2 A3 A4 A5

BIZPLAN

(1~12월누적)

ACTUAL

(1~9월 누적)

progress

(actual/bizplan)

biz plan

(MBS+EBS)

current

(MBS+EBS)

actual

(MBS+EBS)

 vs biz(%) vs current(%)

biz plan

(MBS)

current

(MBS)

actual

(MBS)

biz plan

(EBS)

current

(EBS)

actual

(EBS)
brake korea PT

A

 H       180 180 180 actual/bizplan actual/current 140 140 140 40 40 40
brake korea PT A K       350 350 350     100 100 100 250 250 250
      소계                              
brake korea PT B M       140 140 140     100 100 100 40 40 40
brake korea PT B E       220 220 220     100 100 100 120 120 120
      소계                              
brake korea PT C A       290 290 290     150 150 150 140 140 140
      소계                              
      TOTAL(총계)                              

 

상기 표와 동일하게 3개 테이블을 이용하여 데이터를 만들고 싶습니다... 

테이블 1,2,3 join 을 통해 현재 데이터가 9월만 있는데 앞쪽 데이터중  biz_Plan의 경우 1월 ~ 12월의 1년 예산이 입력되있고 actual의 경우 실제 1~9월 까지 데이터입니다. 

조건절의 경우 A1, Period_date를 기준으로 조회하려 합니다 

그리고 vs Current 뒤에 있는 MBS와 EBS의 경우 원데이터(attribute5의 값에 따라 유동적으로 변하는 부분입니다. MBS만 있는경우 MBS만 표기 등)

 

 

 

SELECT "YEAR","PERIOD_CODE","AFFILIATE","BU","ATTRIBUTE2","ATTRIBUTE3","ATTRIBUTE4","ATTRIBUTE5","BIZ_PLAN","CURRENT_PLAN","ACTUAL","VS_BIZ","VS_CURRENT","MBS_BIZ_PLAN","MBS_CURRENT_PLAN","MBS_ACTUAL","EBS_BIZ_PLAN","EBS_CURRENT_PLAN","EBS_ACTUAL" FROM

(SELECT YEAR,

       PERIOD_CODE,

       AFFILIATE,

       BU,

       ATTRIBUTE2,

       ATTRIBUTE3,

       ATTRIBUTE4,

       ATTRIBUTE5,

       (NVL(MBS_BIZ_PLAN, 0) + NVL(EBS_BIZ_PLAN, 0)) BIZ_PLAN,

       (NVL(MBS_CURRENT_PLAN, 0) + NVL(EBS_CURRENT_PLAN, 0)) CURRENT_PLAN,

       (NVL(MBS_ACTUAL, 0) + NVL(EBS_ACTUAL, 0)) ACTUAL,

       ((NVL(MBS_ACTUAL, 0) + NVL(EBS_ACTUAL, 0)) /

       NULLIF((NVL(MBS_BIZ_PLAN, 0) + NVL(EBS_BIZ_PLAN, 0)), 0)) * 100 "VS_BIZ",

       ((NVL(MBS_ACTUAL, 0) + NVL(EBS_ACTUAL, 0)) /

       NULLIF((NVL(MBS_CURRENT_PLAN, 0) + NVL(EBS_CURRENT_PLAN, 0)), 0)) * 100 "VS_CURRENT",

       MBS_BIZ_PLAN,

       MBS_CURRENT_PLAN,

       MBS_ACTUAL,

       EBS_BIZ_PLAN,

       EBS_CURRENT_PLAN,

       EBS_ACTUAL

  FROM (SELECT MBS.YEAR,

               MBS.PERIOD_CODE,

               MBS.AFFILIATE,

               MBS.BU,

               MBS.ATTRIBUTE1,

               EBS.ATTRIBUTE1,

               MBS.ATTRIBUTE2,

               MBS.ATTRIBUTE3,

               MBS.ATTRIBUTE4,

               MBS.ATTRIBUTE5,

               MBS.BIZ_PLAN     AS "MBS_BIZ_PLAN",

               MBS.CURRENT_PLAN AS "MBS_CURRENT_PLAN",

               MBS.ACTUAL       AS "MBS_ACTUAL",

               EBS.BIZ_PLAN     AS "EBS_BIZ_PLAN",

               EBS.CURRENT_PLAN AS "EBS_CURRENT_PLAN",

               EBS.ACTUAL       AS "EBS_ACTUAL"

          FROM (SELECT *

                  FROM (SELECT BIZ.YEAR,

                               ACT.PERIOD_CODE,

                               ACT.AFFILIATE,

                               ACT.BU,

                               ACT.ATTRIBUTE1,

                               ACT.ATTRIBUTE2,

                               ACT.ATTRIBUTE3,

                               ACT.ATTRIBUTE4,

                               ACT.ATTRIBUTE5,

                               to_number(BIZ.ATTRIBUTE8) AS BIZ_PLAN,

                               NVL(to_number(ACT.ATTRIBUTE6), to_number(BIZ.ATTRIBUTE8)) AS CURRENT_PLAN,

                               to_number(RE.ATTRIBUTE6) AS ACTUAL

                          FROM (SELECT *

                                  FROM XXCST_APL_CONS_EISBIS_UPLOAD

                                 WHERE TEMPLATE_CODE = 'PC_SALES_BIZ_PLAN') BIZ,

                               (SELECT *

                                  FROM XXCST_APL_CONS_EISBIS_UPLOAD

                                 WHERE TEMPLATE_CODE = 'PC_SALES_ACT_PLAN') ACT,

                               (SELECT *

                                  FROM XXCST_APL_CONS_EISBIS_UPLOAD

                                 WHERE TEMPLATE_CODE = 'PC_SALES_RESULT') RE

                         WHERE BIZ.ATTRIBUTE1 = ACT.BU

                           AND ACT.BU = RE.BU

                           AND BIZ.ATTRIBUTE7 = ACT.PERIOD_CODE

                           AND ACT.PERIOD_CODE = RE.PERIOD_CODE

                           AND BIZ.ATTRIBUTE2 = ACT.ATTRIBUTE1

                           AND ACT.ATTRIBUTE1 = RE.ATTRIBUTE1

                           AND BIZ.ATTRIBUTE3 = ACT.ATTRIBUTE2

                           AND ACT.ATTRIBUTE2 = RE.ATTRIBUTE2

                           AND BIZ.ATTRIBUTE4 = ACT.ATTRIBUTE3

                           AND ACT.ATTRIBUTE3 = RE.ATTRIBUTE3

                           AND BIZ.ATTRIBUTE5 = ACT.ATTRIBUTE4

                           AND ACT.ATTRIBUTE4 = RE.ATTRIBUTE4

                           AND BIZ.ATTRIBUTE6 = ACT.ATTRIBUTE5

                           AND ACT.ATTRIBUTE5 = RE.ATTRIBUTE5

                           AND BIZ.ATTRIBUTE7 = ACT.PERIOD_CODE

                           AND ACT.PERIOD_CODE = RE.PERIOD_CODE)

                 WHERE ATTRIBUTE1 = 'MBS') MBS,

 

               (SELECT *

                  FROM (SELECT BIZ.YEAR,

                               ACT.PERIOD_CODE,

                               ACT.AFFILIATE,

                               ACT.BU,

                               ACT.ATTRIBUTE1,

                               ACT.ATTRIBUTE2,

                               ACT.ATTRIBUTE3,

                               ACT.ATTRIBUTE4,

                               ACT.ATTRIBUTE5,

                               to_number(BIZ.ATTRIBUTE8) AS BIZ_PLAN,

                               NVL(to_number(ACT.ATTRIBUTE6), to_number(BIZ.ATTRIBUTE8)) AS CURRENT_PLAN,

                               to_number(RE.ATTRIBUTE6) AS ACTUAL

                          FROM (SELECT *

                                  FROM XXCST_APL_CONS_EISBIS_UPLOAD

                                 WHERE TEMPLATE_CODE = 'PC_SALES_BIZ_PLAN') BIZ,

                               (SELECT *

                                  FROM XXCST_APL_CONS_EISBIS_UPLOAD

                                 WHERE TEMPLATE_CODE = 'PC_SALES_ACT_PLAN') ACT,

                               (SELECT *

                                  FROM XXCST_APL_CONS_EISBIS_UPLOAD

                                 WHERE TEMPLATE_CODE = 'PC_SALES_RESULT') RE

                         WHERE BIZ.ATTRIBUTE1 = ACT.BU

                           AND ACT.BU = RE.BU

                           AND BIZ.ATTRIBUTE7 = ACT.PERIOD_CODE

                           AND ACT.PERIOD_CODE = RE.PERIOD_CODE

                           AND BIZ.ATTRIBUTE2 = ACT.ATTRIBUTE1

                           AND ACT.ATTRIBUTE1 = RE.ATTRIBUTE1

                           AND BIZ.ATTRIBUTE3 = ACT.ATTRIBUTE2

                           AND ACT.ATTRIBUTE2 = RE.ATTRIBUTE2

                           AND BIZ.ATTRIBUTE4 = ACT.ATTRIBUTE3

                           AND ACT.ATTRIBUTE3 = RE.ATTRIBUTE3

                           AND BIZ.ATTRIBUTE5 = ACT.ATTRIBUTE4

                           AND ACT.ATTRIBUTE4 = RE.ATTRIBUTE4

                           AND BIZ.ATTRIBUTE6 = ACT.ATTRIBUTE5

                           AND ACT.ATTRIBUTE5 = RE.ATTRIBUTE5

                           AND BIZ.ATTRIBUTE7 = ACT.PERIOD_CODE

                           AND ACT.PERIOD_CODE = RE.PERIOD_CODE)

                 WHERE ATTRIBUTE1 = 'EBS') EBS

         WHERE MBS.YEAR = EBS.YEAR

           AND MBS.PERIOD_CODE = EBS.PERIOD_CODE

           AND MBS.AFFILIATE = EBS.AFFILIATE

           AND MBS.BU = EBS.BU

           AND MBS.ATTRIBUTE2 = EBS.ATTRIBUTE2

           AND MBS.ATTRIBUTE3 = EBS.ATTRIBUTE3

           AND MBS.ATTRIBUTE4 = EBS.ATTRIBUTE4

           AND MBS.ATTRIBUTE5 = EBS.ATTRIBUTE5)) A1

테이블 정보가 조금 다르긴 하나... 현재 누적 데이터 부분을 제외하고 나머지 데이터가 출력되도록 view를 작성하긴하였는데

제가 짜놓은 쿼리의 경우 MBS, EBS 값이 고정되어 유동적이 변경이 어려 울 뿐만아니라.. 누적 데이터 산출에 어려움이 있습니다.. 

하여 다시 VIEW 생성을 해야 할것 같은데.. 상기 쿼리정도까지가 제수준인것같습니다... 

이렇게 쓰고 보니 내용정리가 잘 안되있는것 같아 죄송합니다.... 

혹시나 해당 view에 대한 아이디어 있으신 분들은 의견 주시면 감사하겠습니다.... 
이 글에 대한 댓글이 총 3건 있습니다.

실제 테이블은 1개인데 구분에 따라 3가지 로 나뉘는 듯 하네요?
attribute1, 2 ... 은 실제 컬럼명인가요?
조인 조건도 좀 이상한게 act 와 re 는 같은 컬럼명으로 조인하는데?
biz 부분만 컬럼명이 다른 걸로 조인하네요? 이게 맞는건가요?
이런 경우 조건별로 따로 조회해와서 조인하는 방식이 아닌
테이블 한번만 읽어서 조건에 따라 집계하는 방식을 써야 합니다.
테이블 예시를 3개의 중간집합으로 나누어 주셨는데?
이걸 원본테이블 1개에 어떻게 들어 있는지를 표시해 주세요.

마농(manon94)님이 2019-12-12 14:11에 작성한 댓글입니다.

 

1. 말씀하신것처럼 실제 테이블은 1개의 테이블에 column 구분에 따라 3가지로 나눠져있습니다. 

그리고 해당 테이블에 attribute1~40까지의 컬럼에 값을 기재하게 되어있습니다. 

2. 조인 조건의 경우 biz의경우 act와 re는 동일 컬럼에 값들이 들어가 있고 biz의 경우 조금 다르게 들어가 있어 해당 join  조건은 맞게 기재되어있습니다. 

3 원본 테이블의 경우 

template_code period_code year affiliate org attribute1 attribute2 attribute3 attribute4 attribute5 attribute6 attribute7 attribute8 bu weekly
biz   2019 mdk   brake mbs korea p oem a 2019-09 100    
biz   2019 mdk   brake mbs korea p oem b 2019-09 120    
biz   2019 mdk   brake mbs korea p ckd c 2019-09 13    
biz   2019 mdk   brake mbs korea p de d 2019-09 140    
biz   2019 mdk   brake ebs korea p de a 2019-09 16    
biz   2019 mdk   brake ebs korea p oem c 2019-09 100    
act 2019-09   mdk   mbs korea p oem a 124     brake  
act 2019-09   mdk   mbs korea p oem b 122     brake  
act 2019-09   mdk   mbs korea p ckd c 120     brake  
act 2019-09   mdk   mbs korea p de d 150     brake  
act 2019-09   mdk   ebs korea p de a 11     brake  
act 2019-09   mdk   ebs korea p oem c 15     brake  
re 2019-09   mdk   mbs korea p oem a 151     brake 1
re 2019-09   mdk   mbs korea p oem b 160     brake 1
re 2019-09   mdk   mbs korea p ckd c 110     brake 1
re 2019-09   mdk   mbs korea p de d 120     brake 1
re 2019-09   mdk   mbs korea p de a 120     brake 1
re 2019-09   mdk   mbs korea p oem c 150     brake 1

 

이와 같이 테이블에 데이터 입력되어있습니다... 

긴글 읽어주셔서 감사합니다. 전 바보가 분명해요 ㅠ

고라니님이 2019-12-12 16:37에 작성한 댓글입니다.
이 댓글은 2019-12-12 18:01에 마지막으로 수정되었습니다. Edit Ｘ

WITH t(template_code, period_code, year, affiliate
, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, bu) AS
(
          SELECT 'biz', ''       , '2019', 'mdk', 'brake', 'mbs'  , 'korea', 'p'  , 'oem', 'a'  , '2019-09', '100', ''      FROM dual
UNION ALL SELECT 'biz', ''       , '2019', 'mdk', 'brake', 'mbs'  , 'korea', 'p'  , 'oem', 'b'  , '2019-09', '120', ''      FROM dual
UNION ALL SELECT 'biz', ''       , '2019', 'mdk', 'brake', 'mbs'  , 'korea', 'p'  , 'ckd', 'c'  , '2019-09', '13' , ''      FROM dual
UNION ALL SELECT 'biz', ''       , '2019', 'mdk', 'brake', 'mbs'  , 'korea', 'p'  , 'de' , 'd'  , '2019-09', '140', ''      FROM dual
UNION ALL SELECT 'biz', ''       , '2019', 'mdk', 'brake', 'ebs'  , 'korea', 'p'  , 'de' , 'a'  , '2019-09', '16' , ''      FROM dual
UNION ALL SELECT 'biz', ''       , '2019', 'mdk', 'brake', 'ebs'  , 'korea', 'p'  , 'oem', 'c'  , '2019-09', '100', ''      FROM dual
UNION ALL SELECT 'act', '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'oem', 'a'  , '124', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 'act', '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'oem', 'b'  , '122', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 'act', '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'ckd', 'c'  , '120', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 'act', '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'de' , 'd'  , '150', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 'act', '2019-09', ''    , 'mdk', 'ebs'  , 'korea', 'p'    , 'de' , 'a'  , '11' , ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 'act', '2019-09', ''    , 'mdk', 'ebs'  , 'korea', 'p'    , 'oem', 'c'  , '15' , ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 're' , '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'oem', 'a'  , '151', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 're' , '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'oem', 'b'  , '160', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 're' , '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'ckd', 'c'  , '110', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 're' , '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'de' , 'd'  , '120', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 're' , '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'de' , 'a'  , '120', ''       , ''   , 'brake' FROM dual
UNION ALL SELECT 're' , '2019-09', ''    , 'mdk', 'mbs'  , 'korea', 'p'    , 'oem', 'c'  , '150', ''       , ''   , 'brake' FROM dual
)
SELECT year, affiliate, bu
     , a2, a3, a4, a5
     ,        SUM(DECODE(template_code, 'biz', y))     biz_sum
     ,        SUM(DECODE(template_code, 're' , y))     act_sum
     , ROUND( SUM(DECODE(template_code, 're' , y, 0))
            / SUM(DECODE(template_code, 'biz', y)), 2) progress
     ,        SUM(DECODE(template_code, 'biz', x))     biz
     ,   NVL( SUM(DECODE(template_code, 'act', x))
            , SUM(DECODE(template_code, 'biz', x)))    cur
     ,        SUM(DECODE(template_code, 're' , x))     act
     , ROUND( SUM(DECODE(template_code, 're' , x, 0))
            / SUM(DECODE(template_code, 'biz', x)), 2) vs_biz
     , ROUND( SUM(DECODE(template_code, 're' , x, 0))
       / NVL( SUM(DECODE(template_code, 'act', x))
            , SUM(DECODE(template_code, 'biz', x))), 2)    vs_cur
     ,        SUM(DECODE(template_code||a1, 'bizmbs', x))  biz_mbs
     ,   NVL( SUM(DECODE(template_code||a1, 'actmbs', x))
     ,        SUM(DECODE(template_code||a1, 'bizmbs', x))) cur_mbs
     ,        SUM(DECODE(template_code||a1, 'rembs' , x))  act_mbs
     ,        SUM(DECODE(template_code||a1, 'bizebs', x))  biz_ebs
     ,   NVL( SUM(DECODE(template_code||a1, 'actebs', x))
            , SUM(DECODE(template_code||a1, 'bizebs', x))) cur_ebs
     ,        SUM(DECODE(template_code||a1, 'reebs' , x))  act_ebs
  FROM (SELECT template_code
             , affiliate
             , DECODE(template_code, 'biz', year, SUBSTR(period_code, 1, 4)) year
             , DECODE(template_code, 'biz', attribute1, bu) bu
             , DECODE(template_code, 'biz', attribute7, period_code) period_code
             , DECODE(template_code, 'biz', attribute2, attribute1) a1
             , DECODE(template_code, 'biz', attribute3, attribute2) a2
             , DECODE(template_code, 'biz', attribute4, attribute3) a3
             , DECODE(template_code, 'biz', attribute5, attribute4) a4
             , DECODE(template_code, 'biz', attribute6, attribute5) a5
             , DECODE(template_code, 'biz', attribute8, attribute6) y
             , DECODE('2019-09'
             , DECODE(template_code, 'biz', attribute7, period_code)
             , DECODE(template_code, 'biz', attribute8, attribute6)) x
          FROM t
         WHERE template_code IN ( 'biz', 'act', 're')
           AND DECODE(template_code, 'biz', year, period_code) LIKE '2019%'
        )
 WHERE period_code  <= '2019-09'
    OR template_code = 'biz'
 GROUP BY year, affiliate, bu, a2, a3, ROLLUP(a4, a5)
;

마농(manon94)님이 2019-12-13 13:09에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41779테이블 칼럼 추가 일시 확인 문의(Alter table add[modify] ....) [2]
박재덕
2020-01-20
2647
41778코멘트를 기준으로 컬럼명을 찾을 수 있나요? [1]
신승익
2020-01-10
2621
41777금액이 변경된데 부터까지 누적합계를 구하고 싶습니다. [2]
빙수
2020-01-08
3120
41776view 생성 도와주세요 ㅠㅠ_내용 추가 및 작성 쿼리 첨부 [3]
고라니
2019-12-12
2904
4177510g reorg 작업에 대해 질문드려요 [1]
lee
2019-11-26
2710
41774select 쿼리에 in 조건이 2개일때 index 를 태울수있나요?? [4]
아벨의꿈
2019-11-20
3142
41773프로시저생성이 안됩니다.. 과제인데..ㅜ [1]
홍승표
2019-11-17
3004
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.021초, 이곳 서비스는 PostgreSQL v16.4로 자료를 관리합니다