[hive][db] rank() 함수를 이용하여 그룹별로 상위 n개의 결과만 출력하는 방법

그룹별로 상위 n개의 데이터만 출력하는 방법은 윈도우 함수(window function) 중에서 RANK() 함수를 이용한다. 

RANK 함수는 ORDER BY를 포함한 쿼리에서 특정 칼럼의 순위를 구하는 함수이다. 

아래와 같이 PARTITION BY, ORDER BY 를 함께 이용하여 그룹별로 순위를 구할 수 있고,

이 순위를 이용하여 상위 n개의 결과만 출력이 가능하다. 

SQL>  SELECT JOB, ENAME, SAL,
             RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, 
             RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
      FROM EMP;

JOB       ENAME             SAL   ALL_RANK   JOB_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING             5000          1          1
ANALYST   FORD             3000          2          1
ANALYST   SCOTT            3000          2          1
MANAGER   JONES            2975          4          1
MANAGER   BLAKE            2850          5          2
MANAGER   CLARK            2450          6          3
SALESMAN  ALLEN            1600          7          1
SALESMAN  TURNER           1500          8          2
CLERK     MILLER           1300          9          1
SALESMAN  WARD             1250         10          3
SALESMAN  MARTIN           1250         10          3
CLERK     ADAMS            1100         12          2
CLERK     JAMES             950         13          3
CLERK     SMITH             800         14          4
 

14 rows selected.

아래와 같이 table1의 map_data 칼럼(맵형식)을 explode() 함수를 이용하여 

테이블 형태로 변환하고 그중에 키 값을 기준으로 정렬하여 첫번째 데이터만 출력하도록 구현할 수 있다. 

SELECT distinct rrr.key, rrr.value

FROM (

    SELECT rr.key, 

           rr.value, 

           RANK() OVER(PARTITION BY rr.key ORDER BY rr.value) as row_num

      FROM (

        SELECT key, value

          FROM table1

       LATERAL VIEW explode(map_data) r as key, value

    ) rr

    WHERE length(rr.value) == 10  

) rrr

WHERE rrr.row_num = 1

;

http://wiki.gurubee.net/pages/viewpage.action?pageId=26744104

하이브 윈도우 함수