마농의 SQL 백문백답: 단순하고 쉽게 작성하는 SQL 노하우 [5회]

분석함수(Analytic Function)

단순하고 쉽게 작성하는 SQL 노하우를 공유하자!

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다. 앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 시간에는 WITH 문에서의 재귀 참조 방식을 이용한 계층쿼리 문제를 풀어보았다. 이번 시간에는 분석함수 문제를 풀어보겠다. 분석함수로 풀어낼 수 있는 다양한 문제의 해법을 제시하고, 이를 통해 분석함수에 대해 배워보는 시간을 갖도록 하자.

[질문 19] 사원의 급여 출력 시 급여합계를 함께 출력하고 싶어요.

10번 부서 사원의 부서번호, 사원번호, 급여, 그리고 전체 급여 합계를 출력하고자 한다.

SELECT deptno
, empno
, sal
, SUM(sal) sum_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno
;
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

위 쿼리를 시도해 보면 오류 메시지를 만나게 된다. 일반 항목과 집계 항목을 함께 출력하려고 해서 발생되는 에러다. 에러를 해결하기 위해서는 일반 항목을 GROUP BY의 기준항목으로 지정하는 방법과 일반항목을 제거하고 집게항목만 출력하는 방법이 있을 것이다.

그러나 일반 항목들을 GROUP BY에 추가하는 것은 무의미하다.

SELECT deptno
, empno
, sal
, SUM(sal) sum_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno, empno, sal
;

집계 기준은 부서번호 뿐이지만, 사원번호와 급여가 GROUP BY에 추가되면서 급여 합계는 구해지지 않는다.

이번에는 일반항목을 제거하고 집계만 구해보자.

SELECT deptno
, SUM(sal) sum_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno
;

집계 결과는 맞게 나왔지만, 나머지 항목들을 조회할 수는 없다.

문제는 이 두 가지 결과가 하나로 합쳐서 보여줘야 한다는 것이다. 집계함수만 가지고는 원하는 결과를 바로 얻을 수 없다. 이때 사용할 수 있는 것이 바로 분석함수를 이용하는 방법이다.

SELECT deptno
, empno
, sal
, SUM(sal) OVER() sum_sal
FROM emp
WHERE deptno = 10
;

집게 함수인 SUM()에 OVER()만 붙여줬을 뿐인데 결과가 쉽게 도출되었다. 이 구문이 바로 분석함수 구문이며, 일반 조회 결과에 집계결과를 추가해서 조회할 때 매우 유용할 것이다.

만약 이 구문이 없었다면 이 문제를 어떻게 풀어야 할까

[질문 20] 분석함수를 사용하지 않고 위 19번 문제를 풀고 싶어요.

위 18번 문제를 풀면서 알아낸 사항은 일반 조회항목과 집계항목을 함께 표현할 수는 없다는 것이다. 결국 집계결과를 별도로 구해 조인하는 방법이 있을 것이다.

SELECT a.deptno
, a.empno
, a.sal
, b.sum_sal
FROM emp a
, (SELECT deptno
, SUM(sal) sum_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno
) b
WHERE a.deptno = b.deptno
;

다음과 같이 스칼라서브쿼리를 이용할 수도 있다.

SELECT a.deptno
, a.empno
, a.sal
, (SELECT SUM(sal) sum_sal
FROM emp b
WHERE b.deptno = a.deptno
) sum_sal
FROM emp a
WHERE deptno = 10
;

어떤 방법을 사용하든 성능이나 가독성면에서 분석함수보다 나을 수는 없다.

[질문 21] 사원의 급여 누적합계를 구하고 싶어요.

이번에는 전체 합계가 아닌 순차적인 누적합계를 구하는 문제이다. 10번 부서에 대해 사원번호순으로 누적 급여를 구해보자

SELECT deptno
, empno
, sal
, SUM(sal) OVER(ORDER BY empno) sum_sal
FROM emp
WHERE deptno = 10
;

앞서 배운 분석함수 구문인 OVER() 안에 정렬 구문이 추가된 형태다. 첫 번째 사원은 자신의 급여를, 두 번째 사원은 첫 번째 사원과 자신의 급여 합계를, 세 번째는 1, 2, 3행의 급여합계를 출력하는, 즉 정렬된 순서대로의 누적 합계를 구해주는 구문이다.

[질문 22] 사원의 급여와 부서별 급여합계를 함께 보여주고 싶어요.

이전 질문에서는 특정부서의 전체 합계를 구했다면, 이번에는 여러 부서에 대해 부서별 합계를 표현하는 문제다.

SELECT deptno
, empno
, sal
, SUM(sal) OVER(PARTITION BY deptno) sum_sal
FROM emp
;

이번에는 OVER 구문 안에 PARTITION BY 구문이 사용됐다. PARTITION BY는 집계함수의 GROUP BY와 같은 기능을 한다.

[질문 23] 사원의 급여와 부서별 급여 누적 합계를 함께 보여주고 싶어요.

이번 문제는 22번 문제와 23번 문제의 결합형 문제다.

SELECT deptno
, empno
, sal
, SUM(sal) OVER(PARTITION BY deptno ORDER BY empno) sum_sal
FROM emp
;

PARTITION BY 구문과 ORDER BY 구문이 동시에 사용된 예다.

정리하며

집계함수에 OVER()를 붙여줌으로써 분석함수가 된다. 집계함수는 집계 결과를 하나로 집게해서 보여주는 반면, 분석함수는 집계결과를 행마다 보여준다. 즉, 집계 결과는 같지만 보여주는 형태가 다르다.

이번시간에는 분석함수의 기본 구문을 살펴보았다.
OVER(PARTITION BY 그룹기준항목 ORDER BY 정렬기준항목)
- OVER: 분석함수임을 나타내는 키워드
- PARTITION BY: 집계 대상 그룹을 지정하는 구문
- ORDER BY: 집계 대상을 정렬하는 구문

다음시간에는 분석함수에 대해 좀 더 자세히 다루어 보도록 하겠다. (다음 회에 계속)