데이터베이스 질문과 답변
년월별 집계 Data 쿼리 방법 문의드립니다.
대략적인 Raw Data는 아래와 같으며, Part No, Supplier, type, Currency 별로
PRICE 값이 년월(YYYYMM)으로 열의 값으로 표시되어야 됩니다.
꼭 반영되어야 하는 고려사항은 아래 두개고요, 여기 Q/A에 올라온 내용을 참조해서
쿼리를 만들어봤는데 정말 어렵네요...ㅠ 고수님들 부탁 드리겠습니다.
<고려사항>
1) 월 중에 PRICE값이 한번이상 변경 시 해당월의 값은 마지막 일자의 PRICE 값을 표시
2) END_DATE 값이 '29991231'인 경우 현재 유효한 PRICE임
<raw data>
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>
대략적인 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월에 종료와 시작이 공존하는 경우가 있는지?
있다면 결과는 어떻게 표시해야 하는지?
1. 조회컬럼의 갯수가 가변적으로 늘수 없고
2. 조회컬럼의 타이틀을 데이터 값으로 표시하는것도 불가능
동적 SQL을 사용해야 할 듯 하고...
일단 시작일, 종료일 구간이 서로 겹치지는 않는다고 가정하고...
시작일과 종료일이 월단위로만 돌아가는 것인지?
1월~3월, 4월~5월 이런식만 존재하는지?
1월~3월15일, 3월16일~5월 이런식으로 3월에 종료와 시작이 공존하는 경우가 있는지?
있다면 결과는 어떻게 표시해야 하는지?
by 우리집아찌
[2013.11.06 15:38:00]
-- 억지로 답에만 맞췄어요..
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로는 한계가 있는것 같네요..
프로시저로 프로그램을 만들어서 해결해야 될것 같네요.
) 버튼을 클릭하여 작성 하시면 됩니다.