[SQL]분석함수(Analytic Function)[7회]

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

분석함수(Analytic Function)(3)

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

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

지난 시간에는 분석함수 중 이전/이후 행을 조회할 수 있는 LAG/LEAD 함수를 살펴보았다. 또한 분석함수의 계산 범위를 한정짓는 Window 절 사용법도 함께 배웠다. 이번 시간에도 분석함수의 다른 기능들을 살펴볼 생각이다. 분석함수로 풀어낼 수 있는 다양한 문제의 해법을 제시하고, 이를 통해 분석함수에 대해 배워보는 시간을 갖도록 하겠다.

[질문 29] 사원의 연봉 순위를 구하고 싶어요.

이번 문제는 순위를 구하는 문제다. 분석함수 중 RANK 함수를 알고 있다면 간단히 해결되는 문제다.

SELECT empno, sal
     , RANK() OVER(ORDER BY sal DESC) rk
  FROM emp
;

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

우 리는 구문 이외에 RANK 함수의 결과를 눈여겨 볼 필요가 있다. 결과를 보면 연봉이 같은 사원은 동순위로 표시가 된다. 그리고 동순위의 다음 순위는 중간 순위를 건너뛰게 된다. 2등이 2명이면 다음 순위는 3등이 아닌 4등이 된다.

순위 함수에서 정렬항목은 순위를 구하는 기준이 된다. 따라서 ORDER BY 구문은 생략할 수 없다. 또 한 가지 특징은 계산 범위를 한정하는 WINDOW 절을 사용하지 못한다는 것이다. PARTITION BY 구문은 사용할 수 있다. 그룹별 순위를 구할 때 PARTITION BY 구문이 사용된다.

-- 1. Order By 절 필수 입력(생략 불가)
SELECT empno, sal
     , RANK() OVER( ) rk
  FROM emp
;
ORA-30485: 윈도우 지정에 ORDER BY 표현식이 없습니다

-- 2. Window 절 사용 불가
SELECT empno, sal
     , RANK() OVER(ORDER BY sal DESC
       ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) rk
  FROM emp
;
ORA-00907: 누락된 우괄호

-- 3. PARTITION BY 절 사용 가능
SELECT deptno, empno, sal
     , RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rk
  FROM emp
;

[질문 30] 동순위와 관계없이 순위가 이어져서 나오게 하고 싶어요.

29번의 문제와 달리 2등이 동순위가 있더라도 3등을 건너뛰지 말아야 한다. 이번 문제는 DENSE_RANK 사용에 관한 것이다.

SELECT empno, sal
     , DENSE_RANK() OVER(ORDER BY sal DESC) dr
  FROM emp
;

RANK 와는 다른 형태로 순위가 출력됐다. 2등이 동순위 2명이고, 바로 다음 순위는 4등이 아닌 3등이 된다.

[질문 31] 동순위를 무시하고 모두 다른 순번을 부여하고 싶어요.

이번에는 RANK, DENSE_RANK 가 아닌 또 다른 순위 함수가 필요하다. 이번 문제는 ROW_NUMBER 사용에 관한 것이다.

SELECT empno, sal
     , ROW_NUMBER() OVER(ORDER BY sal DESC) rn
  FROM emp
;

순위를 구하는 분석함수 세 가지를 배웠다.

[질문 32] 분석함수 없이 순위를 구할 수 있을까?

RANK 함수를 이용해 손쉽게 순위를 구할 수 있다. 그런데 이 분석함수를 사용할 수 없다면, 어떻게 순위를 구할 수 있을까? 이런 상황은 의외로 자주 접하게 된다. 분석함수를 지원하지 않는 DBMS를 사용하는 경우다.

이 문제를 풀기 위해서는 ‘순위’의 개념을 다른 각도로 살펴볼 필요가 있다. 1등인 직원은 자신보다 높은 점수를 가진 직원이 없다. 2등인 직원은 자신보다 높은 점수를 가진 직원이 1명 있는 셈이다.

그 렇다면 5등인 직원은 어떨까? 자신보다 높은 점수를 가진 직원이 4명 있는 것이다. 우리가 발견한 이 일정한 규칙을 기반으로 쿼리를 작성하면 된다. 다시 말하면 사원 테이블을 기준으로 점수가 더 큰 직원을 찾은 다음, 찾은 직원 수에 1을 더하면 순위가 나온다.

SELECT a.empno, a.sal
     , COUNT(b.empno) + 1 rk
  FROM emp a
     , emp b
 WHERE a.sal < b.sal(+)
 GROUP BY a.empno, a.sal
 ORDER BY rk
;

사원 테이블을 두 번 사용했다. a는 기준이 되는 테이블이고, b는 비교 대상 테이블이다. 기준테이블의 급여보다 큰 급여를 받는 직원을 비교대상 테이블에서 검색한다. 이때 1등인 직원보다 급여가 많은 직원은 없기 때문에 1등인 직원도 나오게 하기 위해 아우터 조인을 사용했다. 그리고 기준테이블의 사원번호를 기준으로 그룹핑하여 건수를 구한다. 건수만 한다.

문제를 풀기 위해 셀프조인, 아우터조인, 그룹바이, 카운트 등을 이용했다.

[질문 33] 그룹별 순위도 분석함수 없이 구할 수 있을까?

앞선 [질문 33] 의 풀이를 약간만 개선하면 문제를 해결할 수 있다.

SELECT a.deptno, a.empno, a.sal
     , COUNT(b.empno) + 1 rk
  FROM emp a
     , emp b
 WHERE a.deptno = b.deptno(+)
   AND a.sal < b.sal(+)
 GROUP BY a.deptno, a.empno, a.sal
 ORDER BY deptno, rk
;

기준급여보다 높은 급여를 받는 직원을 찾는 것은 동일하다. 여기에 같은 부서에서만 찾는다는 조건만 추가하면 된다.

[질문 34] DENSE_RANK 와 ROW_NUMBER 도 분석함수 없이 구해 보자.

-- DENSE_RANK --
SELECT a.empno, a.sal
     , COUNT(DISTINCT b.sal) + 1 dr
  FROM emp a
     , emp b
 WHERE a.sal < b.sal(+)
 GROUP BY a.empno, a.sal
 ORDER BY dr
;

-- ROW_NUMBER --
SELECT empno, sal
     , ROWNUM rn
  FROM (SELECT empno, sal
          FROM emp
         ORDER BY sal DESC
        )
;

정리하며

분석함수 중 순위를 구하는 함수를 배웠다.
- RANK : 일반적인 순위이다. 공동 순위가 있을 경우 다음 순위를 건너뛴다.
- DENSE_RANK : 공동순위와 관계없이 연속된 순위를 부여한다.
- ROW_NUMBER : 공동순위 없이 일련번호를 부여한다.

분석함수 없이 순위를 구하는 방법을 배웠다.
- 자신의 급여보다 큰 직원을 검색하여 검색된 수에 1을 더하면 순위가 된다.
- Self Join, Outer Join, Group By Count가 사용된다.

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

[출처 : http://www.dbguide.net/knowledge.db?cmd=view&boardUid=188381&boardConfigUid=19&boardStep=&categoryUid=205]