꿈꾸는 개발자, DBA 커뮤니티 구루비

oracle 행간 계산

by 트집이 [SQL Query] oracle pivot 계산식 [2023.04.06 10:04:06]

안녕하세요.

WITH t AS (
	SELECT '2023' YEAR, '02' MONTH, '09' WEEK, 'E' GUBUN, 195 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '02' MONTH, '09' WEEK, 'I' GUBUN, 195 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '05' MONTH, '20' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '05' MONTH, '19' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '18' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '17' WEEK, 'P' GUBUN, 555 COST FROM DUAL	
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '16' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '15' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '14' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '02' MONTH, '09' WEEK, 'P' GUBUN, 345 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '13' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '12' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '11' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '10' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '13' WEEK, 'I' GUBUN, 600 COST FROM DUAL	
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '12' WEEK, 'I' GUBUN, 600 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '11' WEEK, 'I' GUBUN, 600 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '10' WEEK, 'I' GUBUN, 600 COST FROM DUAL	
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '18' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '17' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '16' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '15' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '14' WEEK, 'I' GUBUN, 450 COST FROM DUAL	
)
SELECT * FROM T

위와 같은 원시 테이블 데이터가 있다고 가정을 합니다.

위 데이터를 아래와 같이 만들어야 합니다.

GUBUN	W09	W10	W11	W12	W13	W14	W15	W16	W17	W18	W19	W20
E	195
I	195	600	600	600	600	450	450	450	450	450	450	450
P	345	450	450	450	450	555	555	555	555	555
CALC1	-43.4	33.3	33.3	33.3	33.3	-18.9	-18.9	-18.9	-18.9	-18.9
CALC2		207.6	0	0	0	-25	0	0	0	0	0	0

공식은

CALC1 = (I - P) / P * 100

CALC2 = I(현재주) - I(전주) / I(현재주) * 100

입니다.

CAL1의 W19, W20이 공백인 이유는 W19&W20의 P의 값이 공백이기 때문입니다.

CAL2의 W09가 공백인 이유는 W09가 시작주이기 때문입니다.

제가 아래와 같이 DECODE와 GROUP BY를 이용해서 pivot까지는 어떻게 했는데 CAL1과 CAL2를 어떻게 아래에 붙여야 할지 모르겠습니다. 도움 주시면 감사하겠습니다.

WITH t AS (
	SELECT '2023' YEAR, '02' MONTH, '09' WEEK, 'E' GUBUN, 195 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '02' MONTH, '09' WEEK, 'I' GUBUN, 195 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '05' MONTH, '20' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '05' MONTH, '19' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '18' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '17' WEEK, 'P' GUBUN, 555 COST FROM DUAL	
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '16' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '15' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '14' WEEK, 'P' GUBUN, 555 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '02' MONTH, '09' WEEK, 'P' GUBUN, 345 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '13' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '12' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '11' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '10' WEEK, 'P' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '13' WEEK, 'I' GUBUN, 600 COST FROM DUAL	
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '12' WEEK, 'I' GUBUN, 600 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '11' WEEK, 'I' GUBUN, 600 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '03' MONTH, '10' WEEK, 'I' GUBUN, 600 COST FROM DUAL	
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '18' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '17' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '16' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '15' WEEK, 'I' GUBUN, 450 COST FROM DUAL
	UNION ALL
	SELECT '2023' YEAR, '04' MONTH, '14' WEEK, 'I' GUBUN, 450 COST FROM DUAL	
)
SELECT 	GUBUN
      , SUM(W09) W09
      , SUM(W10) W10
      , SUM(W11) W11
      , SUM(W12) W12
      , SUM(W13) W13
      , SUM(W14) W14
      , SUM(W15) W15
      , SUM(W16) W16
      , SUM(W17) W17
      , SUM(W18) W18
      , SUM(W19) W19
      , SUM(W20) W20
FROM 	(
		SELECT  YEAR
			  , MONTH
			  , GUBUN
			  , COST	  
			  , DECODE(WEEK, '09', COST, NULL) W09
			  , DECODE(WEEK, '10', COST, NULL) W10
			  , DECODE(WEEK, '11', COST, NULL) W11
			  , DECODE(WEEK, '12', COST, NULL) W12
			  , DECODE(WEEK, '13', COST, NULL) W13
			  , DECODE(WEEK, '14', COST, NULL) W14
			  , DECODE(WEEK, '15', COST, NULL) W15
			  , DECODE(WEEK, '16', COST, NULL) W16
			  , DECODE(WEEK, '17', COST, NULL) W17
			  , DECODE(WEEK, '18', COST, NULL) W18
			  , DECODE(WEEK, '19', COST, NULL) W19
			  , DECODE(WEEK, '20', COST, NULL) W20
		FROM T
)
GROUP BY
		GUBUN

by 마농 [2023.04.06 11:18:32]

