[개인공부 / 오라클] 윈도우 함수 Window Function

SQLD 공부에 참고한 영상
홍쌤의 데이터랩 - SQLD 2과목 PART2. SQL 활용 완벽 정리(2024 신유형 반영)

김강민SQLP - SQLD 최종 정리강의 2편 - 윈도우 함수 ~ 인덱스(Index)

 

서로 다른 행의 비교나 연산을 위해 만든 함수이다. GROUP BY를 쓰지 않고 그룹 연산 가능

종류 : LAG, LEAD, SUM, AVG, MIN,MAX, COUNT, RANK

 

예시

* 쓰는 순서 중요

SELECT 윈도우 함수([대상]) OVER ([PARTITION BY 컬럼]
                              [ORDER BY 컬럼 ASC|DESC]
                              [ROWS|RANGE BETWEEN A AND B]);

 

- PARTITION BY 절 : 출력할 총 데이터 수 변화없이 그룹 연산 수행할 GROUP BY 컬럼
- ORDER BY 절 : RANK 의 경우 필수 (정렬 순서나 컬럼에 따라 순위 변화), SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력시 사용
- ROWS|RANGE BETWEEN A AND B : 연산범위 설정, ORDER BY 절 필수

 

그룹함수 오류 예시

select employee_id, first_name, salary, department_id, sum(salary)
from employees;

오류 : 00937. 00000 -  "not a single-group group function"

 

그룹 함수의 형태

SUM, AVG, MIN, MAX, COUNT 등 OVER 절을 사용하여 윈도우 함수 사용가능
반드시 연산할 대상을 그룹 함수의 입력값으로 전달해야함

- SUM OVER() : 전체 총 합, 그룹 별 총 합 출력 가능

[Q] 각 직원의 정보와 함께 급여 총합 출력

오류

select employee_id, first_name, salary, department_id, sum(salary)
from employees;

 

해결 1 ) 스칼라 서브쿼리

select employee_id, first_name, salary, department_id
        ,(select sum(salary) from employees) as total
from employees;

 

해결 2 ) 윈도우 함수

AVG OVER(), MIN/MAX OVER(), COUNT OVER() 동일하게 쓰인다.

select employee_id, first_name, salary, department_id,
        SUM(salary) OVER() as total
from employees;

 

 

평균 구하는 예시

select employee_id, first_name, salary, department_id,
        AVG(salary) OVER(PARTITION BY department_id) as total
from employees;

 

윈도우 함수의 연산 범위

: 집계 연산시 행위 범위 설정 가능

1. ROWS 와 RANGE

- ROWS : 값이 같더라도 각 행씩 연산
- RANGE : 같은 값인 경우 하나의 RANGE로 묶어서 동시 연산 (디폴트)

 

2. BETWEEN A AND B

ROWS 쓸때 반드시 범위 지정해야한다.

- A : 시작점 정의 
     CURRENT ROW(현재행부터), UNBOUNDED PRECEDING(처음부터: 디폴트), N PRECEDING(N 이전부터)

- B : 마지막 시점 정의
    CURRENT ROW(현재행까지: 디폴트), UNBOUNDED FOLLOWING(마지막까지), N FOLLOWING(N 이후까지)

 

1. RANGE 범위전달

값이 같을 경우 같은 범위로 취급하여 동시 연산 (누적합은 ORDER BY 필수)

select e.*, SUM(salary) OVER(ORDER BY salary) result1
from employees e;

 

2. ROWS 범위 설정시

각 행별로 연산 수행

select e.*, SUM(salary) OVER(ORDER BY salary
                            ROWS BETWEEN UNBOUNDED PRECEDING
                            AND CURRENT ROW) as result2
from employees e;

 

3. BETWEEN A AND B 범위 변경

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING = 각 행마다 누적합 계산시 처음부터 다음 행까지 연산

select e.*, SUM(salary) OVER(ORDER BY salary
                            ROWS BETWEEN UNBOUNDED PRECEDING
                            AND 1 FOLLOWING) as result3
from employees e;

 

순위 관련 함수

1)RANK WITHIN GROUP

특정값에 대한 순위 확인, 윈도우 함수가 아닌 일반 함수

SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼);

 

[Q] EMP 에서 급여가 3000이면 전체 급여 순위가 얼마나?

select RANK(3000) WITHIN GROUP(ORDER BY salary DESC) as rank_value
from employees;

 

2) RANK() OVER()

전체 중 / 특정 그룹 중 값의 순위 확인
ORDER BY절 필수 (순위를 구할 대상을 ORDER BY 절에 명시, 여러개 나열 가능)
그룹 내 순위 구할 시 PARTITION BY 절 사용

