[SQL]WINDOW FUNCTION(윈도우 함수),분석함수(Analytic Function)

윈도우 함수(Window Function)는 데이터 집합의 각 행에 대해 계산을 수행하면서도, 전체 결과 집합에 대한 집계 결과를 제공하는 함수이다. 즉, 그룹화된 결과가 아닌, 원래의 데이터와 함께 결과를 제공한다. OVER() 절을 사용하여 특정 범위(윈도우)를 정의하고, 그 범위 내에서 계산을 수행한다.

주요 특징

• 행 유지: 윈도우 함수는 결과 집합의 각 행을 유지하면서 계산을 수행하고 집계 결과를 추가한다.
• 프레임 정의: OVER() 절을 사용하여 어떤 행을 기준으로 계산할지를 정의할 수 있다. 이 절 안에서 PARTITION BY와 ORDER BY를 사용하여 데이터의 구분과 정렬을 설정할 수 있다.
• 다양한 함수 지원: SUM, AVG, COUNT, RANK, ROW_NUMBER 등 다양한 집계 및 분석 함수가 윈도우 함수로 사용될 수 있다.

 

1. ROW_NUMBER()

각 행에 대해 고유한 번호를 부여한다. 주로 정렬된 결과에서 순서를 매길 때 사용된다.

ROW_NUMBER() OVER (ORDER BY column_name ASC/DESC)

예시

SELECT employee_id, 
       salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

 

이 쿼리는 급여가 높은 순서로 각 직원에게 고유한 번호를 부여한다.

2. RANK()

동일한 값에 대해 동일한 순위를 부여하고, 다음 순위는 건너뛴다.

RANK() OVER (ORDER BY column_name ASC/DESC)

예시

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

 

급여가 동일한 직원들은 같은 순위를 가지며, 다음 순위는 건너뛰게 된다.

3. DENSE_RANK()

동일한 값에 대해 동일한 순위를 부여하지만, 다음 순위는 건너뛰지 않는다.

DENSE_RANK() OVER (ORDER BY column_name DESC)

예시

SELECT employee_id, 
       salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees;

급여가 동일한 직원들은 같은 순위를 가지며, 다음 순위는 연속적으로 부여된다.

4. SUM()

누적 합계를 계산한다.

SUM(column_name) OVER (ORDER BY column_name)

예시

SELECT employee_id, 
       salary, 
       SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM employees;

이 쿼리는 직원 ID 순서에 따라 누적 급여를 계산한다.

5. AVG()

평균을 계산한다.

AVG(column_name) OVER (PARTITION BY column_name)

예시

SELECT department_id, 
       employee_id, 
       salary, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

각 부서 내에서 직원들의 평균 급여를 계산한다.

6. NTILE()

결과 집합을 지정된 수의 그룹으로 나누고, 각 행에 해당하는 그룹 번호를 반환한다.

NTILE(n) OVER (ORDER BY column_name)

n: 나누고자 하는 그룹의 수

column_name: 정렬 기준이 되는 열

예시

SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

 

이 쿼리는 직원의 급여를 기준으로 4개의 그룹(사분위수)으로 나누고, 각 직원이 속한 그룹 번호를 반환한다.

 

7. LISTAGG()

그룹화된 데이터의 값을 하나의 문자열로 결합하여 반환한다.

LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name)

column_name: 결합할 값이 있는 열

delimiter: 각 값 사이에 삽입할 구분자

예시

SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_names
FROM employees
GROUP BY department_id;

 

이 쿼리는 각 부서의 직원 이름을 알파벳 순으로 정렬하여 하나의 문자열로 결합하고, 각 부서별로 결과를 반환한다.

LISTAGG전

LISTAGG후

이러한 분석 함수들은 데이터 분석을 보다 효율적으로 수행할 수 있게 해주며, 복잡한 데이터 집합에서 유용한 통찰을 제공하는 데 필수적인 도구이다. 각 함수는 특정한 요구 사항에 맞춰 데이터를 처리하는 데 유용하다.

 

↓ 윈도우함수와 집계함수의 차이

집계 함수와의 차이점은 일반적으로 윈도우 함수는 결과 집합의 각 행을 독립적으로 유지하면서도, 특정 "윈도우" 또는 "프레임" 내에서 계산을 수행할 수 있다는 점이다.
1. 집계 함수 (Aggregate Function)
정의: 집계 함수는 여러 행의 값을 하나의 결과로 집계하는 함수이다. 주로 GROUP BY 절과 함께 사용되어 그룹화된 데이터에 대한 요약 정보를 제공한다.
특징
▪결과는 그룹화된 데이터의 각 그룹에 대해 하나의 행으로 반환된다.
▪ 원래의 데이터 행은 결과에 포함되지 않으며, 집계된 값만 반환된다.
예: SUM(), AVG(), COUNT(), MAX(), MIN() ...
2. 윈도우 함수 (Window Function)
정의: 윈도우 함수는 데이터 집합의 각 행에 대해 계산을 수행하면서도, 전체 결과 집합에 대한 집계 결과를 제공하는 함수이다. OVER() 절을 사용하여 특정 범위(윈도우)를 정의하고, 그 범위 내에서 계산을 수행한다.
특징
▪ 원래의 데이터 행을 유지하면서 추가적인 계산 결과를 제공한다.
▪ PARTITION BY와 ORDER BY를 사용하여 데이터의 구분과 정렬을 설정할 수 있다.
예: ROW_NUMBER(), RANK(), SUM() OVER (PARTITION BY ...), AVG() OVER (ORDER BY ...)...