인포믹스에서 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문으로 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

다음은 사용자 정의 함수로 RANK/DENSE_RANK 기능을 구현한 예제입니다.
동일한 값에 대한 순위를 평가하는 방법에 따라 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