[SQL] OVER

결과를 반환하면서, 집계/순위/이전값 비교 등을 할 때 활용하는 윈도우 함수

집계 함수가 결과의 전체를 대상으로 계산하지 않고, 행에 대해 일정 범위 내에서만 계산 할 수 있게 함.

SELECT COLUMN1, COLUMN2,
       AGGREGATE_FUNCTION(COLUMN3) OVER (PARTITION BY COLUMN1 ORDER BY COLUMN2)
FROM MY_TABLE;

PARTITION BY	데이터를 그룹으로 나눈다(나누지 않을 시 결과 전체를 대상으로 계산)
ORDER BY	계산된 결과에 대한 정렬

횔용 ① 집계함수를 쓰고싶은데, 그룹바이 하긴 싫을 때 OVER()을 활용할 수 있다.

-- 그룹화 하지 않으면 에러가 남(단일 그룹의 그룹함수가 아닙니다)
SELECT DEPARTMENT_ID, SALARY, SUM(SALARY) -- ERR
FROM EMPLOYEES;
-- OVER의 활용
SELECT DEPARTMENT_ID, SALARY, SUM(SALARY) OVER()
FROM EMPLOYEES;

활용 ② 집계 함수의 활용1

-- 부서로 그룹화해서 사용자 옆에 소속 부서의 평균 급여를 붙이자
SELECT EMPLOYEE_ID, EMP_NAME, DEPARTMENT_ID,SALARY,
    AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS AVG_SALARY
FROM EMPLOYEES;

활용 ③ 집계 함수의 활용2

-- 소속 부서의 누적 급여합을 붙여보고, EMPLOYEE ID로 정렬하자
SELECT EMPLOYEE_ID,EMP_NAME,DEPARTMENT_ID,SALARY,
    SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID) AS CUMULATIVE_SALARY
FROM EMPLOYEES;

활용 ④ 윈도우 함수와의 결합

-- 부서별 급여 순위
SELECT EMPLOYEE_ID, EMP_NAME, DEPARTMENT_ID, SALARY,
    RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMPLOYEES;

같이 자주 쓰는 윈도우 함수

윈도우 함수	설명	예시
RANK()	중복 순위 개수만큼 다음 순위 값 증가	1 > 2 > 3 > 3 > 5 > 6...
DENSE_RANK()	중복 순위가 있어도, 순차적으로 순위 값 증가	1 > 2 > 3 > 3 > 4 > 5...
ROW_NUMBER()	중복 값들에도 순차적인 순위를 표시함	1 > 2 > 3 > 4 > 5 > 6...  3,4가 동일한 값이어도 순위는 순차적으로 증가
NTILE(n)	데이터를 n개의 그룹으로 나누고, 각 행에 해당하는 그룹 번호 반환	NTILE(3)  1,2 / 3,4 / 5, 6                    1      2      3
LEAD()	현재 행 이후의 데이터 참조	현재 행을 기준으로 상대적인 위치의 데이터를 가져옴
LAG()	현재 행 이전의 데이터 참조
FIRST_VALUE()	해당 그룹의 첫번째 값을 반환
LAST_VALUE()	해당 그룹의 마지막 값을 반환