GROUP BY 함수

어제 GROUP BY를 정리하면서 ROLLUP, CUBE 함수에 대해 잠시 알아보았다. 또 어떤 함수가 있는지 알아보도록 하겠다.

 

GROUPING SETS

GROUPING SETS의 경우에는 같은 수준의 그룹화 열이 여러 개일 때 열별 그룹화를 통해 값을 조회하는데 사용된다.

 

SELECT
	DEPTNO,
	JOB,
	COUNT(*)
FROM
	EMP
GROUP BY
	GROUPING SETS(DEPTNO, JOB)

 

해당 결과는 어떻게 값이 도출될까?

 

그룹화를 각각 따로 진행해서 결과를 조회해낸 것을 확인할 수 있다. 

 

GROUPING

그룹화 함수는 데이터 자체의 가공이나 특별한 연산 기능을 수행하지는 않는다. 하지만 그룹화한 데이터의 식별이 쉽고 가독성이 높아지기에 많이 사용하고 있다. GROUPING과 GROUPING_ID 함수가 있는데 GROUPING부터 알아보자.

 

SELECT
	DEPTNO,
	JOB,
	COUNT(*),
	MAX(SAL),
	SUM(SAL),
	AVG(SAL),
	GROUPING(DEPTNO),
	GROUPING(JOB)
FROM
	EMP
GROUP BY
	CUBE(DEPTNO, JOB)
ORDER BY
	DEPTNO, JOB;

위와 같이 코드를 작성해보고 출력해보자. 어떤 결과를 얻을 수 있을까?

 

CUBE 함수는 이전 게시글에서도 다루었지만 부서번호를 그룹화한 데이터와 직업 별 그룹화한 데이터의 결과를 출력해주면서 ROLLUP 함수의 각 그룹별 정보를 더하고 평균값을 내주는 등의 데이터까지 조회해준다. GROUPING을 한 컬럼이 추가되었는데 0과 1은 그룹화한 데이터에 조금 더 식별성을 추가해준 것임을 확인할 수 있다.

 

먼저 DEPTNO이 10이고 CLERK 직업인 데이터만 생각했을 때, 이 데이터는 어떤 데이터일지 생각해보자. 바로 DEPTNO, JOB을 그룹화한 데이터이다. 그렇기 때문에 모두 0이 조회되었음을 알 수 있고 ROLLUP된 부분을 확인해보자. 이는 DEPTNO의 총계를 나타내기 때문에 JOB컬럼이 그룹화되지 않았다. 이를 1로 표현함으로써 무엇이 그룹화되었는지 조금 더 식별성을 추가해주었다고 생각하면 된다.

 

SELECT
	DECODE(GROUPING(DEPTNO), 1, 'ALL_DEPT', DEPTNO) AS DEPTNO,
	DECODE(GROUPING(JOB), 1, 'ALL_JOB', JOB) AS JOB,
	COUNT(*),
	MAX(SAL),
	SUM(SAL),
	AVG(SAL)
FROM
	EMP
GROUP BY
	CUBE(DEPTNO, JOB)
ORDER BY
	DEPTNO,
	JOB

이런 식으로 DECODE문을 활용해 GROUPING 함수를 적용해 결과를 표기할 수도 있다. 

 

LISTAGG

LISTAGG 함수를 알아보기 전에 이 함수는 오라클 11g 버전 이상부터 사용할 수 있다. 먼저 부서번호가 10인 사람을 조회해보자.

 

SELECT ENAME FROM EMP WHERE DEPTNO = 10

기본 중의 기본 쿼리이기에 쉽게 작성할 수 있다. 여기서 부서번호와 이름을 그룹화하면 어떻게 될까?

 

SELECT DEPTNO, ENAME FROM EMP GROUP BY DEPTNO, ENAME

쿼리는 이런 식으로 작성될 것이다.

 

ENAME은 GROUP BY에 명시하지 않는 이상 이러한 데이터를 얻을 수밖에 없다. 사실상 GROUP BY를 하지 않아도 되는 쿼리가 되어버린 것이다. 하지만 우리는 부서번호 10에 해당하는 사람들의 이름, 20에 해당하는 사람들의 이름을 기술하고 싶다고 가정하면 여기서 LISTAGG 함수를 사용하면 된다.

 

SELECT
	DEPTNO,
	LISTAGG(ENAME, ', ') WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM
	EMP
GROUP BY
	DEPTNO

이렇게 작성해보고 결과를 실행해보자.

 

의외로 깔끔하게 나와 실무적으로도 활용하기 적합하다고 생각이 들었다.

 

아래부터는 그동안 GROUP BY에 대해 해온 것에 간단히 예제를 구성해보고 쿼리를 작성해보려고 한다. 더보기를 눌러야 쿼리가 나오기 때문에 생각해보면서 짜본다. EMP 테이블에 대한 정보는 위와 같으니 참고하면서 작성해보자.

 

문제 1.

EMP 테이블을 이용해 부서번호, 평균 급여, 최고 급여, 최저 급여, 사원 수를 출력한다.

SELECT
	DEPTNO,
	TRUNC(AVG(SAL)) AS AVG_SAL,
	MAX(SAL) AS MAX_SAL,
	MIN(SAL) AS MIN_SAL,
	count(*) AS CNT
FROM
	EMP
GROUP BY
	DEPTNO
ORDER BY
	DEPTNO DESC

 

문제 2.

같은 직책에 종사하는 사원이 3명 이상인 직책과 인원수를 출력한다.

SELECT
	JOB,
	COUNT(*)
FROM
	EMP
GROUP BY
	JOB
HAVING
	count(*) >= 3

 

문제 3.

사원들의 입사 년도를 기준으로 부서별로 몇 명이 입사했는지 출력한다. (내 경우에는 SCOTT 계정을 직접 만들었기에 HIREDATE가 모두 2024년으로 나와있다.)

SELECT
	EXTRACT(YEAR FROM HIREDATE) AS HIRE_YEAR,
	DEPTNO,
	count(*) AS CNT
FROM
	EMP
GROUP BY
	EXTRACT(YEAR FROM HIREDATE),
	DEPTNO

 

문제 4.

추가 수당을 받는 사원 수와 받지 않는 사원 수를 출력한다.

SELECT
	CASE
		WHEN COMM >= 0 THEN 'O'
		ELSE 'X'
	END AS EXIST_COMM,
	count(*) AS CNT
FROM
	EMP
GROUP BY
	CASE
		WHEN COMM >= 0 THEN 'O'
		ELSE 'X'
	END

 

문제 5. (연도 이슈로 인해 책과 다르게 수정)

각 부서의 최고 급여, 급여 합, 평균 급여를 출력하고 각 부서별 소계와 총계를 출력한다.

SELECT
	DEPTNO,
	count(*) AS CNT,
	MAX(SAL) AS MAX_SAL,
	SUM(SAL) AS SUM_SAL,
	AVG(SAL) AS AVG_SAL
FROM
	EMP
GROUP BY
	ROLLUP(DEPTNO)
ORDER BY
	DEPTNO