[Inflearn] SQL 강의 고급 - 사용자정의함수

기본구조

CREATE FUNCTION 'function name(함수이름)' ('parameter name', 'datatype')
	RETURNS 'datatype' (DETERMINTSTIC) 
    -- 출력될 결과의 datatype
    -- input 값이 똑같을 때 output 값이 똑같고 싶으면 DETERMINTSTIC
    -- inputt 값이 똑같지만 내 함수에 따라 값이 다르고싶을 때 NOT      DETERMINTSTIC > 이게 DEFAULT임
BEGIN
	DECLARE 'variable name' 'datatype';
    SET;
    RETURN (Query) / 'variable name';
END

예시

여기서 customerLevel를 계산해주는 함수를 만들어보자

*)creditLimit가 50,000 초과되면 Platinum
10,000 이상 50,000 이하면 Gold
10,000 미만이면 Sliver

CREATE FUNCTION CustomerLevel (credit DECIMAL(10,2))
                          --PARAMETER 이름은 Credit, 얘의 데이터 타입은 실수형 (demical)
	RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
	DECLARE Level VARCHAR(20); -- 변수 선언
    IF credit > 50000 THEN
    	SET Level = 'PLATINUM';
    ELSEIF (credit <=50000 AND credit >= 10000) THEN
    	SET Level = 'GOLD';
    ELSEIF credit < 10000 THEN
    	SET Level = 'SILVER';
    END IF;
    -- return the customer level
    RETURN (Level);
END

사용방법
SELECT customerName, CustomerLevel(creditLimit)
FROM customers
ORDER BY customerName;

문제풀이

LeetCode 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.

문제 힌트1) CASE WHEN condition THEN NULL ELSE value

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(condition, value_if_true, value_if_false)

ex) SELECT IF(500<1000, "YES", "NO")

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 심화

SELECT * FROM TABLE LIMIT 5, 10
ㄴ 5까지는 가져오지말고, 그 다음부터 데이터를 가져오는데 10개를 가지고와
ㄴ ROWS 6~15를 가져옴

SELECT * FROM TABLE LIMIT 5,1
ㄴ ROWS 6을 가져옴

SELECT FROM TABLE LIMIT N,1
=SELECT FROM TABLE LIMIT 1 OFFSET N
ㄴ ROWS N+1을 가져옴

SELECT DISTINCT SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
LIMIT N-1,1 -- N번째를 가져옴

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE A INT; -- A 변수는 정수에요 선언
    SET A = N-1;
  RETURN ( 
      SELECT DISTINCT SALARY
      FROM EMPLOYEE
      ORDER BY SALARY DESC
      LIMIT A,1        
  );
END

DECLARE 없이 이렇게도 가능
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N-1;
  RETURN ( 
      SELECT DISTINCT SALARY
      FROM EMPLOYEE
      ORDER BY SALARY DESC
      LIMIT N,1        
  );
END