TIL (8/1) 윈도우 함수(My SQL)

1. 윈도우 함수란?

윈도우 함수는 SQL에서 집계 함수와 비슷하게 데이터를 집계하지만, 전체 데이터 집합이 아닌 특정 "윈도우" 또는 "슬라이스"에 대한 집계를 수행합니다. 각 행에 대해 계산을 수행하며, 그룹화된 데이터가 아닌 개별 행에서 결과를 계산합니다.

 

2. 윈도우 함수의 주요 구성 요소

• PARTITION BY : 데이터를 분할할 기준을 설정합니다. 이 구문으로 데이터 집합을 여러 파티션으로 나누어 각 파티션 내에서 윈도우 함수를 적용합니다.
• ORDER BY : 윈도우 함수가 계산될 순서를 정의합니다. 정렬된 순서에 따라 함수 결과가 달라질 수 있습니다.
• ROWS/ RANGE : 윈도우의 범위를 정의합니다. 특정 범위의 행을 지정할 수 있습니다.

3. 윈도우 함수를 사용하는 이유는 ?

1) 행별 계산 수행

전체 집합에서 특정 슬라이스 내의 데이터를 기준으로 계산을 수행합니다.

집계 함수와 달리, 개별 행을 유지하면서도 복잡한 집계 작업을 가능하게 합니다.

 

2) 복잡한 분석 및 순위 계산

윈도우 함수는 데이터 집합 내에서 순위나 분포를 계산할 때 유용합니다.

 

3) 이전 및 이후 행에 대한 데이터 참조

윈도우 함수는 현재 행의 이전이나 이후의 데이터를 참조하여 계산할 수 있습니다.

이를 통해 이동 평균, 누적 합계 등을 쉽게 계산할 수 있습니다.

 

4) 그룹화 없이 집계 결과 계산

윈도우 함수는 'GROUP BY'를 사용하지 않고도 집계 결과를 개별 행에 대해 계산할 수 있습니다.

이는 집계와 개별 데이터의 조합을 가능하게 합니다.

 

5) 데이터의 상대적 위치 평가

윈도우 함수는 데이터를 특정 기준으로 정렬하고, 각 행의 상대적 위치를 평가할 수 있습니다.

이를 통해 데이터의 패턴이나 트렌드를 분석할 수 있습니다.

 

결론 : 윈도우 함수는 데이터 분석에 있어 강력한 도구임.

          개별 행을 유지하면서 복잡한 집계를 가능하게 하며 다양한 작업을 효율적으로 처리할 수 있음.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

 

윈도우 함수의 일반적인 구문

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

ORDER BY 구문은 윈도우 함수 내에서 데이터를 정렬하는데 사용됨. 일반적인 ORDER BY 구문과 달리 윈도우 함수가 적용되는 범위 내에서만 정렬

 

PARTITION BY 구문은 데이터를 특정 기준으로 그룹화 하여 별도로 윈도우 함수를 적용하는 방법

GROUP BY 구문은 전체 데이터를 기준으로 그룹화 한다는 것과 대조적임.

 

위 구문은 함께 사용 가능하다.

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

결과

order by salary AESC로 바꾸면 낮은 순서대로 rank를 메긴다.

 

1. 윈도우 함수 종류

1) 집계 함수

sum() : 더하기

avg() : 평균

count() : 갯수

min() : 최소

max() : 최대

 

2) 순위 함수

rank() : 공동 몇 위, 이런게 있으면 다음 순위 건너뜀.

dense_rank() : 공동 몇 위 있어도 다음 순위를 건너뛰지않음.

row_number() : 동일한 값이 있어도 유일한 순위를 부여함. 내부에서 정해진 순서

percent_rank() : 백분율 순위 계산

ntile(N) : 파티션 내의 행을 N등분, 기본적으로 ORDER BY가 있어야 작동함.

 

3) 값 반환 함수

first_value() : 윈도우 프레임 내 첫 번째 값 반환 --> rank에 limit1 생각하면 편할듯

last_value() : 윈도우 프레임 내 마지막 값 반환

lag() : 윈도우 프레임 내 이전 행 값 반환

lead() : 윈도우 프레임 내 다음 행 값 반환

 

2. 윈도우 함수 범위 지정

• 프레임은 윈도우의 하위 집합
• 만약 윈도우 내에서 집계하는 범위를 정하고 싶다면 (EX 누적 월급)

SELECT 집계함수() OVER ( [PARTITION BY {컬럼명1}], [ORDER BY {컬럼명2}] [ROWS|RANGE {범위}])
FROM {테이블};

ROWS : 물리적인 행 단위 프레임 설정

BETWEEN : 논리적인 값을 범위로 지정

#1. 기본 구문
SELECT employee_id,
               department_id,
               salary,
               ROUND(AVG(salary) OVER (PARTITION BY department_id)) AS avg_salary_by_dept
FROM hr_employees
ORDER BY EMPLOYEE_ID ASC

#2. RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
SELECT employee_id,
               department_id,
               salary,
               ROUND(AVG(salary) OVER (PARTITION BY department_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS avg_salary_by_dept
FROM hr_employees
ORDER BY EMPLOYEE_ID ASC;

위의 두 구문은 등가이다.

SELECT employee_id,
               department_id,
               salary,
               ROUND(SUM(salary) OVER (ORDER BY EMPLOYEE_ID ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS avg_salary_by_dept
FROM hr_employees
ORDER BY EMPLOYEE_ID ASC

위의 실행결과

현재 행을 포함한다는 의미 > 누적

 

범위를 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 이렇게 바꾸면,

이렇게 출력된다. 차이를 잘 이해하자

SELECT 
    employee_id,
    department_id,
    salary,
    ROUND(SUM(salary) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS avg_salary_by_dept
FROM 
    hr_employees
ORDER BY EMPLOYEE_ID ASC

PARTITION 으로 범위를 지정하면,

부서별 누적값을 계산한다.

^ㅁ^