안녕하세요, 제목 그대로 쿼리 작성 중에 막히는 부분이 있어 문의드립니다..
일단, DB는 Oracle 11g 혹은 12c 이구요.
현재 아래와 같은 구조의 테이블이 있습니다.
각각 기간 당 PRICE 가 지정되어있는데요.
GROUP_NAME | SUB_GROUP_NAME | TITLE | START_DATE | END_DATE | PRICE |
GROUP1 | SUB1 | T1 | 202101 | 202103 | 2000000 |
GROUP1 | SUB2 | T2 | 202101 | 202112 | 1000000 |
GROUP2 | SUB1 | T3 | 202101 | 202105 | 5000000 |
아래와 같이 지정된 기간의 PRICE를 월별로 나눠서 피벗 형태로 뽑아내는게 목적이었습니다.
GROUP_NAME | SUB_GROUP_NAME | TITLE | 2021.01 | 2021.02 | 2021.03 | ... | 2021.12 |
GROUP1 | SUB1 | T1 | 666666.67 | 666666.67 | 666666.67 | … | 0 |
GROUP1 | SUB2 | T2 | 83333.33 | 83333.33 | 83333.33 | … | 83333.33 |
GROUP2 | SUB1 | T3 | 1000000 | 1000000 | 1000000 | … | 0 |
그래서 쿼리를 작성해보았는데...
일단은 의도한대로 결과가 나오는 것 같긴합니다. (더 좋은 방법이 있다면 알려주시면 감사하겠습니다)
WITH TEMP_DATA AS ( SELECT 'GROUP1' GROUP_NAME, 'SUB1' SUB_GROUP_NAME, 'T1' TITLE, '202101' START_DATE, '202103' END_DATE, 2000000 PRICE FROM DUAL UNION ALL SELECT 'GROUP1' GROUP_NAME, 'SUB2' SUB_GROUP_NAME, 'T2' TITLE, '202101' START_DATE, '202112' END_DATE, 1000000 PRICE FROM DUAL UNION ALL SELECT 'GROUP2' GROUP_NAME, 'SUB1' SUB_GROUP_NAME, 'T3' TITLE, '202101' START_DATE, '202105' END_DATE, 5000000 PRICE FROM DUAL ) SELECT * FROM ( SELECT GROUP_NAME, SUB_GROUP_NAME, TITLE, V_DATE, ROUND(RATIO_TO_REPORT(PRICE) OVER (PARTITION BY GROUP_NAME, SUB_GROUP_NAME, TITLE) * PRICE, 2) AS RESULT FROM ( SELECT GROUP_NAME, SUB_GROUP_NAME, TITLE, TO_CHAR(ADD_MONTHS(TO_DATE(START_DATE,'YYYYMM'), LEVEL-1),'YYYYMM') AS V_DATE, PRICE FROM TEMP_DATA CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(END_DATE,'YYYYMM'), TO_DATE(START_DATE,'YYYYMM'))+1 AND GROUP_NAME = PRIOR GROUP_NAME AND SUB_GROUP_NAME = PRIOR SUB_GROUP_NAME AND TITLE = PRIOR TITLE AND PRIOR SYS_GUID() IS NOT NULL ) ORDER BY GROUP_NAME, SUB_GROUP_NAME, TITLE, V_DATE ) PIVOT (SUM(RESULT) FOR V_DATE IN(202101, 202102, 202103, 202104, 202105, 202106, 202107, 202108, 202109, 202110, 202111, 202112)) ;
그런데 여기서 추가로!!
중복으로 데이터가 추가 될 시 중복되는 제품, 기간에 금액을 추가하고 싶습니다.
GROUP_NAME | SUB_GROUP_NAME | TITLE | START_DATE | END_DATE | PRICE |
GROUP1 | SUB1 | T1 | 202101 | 202103 | 2000000 |
GROUP1 | SUB2 | T2 | 202101 | 202112 | 1000000 |
GROUP1 | SUB2 | T2 | 202101 | 202102 | 100000 |
GROUP2 | SUB1 | T3 | 202101 | 202105 | 5000000 |
아래와 같이 T2의 1월, 2월에 추가적용되게 말이죠..
GROUP_NAME | SUB_GROUP_NAME | TITLE | 2021.01 | 2021.02 | 2021.03 | ... | 2021.12 |
GROUP1 | SUB1 | T1 | 666666.67 | 666666.67 | 666666.67 | … | 0 |
GROUP1 | SUB2 | T2 | 133333.33 | 133333.33 | 83333.33 | … | 83333.33 |
GROUP2 | SUB1 | T3 | 1000000 | 1000000 | 1000000 | … | 0 |
이거 가능할까요..? 혼자 해보려고 이런저런 방법으로 해보았는데 잘안되네요.
도움에 미리 감사드리겠습니다.. (__)
1. CONNECT BY LEVEL <= n 를 이용한 행 복제 방식은
- 반드시 dual 처럼 1행의 집합에만 적용해야 합니다.
- 여러건의 집합에 직접 적용시 원치않는 행복제가 발생됩니다. http://gurubee.net/article/55635
- 건수가 많아질수록 복제되는 양이 기하급수로 늘어 성능저하의 원인이 됩니다.
- 여기서는 SYS_GUID() 조건을 추가하여 카티션곱 현상을 막은 듯 하긴 한데.
- 이렇게까지 복잡하게 계층쿼리를 작성할 필요가 없습니다.
- 그냥 12개월 자료 별도로 만들어 조인하세요.
2. 년도 조건 검색인 듯 한데요?
- 검색조건으로 연도 조건이 추가되어야 할 듯 하고
- 기간이 여러해에 걸쳐 있는 경우도 고려해야 할 것 같네요. (예 : 202010 ~ 202103)
- 컬럼 타이틀도 년월이 아닌 월만 표시하면 간단할 것 같습니다.
WITH temp_data AS ( SELECT 'GROUP1' group_name, 'SUB1' sub_group_name, 'T1' title, '202101' start_date, '202103' end_date, 2000000 price FROM dual UNION ALL SELECT 'GROUP1', 'SUB2', 'T2', '202101', '202112', 1000000 FROM dual UNION ALL SELECT 'GROUP1', 'SUB2', 'T2', '202101', '202102', 100000 FROM dual UNION ALL SELECT 'GROUP2', 'SUB1', 'T3', '202101', '202105', 5000000 FROM dual ) SELECT * FROM (SELECT group_name, sub_group_name, title , TO_NUMBER(SUBSTR(ym, 5)) mm , ROUND(price / (MONTHS_BETWEEN(TO_DATE(end_date, 'yyyymm'), TO_DATE(start_date, 'yyyymm')) + 1) , 2) x FROM temp_data a , (SELECT '2021' || LPAD(LEVEL, 2, '0') ym FROM dual CONNECT BY LEVEL <= 12) b WHERE b.ym BETWEEN a.start_date AND a.end_date ) PIVOT (SUM(x) FOR mm IN (01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12)) ORDER BY group_name, sub_group_name, title ;