[SQL] MySQL Window 함수 rank() over 대체하기

베하~!

BTC 블랙아웃입니다!!

이번주에는 SQL 쿼리에 대해 공부해보았습니다.

SQL 종류와 버전은 MySQL 8.0.11을 사용했으며 DB 툴은 MySQL Workbench를 사용했습니다.

 

---

 

SQL 쿼리 작성 시 주의사항은 저희 블로그에 있는 아래의 글을 참조해주세요!

https://btcd.tistory.com/1086

[SQL] 쿼리 사용시 주의사항

 

 

---

 

 

쿼리 연습 문제를 풀던 과정에서 window function을 알게 되었는데요.

다만 MySQL의 경우 8.0 버전부터 사용할 수 있는 함수이기 때문에 이전 버전에서는 사용할 수 없다는 문제가 있었습니다.

 

추가적으로 window 함수는 버전에 의존성이 있어 DB버전에 따라 프로그램이 돌았다 안돌았다 하기 때문에 윈도우 함수를 최대한 줄이는게 좋습니다.

 

그래서 MySQL 8.0 미만 버전에서 윈도우 함수와 같은 기능을 구현하는 방법에 대해 알아보았습니다.

 

윈도우 함수란?

window function이란 하나 또는 여러 행의 값을 사용하여 각 행에 대한 값을 반환하는 함수입니다.

윈도우 함수의 구조는 아래와 같이 사용하면 됩니다.

SELECT window_function(argument) OVER (PARTITION BY column ORDER BY column)
  FROM table;

윈도우 함수에는 대표적으로 RANK(), LAG(), LEAD(), ROW_NUMBER(), AVG() 등이 있으며 이 외에도 다양한 윈도우 함수들이 있습니다.

 

SQL 벤더마다 함수의 차이는 있으니 주의해주세요!

 

Schema

아래는 저희가 사용했던 데이터베이스 스키마 목록입니다

예제 쿼리를 보실 때 참고해주세요!

employee

id(PK)	int(auto_increament)
em_id	int
em_name	varchar(8)
grade_code	int
place_code	char(2)
depart_code	char(1)
boss_id	int
salary	int
hiredate	date

 

place

place_code	char(2)
place_name	char(3)
place_address	varchar(20)

 

Window 함수 사용 예제

아래의 쿼리는 window 함수를 대체하기 전에 rank() 함수를 사용한 예시입니다.

 

Q. 상암근무자중 어제 일자기준으로 근무년수(계산시 월단위는 절삭)가 10년 이상된 사원의 사원명, 근무년수, 급여를 출력하고 이 사원의 급여순위를 출력하시오.

SELECT e.em_name AS 사원명
      ,SUBSTR((CURDATE()-1) - e.hiredate, 1, 2) AS 근무년수
      ,RANK() OVER (ORDER BY e.salary DESC) AS 순위
  FROM employee AS e
 WHERE e.place_code = (
                       SELECT place_code
                         FROM place
                        WHERE place_name = '상암') AND
                     DATEDIFF(CURDATE() - 1, e.hiredate) >= 10 * 365;

window function을 사용해서 작성한 쿼리는 이렇게 작성했습니다.

 

RANK() OVER (ORDER BY e.salary DESC) AS 순위 부분을 보시면 급여를 내림차순으로 정렬한 뒤에 순위를 매겼다는 것을 알 수 있습니다.

 

rank() 함수를 통해 작성하니 아주 편리하고 좋았지만 이를 사용할 수 없는 상황이라고 하면 다른 방법이 필요하겠죠?

 

이제 해당 방법을 알려드리겠습니다.

 

Rank() 함수 대체하기

rank 함수를 대체하는 방법은 셀프 조인, 변수 사용, 서브 쿼리 사용 등등 여러가지가 있습니다.

저희는 그 중 변수를 사용해서 작성해 보았는데 바로 보여드릴게요.

SELECT result.em_name AS 사원명
      ,SUBSTR((CURDATE()-1)-result.hiredate, 1,2) AS 근무년수
      ,result.rnk AS 순위
  FROM (
        SELECT em_name
              ,hiredate
              ,@rank := CASE WHEN @before_user_depart=depart_code
                             THEN CASE WHEN @before_user_sal=salary
                                       THEN @rank
                                       ELSE @rank+@same_sal_cnt
                                        END
                             ELSE @rank+1
                              END AS rnk
              ,@same_sal_cnt := CASE WHEN @before_user_depart=depart_code
                                     THEN CASE WHEN @before_user_sal=salary
                                               THEN @same_sal_cnt+1
                                               ELSE 1
                                               END
                                     ELSE 1
                                      END AS cnt
              ,@before_user_depart := depart_code
              ,@before_user_sal := salary
          FROM (
                SELECT a.depart_code
                      ,a.em_name
		                  ,a.salary
                      ,a.hiredate
                  FROM employee a
                  JOIN (
                        SELECT *
                          FROM place
                         WHERE place_name = '상암'
                       ) b
                    ON a.place_code=b.place_code
                 WHERE DATEDIFF(CURDATE()-1, a.hiredate) >= 10 * 365
                 ORDER BY salary DESC
               ) data1
              ,(
                SELECT @rank := 0
                      ,@before_user_sal := ''
                      ,@same_sal_cnt := 1
                      ,@before_user_depart := ''
               ) data2
           ) result;

어떤가요?

 

이 쿼리는 변수를 사용해 row들을 비교하여 결과값을 도출해낼 수 있고, 위에 rank() over를 사용했을 때와 동일한 결과를 보여줍니다.

 

이처럼 MySQL 버전이 8.0 미만이거나 다른 데이터베이스 벤더를 사용해서 윈도우 함수를 지원하지 않는다거나, 직접 로직을 구현하고 싶을 때는 상황에 맞게 쿼리를 작성하시면 되고, 때에 따라선 윈도우 함수를 사용하셔도 좋을 것 같습니다.

그럼 다음 주에 봐요~ 베빠!