② FIRST와 LAST

FISTST와 LAST 함수는 MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV 같은 집계 함수와 같이 사용되어 주어진 그룹에 대해 내부적으로 순위를 매겨 결과를 산출하는 함수다. 이 두 함수의 특징은 A란 컬럼을 기준으로 순위를 매긴 다음 B란 컬럼 값으로 집계한 결과를 뽑아낼 수 있다. 예를 들어, kor_loan_status 테이블에서 월별 대출잔액이 최소인 지역과 최대인 지역을 한 번에 조회하는 쿼리를 작성해야 한다면 다음과 같이 WITH 절을 사용하면 된다.

입력

    WITH basis AS ( SELECT period, region, SUM(loan_jan_amt) jan_amt
                      FROM kor_loan_status
                     GROUP BY period, region
                  ),
        basis2 as ( SELECT period, MIN(jan_amt) min_amt, MAX(jan_amt) max_amt
                      FROM basis
                     GROUP BY period
                  )
     SELECT a.period,
            b.region "최소지역", b.jan_amt "최소금액",
            c.region "최대지역", c.jan_amt "최대금액"
       FROM basis2 a, basis b, basis c
      WHERE a.period  = b.period
        AND a.min_amt = b.jan_amt
        AND a.period  = c.period
        AND a.max_amt = c.jan_amt
      ORDER BY 1, 2;

쿼리가 좀 복잡한데, FIRST와 LAST 함수를 이용하면 좀더 간단히 변경할 수 있다.

입력

    WITH basis AS (
                   SELECT period, region, SUM(loan_jan_amt) jan_amt
                     FROM kor_loan_status
                    GROUP BY period, region
                  )
    SELECT a.period,
           MIN(a.region) KEEP ( DENSE_RANK FIRST ORDER BY jan_amt) "최소지역",
           MIN(jan_amt) "최소금액",
           MAX(a.region) keep ( DENSE_RANK LAST ORDER BY jan_amt) "최대지역",
           MAX(jan_amt) "최대금액"
      FROM basis a
     GROUP BY a.period
     ORDER BY 1, 2;

DENSE_RANK FIRST ORDER BY jan_amt란 jan_amt 값 기준으로 정렬을 해서 순위를 매긴 다음 그 첫 번째 값을 가져오고, 마찬가지로 DENSE_RANK LAST ORDER BY jan_amt는 마지막 값을 가져온다는 의미다. 금액을 기준으로 정렬시켜 첫째와 마지막 값을 계산해 내고 해당 값과 같은 지역을 추출한 것이다.