WITH t AS
(
SELECT '2023' year, '02' month, '09' week, 'E' gubun, 195 cost FROM dual
UNION ALL SELECT '2023', '02', '09', 'I', 195 FROM dual
UNION ALL SELECT '2023', '05', '20', 'I', 450 FROM dual
UNION ALL SELECT '2023', '05', '19', 'I', 450 FROM dual
UNION ALL SELECT '2023', '04', '18', 'P', 555 FROM dual
UNION ALL SELECT '2023', '04', '17', 'P', 555 FROM dual
UNION ALL SELECT '2023', '04', '16', 'P', 555 FROM dual
UNION ALL SELECT '2023', '04', '15', 'P', 555 FROM dual
UNION ALL SELECT '2023', '04', '14', 'P', 555 FROM dual
UNION ALL SELECT '2023', '02', '09', 'P', 345 FROM dual
UNION ALL SELECT '2023', '03', '13', 'P', 450 FROM dual
UNION ALL SELECT '2023', '03', '12', 'P', 450 FROM dual
UNION ALL SELECT '2023', '03', '11', 'P', 450 FROM dual
UNION ALL SELECT '2023', '03', '10', 'P', 450 FROM dual
UNION ALL SELECT '2023', '03', '13', 'I', 600 FROM dual
UNION ALL SELECT '2023', '03', '12', 'I', 600 FROM dual
UNION ALL SELECT '2023', '03', '11', 'I', 600 FROM dual
UNION ALL SELECT '2023', '03', '10', 'I', 600 FROM dual
UNION ALL SELECT '2023', '04', '18', 'I', 450 FROM dual
UNION ALL SELECT '2023', '04', '17', 'I', 450 FROM dual
UNION ALL SELECT '2023', '04', '16', 'I', 450 FROM dual
UNION ALL SELECT '2023', '04', '15', 'I', 450 FROM dual
UNION ALL SELECT '2023', '04', '14', 'I', 450 FROM dual
)
SELECT *
  FROM (SELECT year, week
             , e, i, p
             , ROUND((i / p - 1) * 100, 2) calc1
             , ROUND((i / LAG(i) OVER(ORDER BY year, week) - 1) * 100, 2) calc2
          FROM t
         PIVOT (MIN(cost) FOR gubun IN ('E' e, 'I' i, 'P' p))
        )
 UNPIVOT (cost FOR gubun IN (e, i, p, calc1, calc2))
 PIVOT (MIN(cost) FOR week IN ( '09' w09
                              , '10' w10
                              , '11' w11
                              , '12' w12
                              , '13' w13
                              , '14' w14
                              , '15' w15
                              , '16' w16
                              , '17' w17
                              , '18' w18
                              , '19' w19
                              , '20' w20
                              ) )
 ORDER BY LENGTH(gubun), gubun
;

by 트집이 [2023.04.07 13:26:04]

WITH t AS (
    SELECT '2022' year, '12' month, '50' week, 'E' gubun, 200 cost FROM DUAL
    UNION ALL SELECT '2022', '12', '50', 'I', 100 FROM DUAL
    UNION ALL SELECT '2022', '12', '50', 'P', 100 FROM DUAL
    UNION ALL SELECT '2022', '12', '51', 'E', 230 FROM DUAL
    UNION ALL SELECT '2022', '12', '51', 'I', 122 FROM DUAL
    UNION ALL SELECT '2022', '12', '51', 'P', 250 FROM DUAL
    UNION ALL SELECT '2022', '12', '52', 'E', 180 FROM DUAL
    UNION ALL SELECT '2022', '12', '52', 'I', 185 FROM DUAL
    UNION ALL SELECT '2022', '12', '52', 'P', 199 FROM DUAL
    UNION ALL SELECT '2023', '01', '01', 'E', 195 FROM DUAL
    UNION ALL SELECT '2023', '01', '01', 'P', 450 FROM DUAL
    UNION ALL SELECT '2023', '01', '01', 'I', 450 FROM DUAL
    UNION ALL SELECT '2023', '01', '02', 'E', 555 FROM DUAL
    UNION ALL SELECT '2023', '01', '02', 'P', 555 FROM DUAL    
    UNION ALL SELECT '2023', '01', '02', 'I', 555 FROM DUAL
    UNION ALL SELECT '2023', '01', '03', 'E', 555 FROM DUAL
    UNION ALL SELECT '2023', '01', '03', 'P', 555 FROM DUAL
    UNION ALL SELECT '2023', '01', '03', 'I', 345 FROM DUAL
    UNION ALL SELECT '2023', '01', '04', 'E', 450 FROM DUAL
    UNION ALL SELECT '2023', '01', '04', 'P', 450 FROM DUAL
    UNION ALL SELECT '2023', '01', '04', 'I', 450 FROM DUAL
    UNION ALL SELECT '2023', '01', '05', 'E', 450 FROM DUAL
    UNION ALL SELECT '2023', '01', '05', 'P', 500 FROM DUAL    
    UNION ALL SELECT '2023', '01', '05', 'I', 550 FROM DUAL
    UNION ALL SELECT '2023', '02', '06', 'E', 220 FROM DUAL
    UNION ALL SELECT '2023', '02', '06', 'P', 350 FROM DUAL    
    UNION ALL SELECT '2023', '02', '06', 'I', 150 FROM DUAL   
)
SELECT *
  FROM (SELECT week
             , e, i, p
             , ROUND((i / p - 1) * 100, 2) calc1
             , ROUND((i / LAG(i) OVER(ORDER BY year, week) - 1) * 100, 2) calc2
          FROM t
         PIVOT (MIN(cost) FOR gubun IN ('E' e, 'I' i, 'P' p))
        )
 UNPIVOT (cost FOR gubun IN (e, i, p, calc1, calc2))
 PIVOT (MIN(cost) FOR week IN ( '50' w50
                              , '51' w51
                              , '52' w52
                              , '01' w01
                              , '02' w02
                              , '03' w03
                              , '04' w04
                              , '05' w05
                              , '06' w06
                              ) )
 ORDER BY LENGTH(gubun), gubun;

년도가 변경되면 데이터가 년도를 기준으로 2줄로 늘어나는 현상이 있어서 year만 제거했습니다. 저는 어차피 12주를 넘기지 않기 때문에... 년도가 변경되도 week로만 뽑으면 될거 같아요.

매번 도움 감사드립니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변