[SQL]윈도우 함수

윈도우 함수

  - 순위, 집계 등 행과 행 사이에 관계를 정의하는 함수

  - OVER 구문을 필수로 함

 

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 컬럼] [ORDER BY절] [WINDOWING절])
FROM 테이블명
;

구조	설명
ARGUMENTS	윈도우 함수에 따라서 필요한 인수
PARTITION BY	전체 집합에 대해 소그룹으로 나누는 기준
ORDER BY	소그룹에 대한 정렬 기준
WINDOWING	행에 대한 범위 기준

구조	설명
ROWS	물리적 단위로 행의 집합을 지정
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫번째 행
UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 행
CURRENT ROW	윈도우의 시작 위치가 현재 행

 

 

 

순위함수

RANK() OVER ([PARTITION BY 컬럼] [ORDER BY절] [WINDOWING절])

   - RANK: 동일한 값에는 동일한 순위를 부여

   - DENSE_RANK: RANK와 같이 같은 값에는 같은 순위를 부여하나, 한 건으로 취급

   - ROW_NUMBER: 동일한 값이라도 고유한 순위를 부여

 

 

일반 집계 함수

일반 집계 함수(SUM, AVG, MAX, MIN ...)를  GROUP BY 구문 없이 사용할 수 있다

-- 스칼라 서브쿼리 사용
SELECT
    id, name, score, class_id
    (SELECT AVG(score)
    FROM student b
    WHERE b.class_id = a.class_id) class_score_avg
FROM student a
;

-- 윈도우 함수 사용
SELECT
    id, name, socre, class_id, AVG(score) OVER (PARTITION BY class_id)  class_score_avg
FROM student a
;

 

 

 

그룹 내 행 순서 함수

  - FIRST_VALUE: 가장 먼저 나온 값을 구함

  - LAST_VALUE: 가장 나중에 나온 값을 구함

  - LAG: 이전 X번째 행을 가져옴

  - LEAD: 이후 X번째 행을 가져옴

 

그룹 내 행 순서함수 예시(FIRST_VALUE, LAST_VALUE): 본인 반의 최고점, 최저점 함께 출력

SELECT
    id, name, class_id, score,
    FIRST_VALUE(score) 
    OVER(PARTITION BY class_id ORDER BY score 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    AS class_min_score,
    LAST_VALUE(score) 
    OVER(PARTITION BY class_id ORDER BY score 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    AS class_max_score
FROM student
;

 

그룹 내 행 순서함수 예시(LAG,LEAD): 본인 번호 앞과 뒤에 해당하는 학생의 이름을 함께 출력

SELECT
    id, name, 
    LAG(name,1) OVER(ORDER BY id) AS pre_name,
    LEAD(name,1) OVER(ORDER BY id) AS post_name
FROM student
;

 

 

 

그룹 내 비율함수

  - RATIO_TO_REPORT: 파티션 내 전체 SUM에 대한 비율을 구함

  - PERCENT_RANK: 파티션 내 순위를 백분율로 구함

  - CUM_DIST: 파티션 내 현재 행보다 작거나 같은 건들의 수 누적 백분율로 구함

  - NTILE: 파티션 내 행들을  N등분한 결과를 구함

 

그룹 내 비율 함수 예시(RATIO_TO_REPORT): 직원 전체 급여의 합 중 각 행이 차지하는 비율을 출력

SELECT
    id, name, salary,
    SUM(salary) OVER() as total_salary
    RATIO_TO_REPORT(salary) OVER() as salary_ratio
FROM employee
;
    
-- Maria DB는 지원 안 됨
SELECT
	id, name, salary,
    SUM(salary) OVER() as total_salary
    (SALARY / SUM(salary) over()) as salary_ratio
FROM employee
;

 

그룹 내 비율 함수 예시(PERCENT_RANK, CUME_DIST)

  - PERCENT_RANK는 순위를 백분율로 나타내며, 제일 높은 순위 행은 0, 가장 낮은 순위 행은 1

  - CUM_DIST는 현재 행보다 같거나 낮은 값들을 가지는 행들의 누적 백분율 값을 나타냄

SELECT
    id, name, salary,
    PERCENT_RANK() OVER(ORDER BY salary DESC) AS percent_rank,
    ROUND(CUME_DIST() OVER(ORDER BY salary DESC), 4) AS CUME_DIST
FROM employee
;

 

그룹 내 비율 함수 예시(NTILE): 급여에 따라 직원들을 세 그룹으로 분류

SELECT
    id, name, salary,
    NTILE(3) OVER(ORDER BY salary DESC) as NTILE
FROM employee
;