[ SQL : 사용자 정의 함수(DECLARE, SET), CASE , IF, LIMIT 심화 ] 177. Nth Highest Salary

 

>> 문제

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

ex, 4번째로 높은 셀러리가 없을 경우  null 을 나온다 

The query result format is in the following example.

 

동점인 점수는 다 1등 그 다음은 2등으로, dense rank 를 기준으로 생각해주면 된다. 

 

https://leetcode.com/problems/nth-highest-salary/submissions/

 

>> 문제 풀이 방법

 

* 사용자 정의 함수의 방법

https://hazel01.tistory.com/82?category=856313 

[ SQL : 사용자 정의 함수, User - defined Function ]

: CREATE FUNCITON  - 이 부분에는 내가 만들고 싶은 함수를 정의하는 부분이다.

 RETURNS - 리턴값의 형태 

 RETURN - 변수를 리턴해줄 수 있었는데, 아래처럼 쿼리를 이용해서 그 쿼리의 값을 나올 수 있게도 가능하다.

 

1. CASE WHEN /  서브쿼리  을 사용하여 풀이

: 서브쿼리를 이용해서 상위 n개를 뽑고 그것의 최솟값을 사용하여서 함. ( 이러한 방식 없이도 offset 을 이용해서 풀 수 도 있음 )

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

 

2. IF Function 을 사용하여 풀이

- IF 문 사용하는 방법

: IF ( condition, value_if_ture, value_if_false ) - 조건문이 있으며, 참일때와 거짓일 떄의 출력 값을 입력한다.

: 조건이 1개일경우, IF 문도 좋지만 조건이 여러개이고 순차적으로 있을 때는 CASE문을 써주는 것이 좋다.

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

 

3. LIMIT 을 이용하여 문제 풀이

- LIMIT 심화 : LIMIT 는 인자를 2개 받을 수 있다.

: SELECT * FROM table LIMIT 5, 10             # 6 ~ 15 row 를 가져오기

: SELECT * FROM table LIMIT 5, 1             

   # 6 째 row 가져오기 < 5까지는 가져오지말고 그 다음부터가져오되, 하나만 가져와라 

: SELECT * FROM table LIMIT N , 1            # row n+1 가져오기

   = SELECT * FROM table LIMIT 1 OFFSET N     # # row n 개는 offset (지우고) 나머지 1개를 가져와라

 * 위와 같이 N을 이용할때, 즉 변수를 이용할 때는 그냥 쿼리에 넣어주는 것이 아니라 DECLARE와 SET을 사용해줘야한다.

 

: 장점은 서브쿼리를 사용하지않게 되며, IF문or CASE문 작성 필요없음. Aggregation function 작성 필요없어짐.

 

1 ) DECLARE / SET 을 사용 : 변수를 만들어서 문제를 푸는 법

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT
BEGIN
  DECLARE A INT;
  SET A = N - 1;
  RETURN (
          SELECT DISTINCT Salary
          FROM employee
          ORDER BY Salary DESC
          LIMIT A, 1 -- N-1번째까지는 지우고 그 N번째를 가져오라는 의미
         );
END

 

2) 변수를 만들지 않고, SET만 사용하는 법

: 원래 있는 변수로 사용

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT
BEGIN
-- DECLARE A INT;
  SET N = N - 1;
  RETURN (
          SELECT DISTINCT Salary   		   
          FROM employee
          ORDER BY Salary DESC
          LIMIT N, 1 -- N-1번째까지는 지우고 그 N번째를 가져오라는 의미
		 );
END

 

3 ) LIMIT OFFSET을 활용한 방법

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

 

 

위의 쿼리를 실제로 사용할 때,

SELECT getNthHighestSalary(3)