[SQL 개념 정리 6] WINDOW 함수

WINDOW 함수

SQL의 윈도우 함수는 집계와 변환 작업을 특정한 범위(윈도우) 내에서 수행하여, 결과를 집계와는 다르게 각 행에 대해 별도의 값을 반환하는 함수입니다.

윈도우 함수를 사용하면 데이터에 대한 누적 합계, 순위, 이동 평균 등을 쉽게 계산할 수 있습니다.

---

윈도우 함수의 개념

윈도우 함수는 각 행에 대한 연산을 수행하되, 전체 또는 특정 그룹(윈도우) 내에서 계산을 수행하여 개별 행에 결과를 반환합니다. 이를 통해 누적 합계, 이동 평균, 순위 등을 계산할 수 있으며, 행을 유지하면서 데이터를 분석할 때 유용합니다.

• 행과 행 간의 관계를 나타내는 연산을 쉽게 하기 위한 함수

윈도우 함수는 OVER 절을 사용하여 실행되며, 여기서 특정 기준(PARTITION BY, ORDER BY)을 설정하여 원하는 데이터 그룹에 대한 연산을 정의합니다.

 

기본 구조

윈도우 함수() OVER (
    PARTITION BY 그룹 기준
    ORDER BY 정렬 기준
)

---

순위 관련 함수

1) ROW_NUMBER()

 

각 행에 고유한 순위를 부여합니다. 동일한 값이 있어도 고유한 순위를 가지므로 중복 없이 순위를 매길 때 사용합니다.

SELECT
    type1,
    kor_name,
    attack,
    ROW_NUMBER() OVER (
        PARTITION BY type1
        ORDER BY attack DESC
    ) AS attack_rank
FROM pokemon;

• 각 type1(포켓몬의 타입) 그룹 내에서 attack(공격력)을 기준으로 높은 순위부터 순위를 매겨 attack_rank에 저장합니다.

2) RANK()

 

데이터에 순위를 매기지만, 동일한 값이 있을 경우 같은 순위를 부여하고, 그다음 순위는 건너뜁니다.

SELECT
    type1,
    kor_name,
    attack,
    RANK() OVER (
        PARTITION BY type1
        ORDER BY attack DESC
    ) AS attack_rank
FROM pokemon;

• 각 타입별로 attack을 기준으로 높은 순위부터 순위를 부여하며, 같은 공격력이라면 같은 순위를 부여하고 그다음 순위는 건너뜁니다.

3) DENSE_RANK()

 

RANK()와 비슷하지만, 순위가 중간에 건너뛰지 않고 연속적으로 부여됩니다.

SELECT
    type1,
    kor_name,
    attack,
    DENSE_RANK() OVER (
        PARTITION BY type1
        ORDER BY attack DESC
    ) AS attack_rank
FROM pokemon;

• 동일한 attack 값을 가진 포켓몬들이 동일 순위를 가지며, 그다음 순위는 연속적으로 부여됩니다.

4) PERCENT_RANK()

 

각 행의 백분율 순위를 계산합니다. 이 값은 0부터 1 사이의 소수로 표현되며, PARTITION BY와 ORDER BY를 통해 기준을 설정합니다.

SELECT
    type1,
    kor_name,
    attack,
    PERCENT_RANK() OVER (
        PARTITION BY type1
        ORDER BY attack DESC
    ) AS attack_percent_rank
FROM pokemon;

• attack을 기준으로 백분율 순위를 계산하여 attack_percent_rank로 나타냅니다.

---

그룹 나누기 함수

NTILE(n)

 

데이터를 n개의 그룹으로 나누어 각 그룹에 번호를 부여합니다. 이는 사분위수, 백분위수 등의 범주형 변수를 생성하는 데 유용합니다.

SELECT
    type1,
    kor_name,
    total,
    NTILE(4) OVER (
        PARTITION BY type1
        ORDER BY total DESC
    ) AS quartile
FROM pokemon;

• type1 그룹 내에서 total을 기준으로 데이터를 4개의 그룹으로 나누고, 각 그룹에 번호(1, 2, 3, 4)를 부여하여 사분위수를 구합니다.

---

집계 함수

SUM()과 AVG()

 

누적 합계 또는 평균을 계산합니다. PARTITION BY를 통해 그룹화하고, ORDER BY로 정렬 기준을 설정할 수 있습니다.

SELECT
    location,
    level,
    SUM(level) OVER (PARTITION BY location) AS total_level,
    AVG(level) OVER (PARTITION BY location) AS avg_level
FROM trainer_pokemon;

• location별로 level을 합산한 total_level과 평균을 계산한 avg_level을 반환합니다.

---

이전/다음 값 참조 함수

