WITH DATA(NAME, ID, DAY1, DAY2, DAY3) AS ( SELECT 'BW', 18011711, 0, 13017, 15370 FROM DUAL UNION ALL SELECT 'BW', 18011712, 0, 95, 0 FROM DUAL UNION ALL SELECT 'BW', 18013111, 0, 3966, 0 FROM DUAL UNION ALL SELECT 'BP', 18011711, 15588, 8323, 0 FROM DUAL UNION ALL SELECT 'BP', 18011712, 2, 0, 0 FROM DUAL UNION ALL SELECT 'BP', 18013111, 0, 7277, 15412 FROM DUAL ) SELECT 'BW VS BP' NAME, ID, DAY1, DAY2, DAY3 FROM ( SELECT NAME, ID, DAY1, DAY2, DAY3 FROM DATA MODEL DIMENSION BY (NAME, ID) MEASURES ( DAY1, DAY2, DAY3) RULES ( DAY1['BW', 18011711] = DAY1['BP', 18011711] - DAY1['BW', 18011711] , DAY1['BW', 18011712] = DAY1['BP', 18011712] - DAY1['BW', 18011712] , DAY1['BW', 18013111] = DAY1['BP', 18013111] - DAY1['BW', 18013111] , DAY2['BW', 18011711] = DAY2['BP', 18011711] - DAY2['BW', 18011711] + DAY1['BW', 18011711] , DAY2['BW', 18011712] = DAY2['BP', 18011712] - DAY2['BW', 18011712] + DAY1['BW', 18011712] , DAY2['BW', 18013111] = DAY2['BP', 18013111] - DAY2['BW', 18013111] + DAY1['BW', 18013111] , DAY3['BW', 18011711] = DAY3['BP', 18011711] - DAY3['BW', 18011711] + DAY2['BW', 18011711] , DAY3['BW', 18011712] = DAY3['BP', 18011712] - DAY3['BW', 18011712] + DAY2['BW', 18011712] , DAY3['BW', 18013111] = DAY3['BP', 18013111] - DAY3['BW', 18013111] + DAY2['BW', 18013111] ) ) WHERE NAME = 'BW' ;
모델절이라는 게 있어서 만들어봤습니다.
어렵네요.
WITH data AS ( SELECT 'BW' name, 18011711 id, 0 day1, 13017 day2, 15370 day3 FROM dual UNION ALL SELECT 'BW', 18011712, 0, 95, 0 FROM dual UNION ALL SELECT 'BW', 18013111, 0, 3966, 0 FROM dual UNION ALL SELECT 'BP', 18011711, 15588, 8323, 0 FROM dual UNION ALL SELECT 'BP', 18011712, 2, 0, 0 FROM dual UNION ALL SELECT 'BP', 18013111, 0, 7277, 15412 FROM dual ) SELECT * FROM data MODEL -- RETURN UPDATED ROWS -- 결과만 보고자 할때 주석 해제 PARTITION BY (id) DIMENSION BY (CAST(name AS VARCHAR2(10)) name) MEASURES (day1, day2, day3) RULES ( day1['BW vs BP'] = day1['BP'] - day1['BW'] , day2['BW vs BP'] = day2['BP'] - day2['BW'] + day1[CV()] , day3['BW vs BP'] = day3['BP'] - day3['BW'] + day2[CV()] ) ORDER BY DECODE(name, 'BW', 1, 'BP', 2, 3), id ;
WITH data AS ( SELECT 'BW' name, 18011711 id, 0 day1, 13017 day2, 15370 day3 FROM dual UNION ALL SELECT 'BW', 18011712, 0, 95, 0 FROM dual UNION ALL SELECT 'BW', 18013111, 0, 3966, 0 FROM dual UNION ALL SELECT 'BP', 18011711, 15588, 8323, 0 FROM dual UNION ALL SELECT 'BP', 18011712, 2, 0, 0 FROM dual UNION ALL SELECT 'BP', 18013111, 0, 7277, 15412 FROM dual ) SELECT * FROM (SELECT id, gb, bw, bp , SUM(bp - bw) OVER(PARTITION BY id ORDER BY gb) x FROM data UNPIVOT (day FOR gb IN (day1, day2, day3)) PIVOT (MIN(day) FOR name IN ('BW' bw, 'BP' bp)) ) UNPIVOT (day FOR name IN (bw, bp, x AS 'BW vs BP')) PIVOT (MIN(day) FOR gb IN ('DAY1' day1, 'DAY2' day2, 'DAY3' day3)) -- WHERE name = 'BW vs BP' ORDER BY DECODE(name, 'BW', 1, 'BP', 2, 3), id ;