[MariaDB][SQL]사용자변수를 활용한 DENSE_RANK 분석함수 작성

[테스트 환경]

  - Windows 7

  - MariaDB 10.1.12 

1.DENSE_RANK 분석함수 구현하기

  - MariaDB(mysql)의 사용자변수를 활용하여 Oracle의 DENSE_RANK분석함수와 동일한 쿼리를 작성 한다. 기존의 사용자변수와 조금 다르게 활용한 것은  SET 사용자변수를 선언하지 않고, 테이블 안쪽에서 사용자변수를 선언하여 쿼리가 수행 될 때마다 사용자변수를 초기화 할 수 있는 장점이 있다. 

1.1 테이블 스크립트

CREATE TABLE DA_USER_INFO (
  USER_GRP_ID_ID VARCHAR(10) COMMENT '사용자그룹ID',
  USER_NO  BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY  COMMENT '사용자번호',
  USER_SAL    BIGINT NOT NULL COMMENT '사용자연봉',
  KEY IDX_USER_SAL (USER_SAL)
)
ENGINE=INNODB;

1.2 데이터 스크립트 

insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',10000001);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',10000002);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A2',10000002);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A2',10000003);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A2',10000003);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',10000003);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',20000001);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',20000002);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',20000002);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',20000003);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A1',20000003);
insert into DA_USER_INFO (USER_GRP_ID_ID,USER_SAL) values ('A2',20000003);

1.3 사용자변수를 활용한 DENSE_RANK분석 함수 쿼리 

/*사용자별 연봉순위를 나타내시오.(단, 연봉이 같을 경우 동일 순위로 기록) */
SELECT   A.USER_GRP_ID,
         A.USER_NO,
         A.USER_SAL,
         A.DENSE_RANK
FROM   ( SELECT   A.USER_GRP_ID,
                  A.USER_NO,
                  A.USER_SAL,
                  @ROWNUM:= @ROWNUM + 1 AS ROWNUMBER , /*ROWNUMBER 또는 RANK*/
                  @RANK := IF(@BF_USER_SAL = A.USER_SAL, @RANK, @RANK+@SAME_SAL_CNT) AS DENSE_RANK ,
                  @SAME_SAL_CNT  := IF(@BF_USER_SAL = A.USER_SAL, @SAME_SAL_CNT+1,1)  AS CNT, /*연봉이 동일 할 때 + 1 */
                  @BF_USER_SAL AS BF_USER_SAL /*이전 로우의 사용자연봉*/,
                  @BF_USER_SAL := A.USER_SAL 
         FROM    (SELECT   A.USER_GRP_ID,
                           A.USER_NO,
                           A.USER_SAL
                  FROM     DA_USER_INFO A
                  ORDER BY A.USER_SAL DESC) A, (SELECT @RANK:=0, @BF_USER_SAL:=0, @SAME_SAL_CNT:=1, @ROWNUM:=0) B 
                  ) A;

1.3 SQL쿼리 결과