[SQL] WINDOW FUNCTION, REGEXP, UDF (feat. 데이터리안, 인프런)

인프런 [백문이 불여일타] 데이터 분석을 위한 고급 SQL 완강 기념 정리노트🙋‍♂️ 

 

1. Window Function

함수(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

 

 GROUP BY와 달리 윈도우 함수는 데이터 행의 수를 유지한다(찌부시키지 ✖✖). 함수 자리에는 GROUP BY에서 사용하는 집계 함수 뿐만 아니라 특수 함수(ROW_NUMBER, RANK 등)를 사용할 수 있다. 단, SELECT 절 또는 ORDER BY 절에서만 사용이 가능하다.

윈도우 함수에서 사용하는 특수 함수 중 몇 가지를 알아보자.

 

 

1-1. 순위 정하기

• ROW_NUMBER() : 예) 1, 2, 3
• RANK() : 예) 1, 1, 3
• DENSE_RANK() : 예) 1, 1, 2 (비는 숫자 없이 빽빽하게)

 

사용 예시

• LeetCode, 185. Department Top Three Salaries (문제 보기)

-- 문제: 각 Department에서 Salary Top 3인 직원들 찾기(중복 O)
-- 풀이방식: window function중 DENSE_RANK() 사용

SELECT
    temp.Department
    , temp.Employee
    , temp.Salary
FROM(
    SELECT 
        d.name AS Department
        , e.name AS Employee
        , e.salary AS Salary
        , DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS Rank
    FROM Employee e
        INNER JOIN Department d ON e.departmentId=d.id 
) temp
WHERE temp.Rank < 4

 

1-2. 위치 바꾸기

• LAG(컬럼, [칸 수], [빈칸에 들어갈 값]) : 컬럼 밀기
• LEAD(컬럼, [칸 수], [빈칸에 들어갈 값]) : 컬럼 당기기

 

지정하지 않으면 [칸 수]는 한 칸, [빈칸에 들어갈 값]은 NULL로 설정된다.

 

사용 예시

• LeetCode, 180. Consecutive Numbers (문제 보기)

-- 문제: 3번 이상 연속되는 num 찾기
-- 풀이방식: window function중 LAG()를 사용

SELECT DISTINCT(afternext) AS ConsecutiveNums
FROM(
    SELECT 
        num
        , LAG(num, 1) OVER (ORDER BY id) AS next
        , LAG(num, 2) OVER (ORDER BY id) AS afternext
    FROM LOGS
) temp 
WHERE temp.num=temp.next 
    AND temp.next=temp.afternext

 

2. Regular Expression

LIKE와 달리 Regular Expression, 정규 표현식 REGEXP은 조금 더 복잡한 패턴을 사용해 문자열을 검색한다. (패턴을 일일이 외우기보다는 그때그때 필요한 걸 찾아서 쓰기. 여기에서 테스트하자💡)

 

 

3. User-Defined Function

SQL에서도 변수를 입력받아 원하는 작업을 하고, 결과를 반환하는 함수를 만들 수 있다. 기본 형태는 다음과 같다. (형태가 복잡해 보일 땐 바로 간단한 예제로👨‍💻)

 

CREATE FUNCTION 함수이름 (파라미터명, 입력 데이터 타입)
RETURNS 출력 데이터 타입 [DETERMINISTIC] AS

BEGIN

[DECLARE ;] -- 지역변수 선언
[SET ;]     -- 변수값 지정

RETURN  
END

 

사용 예시

• LeetCode, 177. Nth Highest Salary (문제 보기)

-- 문제: N번째로 높은 Salary 찾기
-- 풀이방식: 사용자 정의 함수 사용

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT
BEGIN
  RETURN (
        SELECT 
        -- CASE문 사용해서 NULL값 처리
            CASE WHEN COUNT(temp.Salary) < N THEN NULL
                ELSE MIN(temp.Salary)
            END
        FROM (
            -- Salary 중복 제거 후 정렬, N번째까지 자르기
            SELECT DISTINCT Salary
            FROM Employee
            ORDER BY Salary DESC
            LIMIT N
        ) temp  
  );
END

-- 풀이방식 2) IF (condition, value_if_ture, value_if_false)
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN 
  RETURN (
    -- IF 함수 사용
    SELECT IF(COUNT(temp.salary)=N, MIN(temp.salary), NULL)
    FROM(
        SELECT DISTINCT salary 
        FROM employee
        ORDER BY salary DESC
        LIMIT N   
    ) temp
  );
END

 

• CASE : CASE WHEN 조건 THEN 결과 ELSE 다른결과 END
• OFFSET : 몇 번째 ROW부터 출력할 지(0부터 시작). LIMIT 행개수 OFFSET 시작행