10. GROUP BY 절과 HAVING 절

GROUP BY 절은 행 그룹을 생성하고, HAVING 절은 조회할 행 그룹을 선택한다. GROUP BY 절은 WHERE 절 다음에 기술하며, WHERE 절이 수행된 후 수행된다.

10.1 GROUP BY 절

SELECT SUM(sal) AS c1
FROM emp
WHERE sal > 2000
GROUP BY ();

GROUP BY 절을 기술하지 않거나, GROUP BY 절에 NULL이나 ()을 기술하면 전체 행이 하나의 행 그룹으로 처리된다.

SELECT deptno, SUM(sal) AS sal
FROM emp
WHERE sal > 2000
GROUP BY deptno;

SELECT deptno, SUM(sal) AS sal
FROM emp
WHERE sal > 2000
GROUP BY deptno, job;

SELECT deptno
FROM emp
WHERE sal > 2000
GROUP BY deptno
ORDER BY SUM(sal);

GROUP BY 절을 사용한 쿼리는 SELECT 절과 ORDER BY 절에 GROUP BY 절의 표현식이나 집계 함수를 사용한 표현식만 기술할 수 있다.

# deptno별 sal의 합계에 대한 평균값 집계
SELECT AVG(SUM(sal) AS c1
FROM emp
WHERE sal > 2000
GROUP BY deptno;

# deptno가 10이나 20인 행을 1020행 그룹, 나머지 행을 3040 행 그룹으로 sal의 합계 값을 집계
SELECT CASE WHEN deptno IN (10, 20) THEN 1020 ELSE 3040 END AS deptno, SUM(sal) AS sal
FROM emp
GROUP BY CASE WHEN deptno IN (10, 20) THEN 1020 ELSE 3040 END
;

10.2 GROUP BY 절의 확장 기능

10.2.1 ROLLUP

ROLLUP은 지정한 표현식의 계층별 소계와 총계를 집계한다.

ROLLUP(a) ==> (a),()
ROLLUP(a, b) ==> (a, b), (a), ()
ROLLUP(a, b, c) ==> (a,b,c), (a,b), (a), ()

SELECT deptno, COUNT(*) AS c1
FROM emp
WHERE sal > 2000
GROUP BY ROLLUP (deptno)
ORDER BY 1;

DEPTNO C1
______ __
    10 2
    20 3
    30 1
       6

10.2.2 CUBE

CUBE는 지정한 표현식의 모든 조합을 집계한다.

CUBE(a) ==> (a), ()
CUBE(a, b) ==> (a, b), (a), (b), ()
CUBE(a, b, c) ==> (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ()

SELECT deptno, COUNT(*) AS c1
FROM emp
WHERE sal > 2000
GROUP BY CUBE(deptno)
ORDER BY 1;

DEPTNO C1
______ __
    10 2
    20 3
    30 1
       6

10.2.3 GROUPING SETS

GROUPING SETS은 지정한 행 그룹으로 행을 집계한다. 행 그룹으로 ROLLUP과 CUBE를 사용할 수도 있다.

GROUPING SET(a, b) ==> (a), (b)
GROUPING SET((a, b), a, ()) ==> (a, b), (a), ()
GROUPING SET(a, ROLLUP, (b)) ==> (a), (b), ()
GROUPING SET(a, ROLLUP, (b, c)) ==> (a), (b, c), (b), ()
GROUPING SET(a, b, ROLLUP, (c)) ==> (a), (b), (c), ()

SELECT deptno, job, COUNT(*) AS c1
FROM emp
WHERE sal > 2000
GROUP BY GROUPING SETs (deptno, job)
ORDER BY 1, 2;

DEPTNO JOB       C1
______ _________ __
    10           2
    20           3
    30           1
       ANALYST   2
       MANAGER   2
       PERSIDENT 1

SELECT deptno, job, COUNT(*) AS c1
FROM emp
WHERE sal > 2000
GROUP BY GROUPING SETs (deptno, ROLLUP, job)
ORDER BY 1, 2;

DEPTNO JOB       C1
______ _________ __
    10           2
    20           3
    30           1
       ANALYST   2
       MANAGER   2
       PERSIDENT 1
                 6

10.2.4 조합 열

조합 열은 하나의 단위로 처리되는 열의 조합이다.

ROLLUP((a, b)) ==> (a, b), ()
ROLLUP(a,(b, c)) ==> (a, b, c), (a), ()
ROLLUP((a,b), c) ==> (a, b, c), (a, b), ()

10.2.5 연결 그룹

연결 그룹을 사용하면 행 그룹을 간결하게 작성할 수 있다.

a, ROLLUP, (b) ==> (a, b), (a)
a, ROLLUP, (b, c) ==> (a, b, c), (a, b), (a)
a, ROLLUP, (b), ROLLUP (c) ==> (a, b, c), (a, b), (a, c), (a)
GROUPING SETS(a, b), GROUPING SETS(c, d) ==> (a, c), (a, d), (b, c), (b, d)

10.2.6 관련 함수

GROUPING 함수

GROUPING은 expr이 행 그룹에 포함되면 0, 포함되지 않으면 1을 반환한다.

SELECT deptno, job, COUNT(*) AS c1
    , GROUPING(deptno) AS g1, GROUPING(job) AS g2
FROM emp
WHERE sal > 2000
GROUP BY ROLLUP (deptno, job)
ORDER BY 1, 2;

GROUPING_ID 함수

GROUPING_ID는 GROUPING 함수의 결과 값을 연결한 값의 비트 벡터에 해당하는 숫자 값을 반환한다.

GROUP_ID 함수

GROUP_ID는 중복되지 않은 행 그룹은 0, 중복된 행 그룹은 1을 반환한다.

10.3 HAVING 절

HAVING 절을 사용하면 조회할 행 그룹을 선택할 수 있다. (WHERE 절과 유사하게 동작한다.) WHERE 절은 GROUP BY 절보다 먼저 수행되기 때문에 집계 함수를 사용하면 에러가 발생한다. 이때 HAVING 절을 사용하면 에러가 발생하지 않는다. (GROUP BY 절 없이도 사용가능하다.)

SELECT SUM(sal) AS sal FROM emp HAVING SUM(sal) > 2000;

HAVING 절은 SELECT 절보다 먼저 수행되고, SELECT 절에 기술되지 않는 집계 함수를 사용해도 무방하다.

# 집계 후 HAVING 절에서 행 그룹 전체를 제외(비효율적임)
SELECT deptno, SUM(sal) AS sal
FROM emp
GROUP BY deptno
HAVING deptno <> 30
ORDER BY 1;

# WHERE 절에서 집계 대상이 아닌 행 제외(바람직)
SELECT deptno, SUM(sal) AS sal
FROM emp
WHERE deptno <> 30
GROUP BY deptno
ORDER BY 1;

HAVING절에서 GROUP_ID 함수를 사용하면 중복된 행 그룹을 제외할 수 있다.