[SQL] MySQL - 나만의 Function 만들기(Leetcode - 177번)

🔊 본 포스팅에서 사용되는 테이블의 자료와 출처는 Leetcode임을 밝힙니다. 더 다양한 SQL 문제를 풀어보시려면 Leetcode 사이트를 방문해 보세요!

 

LeetCode 사이트

 

이번 포스팅에서는 SQL도 Python과 같은 다른 프로그래밍 언어들처럼 사용자 함수를 만드는 방법에 대해 알아보고 이를 이용해 문제를 풀어보려 한다. 여기서는 MySQL 기준으로의 사용자 함수 만드는 방법에 대해서만 소개한다. 우선 MySQL로 사용자 함수를 만드는 구조에 대해서 알아보자.

 

MySQL 함수 만드는 구조

 

위와 같은 구조를 간단히 설명하면 CREATE FUNCTION~ 첫 번째 RETURNS 부분은 인풋 변수와 아웃풋으로 내뱉을 값의 type을 정하는 부분이며 BEGIN ~ END 사이에는 출력값을 내뱉기 위한 연산과 변수를 할당하는 부분이라 할 수 있다. 구현해야 할때 주의해야 할 점은 두 가지이다.(필자가 자주하는 실수다..)

 

1. 첫 번째는 RETURNS 이고 두 번째는 RETURN 이다! S의 유무를 주의하자.
2. 다른 부분에는 세미콜론(;)을 안 붙여도 되지만 빨간색으로 표기된 부분(변수를 할당하고 쿼리로 아웃풋을 return 하는 부분)은 무조건 세미콜론(;)을 적어주어야 한다.

자, 이제 Leetcode의 177번 문제를 사용자 함수를 정의해서 풀이해보자. 우선 문제와 원하는 형태의 출력 포맷을 확인해보자.

 

Write a SQL query to get the nth highest salary from the Employee table.
(Employee 테이블에서 n번째로 가장 많은 salary를 출력해라. 이 때 만약 n번째 가장 많은 salary값이 없다면 null값을 출력시켜라.)

 

문제에서 요구하는 출력 형태

 

이 문제를 해결하기 위해서 큰 틀은 사용자 정의 함수를 사용해서 풀이하지만 함수 내부적으로 풀이방법이 3가지가 있다. 이를 하나씩 소개해본다.

 

1. LIMIT 과 OFFSET 활용하기

우선 LIMIT과 OFFSET에 대한 개념을 모른다면 여기를 참조하자. 이 포스팅에서는 예시 쿼리문 하나로 간단하게 개념을 짚고 넘어가자.

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2, 3

위 쿼리를 해석하면 다음과 같다. Employee 테이블에서 Salary를 기준으로 내림차순으로 정렬한 후 상위 2번째 이후의 상위 3개 값. 만약 [1, 2, 3, 4, 5, 6, ..] 순서로 데이터가 있다면 위 쿼리는 3, 4, 5 번째 데이터를 출력시킨다.

 

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 3 OFFSET 2

 

이 쿼리는 OFFSET과 LIMIT을 결합하여 위의 쿼리와 동일한 결과를 출력시킨다. 추가로 OFFSET은 인자로 큰 숫자를 넣을수록 쿼리 퍼포먼스가 떨어진다는 점을 참고하자. 예를 들어 상위 100번째 이후의 상위 3개의 값을 추출하려 한다면

 

LIMIT 3 OFFSET 100 이 아닌  LIMIT 100, 3을 사용하도록 하자.

이제 LIMIT과 OFFSET을 활용해 177번 문제를 풀어보자.

 

CREATE FUNCTION getNthHighestSalary (N INT)
        RETURNS INT
BEGIN
     DECLARE A INT;
     SET A = N-1;
     RETURN (SELECT Salary
             FROM Employee
             ORDER BY Salary DESC
             LIMIT 1 OFFSET A);
END

 

여기서 의문이 들 수 있는 부분은 왜 굳이 A를 N-1로 새로운 변수를 할당했냐는 것이다. 하지만 이렇게 한 이유는 사용자함수의 인풋 파라미터로 받고 있는 N을 함수 내부에서 새로운 변수를 할당하지 않고 N-1 그대로 사용하면 에러가 발생하기 때문이다. 

 

그리고 N번째 상위의 값이 없다면 Null을 반환하라고 했는데, LIMIT 과 OFFSET은 해당 조건을 만족하는 값이 없다면 자동으로 Null을 반환하기 때문에 이를 위해 따로 처리해줄 필요는 없다.

2. CASE WHEN과 서브쿼리 사용하기

CASE WHEN을 사용해볼 수 도 있다. 다음 SQL 구문을 보자.

 

CREATE FUNCTION getNthHighestSalary (N INT)
        RETURNS INT
BEGIN
     RETURN (SELECT CASE WHEN COUNT(sub.Salary) < N 
                          THEN Null
                          ELSE MIN(sub.Salary)
                          END
              FROM (SELECT DISTINCT Salary
                    FROM Employee
                    ORDER BY Salary DESC
                    LIMIT N) sub);
END

 

3. IF와 서브쿼리 사용하기

2번 풀이방법에서 CASE WHEN 구문을 IF 구문으로 바꾸어 주기만 하면 된다.

 

CREATE FUNCTION getNthHighestSalary (N INT)
        RETURNS INT
BEGIN
     RETURN (SELECT IF(COUNT(sub.Salary) < N, Null, MIN(sub.Salary))
              FROM (SELECT DISTINCT Salary
                    FROM Employee
                    ORDER BY Salary DESC
                    LIMIT N) sub);
END

 

이제 모든 풀이방법에 대해 알아보았다. 그렇다면 직접 정의해준 함수를 어떻게 사용할까? 아래와 같이 사용하면 된다.(해당 리트코드 문제에서는 함수 쿼리문만 제출하면 통과된다)

 

-- 상위 10번째로 높은 Salary를 출력하기!
SELECT getNthHighestSalary(10)
FROM Employee