데이터베이스 질문과 답변

년월 가격 집계쿼리 방법에 대해 문의드립니다. 0 4 1,322

by 슈엔 [SQL Query] [2013.11.06 14:28:31]

년월별 집계 Data 쿼리 방법 문의드립니다.
대략적인 Raw Data는 아래와 같으며, Part No, Supplier, type, Currency 별로
PRICE 값이 년월(YYYYMM)으로 열의 값으로 표시되어야 됩니다. 

꼭 반영되어야 하는 고려사항은 아래 두개고요, 여기 Q/A에 올라온 내용을 참조해서
쿼리를 만들어봤는데 정말 어렵네요...ㅠ  고수님들 부탁 드리겠습니다.

<고려사항>
1) 월 중에 PRICE값이 한번이상 변경 시 해당월의 값은 마지막 일자의 PRICE 값을 표시
2) END_DATE 값이 '29991231'인 경우 현재 유효한 PRICE임

<raw data>
    PART_NO SUPPLIER TYPE CURRENCY PRICE START_DATE END_DATE
1 ABC S1 1 KRW 0.0608 20130501 20130630
2 ABC S1 1 KRW 0.0596 20130701 29991231
3 ABC S1 2 KRW 0.0608 20130304 20130630
4 ABC S1 2 KRW 0.0596 20130701 29991231
5 ABC S1 1 USD 0.0097 20130304 20130430
6 DEF S2 2 KRW 0.0005 20121204 20130330
7 DEF S2 2 KRW 0.0004 20130401 20130420
8 DEF S2 2 KRW 0.0003 20130421 29991231

WITH t AS
SELECT 'ABC' part_no ,'S1' supplier ,'1'  TYPE ,'KRW' currency ,0.0608 price ,'20130501' start_date ,'20130630' end_date  FROM dual  
UNION ALL
SELECT 'ABC'  ,'S1'  ,'1'   ,'KRW'  ,0.0596  ,'20130701'  ,'29991231'   FROM dual
UNION 
SELECT 'ABC'  ,'S1'  ,'2'   ,'KRW'  ,0.0608  ,'20130304'  ,'20130630'   FROM dual
UNION ALL
SELECT 'ABC'  ,'S1'  ,'2'   ,'KRW'  ,0.0596  ,'20130701'  ,'29991231'   FROM dual
UNION 
SELECT 'ABC'  ,'S1'  ,'1'   ,'USD'  ,0.0097  ,'20130304'  ,'20130430'   FROM dual
UNION ALL
SELECT 'DEF'  ,'S2'  ,'2'   ,'KRW'  ,0.0005  ,'20121204'  ,'20130330'   FROM dual
UNION ALL
SELECT 'DEF'  ,'S2'  ,'2'   ,'KRW'  ,0.0004  ,'20130401'  ,'29991231'   FROM dual
UNION ALL
SELECT 'DEF'  ,'S2'  ,'1'  i ,'USD'  ,0.0003  ,'20130304'  ,'20130430'   FROM dual


<집계 data>

No. Part No Supplier type Currency 201212 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312
1 ABC S1 1 KRW 　 　 　 　 　 0.0608 0.0608 0.0596 0.0596 0.0596 0.0596 0.0596 　
2 ABC S1 2 KRW 　 　 　 0.0608 0.0608 0.0608 0.0608 0.0596 0.0596 0.0596 0.0596 0.0596 　
3 ABC S1 1 USD 　 　 　 0.0097 0.0097 　 　 　 　 　 　 　 　
4 DEF S2 2 KRW 0.0005 0.0005 0.0005 0.0005 0.0003 0.0003 0.0003 0.0003 0.0003 0.0003 0.0003 0.0003 　
by 마농 [2013.11.06 15:10:21]
기본적으로 정적SQL만으로는 한계가 있죠.
1. 조회컬럼의 갯수가 가변적으로 늘수 없고
2. 조회컬럼의 타이틀을 데이터 값으로 표시하는것도 불가능
동적 SQL을 사용해야 할 듯 하고...

일단 시작일, 종료일 구간이 서로 겹치지는 않는다고 가정하고...
시작일과 종료일이 월단위로만 돌아가는 것인지?
1월~3월, 4월~5월 이런식만 존재하는지?
1월~3월15일, 3월16일~5월 이런식으로 3월에 종료와 시작이 공존하는 경우가 있는지?
있다면 결과는 어떻게 표시해야 하는지?
by 우리집아찌 [2013.11.06 15:38:00]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 억지로 답에만 맞췄어요..
WITH T1 (PART_NO, SUPPLIER, TYPE, CURRENCY, PRICE, START_DATE, END_DATE ) AS (
SELECT 'ABC','S1','1','KRW','0.0608','20130501','20130630' FROM DUAL UNION ALL
SELECT 'ABC','S1','1','KRW','0.0596','20130701','29991231' FROM DUAL UNION ALL
SELECT 'ABC','S1','2','KRW','0.0608','20130304','20130630' FROM DUAL UNION ALL
SELECT 'ABC','S1','2','KRW','0.0596','20130701','29991231' FROM DUAL UNION ALL
SELECT 'ABC','S1','1','USD','0.0097','20130304','20130430' FROM DUAL UNION ALL
SELECT 'DEF','S2','2','KRW','0.0005','20121204','20130330' FROM DUAL UNION ALL
SELECT 'DEF','S2','2','KRW','0.0004','20130401','20130420' FROM DUAL UNION ALL
SELECT 'DEF','S2','2','KRW','0.0003','20130421','29991231' FROM DUAL
),T2 (DT) AS (
 SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201212','YYYYMM') , LEVEL - 1 ) ,'YYYYMMDD')    
 FROM DUAL CONNECT BY LEVEL <=
  (SELECT MONTHS_BETWEEN ( LEAST(TO_CHAR(SYSDATE , 'YYYYMMDD'),MAX(END_DATE)),MIN(START_DATE)) FROM T1) + 1
)
 
