GROUP BY

GROUP BY라고 하는 쿼리문에 대해 들어본 적은 있을 것이다. 들어본 적이 없더라도 해당 절은 실무에서도 유용하게 사용된다고 생각하기에 알아두면 좋을 것이라고 생각한다.

 

GROUP BY

GROUP BY는 어디에 사용해야 하나? 예시를 하나 들어보자. 회사에는 부서 번호가 존재하고 이 부서 번호에 해당되는 사람들의 평균 급여를 확인하고 싶다면 어떻게 해야할까? SCOTT 계정으로 확인해보자.

 

SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10;

이런 식으로 쿼리를 조회해 평균을 구할 수 있다. 현재는 10에 해당하는 평균 급여만 확인했는데 20, 30에 해당하는 평균 급여도 따로 구하고 싶을 땐 어떻게 해야할까?

 

SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30;

물론 이렇게 각각 따로 쿼리를 호출해서 사용할 수도 있다. 프레임워크 단에서는 이 값을 따로 보여주거나 할 것이다. 하지만 3개가 아닌 엄청난 많은 수의 평균 급여를 확인하는 것에는 분명 바람직하지 않다. 물론 하나의 쿼리로 GROUP BY를 사용하지 않고 처리할 수도 있다.

 

SELECT AVG(SAL) AS AVERAGE, '10' AS DEPTNO FROM EMP WHERE DEPTNO = 10
UNION ALL
SELECT AVG(SAL) AS AVERAGE, '20' AS DEPTNO FROM EMP WHERE DEPTNO = 20
UNION ALL
SELECT AVG(SAL) AS AVERAGE, '30' AS DEPTNO FROM EMP WHERE DEPTNO = 30

집합을 사용해서 각자를 호출해 아랫쪽으로 데이터를 쌓아 보여지게 할 수도 있다. 하지만 집합의 경우에 해당 쿼리를 수정하는 일이 생기면 일일이 묶었던 쿼리문을 하나씩 다 호출해줘야하는 경우가 생긴다. 따라서 부적합하다고 볼 수 있다.

 

SELECT AVG(SAL) AS AVERAGE, DEPTNO FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO ASC

이번엔 GROUP BY를 사용해보자. 집합보다 훨씬 간결해졌고 쿼리 수정에도 용이해졌다. ORDER BY의 경우에 위의 결과물과 순서를 맞춰주고 싶어서 한거기에 신경쓰지 않아도 된다. 이렇게 GROUP BY를 사용하면 원하는 컬럼만의 데이터를 추출해 확인할 수 있다.

 

GROUP BY 주의사항

GROUP BY도 주의사항은 있다. SAL 컬럼의 경우에 AVG 내장함수를 사용해 여러 결과의 행들을 평균처리할 수 있기에 문제가 되지 않았다. 여기서 AVG 내장 함수를 빼보자.

 

다음과 같은 에러를 확인할 수 있다. 그 이유는 GROUP BY를 통해 동일한 컬럼을 묶어 하나로 출력하려고 했으나 해당 컬럼에 여러 데이터 값이 출력되었기에 이를 표현할 수 없어 발생한 문제이다. 따라서 GROUP BY를 사용하는데 SELECT 절 또한 확인이 필요하다.

 

HAVING

GROUP BY에 조건을 주려할 때 사용하는 절이다. 

 

SELECT
	AVG(SAL) AS AVERAGE,
	DEPTNO
FROM
	EMP
GROUP BY
	DEPTNO
ORDER BY
	DEPTNO ASC

맨 처음에 만들었던 이 쿼리문을 활용해보자. 평균 급여가 2000이상의 부서 번호인 것만 출력해보자,

 

SELECT
	AVG(SAL) AS AVERAGE,
	DEPTNO
FROM
	EMP
GROUP BY
	DEPTNO
HAVING
	AVG(SAL) >= 2000
ORDER BY
	DEPTNO ASC

기존 쿼리문에서 HAVING 절을 사용하면 된다. WHERE절과 비슷하기에 HAVING절 대신에 사용하면 어떻게 될까?

 

GROUP BY에서는 사용할 수 없는 걸 볼 수 있다. WHERE절과 HAVING절을 동시에 사용하는 경우를 확인해보자.

 