SELECT RANK(값) OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);

 

[Q] 각 직원의 급여와 전체 순위 (큰 순서대로)

select first_name, department_id, salary,
        RANK() OVER(ORDER BY salary DESC) as rank_value
from employees;    -- 동순위 처리 가능 (동순위가 몇명인지 따라 다음 랭크가 달라짐)

 

[Q] 각 직원 이름, 부서번호, 급여, 부서별 급여 순위 (큰 순서대로)

select first_name, department_id, salary,
        RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rank
from employees;

 

3) DENSE_RANK : 누적 순위

값이 같을 때 동일한 순서 부여 후 다음 순위가 바로 이어지는 순위 부여 방식

4) ROW_NUMBER : 연속된 행 번호

동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서값 리턴

select first_name, department_id, salary,
    RANK() OVER(ORDER BY salary DESC) as rank1
    , DENSE_RANK() OVER(ORDER BY salary DESC) as rank2
    , ROW_NUMBER() OVER(ORDER BY salary DESC) as rank3
from employees;

 

5) LAG, LEAD

행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
ORDER BY절 필수

SELECT LAG(컬럼, -- 가져올 값을 갖는 컬럼
            N,  -- 몇 번째 값을 가져올지 (디폴트 : 1)
            OVER([PARTITION BY 컬럼]) -- 행의 이동 그룹
                ORDER BY 컬럼 ASC|DESC); -- 정렬컬럼

 

[Q]EMP 에서 바로 이전 입사자와 급여 비교

select first_name, hire_date, salary,
    LAG(salary) OVER(ORDER BY hire_date) as "바로 직전 상사급여"
from employees;

-- 참고 : LAG 는 행의 순서대로 가져온다.

 

 

6) FIRST_VALUE, LAST_VALUE

정해진 범위에서의 정렬 순서대로 처음값과 마지막 값 출력
순서와 범위 정의에 따라 최소값,최대값 리턴

 

FIRST_VALUE 를 사용한 최대 출력

select first_name, department_id, salary
    , FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary) as minValue
    , FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary DESC) as maxValue
from employees;

 

LAST_VALUE 를 사용한 최대 출력

select first_name, department_id, salary
    , LAST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary) as maxValue1
    -- 자기자신
    , LAST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary 
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as maxValue2
from employees;

 

7) NTILE

특정 순서에 따라 행을 정해진 수의 그룹으로 나누기 위한 함수
그룹번호가 리턴 됨. ORDER BY 필수. PARTITION BY 사용하여 특정그룹 원하는 만큼 분리 가능

select NTILE(N) OVER([PATITION BY 컬럼]
                     ORDER BY 컬럼 ASC|DESC)

 

NTILE 을 사용한 그룹 분리

select first_name, salary, department_id,
        NTILE(3) OVER (ORDER BY salary) as groupNumber
from employees;

 

비율 관련 함수

1) RATIO_TO_REPORT

각 값의 비율 리턴(전체 비율 또는 특정 그룹 내 비율가능)
ORDER BY 사용 불가 (값 하나의 비율이기 때문)

RATIO_TO_REPORT(대상) OVER([PARTITION BY ....])

 

2) CUME_DIST : 누적 비율

누적 비율 리턴(전체 비율 또는 특정 그룹 내 비율가능)
ORDER BY 필수(누적비율 구하는 순서 정할 수 있음)

CUME_DIST() OVER([PARTITION BY 컬럼] ORDER BY 컬럼)

[Q] 누적 비율 비교 (소수점 둘 째 자리)

select first_name, department_id, salary
        , ROUND(RATIO_TO_REPORT(salary) OVER(PARTITION BY department_id),2) rate1
        , ROUND(CUME_DIST() OVER(PARTITION BY department_id 
                                 ORDER BY salary),2) rate2

        , ROUND(CUME_DIST() OVER(PARTITION BY department_id 
                                 ORDER BY salary,first_name),2) rate3
                                 -- 가격이 같아도 이름이 다르니까 다른 그룹이라고 인지한다.
from employees;

3) PERCENT_RANK

PERCENTILE(분위수) 출력
전체 COUNT 중 상대적 위치 출력(0 ~ 1 범위 내) ORDER BY 필수

PERCENT_RANK() OVER([PARTITION BY 컬럼] ORDER BY 컬럼)

예제

select first_name, department_id, salary,
        round(PERCENT_RANK() OVER(ORDER BY salary),2)
from employees;