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

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

분석함수(Analytic Function)(2)

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

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

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

지난 시간에는 집계 결과를 원본 결과와 함께 조회할 수 있는 분석함수 문제를 풀어보았다. 이번 시간에도 분석함수로 풀어낼 수 있는 다양한 문제의 해법을 제시하고, 이를 통해 분석함수에 대해 배워보는 시간을 갖도록 하자.

[질문 24] 사원의 급여를 급여가 많은 순으로 출력하면서, 출력 순서 바로 앞 사원과의 급여 차이를 출력하고 싶어요.

지난 시간에는 급여 합계 문제를 풀어보았으나 이번에는 합계 문제가 아니다. 바로 직전 자료와 이후 자료를 조회해야 하는 문제인데 이 문제도 간단하다. 분석함수 중 이전 값 조회 함수인 LAG를 사용하면 된다.

SELECT empno, sal
     , LAG(sal) OVER(ORDER BY sal DESC) - sal sal_cha
  FROM emp
;

구문은 누적합계를 구할 때 사용했던 구문과 동일하다. 다만 합계를 구하는 SUM 대신 이전 값을 구하는 LAG 를 사용했을 뿐이다. SUM 이라는 집계 함수에 OVER 구문을 사용해 분석함수로 사용한 것과 달리, LAG 함수는 분석함수 전용 함수이다.

[질문 25] 사원의 급여를 급여가 많은 순으로 출력하면서, 출력 순서 바로 뒤 사원과의 급여 차이를 출력하고 싶어요.

24번의 문제에서 바로 앞 사원의 급여를 구하는 LAG 함수를 배웠다. 이번에는 반대로 뒷 사원의 급여를 구하는 문제이다. LAG 함수를 배웠으니 응용이 가능하다. 정렬을 반대로 하여 LAG 함수를 사용한다면 원하는 결과를 얻을 수 있을 것이다.

SELECT empno, sal
     , sal - LAG(sal) OVER(ORDER BY sal ASC) sal_cha
  FROM emp
 ORDER BY sal DESC
;

정렬을 DESC 가 아닌 ASC 로 하여 이전행을 구한 후 최종 DESC 정렬을 추가했다. 응용력을 발휘한 결과이지만 이후 행의 값을 조회하는 함수는 따로 있다.

SELECT empno, sal
     , sal - LEAD(sal) OVER(ORDER BY sal DESC) sal_cha
  FROM emp
;

이전행 값인 LAG 대신 이후 값인 LEAD 를 사용했다.

[질문 26] 직전 행, 2행 앞, 3행 앞 자료를 함께 조회하고 싶어요.

LAG 함수를 이용해 바로 이전행의 값을 조회할 수 있었다. 이번에는 이전행의 이전행, 이전행의 이전행의 이전행의 값을 구하고자 한다. LAG 함수를 중첩해 사용하면 될 것인가?

SELECT empno, sal
     , LAG(sal) OVER(ORDER BY sal) sal_1
     , LAG(LAG(sal) OVER(ORDER BY sal)) OVER(ORDER BY sal) sal_2
  FROM emp
;
ORA-30483: 윈도우 함수를 여기에 사용할 수 없습니다

일반 단일 행 함수의 경우 함수를 중첩해서 사용할 수 있다. 그러나 집계함수나 분석함수의 경우 함수의 중첩 사용은 불가능하다. 이 경우 해결 방안은 간단하다. LAG 함수에서는 몇행 이전 값을 가져올지 두 번째 인자 값을 통해 제어할 수 있다.

SELECT empno, sal
     , LAG(sal) OVER(ORDER BY sal) sal_1
     , LAG(sal, 2) OVER(ORDER BY sal) sal_2
     , LAG(sal, 3) OVER(ORDER BY sal) sal_3
  FROM emp
;

[질문 27] 이전행 값을 조회하되 이전행이 없을 경우 다른 값을 표시하고 싶어요.

