Hypothetical Functions 은 어떨때 써야하나?

필자는 며칠전 고객에게 아래와 같은 문의 사항을 받았다.

고객 :"우리지점은 9월 달에 매출액이 112 억 에 전체 지점중에서 9등을 하였습니다.
          만약 9월 달에 우리지점이 매출 130억을 달성 했다면 전체지점에서 몇등인지
          볼수 있는 기능을 오라클 에서 제공 합니까?"

필자 : "제공합니다. "
         "현재는 어떻게 개발하시고 계시나요?"

고객 : "현재는 개발자가 월별 매출액 테이블에 한건(130억)을 insert 하고 COMMIT 되지 않은 상태에서
          RANK 함수를 써서 순위를 구하고 나머지 지점들의 순위는 화면단 로직으로 다걸러서 버리고
          해당지점의 순위가 화면에 출력되면 ROLLBACK 하고 있습니다."

필자 : "켁"(마시던 커피가 넘어가다가 목에서 막히는 소리임.^^)  

필자는 고객에게 Hypothetical Functions(가정에 근거한 함수) 를 추천 하였다.
이상하게도 개발자들 뿐아니라 DBA들 또한 Ranking Family (Rank/Dense Rank/Percent rank 등등) 함수는
즐겨사용함 에도 불구하고 가정함수(Hypothetical Functions) 는 쓰지 않는다.

아래는 2001년에 고객이 매출한 금액별로 순위를 정한 SQL 문이다.

SELECT cust_nbr, SUM(tot_sales) cust_sales,
  RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) rank,
  DENSE_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) dense_rank,
  CUME_DIST( ) OVER (ORDER BY SUM(tot_sales) DESC) cume_dist,
  PERCENT_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) percent_rank
FROM orders
WHERE year = 2001
GROUP BY cust_nbr
ORDER BY rank;

  CUST_NBR CUST_SALES       RANK DENSE_RANK  CUME_DIST PERCENT_RANK
---------- ---------- ---------- ---------- ---------- ------------
.....................................................      중간생략
         1    1151162         23         21 .766666667    .75862069
         8    1141638         24         22         .8   .793103448
        16    1068467         25         23 .833333333   .827586207
        22    1036146         26         24 .866666667   .862068966
        21    1020541         27         25         .9   .896551724
        28     986964         28         26 .933333333   .931034483
         7     971585         29         27 .966666667   .965517241
        29     903383         30         28          1            1

분석함수의 전형적인 결과이다.
위쿼리의 결과를 보고 오라클에게 이렇게 질문할수 있다.
만약 내가 백만원(1000000 원)의 매출(CUST_SALES)을 올렸다면 나의 순위(RANK)는 몇등인가?
위결과에서 밑줄친 부분을 보고 생각을 해보면 나의 순위는 28위임을 알수 있다.
즉 백만원이 28등 이라는것만 알면되는 요구사항이다. --> 결과가 한줄로 나와야 한다.
하지만 이것을 SQL 로 구현하기란 만만치 않다는걸 알수 있다.
이럴때 아래처럼 Hypothetical Functions을 사용한다면 손쉽게 불필요한 고객들의 정보를 제외하면서
보고싶은 나의 순위만 화면에 출력된다.

SELECT
  RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_rank,
  DENSE_RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_dense_rank,
  CUME_DIST(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_cume_dist,
  PERCENT_RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_pcent_rank
FROM orders
WHERE year = 2001
GROUP BY cust_nbr;

  HYP_RANK HYP_DENSE_RANK HYP_CUME_DIST   HYP_PCENT_RANK
---------- -------------- ------------- ----------------
        28             26    .903225806               .9

결과가 너무나 환상적이지 않은가?
원하는 결과 한줄만 나오는 것은 물론이고 순위는 28위(Dense Rank 로는 26위) 라고 친절하게 나온다.

결론:
위의 상황과 같이 순위나 백분율 등을 예측하고 싶을때 Hypothetical Functions 을 쓰지 않는다면
고생을 하는것은 물론이고 성능이 좋을리도 없음을 기억하자.

편집후기 : 참고로 필자는 Hypothetical Function 이라는 말을 싫어한다.
                What IF Function 이라고 기억해야 머리에 오래남을 것이다.