해당 글에서는 WITH ROLLUP, PIVOT에 대한 구조 및 활용방법에 대해 알아봅니다.
1) WITH ROLLUP
💡 WITH ROLLUP
- MySQL에서 GROUP BY 절과 함께 사용되는 확장 기능으로 집계된 데이터에 대해 소계 및 총 합계를 계산할 수 있게 해 줍니다. - 이를 사용하면 그룹별로 데이터의 합계뿐만 아니라, 전체 데이터의 합계도 계산할 수 있습니다. 이는 보고서나 데이터 분석에서 매우 유용하게 사용됩니다.
1. WITH ROLLUP 형식
💡 WITH ROLLUP 형식
- SELECT 문에서 일반 컬럼과 집계함수(SUM, COUNT, AVG 등)를 사용하고 지정된 그룹별로 데이터를 집계한 후에 최종적으로 전체 데이터의 합계를 추가적으로 계산합니다.
SELECT column1, column2, ..., aggregate_function(column)
FROM table
GROUP BY column1, column2, ...
WITH ROLLUP;
2. 간단한 활용 예시
💡 간단한 활용 예시
- 직원 테이블(employees) 내에서 부서(department)와 부서 별 급여의 합(salary)을 조회합니다. 이를 위해 부서 별(department)에 대한 그룹을 형성하였습니다. - 아래의 결과로 각각의 부서와 부서별 급여의 합이 출력이 되며, 가장 마지막 행에는 총 합계가 출력이 됩니다.
SELECT department
, SUM(salary)
FROM employees
GROUP BY department
WITH ROLLUP;
3. 활용 예시 -1 : 각 부서별 급여에 대한 합계(소계)와 총 합계를 구하는 예시
💡 활용 예시 -1 : 각 부서별 급여에 대한 합계(소계)와 총 합계를 구하는 예시
- 각 부서별로 급여에 대한 소계를 함께 출력을 하며 마지막에는 총 합계를 출력합니다.
-- 각 부서별 급여에 대한 합계(소계)를 구합니다.
SELECT d.id
, d.pid
, MAX(d.dname) AS '부서명'
, SUM(e.salary) AS '급여합'
FROM Dept d
INNER JOIN EMP e
ON d.id = e.dept
GROUP BY d.id, d.pid
WITH ROLLUP;
4. 활용 예시 -2 : 최종 부서별 합계 및 총 합계를 구하는 예시
💡 활용 예시 -2 : 최종 부서별 합계 및 총 합계를 구하는 예시
- 각각 상위부서를 출력하며, 부서 별로 급여에 대한 합계를 출력합니다. 또한 최종 마지막에는 부서들의 총합을 반환하는 예시입니다.
-- 최종 부서별 합계 및 총 합계를 구합니다.
SELECT d.id
, d.pid
, (CASE WHEN d.id IS NOT NULL THEN max(p.dname) ELSE 'TOTAL' END) AS '상위부서'
, (CASE WHEN d.id IS NOT NULL THEN max(d.dname) ELSE '-- 소계 --' END) AS '부서명'
, SUM(e.salary) AS '급여합'
FROM Dept p
INNER JOIN Dept d
ON p.id = d.pid -- SELF JOIN
INNER JOIN EMP e
ON d.id = e.dept
GROUP BY d.id, d.pid
WITH ROLLUP;
2) PIVOT
💡 PIVOT
- 테이블의 행 데이터를 열 데이터로 변환하여 요약된 형태로 데이터를 볼 수 있게 해주는 기능입니다. 이를 통해, 데이터 분석 및 보고서 작성이 보다 직관적이고 효율적으로 이루어질 수 있습니다.
- MySQL 내에서 PIVOT 기능을 직접 지원하지 않지만, CASE 문과 GROUP BY를 사용하여 PIVOT과 같은 기능을 구현할 수 있습니다.
- 피벗 테이블을 생성하는 쿼리입니다. 특정 열을 행으로 만들고, 조건에 따라 값을 합산하여 새로운 열을 생성합니다. - 그룹화는 지정된 열을 기준으로 이루어집니다.
SELECT
<행으로 만들 열>,
SUM(CASE WHEN <조건1> THEN <피벗할 값> ELSE 0 END) AS <새로운 열1>,
SUM(CASE WHEN <조건2> THEN <피벗할 값> ELSE 0 END) AS <새로운 열2>,
...
FROM <테이블 이름>
GROUP BY <행으로 만들 열>;
2. 기본 예시
💡 기본 예시
- sales_data 테이블에서 제품별로 2022년과 2023년의 총판매량을 조회합니다. - 조건부 집계를 사용하여 각 연도의 판매량을 합산하고, 결과를 제품별로 그룹화합니다.
SELECT
product,
SUM(CASE WHEN year = '2022' THEN sales ELSE 0 END) AS sales_2022,
SUM(CASE WHEN year = '2023' THEN sales ELSE 0 END) AS sales_2023
FROM sales_data
GROUP BY product;
3. PIVOT 활용 예시 : PIVOT 적용 이전 데이터
💡 PIVOT 활용 예시 : PIVOT 적용 이전 데이터
- 부서별로 평균 급여, 급여 합계, 최소 급여, 최대 급여를 계산하여 출력합니다. - '구분' 열에 부서명을 표시하고, '평균 급여', '급여 합계', '최소 급여', '최대 급여' 열에 각각의 급여 통계를 출력합니다.
SELECT MAX(d.dname) AS '구분'
, FORMAT(AVG(e.salary) * 10000, 0) AS '평균 급여'
, FORMAT(SUM(e.salary) * 10000, 0) AS '급여 합계'
, FORMAT(MIN(e.salary) * 10000, 0) AS '최소 급여'
, FORMAT(MAX(e.salary) * 10000, 0) AS '최대 급여'
FROM Dept d
INNER JOIN EMP e
ON d.id = e.dept
GROUP BY d.id;
4. PIVOT 활용 예시 : PIVOT 적용 데이터
💡 PIVOT 활용 예시 : PIVOT 적용 데이터
- 다양한 부서(dept)의 급여에 대한 통계 정보를 제공합니다. 각 부서의 평균 급여, 급여 합계, 최소 급여 및 최대 급여를 계산하여 출력합니다. - CASE WHEN THEN, boolean 패턴, IF 패턴을 사용하여 각 부서별로 급여를 계산하고, FORMAT 함수로 결과를 보기 쉽게 형식화합니다.
-- CASE WHEN THEN 패턴으로 활용
SELECT '평균 급여' AS '구분'
, FORMAT(AVG(CASE WHEN dept = 3 THEN salary ELSE 0 END) * 10000, 0) AS '영업 1팀'
, FORMAT(AVG(CASE WHEN dept = 4 THEN salary ELSE 0 END) * 10000, 0) AS '영업 2팀'
, FORMAT(AVG(CASE WHEN dept = 5 THEN salary ELSE 0 END) * 10000, 0) AS '영업 3팀'
, FORMAT(AVG(CASE WHEN dept = 6 THEN salary ELSE 0 END) * 10000, 0) AS '서버팀'
, FORMAT(AVG(CASE WHEN dept = 7 THEN salary ELSE 0 END) * 10000, 0) AS '클라팀'
FROM EMP
UNION
-- boolean 패턴으로 활용
SELECT '급여 합계'
, FORMAT(SUM(salary * (dept = 3)) * 10000, 0)
, FORMAT(SUM(salary * (dept = 4)) * 10000, 0)
, FORMAT(SUM(salary * (dept = 5)) * 10000, 0)
, FORMAT(SUM(salary * (dept = 6)) * 10000, 0)
, FORMAT(SUM(salary * (dept = 7)) * 10000, 0)
FROM EMP
UNION
-- IF 패턴으로 활용
SELECT '최소 급여'
, FORMAT(MIN(IF(dept = 3, salary, ~0)) * 10000, 0)
, FORMAT(MIN(IF(dept = 4, salary, ~0)) * 10000, 0)
, FORMAT(MIN(IF(dept = 5, salary, ~0)) * 10000, 0)
, FORMAT(MIN(IF(dept = 6, salary, ~0)) * 10000, 0)
, FORMAT(MIN(IF(dept = 7, salary, ~0)) * 10000, 0)
FROM EMP
UNION
-- IF 패턴으로 활용
SELECT '최대 급여'
, FORMAT(MAX(IF(dept = 3, salary, 0)) * 10000, 0)
, FORMAT(MAX(IF(dept = 4, salary, 0)) * 10000, 0)
, FORMAT(MAX(IF(dept = 5, salary, 0)) * 10000, 0)
, FORMAT(MAX(IF(dept = 6, salary, 0)) * 10000, 0)
, FORMAT(MAX(IF(dept = 7, salary, 0)) * 10000, 0)
FROM EMP
