pajama

안녕하세요. 요즘 IBM 커뮤니티에서 열심히 댓글놀이를 하고 있는데요. 며칠전에 Informix에서 문자열에 대한 최빈값(MODE)을 구하는 방법에 대한 질문글이 올라왔습니다. 최빈값은 통계학에서 쓰는 용어로 액셀이나 분석 솔루션에서 MODE라는 함수로 사용되기도 합니다. 아래는 위키백과의 최빈값에 대한 설명입니다.

그런데 최빈값이라 함은 숫자가 대상이지 문자열은 아닙니다. 그리고 SQL에서는 MODE라는 함수가 따로 제공되는 것도 아니어서 문자열을 대상으로 하려면 별도의 로직을 구현해야합니다.

질문에서 제시하는 샘플데이터와 원하는 결과세트는 아래와 같습니다.

<샘플데이터>

<결과세트>

결과세트에서 요구하는 사항은 다음과 같습니다.

1) 이름별로 가장 연도와 월 기준으로 가장 많은 값을 찾는다.

2) 일자는 해당 월의 아무 일자나 허용한다.

3) 1에서 찾은 값의 개수가 같은 것이 있으면 연도, 월, 일자를 같은 값을 우선한다.

그래서 이리저리 검색을 해서 아래와 같은 쿼리를 만들었습니다.

WITH t1 AS
(
SELECT 'JESSE' Name, '1992/10/27' dob, 10 score 
UNION ALL SELECT 'JESSE', '1992/10/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg	  
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1

쿼리의 흐름은 다음과 같습니다.

1) 이름을 기준으로 연도,월별 개수가 가장 많은 것을 구합니다. 가장 많은 개수가 같을 경우를 고려해 RANK 함수를 사용합니다. 그러면 같은 랭킹인 경우는 모두 첫번째가 됩니다.

2) 1에서 구한 데이터를 기준으로 일자를 포함하여 개수가 가장 많은 것을 구합니다. 여기선 월별로 일자 하나만 선택해야하므로 ROW_NUMBER를 사용합니다.

아래는 실행한 결과입니다.

[informix@db2 skjeong]$ dbaccess stores_demo mode.sql
Database selected.
name    dob                     avg
JESSE   1992/10/27 25.0000000000000
JESSICA 1992/11/30 60.0000000000000
2 row(s) retrieved.

인포믹스에서의 예제이긴 하지만 대부분의 RDBMS에서 지원하는 함수이기 때문에 필요한 경우 적용하기는 어렵지 않으실겁니다. 혹시 이 글을 읽는 분들께서 더 나은 방법을 알고 계시다면 조언 부탁드리겠습니다.

제가 참고한 사이트는 아래와 같습니다.

https://www.mssqltips.com/sqlservertip/3543/calculate-the-statistical-mode-in-sql-server-using-tsql/

데이터베이스 사랑넷에 자문을 구해 쿼리를 수정해보고, 새로운 쿼리도 알게 됐습니다.

위의 요구사항보다 조건을 좀 더 구체적으로 설정했습니다.

1) 평균은 월평균이 되어야한다. 이전 것은 이름만 기준으로한 평균

2) 가장 많은 갯수가 동일할 경우의 우선순위는? (빠른 날짜, 느린 날짜, 낮은 평균, 높은 평균 등)

여기서는 빠른 날짜를 기준으로 합니다.

아래는 수정된 쿼리입니다. 제가 수정한 쿼리는 마농님의 조언에 따라 약간 수정한 것입니다.

테스트를 위해 WITH절에  샘플 레코드를 추가했습니다.

--- 제가 수정한 쿼리
WITH t1 AS
(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40 
UNION ALL SELECT 'JESSE', '1992/07/27', 10
UNION ALL SELECT 'JESSE', '1992/07/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg	  
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC, t1.dob ASC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name, t1.dob[1,7] ) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1
--- 마농님의 쿼리 (WITH문 생략)
SELECT *
  FROM (SELECT name
             , dob
             , avg_m
             , ROW_NUMBER() OVER(
               PARTITION BY name ORDER BY cnt_m DESC, cnt_d DESC, dob) rn
          FROM (SELECT name
                     , dob
                     , COUNT(*)   OVER(PARTITION BY name, dob[1,7]) cnt_m
                     , COUNT(*)   OVER(PARTITION BY name, dob     ) cnt_d
                     , AVG(score) OVER(PARTITION BY name, dob[1,7]) avg_m
                  FROM t1
                ) a
        ) a
 WHERE rn = 1
;

제가 올린 질문글은 아래 링크에서 참고해주세요.

http://database.sarang.net/?inc=read&aid=3484&criteria=informix&subcrit=qna&id=&limit=20&keyword=&page=1

안녕하세요. 인포믹스 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