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: 시계열 데이터의 전후 값 비교.

Previous실습스키마 Next실습스키마

Last updated 1 year ago