데이터베이스 질문과 답변

Oracle 쿼리 작성중에 막히는 부분이 있어 문의드립니다. 1 2 909

by tester0101 [SQL Query] [2021.03.11 14:56:15]

안녕하세요, 제목 그대로 쿼리 작성 중에 막히는 부분이 있어 문의드립니다..

 

일단, 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

 

이거 가능할까요..? 혼자 해보려고 이런저런 방법으로 해보았는데 잘안되네요.

도움에 미리 감사드리겠습니다.. (__)

by 마농 [2021.03.11 15:52:12]

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
;

 

by tester0101 [2021.03.11 16:14:56]

마농님 상세한 답변 감사드립니다.

의도한 결과가 나오긴하지만 정말 효율적이지 못한 쿼리였군요..

열심히 공부해야겠다는 생각을 다시 해봅니다.

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