SELECT
	DEPTNO,
	JOB,
	AVG(SAL)
FROM
	EMP
WHERE
	SAL <= 3000
GROUP BY
	DEPTNO,
	JOB
HAVING
	AVG(SAL) >= 2000
ORDER BY
	DEPTNO ASC;

이렇게 실행시킬 수 있다. 단순히 실행보다는 어떤 차이가 있는지 확인이 필요하다.

 

SELECT
	DEPTNO,
	JOB,
	AVG(SAL)
FROM
	EMP
GROUP BY
	DEPTNO,
	JOB
HAVING
	AVG(SAL) >= 2000
ORDER BY
	DEPTNO ASC;

이 쿼리의 결과와 비교해보면 10번 부서번호의 PRESIDENT 직업 데이터가 출력되지 않는 차이가 있다. 좀 더 확실함을 알기 위해 전체 테이블을 조회해보자.

 

PRESIDENT의 SAL은 5000이어서 WHERE절에서 걸린 것 같다. 여기서 알 수 있는 것은 HAVING절 이전에 WHERE절이 먼저 실행되면서 해당 데이터가 제외되었고 거기서 GROUP BY가 일어났기 때문에 다음과 같은 결과를 얻었던 것이다.

 

GROUP BY 함수

실제로 바로 적용하기는 어려울 것이라 생각들지만 나 또한 미리 정의해두고 공부를 위해 따로 적어두려고 한다. 하나의 예시 상황이 있다고 가정하겠다. 위에서 전체 쿼리를 조회한 것에서 직업군 별로 존재하는 사람의 수와 가장 높은 급여, 총합 급여, 평균 급여를 출력해보자.

 

SELECT
	DEPTNO,
	JOB,
	count(*),
	MAX(SAL) AS max_sal,
	SUM(SAL) AS sum_sal,
	AVG(SAL) AS avg_sal
FROM
	EMP
GROUP BY
	DEPTNO,
	JOB
ORDER BY
	DEPTNO ASC

그동안 배웠던 GROUP BY절을 잘 사용한다면 원하는 결과값을 출력해낼 수 있다, 여기에 ROLLUP이라는 함수를 한 번 사용해보자.

 

SELECT
	DEPTNO,
	JOB,
	count(*),
	MAX(SAL) AS max_sal,
	SUM(SAL) AS sum_sal,
	AVG(SAL) AS avg_sal
FROM
	EMP
GROUP BY
	ROLLUP(DEPTNO, JOB)
ORDER BY
	DEPTNO ASC

GROUP BY절에 컬럼이 아닌 ROLLUP() 함수를 씌워서 조회해보자.

 

각 그룹별로의 COUNT(*)부터의 더한 것에 대한 데이터가 추가되었고 가장 밑에 총합이 구해졌다. 이번엔 CUBE라는 함수를 사용해보겠다.

 

SELECT
	DEPTNO,
	JOB,
	count(*),
	MAX(SAL) AS max_sal,
	SUM(SAL) AS sum_sal,
	AVG(SAL) AS avg_sal
FROM
	EMP
GROUP BY
	CUBE(DEPTNO, JOB)
ORDER BY
	DEPTNO ASC

이번엔 결과가 어떻게 나올까?

 

ROLLUP 함수와 무언가 비슷하지만 몇 가지 행이 더 추가된 것을 확인할 수 있다. ROLLUP의 경우에는 각 그룹의 합과 전체 합 정도만 출력해주었는데 CUBE의 경우에는 그와 추가되어 직업 별로 통계를 내주었다. N만큼 늘어나게 된다면 2의 ^N까지 기하급수적으로 늘어날 수 있기에 상황에 맞게 처리해야될 듯하다.

 

SELECT
	DEPTNO,
	JOB,
	count(*)
FROM
	EMP
GROUP BY
	DEPTNO, ROLLUP(JOB)
ORDER BY
	DEPTNO ASC

DEPTNO를 먼저 그룹화해주고 JOB 부분을 ROLLUP 함수에 지정해보았다.

 

위와 같은 결과를 얻을 수 있다. 적절히 상황에 맞게 잘 사용할 수 있기 위해서는 많은 쿼리문 조회에 대한 경험이 필요할 것 같다.