마농의 SQL 백문백답: FIRST_VALUE/LAST_VALUE 단순하고 쉽게 작성하는 SQL 노하우(8회)

분석함수(Analytic Function)(4)

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

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다.

앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 시간에는 분석함수 중 순위를 구하는 RANK, DENSE_RANK, ROW_NUMBER 함수를 살펴보았다. 또한 분석함수 없이 순위를 구하는 방법도 함께 배웠다. 이번 시간에도 분석함수의 다른 기능들을 살펴볼 생각이다. 분석함수로 풀어낼 수 있는 다양한 문제의 해법을 제시하고, 이를 통해 분석함수에 대해 알아보는 시간을 갖도록 하겠다.

[질문 34] 사원을 4개의 그룹으로 나누고 싶어요.

이번 문제는 전체 사원을 4개의 그룹으로 나누는 문제다. 이 문제를 풀기 위해서는 전체 대상 인원수를 알아야 하며, 그룹별 배정 인원도 구해야 할 것이다.

emp 테이블에는 14명의 사원이 존재하며, 이를 4개 그룹으로 나누면 인원이 정확하게 나누어지지 않게 된다. 남은 인원을 앞쪽 그룹에 배정한다고 하면, 14명의 인원은 4, 4, 3, 3으로 나뉠 것이다.

이를 로직으로 구현하고자 한다면 쉬운 일은 아닐 것이다. 하지만 분석함수 중 TILE 함수를 알고 있다면, 간단히 해결되는 문제다.

SELECT empno, ename
, NTILE(4) OVER(ORDER BY empno) nt
FROM emp
;

구문은 앞서 배운 분석함수 구문과 동일하다. 순위를 구하는 NTILE와 분석함수임을 나타내는 OVER 구문을 사용했다.

NTILE 함수도 앞서 배운 순위함수의 일종으로 동일한 제약사항을 가진다. 그룹을 나누기 위한 정렬 기준이 반드시 필요하며, 계산 범위를 한정하는 WINDOW 절은 사용하지 못한다. PARTITION BY 구문은 사용 가능하다.

NTILE의 인자로 나누고자 하는 그룹의 개수 4가 주어지고, 그룹을 나누는 정렬기준이 OVER 안에 ORDER BY 절로 주어진다. 결과는 예상했던 대로 (4,4,3,3)의 개수로 나누어 순번(1,2,3,4)을 부여한 형태다.

[질문 35] 조회 결과의 첫 번째 값을 함께 조회하고 싶어요.

이번 문제는 첫 번째 값을 구하는 문제다. 분석함수 중 FIRST_VALUE 사용에 관한 문제다.

SELECT empno, sal
, FIRST_VALUE(sal) OVER(ORDER BY empno) fv
FROM emp
;

첫 번째 값을 의미하는 FIRST_VALUE가 사용되었다. 정렬 기준으로 사원번호가 OVER 절 안의 ORDER BY 구문에서 사용되었다. 사원번호 순으로 정렬하였을 때, 가장 사원번호가 빠른 7369의 800 값이 조회되는 형태다.

[질문 36] 조회 결과의 마지막 행의 값을 함께 조회하고 싶어요.

이번 문제는 마지막 행의 값을 구하는 문제다. 눈치가 빠른 독자라면 LAST_VALUE가 바로 떠오를 것이다.

SELECT empno, sal
, LAST_VALUE(sal) OVER(ORDER BY empno) lv
FROM emp
;

35번 문제의 FIRST_VALUE 와 동일한 형태로 LAST_VALUE를 사용했다. 그런데 원하는 결과가 아니다. 마지막 값이라면 1300이 나왔어야 한다. 그런데 각 행의 급여가 그대로 출력됐다.

예상치 못한 결과에 당황스러울 것이다. 어디서 잘못된 것일까 앞서 배웠던 ORDER BY 구문과 생략된 WINDOW 절에 대해 다시 한 번 생각해 봐야 한다.

순위 함수인 RANK, DENSE_RANK, ROW_NUMBER, NTILE 등은 ORDER BY 절이 필수이며 WINDOW 절은 사용할 수 없었다.

그러나 순위함수가 아닌 다른 분석함수의 경우엔 그 반대다. ORDER BY 구문은 선택사항이며, ORDER BY 구문을 사용했을 때에는 WINDOW 절이 함께 따라오게 된다.

WINDOW 절은 ORDER BY 없이는 사용할 수 없지만, ORDER BY 구문을 사용하면서 생략은 가능하다. 단, 구문만 생략된 것이지 내부적으로는 구문의 의미 자체가 없는 것이 아니다.

위에서 ORDER BY empno 를 사용했다면, 기본적으로 다음 WINDOW 구문이 생략된 것이다.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

즉 분석함수 수행 범위가 첫 행부터 현재 행까지로 제한된다. 결국 현재 행까지 범위에서 마지막 자료는 현재행인 것이다.

문제의 원인을 알았으니 문제를 해결해 보자.
분석 범위를 현재 행까지가 아닌 마지막 행까지로 조정하면 될 것이다.

SELECT empno, sal
, LAST_VALUE(sal) OVER(ORDER BY empno
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) lv
FROM emp
;

[질문 37] 사원번호 순으로 커미션 값을 보고 싶어요, 단, 커미션이 없는 사원은 이전 사원의 커미션과 동일한 값을 보여주고 싶어요.

원하는 결과 값은 다음과 같다.

없는 값은 이전 값을 보여주면 된다면, 이전 값을 구하는 LAG 함수를 이용할 수 있을 것이다.

SELECT empno, comm
, NVL(comm, LAG(comm) OVER(ORDER BY empno)) comm_1
FROM emp
;

LAG 함수를 이용했으나 바로 이전 값만 참조되었을 뿐 널 값이 연속으로 나오는 경우에는 원하는 결과를 얻지 못했다.

널 값을 제외하고 이전 값을 가져오도록 하는 옵션이 있다. 바로 IGNORE NULLS 구문을 추가하면 된다.

SELECT empno, comm
, NVL(comm, LAG(comm IGNORE NULLS) OVER(ORDER BY empno)) comm_10g
, NVL(comm, LAG(comm) IGNORE NULLS OVER(ORDER BY empno)) comm_11g
FROM emp
;

IGNORE NULLS 구문 추가로 쉽게 문제를 해결하였다. 11G에서 구문의 위치가 바뀐 것에 주의하자. 11G에서는 변경 전 구문까지 두 가지 구문 모두를 지원하지만, 이전 버전에서는 바뀌기 전 구문만 사용 가능하다.

LAG 함수를 NVL 과 함께 사용하여 문제를 풀었다. LAG 함수가 아닌 LAST_VALUE 함수를 사용한다면 NVL 없이 해결될 것이다.

SELECT empno, comm
, LAST_VALUE(comm IGNORE NULLS) OVER(ORDER BY empno) comm_10g
, LAST_VALUE(comm) IGNORE NULLS OVER(ORDER BY empno) comm_11g
FROM emp
;

정리하며

지정한 개수만큼의 그룹으로 나누어 주는 순위함수 NTILE 함수를 배웠다. 처음 값과 마지막 값을 구하는 FIRST_VALUE/LAST_VALUE 함수를 배웠다. LAG/LEAD, FIRST_VALUE/LAST_VALUE 함수에서 사용할 수 있는 NULL 건너뛰기 구문인 IGNORE NULLS 구문을 배웠다.

다음시간에도 다양한 분석함수에 대해 소개하도록 하겠다. (다음 회에 계속)