인포믹스에서 rank 함수 구현하기

안녕하세요. 인포믹스 12.1 이전 버전에는 RANK/DENSE_RANK 함수 기능이 제공되지 않습니다.

따라서 별도 프로그램을 사용해야 합니다.

인터넷을 검색해보니 변수 처리하거나 상호연관 서브쿼리 (Correlated sub-query)를 이용하는 방법이 일반적인 것 같습니다.

참고해서 인포믹스 SQL과 FUNCTION으로 구현해 보았습니다.

## 샘플 데이터 입력

DROP TABLE scores;

CREATE TABLE scores

     (

         id serial not null,

         score int not null

     );

INSERT INTO scores (score) VALUES     (50);

INSERT INTO scores (score) VALUES     (40);

INSERT INTO scores (score) VALUES     (75);

INSERT INTO scores (score) VALUES     (80);

INSERT INTO scores (score) VALUES     (55);

INSERT INTO scores (score) VALUES     (50);

## SQL문 실행 예제

SQL문으로 RANK/DENSE_RANK 기능을 구현한 예제입니다.

select id, score, 

(select count(*)::int+1 end from scores a where a.score > b.score) rank,

(select count(distinct(a.score))::int+1 end from scores a where a.score > b.score) dense_rank

from scores b order by score desc;

         id       score          rank    dense_rank

          4          80             1             1

          3          75             2             2

          5          55             3             3

          1          50             4             4

          6          50             4             4

          2          40             6             5

## 함수로 구현 (ranking/dense_ranking)

동일한 값에 대한 순위를 평가하는 방법에 따라 RANK/DENSE_RANK 함수로 구분됩니다.

같은 세션에서 전역변수 처리하므로 여러번 실행하려면 init_ranking변수를 초기화 해야합니다.

DROP FUNCTION ranking;

CREATE FUNCTION  ranking (curr_val int) returning int ;

define global counter int default 0;

define global last_val int default 0;

define global seq_val int default 0;

if counter = 0 then

let counter = counter + 1;

elif seq_val - counter = 0 and last_val != curr_val then

let counter = counter + 1;

end if

let seq_val = seq_val + 1;

let last_val = curr_val;

if seq_val - counter > 1 then

return seq_val;

end if

return counter;

end function;

DROP FUNCTION dense_ranking;

CREATE FUNCTION  dense_ranking (curr_val int) returning int ;

define global counter int default 0;

define global last_val int default 0;

if counter = 0 or last_val != curr_val then

let counter = counter + 1;

end if

let last_val = curr_val;

return counter;

end function;

CREATE PROCEDURE  init_ranking ();

define global counter int default 0;

define global last_val int default 0;

define global seq_val int default 0;

let counter = 0;

let last_val = 0;

let seq_val = 0;

end procedure;

## 함수 실행 예제

call init_ranking ();

select id, score, ranking (score) rank from (select id,score from scores order by score desc);

         id       score        rank

          4          80           1

          3          75           2

          5          55           3

          1          50           4

          6          50           4

          2          40           6

call init_ranking ();

select id, score, dense_ranking (score) dense_ranking from (select id,score from scores order by score desc);

         id       score dense_ranking

          4          80             1

          3          75             2

          5          55             3

          1          50             4

          6          50             4

          2          40             5

코드는 예외처리가 더 필요할 것 같지만 결과는 잘 나온 것 같습니다.

참고가 되시길 바랍니다.

참조 사이트

https://mattmazur.com/2017/03/26/exploring-ranking-techniques-in-mysql/

http://informix-myview.blogspot.com/2012/09/in-search-of-rownum.html