09 SQL 활용
1. 윈도우 함수(Window Function)
윈도우 함수는 집계값을 구하되, 원본 데이터의 각 행을 유지하면서 해당 행과 관련된 집계 결과를 계산합니다. 즉, 그룹화하지 않고도 각 행별로 집계 작업을 수행할 수 있습니다.
주요 특징
원본 행 유지: 데이터의 모든 행이 유지됩니다.
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)
default_value: 참조할 데이터가 없을 때 반환할 기본값 (기본값은
NULL)PARTITION BY: 그룹화 기준 (옵션)
ORDER BY: 참조할 행의 순서를 결정
3. LAG와 LEAD의 차이점
함수
값 참조 방향
용도
LAG
이전 행
전년도 데이터, 전월 데이터 비교 등
LEAD
다음 행
다음 행의 값 참조, 예측 데이터 등
4. 예제 데이터
| emp_id | department | salary | join_date |
|--------|------------|--------|------------|
| 1 | Sales | 5000 | 2023-01-01 |
| 2 | Sales | 6000 | 2023-02-01 |
| 3 | Sales | 7000 | 2023-03-01 |
| 4 | IT | 8000 | 2023-01-01 |
| 5 | IT | 9000 | 2023-02-01 |5. LAG 사용 예제
(1) 이전 행의 급여 가져오기
SELECT
emp_id,
department,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY join_date) AS previous_salary
FROM employees;결과:
| emp_id | department | salary | previous_salary |
|--------|------------|--------|-----------------|
| 1 | Sales | 5000 | NULL |
| 2 | Sales | 6000 | 5000 |
| 3 | Sales | 7000 | 6000 |
| 4 | IT | 8000 | NULL |
| 5 | IT | 9000 | 8000 |(2) 급여 변화량 계산
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;결과:
| emp_id | department | salary | previous_salary | salary_change |
|--------|------------|--------|-----------------|---------------|
| 1 | Sales | 5000 | NULL | NULL |
| 2 | Sales | 6000 | 5000 | 1000 |
| 3 | Sales | 7000 | 6000 | 1000 |
| 4 | IT | 8000 | NULL | NULL |
| 5 | IT | 9000 | 8000 | 1000 |6. LEAD 사용 예제
(1) 다음 행의 급여 가져오기
SELECT
emp_id,
department,
salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY join_date) AS next_salary
FROM employees;결과:
| emp_id | department | salary | next_salary |
|--------|------------|--------|-------------|
| 1 | Sales | 5000 | 6000 |
| 2 | Sales | 6000 | 7000 |
| 3 | Sales | 7000 | NULL |
| 4 | IT | 8000 | 9000 |
| 5 | IT | 9000 | NULL |(2) 다음 급여와 현재 급여 비교
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;결과:
| emp_id | department | salary | next_salary | salary_gap |
|--------|------------|--------|-------------|------------|
| 1 | Sales | 5000 | 6000 | 1000 |
| 2 | Sales | 6000 | 7000 | 1000 |
| 3 | Sales | 7000 | NULL | NULL |
| 4 | IT | 8000 | 9000 | 1000 |
| 5 | IT | 9000 | NULL | NULL |7. LAG & LEAD 비교 사용
이전 급여와 다음 급여를 모두 포함한 테이블 만들기:
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;결과:
| emp_id | department | salary | previous_salary | next_salary |
|--------|------------|--------|-----------------|-------------|
| 1 | Sales | 5000 | NULL | 6000 |
| 2 | Sales | 6000 | 5000 | 7000 |
| 3 | Sales | 7000 | 6000 | NULL |
| 4 | IT | 8000 | NULL | 9000 |
| 5 | IT | 9000 | 8000 | NULL |8. OFFSET과 기본값 설정
이전 행 대신 2행 전 참조 (offset 2):
SELECT
emp_id,
salary,
LAG(salary, 2, 0) OVER (PARTITION BY department ORDER BY join_date) AS two_previous_salary
FROM employees;결과: 2행 전 값이 없으면 기본값(0)을 반환합니다.
| emp_id | salary | two_previous_salary |
|--------|--------|---------------------|
| 1 | 5000 | 0 |
| 2 | 6000 | 0 |
| 3 | 7000 | 5000 |
| 4 | 8000 | 0 |
| 5 | 9000 | 0 |9. 활용 사례
매출 데이터 분석:
LAG: 전월 대비 매출 변화량 계산.
LEAD: 다음 달 예산과의 차이 계산.
이탈 분석:
LAG: 고객의 이전 구매 날짜를 참조해 이탈 가능성 분석.
시계열 데이터:
LAG/LEAD: 시계열 데이터의 전후 값 비교.
Last updated