SELECT PART_NO , SUPPLIER , TYPE , CURRENCY
 , MAX(DECODE(DT,'20121201',PRICE)) "201212"
 , MAX(DECODE(DT,'20130101',PRICE)) "201301"
 , MAX(DECODE(DT,'20130201',PRICE)) "201302"
 , MAX(DECODE(DT,'20130301',PRICE)) "201303"
 , MAX(DECODE(DT,'20130401',PRICE)) "201304"
 , MAX(DECODE(DT,'20130501',PRICE)) "201305"
 , MAX(DECODE(DT,'20130601',PRICE)) "201306"
 , MAX(DECODE(DT,'20130701',PRICE)) "201307"
 , MAX(DECODE(DT,'20130801',PRICE)) "201308"
 , MAX(DECODE(DT,'20130901',PRICE)) "201309"
 , MAX(DECODE(DT,'20131001',PRICE)) "201310"
 , MAX(DECODE(DT,'20131101',PRICE)) "201311"
   
 FROM (SELECT * FROM
 (SELECT PART_NO, SUPPLIER, TYPE, CURRENCY, PRICE,
  SUBSTR(START_DATE,1,6)||'01' START_DATE,
  END_DATE ,
  ROW_NUMBER() OVER(PARTITION BY PART_NO , SUPPLIER , TYPE , SUBSTR(START_DATE,1,6) ORDER BY START_DATE DESC) RN
  FROM T1 )
 WHERE RN = 1) T1
 , T2
 WHERE T1.START_DATE <= T2.DT(+)
 AND T1.END_DATE >= T2.DT(+)
GROUP BY PART_NO , SUPPLIER , TYPE , CURRENCY
ORDER BY PART_NO , SUPPLIER , CURRENCY , TYPE
by 아린 [2013.11.06 16:02:04]
SELECT part_no, supplier, type, currency
     , MIN(CASE WHEN :yyyy||'01' BETWEEN start_ym AND end_ym THEN price END) mon01
     , MIN(CASE WHEN :yyyy||'02' BETWEEN start_ym AND end_ym THEN price END) mon02
     , MIN(CASE WHEN :yyyy||'03' BETWEEN start_ym AND end_ym THEN price END) mon03
     , MIN(CASE WHEN :yyyy||'04' BETWEEN start_ym AND end_ym THEN price END) mon04
     , MIN(CASE WHEN :yyyy||'05' BETWEEN start_ym AND end_ym THEN price END) mon05
     , MIN(CASE WHEN :yyyy||'06' BETWEEN start_ym AND end_ym THEN price END) mon06
     , MIN(CASE WHEN :yyyy||'07' BETWEEN start_ym AND end_ym THEN price END) mon07
     , MIN(CASE WHEN :yyyy||'08' BETWEEN start_ym AND end_ym THEN price END) mon08
     , MIN(CASE WHEN :yyyy||'09' BETWEEN start_ym AND end_ym THEN price END) mon09
     , MIN(CASE WHEN :yyyy||'10' BETWEEN start_ym AND end_ym THEN price END) mon10
     , MIN(CASE WHEN :yyyy||'11' BETWEEN start_ym AND end_ym THEN price END) mon11
     , MIN(CASE WHEN :yyyy||'12' BETWEEN start_ym AND end_ym THEN price END) mon12
  FROM (SELECT part_no, supplier, type, currency, price
             , SUBSTR(start_date, 1, 6) start_ym 
             , DECODE(MAX(end_date) OVER(PARTITION BY part_no, supplier, type, currency, SUBSTR(start_date, 1, 6))
                     , '29991231', TO_CHAR(SYSDATE, 'yyyymm'),
               MAX(end_date) OVER(PARTITION BY part_no, supplier, type, currency, SUBSTR(start_date, 1, 6))) end_ym
             , ROW_NUMBER() OVER(PARTITION BY part_no, supplier, type, currency, SUBSTR(start_date, 1, 6) 
               ORDER BY start_date DESC) rn  
          FROM t
        )  
 WHERE rn = 1
 GROUP BY part_no, supplier, type, currency 
 ORDER BY part_no, supplier, type, currency
by 슈엔 [2013.11.07 09:38:40]

답변 주신분들 모두 감사합니다.
고려사항들이 많아 역시 한방SQL로는 한계가 있는것 같네요..
프로시저로 프로그램을 만들어서 해결해야 될것 같네요.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입