[MYSQL] 윈도우 함수 총정리 1 (AGGREGATE, RANKING)

 

윈도우 함수 총정리 1 에서는 윈도우 함수의 기본적 사용부터 집계, 순위 매기기 등의 활용까지 살펴볼 예정입니다.

 

윈도우 함수 WINDOW FUNCTION

윈도우 함수는 서브쿼리처럼 쓰이지만 서브쿼리보다 간단하고 가동성이 높은 특징을 가졌습니다.

특히 이동평균, 누적합계, 순위 계산에 유용하게 쓰입니다. 

 

기본 문법

SELECT 집계함수() OVER (PARTITION BY {컬럼명1}, ORDER BY {컬럼명2}) 
FROM {테이블};

 

• PARTITION BY는 GROUP BY 와 동일한 기능으로 특정 기준으로 윈도우함수 내에서 분류
• ORDER BY는 윈도우함수 내에서 정렬될 기준

 

아래 기본적인 사용 예시를 보겠습니다. 

• 전체 직원을 대상으로 월급을 많이 받는 순위 매기기

SELECT employee_id, department_id, salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM hr_employees;

 

• 월급을 많이 받는 순위를 부서별로 나눠서 매기기

SELECT employee_id, department_id, salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM hr_employees
ORDER BY 1;

 

---

 

활용

윈도우 함수의 꽃은 바로 활용인데요~

이동평균, 누적합, 순위, 앞/뒤 값 반환 등 여러 동작을 수행할 수 있습니다. 

활용 범위

 

Aggregate

함수	역할
SUM()	합
AVG()	평균
COUNT()	개수
MIN()	최솟값
MAX()	최댓값

 

• 부서별 직원별 평균 월급 확인하기

SELECT employee_id, department_id, salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_dept
FROM hr_employees
ORDER BY avg_salary_by_dept;

 

Ranking

함수	역할
RANK()	동일한 값에 동일한 순위 매기고 다음 순위 건너뜀
DENSE_RANK()	동일한 값에 동일한 순위 매기고 다음 순위 건너뛰지 않음
ROW_NUMBER()	동일한 값이 있어도 유일한 순위 매김
PERCENT_RANK()	백분율 순위 계산
NTILE(N)	파티션 내의 행을 N등분, ORDER BY 필수

 

# RANK TYPES
SELECT employee_id, department_id, salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_row,
    PERCENT_RANK() OVER (ORDER BY salary DESC) AS salary_pct
FROM hr_employees;

 

# NTILE
SELECT EMPLOYEE_ID , DEPARTMENT_ID ,salary,
NTILE(5) OVER (PARTITION BY DEPARTMENT_ID ORDER BY salary desc) AS ntile2
FROM HR_EMPLOYEES 
ORDER BY EMPLOYEE_ID ASC ;

 

• 모든 직원을 대상으로 급여 백분위를 이용하여 iqr으로 상한이상치, 정상범위, 하한이상치 대상자를 찾아보자

WITH salary_ranks AS (
    SELECT salary, PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
    FROM hr_employees
),
quartiles AS (
    SELECT MIN(CASE WHEN pct_rank >= 0.25 THEN salary END) AS Q1, # 25% 선의 값을 Q1
           MAX(CASE WHEN pct_rank <= 0.75 THEN salary END) AS Q3  # 75% 선의 값을 Q3 
    FROM salary_ranks
),
iqr_calc AS (
    SELECT Q1, Q3, (Q3 - Q1) AS IQR,
        (Q1 - 1.5 * (Q3 - Q1)) AS lower_bound,
        (Q3 + 1.5 * (Q3 - Q1)) AS upper_bound
    FROM quartiles
)
-- 
-- -- 이상치 판별
SELECT e.first_name, e.last_name, e.salary,
    CASE WHEN e.salary < iqr_calc.lower_bound THEN 'LOWER_OUTLIER'
        WHEN e.salary > iqr_calc.upper_bound THEN 'UPPER_OUTLIER'
        ELSE 'ORDINARY' END AS outlier_chk
FROM hr_employees e , iqr_calc;

 

Value

함수	역할
FIRST_VALUE()	윈도우 프레임 내 첫번째 값 반환
LAST_VALUE()	윈도우 프레임 내 마지막 값 반환
LAG()	윈도우 프레임 내 기준행 이전행 값 반환
LEAD()	윈도우 프레임 내 기준행 다음행 값 반환

 

• 부서별 최고 연봉자 찾아보기

SELECT employee_id, department_id, salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary_in_dept
FROM hr_employees
ORDER BY employee_id ASC;

 

• 사번 기준으로 선배의 월급 정보 가져오기

SELECT employee_id, department_id, salary,
    LAG(salary, 1) OVER (ORDER BY employee_id DESC) AS previous_salary
FROM hr_employees
ORDER BY employee_id DESC;

 

 

 

윈도우 함수 총정리 2 에서는 윈도우 프레임 내 범위 설정을 다루도록 하겠습니다.

 

 

 

오랑우탄이 영어를 하고 오랑이가 쿼리마스터가 되는 그날까지~