LAG()와 LEAD()

 

LAG()는 현재 행의 이전 행을 참조하고, LEAD()는 현재 행의 다음 행을 참조합니다. PARTITION BY와 ORDER BY로 기준을 설정하여 특정 값의 변화를 추적할 때 유용합니다.

SELECT
    pokemon_id,
    experience_point,
    experience_point - LAG(experience_point, 1) OVER (
        ORDER BY pokemon_id
    ) AS exp_diff
FROM trainer_pokemon;

• 각 포켓몬의 experience_point와 이전 포켓몬의 경험치 차이를 exp_diff로 계산합니다. LAG() 함수가 이전 행의 값을 참조해 변화를 계산합니다.

---

윈도우 프레임(Window Frame)

윈도우 프레임은 ROWS 또는 RANGE 절을 사용하여 윈도우 함수가 계산될 행의 범위를 지정하는 기능입니다. 프레임을 설정하면 현재 행을 기준으로 특정 범위 내에서 연산을 수행할 수 있습니다. 예를 들어, 누적 합계나 이동 평균을 구할 때 유용하게 사용됩니다.

 

프레임 옵션

1. ROWS: 현재 행을 기준으로 특정 행 범위를 지정합니다.
2. RANGE: 값이 같은 행들을 포함하여 특정 값 범위를 지정합니다.

기본 구문

윈도우 함수() OVER (
    ORDER BY 열
    ROWS|RANGE BETWEEN 범위1 AND 범위2
)

 

예시: 누적 합계

SELECT
    kor_name,
    attack,
    SUM(attack) OVER (
        ORDER BY attack ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_attack
FROM pokemon;

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 첫 행부터 현재 행까지의 범위를 의미하여, 현재 행까지의 누적 합계를 cumulative_attack에 반환합니다.

예시: 이동 평균

SELECT
    kor_name,
    attack,
    AVG(attack) OVER (
        ORDER BY attack ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM pokemon;

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 현재 행과 그 이전 2행까지의 공격력의 평균을 계산하여 moving_avg에 반환합니다. 이는 이동 평균을 계산하는 방식으로, 일정한 범위 내에서 평균 값을 구할 수 있습니다.

---

PARTITION BY와 ORDER BY의 역할

• PARTITION BY: 윈도우를 나누는 기준입니다. 예를 들어, type1별로 그룹을 나누어 윈도우 함수를 적용하고자 할 때 사용합니다.
• ORDER BY: 윈도우 내에서 정렬 기준을 설정하여 함수의 연산 순서를 지정합니다.

예시

SELECT
    type1,
    kor_name,
    attack,
    RANK() OVER (
        PARTITION BY type1
        ORDER BY attack DESC
    ) AS attack_rank
FROM pokemon;

• PARTITION BY type1은 각 타입(type1)별로 윈도우를 나누고, ORDER BY attack DESC는 각 타입 내에서 attack을 기준으로 내림차순 정렬하여 순위를 매깁니다.

---

윈도우 함수 요약

• 순위 함수:
  • ROW_NUMBER(): 고유 순위를 부여.
  • RANK(): 동일 값에 중복 순위 부여, 다음 순위는 건너뜀.
  • DENSE_RANK(): 동일 값에 중복 순위 부여, 다음 순위는 연속적.
  • PERCENT_RANK(): 백분율 순위 계산.
• 그룹 나누기 함수:
  • NTILE(n): 데이터를 n개의 그룹으로 나눠 각 그룹에 번호 부여.
• 집계 함수:
  • SUM(), AVG(): 누적 합계 또는 평균 계산.
• 이전/다음 값 참조 함수:
  • LAG(): 이전 행의 값을 참조.
  • LEAD(): 다음 행의 값을 참조.
• 윈도우 프레임: ROWS와 RANGE를 사용해 특정 범위 지정 가능.

윈도우 함수는 OVER 절을 사용해 데이터를 그룹화하고 특정 범위 내에서 연산을 수행할 수 있습니다. 이를 통해 데이터를 다양한 방식으로 분석하고, 행을 유지하면서 연산 결과를 개별 행에 반영할 수 있습니다.

---

윈도우 함수는 데이터를 보다 유연하고 직관적으로 분석할 수 있게 해주는 SQL의 강력한 도구입니다. 특히 데이터의 흐름이나 순위를 파악하거나, 누적 합계와 이동 평균 등을 계산하는데 유용합니다. 이를 통해 데이터를 세밀하게 분해하고 분석하여 유의미한 인사이트를 도출할 수 있습니다. 윈도우 함수를 이해하고 활용한다면, SQL 데이터 분석의 깊이를 한층 더 높일 수 있습니다.