윈도우 함수는 집계값을 구하되, 원본 데이터의 각 행을 유지하면서 해당 행과 관련된 집계 결과를 계산합니다. 즉, 그룹화하지 않고도 각 행별로 집계 작업을 수행할 수 있습니다.
주요 특징
원본 행 유지: 데이터의 모든 행이 유지됩니다.
OVER 절 사용: 윈도우 함수는 OVER() 절을 사용해 연산 범위(윈도우)를 정의합니다.
집계와 개별 데이터 동시 제공: 집계 결과를 계산하면서 개별 데이터도 함께 제공합니다.
주요 함수
윈도우 함수는 다음과 같은 함수로 구성됩니다:
순위 함수: ROW_NUMBER(), RANK(), DENSE_RANK()
집계 함수: SUM(), AVG(), MIN(), MAX(), COUNT()
이동 함수: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
기본 구문
SELECT
column1,
column2,
SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS running_total
FROM table_name;
2. GROUP BY
GROUP BY는 데이터를 특정 기준으로 그룹화하고, 그룹별로 데이터를 집계합니다. 그룹화가 이루어지면 각 그룹에 대해 한 행만 반환됩니다.
주요 특징
그룹별 데이터만 반환: 그룹화된 각 그룹에 대해 하나의 결과 행만 반환됩니다.
집계 함수 필수:SUM(), AVG(), COUNT() 등 집계 함수와 함께 사용됩니다.
행 축소: 데이터를 그룹으로 묶기 때문에 결과 데이터의 행 수가 줄어듭니다.
기본 구문
SELECT
column1,
SUM(column2) AS total
FROM table_name
GROUP BY column1;
3. 윈도우 함수와 GROUP BY의 차이점
특징
윈도우 함수
GROUP BY
원본 데이터 유지 여부
원본 데이터를 유지하며 추가 계산값을 함께 표시합니다.
그룹화된 결과만 반환하고 원본 데이터는 제거됩니다.
결과 행 수
원본 데이터의 행 수와 동일합니다.
그룹화된 데이터만 남아 행 수가 줄어듭니다.
사용 방식
OVER() 절을 사용해 윈도우(범위)를 정의합니다.
GROUP BY를 사용해 특정 컬럼을 기준으로 그룹화합니다.
사용 목적
그룹화 없이도 순위 계산, 이동 평균, 누적 합 등을 계산할 때 사용됩니다.
그룹화된 데이터에 대해 요약 정보(합계, 평균 등)를 계산할 때 사용됩니다.
적용 함수
순위 함수, 이동 함수 등 다양한 윈도우 함수.
집계 함수만 사용 가능합니다.
4. 예제 비교
(1) 데이터
| department | employee | salary |
|------------|----------|--------|
| Sales | John | 5000 |
| Sales | Jane | 6000 |
| IT | Alice | 7000 |
| IT | Bob | 8000 |
(2) 윈도우 함수 사용
부서별 급여 합계를 모든 행에서 확인하고 싶을 때:
SELECT
department,
employee,
salary,
SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;
결과:
| department | employee | salary | total_salary |
|------------|----------|--------|--------------|
| Sales | John | 5000 | 11000 |
| Sales | Jane | 6000 | 11000 |
| IT | Alice | 7000 | 15000 |
| IT | Bob | 8000 | 15000 |
(3) GROUP BY 사용
부서별 급여 합계를 계산하고, 그룹화된 결과만 보고 싶을 때:
SELECT
department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;
결과:
| department | total_salary |
|------------|--------------|
| Sales | 11000 |
| IT | 15000 |
5. 윈도우 함수 추가 예제
(1) 순위 계산 (ROW_NUMBER)
각 부서별로 급여 순위를 매기기:
SELECT
department,
employee,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
결과:
| department | employee | salary | rank |
|------------|----------|--------|------|
| Sales | Jane | 6000 | 1 |
| Sales | John | 5000 | 2 |
| IT | Bob | 8000 | 1 |
| IT | Alice | 7000 | 2 |
(2) 누적 합계 (Running Total)
부서별 급여의 누적 합계를 계산하기:
SELECT
department,
employee,
salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total
FROM employees;
결과:
| department | employee | salary | running_total |
|------------|----------|--------|---------------|
| Sales | John | 5000 | 5000 |
| Sales | Jane | 6000 | 11000 |
| IT | Alice | 7000 | 7000 |
| IT | Bob | 8000 | 15000 |
1. LAG와 LEAD 개요
LAG 함수
정의: 현재 행의 기준으로 이전 행의 값을 가져옵니다.
용도: 이전 값과 현재 값을 비교하거나, 변화율 등을 계산할 때 사용합니다.
LEAD 함수
정의: 현재 행의 기준으로 다음 행의 값을 가져옵니다.
용도: 다음 값과 현재 값을 비교하거나, 미래 값을 참조할 때 사용합니다.
2. 기본 구문
LAG(column_name, offset, default_value) OVER (PARTITION BY column1 ORDER BY column2)
LEAD(column_name, offset, default_value) OVER (PARTITION BY column1 ORDER BY column2)
column_name: 참조할 열
offset: 몇 행 앞(LEAD) 또는 몇 행 뒤(LAG)를 참조할지 설정 (기본값은 1)
SELECT
emp_id,
department,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY join_date) AS previous_salary,
salary - LAG(salary) OVER (PARTITION BY department ORDER BY join_date) AS salary_change
FROM employees;
SELECT
emp_id,
department,
salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY join_date) AS next_salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY join_date) - salary AS salary_gap
FROM employees;
SELECT
emp_id,
department,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY join_date) AS previous_salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY join_date) AS next_salary
FROM employees;