석차 구하기

조건.
    TABLE : TEST20

    EMPID      POINT    
    ---------- ----------
    A00000             49
    A00600             56
    A01000             47
    A01002             34
    A01400             29
    A02002             22
    A04003             18
    A09100             14
    A09200             13
    A10000             39
    A10002             20
    A10600             46
    A11000             37
    A11002             24
    A11400             19
    A12002             12
    A14003             28
    A19100             24
    A19200             23
    A90002             10

    위와같이 TEST20 TABLE에 사번별 점수가 들어있다.

문제.
    위의 DATA를 이용하여 석차를 구하여 순위별로
    보여주고싶다.
    석차를 구하는 방식은 일반적으로 우리가 알고 있는 것과 동일하다.
    즉, 동일점수가 2명이상이면 동일점수는 같은 순위가 부여되고
    다음 점수는 그만큼 건너뛴 순위가 부여되어야 한다.
    먼저 답을 보면 다음과 같다.

    EMPID      순위      
    ---------- ----------
    A00600              1
    A00000              2
    A01000              3
    A10600              4
    A10000              5
    A11000              6
    A01002              7
    A01400              8
    A14003              9
    A11002             10
    A19100             10
    A19200             12
    A02002             13
    A10002             14
    A11400             15
    A04003             16
    A09100             17
    A09200             18
    A12002             19
    A90002             20

생각.
    어렵게 생각하면 어려워지고 쉽게 생각하면 쉬운 문제다.
    문제를 해결하는 방법은 모든 문제가 그렇듯이 하나 이상이
    나올 수 있다.
    물론 방법에 따라 결과가 나오는 시간이 다를수 는 있다.
    엿보기1.가장쉽게 생각할 수 있는것은 각 사번별로 자기보다 점수가
            큰 사람의 숫자를 세어서 거기에 1을 더하는 방식이다.
            주의할 것은 자기점수보다 크거나 같은  사람의 숫자가
            자기의 석차가 아니라는 것이다.
            자기 점수와 동일인이 여러명 있을수 있기 때문이다.
            방법은 간단하다.
            SELF JOIN을 이용하되 한쪽을 자기보다 큰점수로 비교하고
            순위 1인 사람을 위하여 (+) 기호를 붙여주면된다.
    엿보기2.다음 방법도 같은 원리다.
            큰점수부터 순위가 붙어야 하므로 먼저 1을 각 점수별로
            나누어서 각 점수별로 GROUP을 만든다.
    엿보기3.다음은 역시 같은방식으로 인원수를 구해서 1을 빼준다.
            그리고 두개의 DATASET을 1/점수로 부등호 JOIN을 걸어준다.
            JOIN된 결과를 1/점수로 GROUP 지으면 1/점수대별로
            각각 자신의 GROUP 지어진 순위와 자신보다 상위순위중
            동일 점수로 인해 중복된 인원수를 구할수 있다.
    엿보기4.이제 TABLE을 한번 더 읽어서 1/점수로 다시 JOIN을 걸고
            EMPID를 가져오고,순위와 중복인원을 더해주면 된다.
            두번째 방법은 말로만 이해하기에 약간의 어려움이
            있을 듯하다
            이해가 안가면 해법을 통해 이해할 수 있는 길을 찾아보자.

