[ORACLE] ROWNUM, ROW_NUMBER(), RANK, FETCH, MAX값 구하기

1. ROWNUM 키워드 사용

 SELECT ROWNUM
      , ANIMAL_ID
      , ANIMAL_TYPE
      , DATETIME
      , NAME
      , SEX_UPON_OUTCOME
   FROM ANIMAL_OUTS

 SELECT ROWNUM
      , ANIMAL_ID
      , ANIMAL_TYPE
      , DATETIME
      , NAME
      , SEX_UPON_OUTCOME
   FROM ANIMAL_OUTS
   WHERE ROWNUM BETWEEN 1 AND 5
   
   -- 아래와 같이 0이나 1부터 시작하지 않으면 결과가 출력되지 않는다.
   -- 이번 외의 숫자 이상부터 출력할 수는 없다.
   -- 결과 집합을 만들 때 ROWNUM이 만들어지기 때문!
   -- ROWNUM은 반환되는 쿼리결과의 임시 행번호이기 때문에 반드시 1부터 나와야한다.

   WHERE ROWNUM BETWEEN 6 AND 10
   
   WHERE ROWNUM <= 10 -- 가능
   WHERE ROWNUM = 5 -- 불가능
   WHERE ROWNUM >= 6 -- 불가능
   

2. 서브쿼리 활용한 ROWNUM

 -- 페이징이나 특정 행을 추출할 때는 서브쿼리를 활용한다!
 SELECT *
   FROM (
            SELECT ROWNUM AS NUM
                 , ANIMAL_OUTS.*
              FROM ANIMAL_OUTS
         )
  WHERE NUM BETWEEN 6 AND 10

 -- BUT, ROWNUM은 ORDER BY 적용 이전에 매겨진다. 
 -- 순서가 먼저 매겨지고 나서 ORDER BY로 정렬됨
 SELECT ROWNUM AS NUM
      , ANIMAL_OUTS.*
   FROM ANIMAL_OUTS
  ORDER BY DATETIME DESC
  
 -- 따라서 서브쿼리에서 테이블을 먼저 정렬시킨 후, 바깥에서 ROWNUM으로 일련번호를 먹인다
SELECT ROWNUM
     , A.*
  FROM (
            SELECT *
              FROM ANIMAL_OUTS
             ORDER BY DATETIME DESC
        ) A

3. ROW_NUMBER() 함수 사용

-- ORDER BY된 결과에 순번을 매길때에는 ROWNUM 보다 ROW_NUMBER() 함수가 더 편리하다.
-- PARTITION을 활용하면 그룹별로 순번을 따로 부여할 수 있다.
SELECT ROW_NUMBER() OVER (ORDER BY DATETIME DESC) AS NUM
     , ANIMAL_OUTS.*
FROM ANIMAL_OUTS

SELECT ROW_NUMBER() OVER (PARTITION BY ANIMAL_TYPE ORDER BY DATETIME DESC) AS NUM
     , ANIMAL_OUTS.*
FROM ANIMAL_OUTS

SELECT ROW_NUMBER() OVER (PARTITION BY SEX_UPON_OUTCOME ORDER BY DATETIME DESC) AS NUM
     , ANIMAL_OUTS.*
FROM ANIMAL_OUTS

4. ROW_NUMBER()를 활용한 MAX값 구하기

-- 동물 보호소에서 가장 최근 입양된 동물의 데이터 구하기
SELECT *
  FROM (
          SELECT ROW_NUMBER() OVER (ORDER BY DATETIME DESC) AS NUM
               , ANIMAL_OUTS.*
            FROM ANIMAL_OUTS
        )
 WHERE NUM = 1

5. RANK() 또는 DENSE_RANK()를 활용한 MAX값 구하기

RANK() : 중복 순위 개수만큼 다음 순위 값을 증가 시킴
DENSE_RANK() : 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시함

SELECT MEMBER_ID
  FROM (
            SELECT A.MEMBER_ID
                 , RANK() OVER (ORDER BY COUNT(B.MEMBER_ID) DESC) AS RANK
             FROM MEMBER_PROFILE A
             JOIN REST_REVIEW B
               ON A.MEMBER_ID = B.MEMBER_ID
            GROUP BY A.MEMBER_ID
       )
 WHERE RANK = 1

6. FETCH 구문 활용

SELECT A.MEMBER_NAME
     , B.REVIEW_TEXT
     , TO_CHAR(B.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
  FROM MEMBER_PROFILE A
  JOIN REST_REVIEW B
    ON A.MEMBER_ID = B.MEMBER_ID
 WHERE A.MEMBER_ID IN (
                            SELECT MEMBER_ID
                              FROM REST_REVIEW
                             GROUP BY MEMBER_ID
                             ORDER BY COUNT(REVIEW_ID) DESC
                             FETCH FIRST 1 ROWS ONLY
                       )
 ORDER BY 3,2