특정기간을 기준으로 부서의 부분합계전 전체 통계를 구할경우 ROLLUP 함수가 아주 유용하게 쓰일 수 있습니다. 쿼리상이 아니라 소스상에서 따로 합계를 구해서 뿌려주려면 소스가 지저분해지며, 유지보수상에도 어려움이 생기기 때문이죠.
간단한 통계양식 쿼리입니다. 특정날짜에 자료가 있으면 1건 카운터를 하는 쿼리이며, 개수에 대한 카운티이 아니라 특정값에 대한 카운팅이면 특정값을 따로 넣어서 합계를 구하시면 됩니다. 특별히 어려운 쿼리가 아니니 천천히 분석해보시면 바로 이해하실꺼에요.
SELECT 그룹기준값 , CASE WHEN 세부부서명 IS NULL THEN NVL(세부부서명,'부분합계') ELSE NVL(세부부서명,'총합') END AS 세부부서명 ,SUM(day01) AS day01 ,SUM(day02) AS day02 ,SUM(day03) AS day03 ,SUM(day04) AS day04 ,SUM(day05) AS day05 ,SUM(day06) AS day06 ,SUM(day07) AS day07 ,SUM(day08) AS day08 ,SUM(day09) AS day09 ,SUM(day10) AS day10 ,SUM(day11) AS day11 ,SUM(day12) AS day12 ,SUM(day13) AS day13 ,SUM(day14) AS day14 ,SUM(day15) AS day15 ,SUM(day16) AS day16 ,SUM(day17) AS day17 ,SUM(day18) AS day18 ,SUM(day19) AS day19 ,SUM(day20) AS day20 ,SUM(day21) AS day21 ,SUM(day22) AS day22 ,SUM(day23) AS day23 ,SUM(day24) AS day24 ,SUM(day25) AS day25 ,SUM(day26) AS day26 ,SUM(day27) AS day27 ,SUM(day28) AS day28 ,SUM(day29) AS day29 ,SUM(day30) AS day30 ,SUM(day31) AS day31 ,SUM(day01)+SUM(day02)+SUM(day03)+SUM(day04)+SUM(day05) +SUM(day06)+SUM(day07)+SUM(day08)+SUM(day09)+SUM(day10) +SUM(day11)+SUM(day12)+SUM(day13)+SUM(day14)+SUM(day15) +SUM(day16)+SUM(day17)+SUM(day18)+SUM(day19)+SUM(day20) +SUM(day21)+SUM(day22)+SUM(day23)+SUM(day24)+SUM(day25) +SUM(day26)+SUM(day27)+SUM(day28)+SUM(day29)+SUM(day30) +SUM(day31) AS 합계 FROM ( SELECT 세부부서명, 그룹기준값 ,CASE WHEN SUBSTR(기준일,7,2) ='01' THEN 1 ELSE 0 END AS day01 ,CASE WHEN SUBSTR(기준일,7,2) ='02' THEN 1 ELSE 0 END AS day02 ,CASE WHEN SUBSTR(기준일,7,2) ='03' THEN 1 ELSE 0 END AS day03 ,CASE WHEN SUBSTR(기준일,7,2) ='04' THEN 1 ELSE 0 END AS day04 ,CASE WHEN SUBSTR(기준일,7,2) ='05' THEN 1 ELSE 0 END AS day05 ,CASE WHEN SUBSTR(기준일,7,2) ='06' THEN 1 ELSE 0 END AS day06 ,CASE WHEN SUBSTR(기준일,7,2) ='07' THEN 1 ELSE 0 END AS day07 ,CASE WHEN SUBSTR(기준일,7,2) ='08' THEN 1 ELSE 0 END AS day08 ,CASE WHEN SUBSTR(기준일,7,2) ='09' THEN 1 ELSE 0 END AS day09 ,CASE WHEN SUBSTR(기준일,7,2) ='10' THEN 1 ELSE 0 END AS day10 ,CASE WHEN SUBSTR(기준일,7,2) ='11' THEN 1 ELSE 0 END AS day11 ,CASE WHEN SUBSTR(기준일,7,2) ='12' THEN 1 ELSE 0 END AS day12 ,CASE WHEN SUBSTR(기준일,7,2) ='13' THEN 1 ELSE 0 END AS day13 ,CASE WHEN SUBSTR(기준일,7,2) ='14' THEN 1 ELSE 0 END AS day14 ,CASE WHEN SUBSTR(기준일,7,2) ='15' THEN 1 ELSE 0 END AS day15 ,CASE WHEN SUBSTR(기준일,7,2) ='16' THEN 1 ELSE 0 END AS day16 ,CASE WHEN SUBSTR(기준일,7,2) ='17' THEN 1 ELSE 0 END AS day17 ,CASE WHEN SUBSTR(기준일,7,2) ='18' THEN 1 ELSE 0 END AS day18 ,CASE WHEN SUBSTR(기준일,7,2) ='19' THEN 1 ELSE 0 END AS day19 ,CASE WHEN SUBSTR(기준일,7,2) ='20' THEN 1 ELSE 0 END AS day20 ,CASE WHEN SUBSTR(기준일,7,2) ='21' THEN 1 ELSE 0 END AS day21 ,CASE WHEN SUBSTR(기준일,7,2) ='22' THEN 1 ELSE 0 END AS day22 ,CASE WHEN SUBSTR(기준일,7,2) ='23' THEN 1 ELSE 0 END AS day23 ,CASE WHEN SUBSTR(기준일,7,2) ='24' THEN 1 ELSE 0 END AS day24 ,CASE WHEN SUBSTR(기준일,7,2) ='25' THEN 1 ELSE 0 END AS day25 ,CASE WHEN SUBSTR(기준일,7,2) ='26' THEN 1 ELSE 0 END AS day26 ,CASE WHEN SUBSTR(기준일,7,2) ='27' THEN 1 ELSE 0 END AS day27 ,CASE WHEN SUBSTR(기준일,7,2) ='28' THEN 1 ELSE 0 END AS day28 ,CASE WHEN SUBSTR(기준일,7,2) ='29' THEN 1 ELSE 0 END AS day29 ,CASE WHEN SUBSTR(기준일,7,2) ='30' THEN 1 ELSE 0 END AS day30 ,CASE WHEN SUBSTR(기준일,7,2) ='31' THEN 1 ELSE 0 END AS day31 FROM 테이블 )rslt GROUP BY ROLLUP(rslt.그룹기준값,rslt.세부부서명)
구문에서 제일 유의있게 보셔야할께 GROUP BY ROLLUP 부분인데요. ROLLUP은 그자체로 통계처리를 하기 때문에 쓰이는 순서에 따라서 결과가 완전히 틀어지는 경우가 발생하며 부분합계뿐만 아니라 총합계까지 리턴을 하니 기준키값이 없으면 순서가 엉망으로 되어서 알아보기 힘들게 됩니다.