LAG 함수 사용시 이전행이 없을 때는 NULL 로 표시된다. 이 값을 다른 값으로 대체하려면? 널 대체 함수인 NVL 을 사용하면 될까?

SELECT empno, sal
     , NVL(LAG(sal) OVER(ORDER BY sal), 0) sal_1
  FROM emp
;

NVL 함수를 추가해 원하는 결과가 나왔다. 그러나 이는 NVL 함수 추가 없이 LAG 함수만으로 가능하다.

SELECT empno, sal
     , LAG(sal, 1, 0) OVER(ORDER BY sal) sal_1
  FROM emp
;

LAG 함수에서는 Null 대체 값을 세 번째 인자 값을 통해 제어한다.

[질문 28] 바로 이전 3행의 평균을 구하고 싶어요.

앞선 26번 질문의 풀이를 이용해 문제를 해결할 수 있을까?

SELECT empno, sal
     , ROUND(
       ( LAG(sal, 1, 0) OVER(ORDER BY sal)
       + LAG(sal, 2, 0) OVER(ORDER BY sal)
       + LAG(sal, 3, 0) OVER(ORDER BY sal)
       ) / 3, 2) AS sal_avg3
  FROM emp
;

원하는 결과가 맞는지 확인해보자. 4번째 행을 보면 앞선 3행의 값(800, 950, 1100)의 평균 값이 정확히 계산된 것을 알 수 있다. 그런데 3번째 행을 보면 앞선 행은 2개(800, 950) 뿐이고 평균은 2개로 나누어 875가 되어야 하지만, 결과는 3개로 나눈 583.33 이 나왔다. 값이 있는지 없는지 체크해 나누는 수를 결정해야 한다면 계산식이 상당히 복잡해 질 것이다. 필자는 SIGN 함수를 이용해 문제를 해결해 보았다.

SELECT empno, sal
     , ROUND(
       ( LAG(sal)       OVER(ORDER BY sal)
       + LAG(sal, 2, 0) OVER(ORDER BY sal)
       + LAG(sal, 3, 0) OVER(ORDER BY sal)
       ) /
       ( SIGN(LAG(sal, 1, 0) OVER(ORDER BY sal))
       + SIGN(LAG(sal, 2, 0) OVER(ORDER BY sal))
       + SIGN(LAG(sal, 3, 0) OVER(ORDER BY sal))
       )
       , 2) AS sal_avg3
  FROM emp
;

이는 결과는 맞게 나오지만 수식이 너무 복잡하다. 개수를 파악하고 나누는 것 보다는 AVG 함수를 이용하는 것이 옳을 것이다. 분석함수를 이용하면서 계산 범위를 지정할 수는 없을까? 분석함수의 윈도우 절을 이용한다면 가능하다.

SELECT empno, sal
     , ROUND(
       AVG(sal) OVER(ORDER BY sal
       ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
       , 2) AS sal_avg3
  FROM emp
;

OVER 절의 ORDER BY 절 뒤에 ROWS BETWEEN 절이 왔다. 이 구문은 계산 범위를 한정짓는 구문이며 위 구문의 의미는 3행 전부터 1행 전까지를 계산 범위로 지정하고 있다. 분석함수 구문에 윈도우 절을 추가함으로써 간단하게 결과를 얻을 수 있다.

정리하며

이전행/다음 행의 자료 조회는 LAG/LEAD 함수를 이용해 구할 수 있다. LAG/LEAD 는 분석함수 전용함수이며, 지난 시간에 배웠던 집계함수에 OVER() 를 붙여 분석함수로 사용할 수 있었던 것과는 구별된다.

LAG/LEAD 함수는 3개의 인자값을 입력할 수 있다.

LAG/LEAD(arg1, arg2, arg3) OVER(ORDER BY 정렬항목)
- arg1 : 조회하고자 하는 값
- arg2 : 몇 번째 이전/이후 행을 가져올지 정하는 값, 생략시 기본값은 1
- arg3 : 이전/이후 값이 없을 때 대체할 값, 생략 가능

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

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