[SQL] 그룹함수 총정리

그룹함수에는 ROLLUP, CUBE, GROUPING SETS 함수가 있는데 각각이 어떤 역할을 하고, 각각의 차이점은 무엇인지 특정한 상황에서 어떤 함수를 쓰는게 좋을지 생각해보자.

 

SQL 그룹함수

 

ROLLUP 함수

1.소 그룹간의 소계를 계산한다.

2. GRUPING 컬럼을 통해 SUBTOTLA을 생성하는데 여기서 GROUPING 컬럼의 수가 N이면, N+1의 SSUBTOTAL이 생선된다.

3. ROLLUP함수 내의 인자의 순서가 바뀌면 결과도 바뀌게 된다.(ROLLUP함수는 계층 구조이기 때문이다.)

 

설명만 봐서는 이해가 쉽지 않기에 실습을 해보도록 하자

 

 

예제1

 

SELECT
    productLine,
    FLOOR(AVG(quantityInStock)) AS avgQuantity,
    FLOOR(AVG(buyPrice)) AS avgBuyPrice,
    FLOOR(AVG(MSRP)) AS avgMSRP
FROM
    s_products
GROUP BY 
    productLine WITH ROLLUP

우선 이 코드에서 ROLLUP함수를 그룹바이 기준이 될 productLine 컬럼 뒤에 WITH ROLLUP을 써줌으로써 사용했는데, 이는 MySql 기준이고, 표준 SQL에선 'GROUP BY ROLLUP (productLine)'으로 사용해 주면 된다.

 

위 코드의 결과에서 마지막 행의 productLine이 'Null'값인데 이를 '평균'이라는 단어로 채우기 위해 GROUPING 함수를 사용한다. GROUPING(NULL) = 1임을 이용하면 된다.

 

SELECT
    CASE GROUPING(productLine) WHEN 1 THEN '평균' ELSE productLine END AS 'productLine',
    FLOOR(AVG(quantityInStock)) AS avgQuantity,
    FLOOR(AVG(buyPrice)) AS avgBuyPrice,
    FLOOR(AVG(MSRP)) AS avgMSRP
FROM
    s_products
GROUP BY 
    productLine WITH ROLLUP

 

 

예제 2

SELECT 
    IFNULL(o.city, '미정') AS 지점 ,
    CONCAT(e.firstName, ' ', e.lastName) AS 직원_성명,
    COUNT(c.customerId) AS 고객수

FROM s_customers c
LEFT JOIN s_employees e ON c.salesRepId = e.employeeId
LEFT JOIN s_offices o ON e.officeCode = o.officeCode
GROUP BY o.city, CONCAT(e.firstName, ' ', e.lastName) WITH ROLLUP  
ORDER BY 1
;

 

 

CUBE 함수

1. 다차원적인 소계를 계산한다.

2. 결합가능한 모든 값에 대하여 다차원 집계를 생성한다 (ex. 2개의 컬럼을 넣으면 총 2^2개인 4개의 집계를 생성함)

3. 시스템에 많은 부담을 준다