MySQL 총 정리11: ROLLUP과 윈도우 함수

 

---

 

[ 목차]
1. ROLLUP
2. 윈도우 함수

 

1. ROLLUP

위와 같이 동물 호텔에 묵는 동물들의 정보가 담긴 테이블이 있다고 하자.

이때 각 그룹별 어떤 집계를 하고 싶다면, GROUP BY와 집계함수를 사용하면 될 것이다.

 

이런 식으로 구해볼 텐데, 총계를 같이 구할 수는 없는 걸까?

 

ROLLUP 이란?

• ROLLUP 함수는 GROUP BY 절과 함께 사용되어 데이터의 소계와 총계를 생성한다.
• ROPPUP은 지정된 각 그룹 수준에 대한 요약 행을 자동으로 추가하여 각 그룹에 대한 부분 합계를 표시한다.
• 지정한 그룹의 개수가 N개라면 총 N + 1개의 조합이 출력된다.

# 기본 구문
SELECT 컬럼1, 컬럼2, ..., 집계함수(컬럼)
FROM 테이블명
GROUP BY 컬럼1, 컬럼2, ... WITH ROLLUP;

 

예시

SELECT species, ROUND(AVG(age),2) AS 평균_나이 
FROM ANIMAL_HOTEL 
GROUP BY species WITH ROLLUP;

결과

 

SELECT species, breed, ROUND(AVG(age),2) AS 평균_나이 
FROM ANIMAL_HOTEL 
GROUP BY species, breed WITH ROLLUP;

결과

• 두 개의 컬럼을 기준으로 ROLLUP을 한 결과,

1). 동물 종류별, 품종별 결과

2). 동물 종류별 결과

3). 전체 결과

이렇게 총 3개의 결과가 나왔다.

※ ROLLUP은 소계 및 총계 행에 NULL을 사용한다.

 

 

NULL 대체하기

1) IFNULL 함수

SELECT IFNULL(species, '합계') AS species,
       IFNULL(breed, '합계') AS breed,
       ROUND(AVG(age),2) AS 평균_나이 
FROM ANIMAL_HOTEL GROUP BY species, breed WITH ROLLUP;

 

2) COALESCE 함수

SELECT COALESCE(species, '합계') AS species, 
       COALESCE(breed, '합계') AS breed, 
       ROUND(AVG(age), 2) AS 평균_나이 
FROM ANIMAL_HOTEL 
GROUP BY species, breed WITH ROLLUP;

 

3) CASE WHEN 사용

SELECT CASE WHEN species IS NULL THEN '합계' ELSE species END AS species,
       CASE WHEN breed IS NULL THEN '합계' ELSE breed END AS breed, 
       ROUND(AVG(age),2) AS 평균_나이 
FROM ANIMAL_HOTEL GROUP BY species, breed WITH ROLLUP;

 

 

✅주의할 점

※ ROLLUP을 사용할 때 GROUP BY 절에 오는 컬럼 순서에 주의해야 한다.

······ GROUP BY a, b, c WITH ROLLUP 은

1). 그룹 a별 그룹 b별 그룹 c의 결과

2). 그룹 a별 그룹 b의 결과

3). 그룹 a의 결과

4). 전체 결과(총계)

의 결과가 나오게 된다.

 

따라서 컬럼의 순서를 바뀐다면 ( ······ GROUP BY c, b, a WITH ROLLUP ) 출력의 결과가 달라진다.

컬럼의 순서를 바꾸니 소계의 기준이 동물종 → 품종으로 바뀌었다.

(동물별 합계가 사라짐)

 

 

---

 

2. 윈도우 함수(Window Function)

• 윈도우 함수는 특정 그룹에 대해 계산을 수행하며, 그 계산 결과를 같은 그룹의 각 행에 적용할 수 있게 해주는 함수다.
• 그룹에 대해 하나의 결과를 반환하는 집계함수와는 달리 데이터의 각 행을 그대로 유지하면서 그룹 내 계산을 수행할 수 있게 한다.
• OVER() 절과 함께 사용되며, 특정한 데이터 그룹에 대한 누적합, 순위 등을 계산할 때 유용하다.

# 기본 구문
SELECT 컬럼1, 
       AGGREGATE_FUNCTION() OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) AS result
FROM 테이블명;

• AGGREGATE_FUNCTION(): SUM(), AVG(), ROW_NUMBER()와 같은 집계 또는 순위 함수
• OVER(): 윈도우 함수가 적용될 범위를 지정
• PARTITION BY: 데이터를 특정 기준으로 그룹화
• ORDER BY: 윈도우 내에서 데이터를 정렬하여 계산을 수행

 

순위 관련 윈도우 함수

1) ROW_NUMBER()

• 1순위부터 순차적으로 순위 부여, 따라서 동일값이어도 같은 순위 부여 ❌

 

2) RANK()

• 동일한 값에 같은 순위를 부여, 그 다음 순위는 건너뜀

 

3) DENSE_RANK()

• 동일한 값에 같은 순위를 부여, 그 다음 순위는 건너뜀 ❌

SELECT 이름, 부서, 급여,
       ROW_NUMBER() OVER(PARTITION BY 부서 ORDER BY 급여 desc) AS row_no,
       RANK() OVER(PARTITION BY 부서 ORDER BY 급여 desc) AS rank_no,
       DENSE_RANK() OVER(PARTITION BY 부서 ORDER BY 급여 desc) AS dense_rank_no
FROM EMPLOYEES;

• 윈도우 함수의 장점은 여기서도 보인다.

• GROUP BY 를통해 부서별 그룹으로 묶었다면, SELECT 절에 올 수 있는 컬럼은 '부서' 나 집계함수를 사용한 컬럼뿐일 텐데, 윈도우 함수를 사용하면 SELECT 절에 컬럼을 자유롭게 추가시킬 수 있다.

 

 

집계 함수 사용해보기

집계 함수: COUNT(), MAX(), MIN(), SUM(), AVG()

-- 출판사별 책들의 가격 합계를 구해보자
SELECT book_name, writer, publisher, price,
       SUM(price) over(PARTITION BY publisher ORDER BY price) AS total
FROM BOOKSHELF;

결과

만약 ORDER BY를 작성하지 않는다면?

➯ 각 행마다 누적합계가 아닌 전체 합계가 출력된다. 누적 합계를 원한다면 꼭 ORDER BY 를 작성해주도록 하자.

 

 

 

---

 

 

참고

SQL 데이터 집계하기 -ROLLUP, WINDOW FUNCTION