해법.엿보기를 통해 두가지 방법이 제시되었는데  어떤방법이 더 빠를지
     한번 생각을 해보고 해법을 접하기 바란다.
     두가지 모두가 부등호 JOIN이다.
     앞의 주제에서도 다뤘듯이 SELF JOIN이 부등호로써 연결될때는
     DATA의 숫자가 증가함에 따라 RESPONSE TIME이 지수함수적으로
     증가한다는 것을 알고있다.
     단계1.첫번째 단계에서는 엿보기1에서 방식을 SQL문으로 옮겨보겠다.
  
          SELECT A.EMPID,
                 COUNT(B.EMPID ) + 1
          FROM   TEST20 A,
                 TEST20 B
          WHERE  B.POINT(+) > A.POINT
          GROUP BY A.EMPID
          ORDER BY COUNT(B.EMPID)
          엿보기1을 통해 충분한 설명이 있었으므로 이해하기에 어려움이
          없으리라  본다.
     단계2.각 점수대별 중복인원수를 구하는 방법은 다음과 같다.
           SELECT 1/POINT POINT_D,COUNT(*) - 1  CNT
           FROM   TEST20
           GROUP BY 1/POINT            
          
     단계3.위의 DATASET을 다시 점수대별로 부등호 JOIN을 거는 방법은
           다음과 같다.
            
           SELECT  A.POINT_D POINT_D,
                   SUM(B.CNT) ADV
           FROM   (SELECT 1/POINT POINT_D  CNT
                   FROM   TEST20
                   GROUP BY 1/POINT
                  ) A,
                  (SELECT 1/POINT POINT_D,COUNT(*) - 1  CNT
                   FROM   TEST20
                   GROUP BY 1/POINT
                  ) B
           WHERE  B.POINT_D(+) < A.POINT_D
           GROUP BY A.POINT_D

           점수대별 중복인원수 구하는 DATASET이 B 이다.
     단계4.위의 결과를 이용해 점수대별 순위와 순위에 더해질
           중복인원수를 구하려면
           다음과 같이 DATASET을 FROM절로 넣고 SELECT절만 추가한다.
           SELECT  POINT_D,ROWNUM GRAD,ADV
     단계5.위의 결과 다시 자기 TABLE과 부등호로 연결하여 사번과함께
           순위 + 중복인원을 읽어오면된다.
           최종 SQL은 다음과 같이 된다.

           SELECT  A.EMPID,
                   DECODE(B.GRAD + B.ADV,NULL,1,B.GRAD + B.ADV) 순위
           FROM   (SELECT  POINT_D,ROWNUM GRAD,ADV
                   FROM
                       (SELECT  A.POINT_D POINT_D,
                                SUM(B.CNT) ADV
                        FROM   (SELECT 1/POINT POINT_D  CNT
                                FROM   TEST20
                                GROUP BY 1/POINT
                               ) A,
                               (SELECT 1/POINT POINT_D,COUNT(*)-1 CNT
                                FROM   TEST20
                                GROUP BY 1/POINT
                               ) B
                        WHERE  B.POINT_D(+) < A.POINT_D
                        GROUP BY A.POINT_D
                      )
                  )       B,
                   TEST20 A
           WHERE 1/A.POINT = B.POINT_D
     두번째 방법이 훨씬 복잡해 보인다.
     하지만 효율적이다.
     RECORD수에 따라 RESPONSE TIME이 지수함수적으로 늘어나는 SQL에서는
     처리되는 RECORD수를 줄일수록 효과가 크다.
     중복점수가 많을수록 두번째방법이 효과적이다.
     처리할 RECORD수를 중복되는 숫자만큼 줄여주기때문이다.
뒷풀이.
     PLAN상으로는 거의 차이가 없다.
     PLAN만을 놓고 보면 오히려 두번째 방법이 복잡하다.
     참고로 두가지 방법의 각각에 대한 PLAN을 보자.

     방법1.
          SELECT STATEMENTCost Estimate:                                                  
            SORTORDER BY                                                                  
              SORTGROUP BY                                                                
                NESTED LOOPSOUTER                                                        
                  TABLE ACCESSFULL:HABC,,,TEST20(1)                                      
                  TABLE ACCESSFULL:HABC,,,TEST20(2)              
     방법2.
          SELECT STATEMENTCost Estimate:                                                  
            MERGE JOIN                                                                    
              SORTJOIN                                                                    
                TABLE ACCESSFULL:HABC,,,TEST20(7)                                        
              SORTJOIN                                                                    
                VIEW(1)                                                                  
                  COUNT                                                                  
                    VIEW(2)                                                              
                      SORTGROUP BY                                                        
                        NESTED LOOPSOUTER                                                
                          VIEW(3)                                                        
                            SORTGROUP BY                                                  
                              TABLE ACCESSFULL:HABC,,,TEST20(4)                          
                          VIEW(5)                                                        
                            SORTGROUP BY                                                  
                              TABLE ACCESSFULL:HABC,,,TEST20(6)                          
     1280건의 DATA 중 다른점수가 714건인 경우의 RESPONSE TIME이다.
     방법1.
         1280 행이 선택되었습니다
         구문분석          0.02 (경과됨)      0.00 (CPU)
         실행/인출         9.67 (경과됨)      0.00 (CPU)
         합계              9.69               0.00
     방법2.
         1280 행이 선택되었습니다
         구문분석          0.01 (경과됨)      0.00 (CPU)
         실행/인출        24.53 (경과됨)      0.00 (CPU)
         합계             24.54               0.00
사족.
     항상 얘기하듯이 여기서 제시되는 방법이 최적의 방법은 아니다.
     그건 본인도 자신할 수 가 없다.
     단지 실무에서 부딪히는 각 경우대해 참고할 수 있는 자료만
     되었으면 하는 바램이다.
     여러가지 어려운 문제에 부딪쳤을때  혼자 고민하고 보내는 시간이
     이 책으로 인해 조금이라도 줄 수 있다면 하는 바램이 있을 뿐이다.
     그걸위해 내가 만났던 사례들을 모아놓는다.