PostgreSQL 윈도우 함수: 데이터 분석의 새로운 지평을 열다

안녕하세요, 데이터 애호가 여러분! 데이터 분석을 하다 보면 단순한 집계만으로는 부족한 순간이 많죠. 그룹별로 요약된 숫자만으로는 전체 그림을 놓치기 쉽습니다. 여기서 PostgreSQL의 윈도우 함수가 등장합니다. 이 강력한 도구는 각 행의 개별성을 유지하면서도 주변 데이터와의 관계를 계산해줍니다. 결과적으로, 판매 추이 분석부터 성과 순위 매기기까지 복잡한 인사이트를 SQL 한 번으로 뽑아낼 수 있어요.

이 포스트에서는 윈도우 함수의 기본 개념부터 실전 예제까지 단계적으로 탐구해보겠습니다. 초보자도 따라할 수 있도록 간단한 쿼리와 가상 데이터를 함께 설명하니, PostgreSQL을 다루는 개발자나 데이터 분석가라면 꼭 읽어보세요. 함께 데이터 분석 스킬을 업그레이드 해보죠!

728x90

윈도우 함수란 무엇인가?

윈도우 함수(Window Function)는 SQL에서 "현재 행을 중심으로 한 특정 범위(윈도우)"에 대해 계산을 수행하는 함수입니다. 일반 집계 함수(GROUP BY와 함께 사용)처럼 전체 그룹을 하나의 값으로 줄이는 대신, 각 행을 유지하면서 그 행 주변의 데이터를 활용해 결과를 생성합니다.

왜 유용할까?

• 개별 행 유지: 원본 데이터의 세밀함을 잃지 않습니다. 예를 들어, 매일 판매액을 나열하면서도 누적 합계를 볼 수 있어요.
• 실제 적용 사례:
  • 시간 기반 분석: 누적 매출, 이동 평균으로 추세 파악.
  • 순위 및 비교: 부서별 랭킹, 이전/이후 값 비교.
  • 복잡한 보고서: 재무 변화율, 분기별 성과 비교.

PostgreSQL은 MySQL이나 다른 DBMS와 달리 윈도우 함수를 강력하게 지원하니, 데이터 웨어하우스나 BI 도구와 결합할 때 특히 빛을 발합니다.

윈도우 함수의 기본 구문

윈도우 함수는 OVER 절을 통해 정의됩니다. 기본 구조는 다음과 같아요:

SELECT 
    column1,
    column2,
    WINDOW_FUNCTION() OVER (
        PARTITION BY column3  -- 파티션 나누기 (선택적)
        ORDER BY column4      -- 순서 지정 (선택적)
        ROWS/RANGE BETWEEN ... -- 범위 지정 (선택적)
    ) AS alias_name
FROM table_name;

주요 구성 요소 설명

• PARTITION BY: 데이터를 논리적 그룹(파티션)으로 나눕니다. 각 파티션 내에서 함수가 독립적으로 계산되죠. 예: 지역별, 부서별 분석.
• ORDER BY: 파티션 내 행의 순서를 정합니다. 누적 계산(예: SUM)이나 순위(RANK)에 필수적입니다.
• ROWS/RANGE: 계산 범위를 정의합니다.
  • ROWS: 물리적 행 수 기반 (예: 이전 3행).
  • RANGE: 값 범위 기반 (예: 현재 값 ±10%).
  • 예시: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (처음부터 현재 행까지).

이 구문을 익히면 대부분의 윈도우 함수를 자유자재로 다룰 수 있습니다. 이제 실전으로 넘어가보죠!

실제 시나리오를 통한 윈도우 함수 활용

가상의 테이블을 사용해 예제를 설명하겠습니다. 각 예제 끝에 결과를 테이블로 보여드릴게요. (PostgreSQL에서 직접 실행해보세요!)

1. 누적 합계 계산: 판매 추이 파악

특정 기간 동안의 매출 누적을 계산하면 비즈니스 성장을 한눈에 볼 수 있습니다. sales_data 테이블을 가정해 보죠:

sale_date	amount	region
2023-01-01	100	North
2023-01-02	150	North
2023-01-01	200	South
2023-01-02	250	South

쿼리:

SELECT 
    sale_date,
    amount,
    region,
    SUM(amount) OVER (
        PARTITION BY region 
        ORDER BY sale_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales_data
ORDER BY region, sale_date;

결과:
| sale_date | amount | region | running_total |
|-----------|--------|--------|---------------|
| 2023-01-01 | 100 | North | 100 |
| 2023-01-02 | 150 | North | 250 |
| 2023-01-01 | 200 | South | 200 |
| 2023-01-02 | 250 | South | 450 |

이처럼 지역별 누적 매출을 통해 성장 패턴을 쉽게 분석할 수 있습니다.

2. 행 순위 지정: 성과 평가

부서별 직원 순위를 매기면 HR 시스템에 유용하죠. employee_performance 테이블 예시:

employee_id	department_id	performance_score
1	Sales	95
2	Sales	90
3	IT	85
4	IT	92

쿼리 (RANK() 사용 – 동점 시 같은 순위):

SELECT 
    employee_id,
    department_id,
    performance_score,
    RANK() OVER (
        PARTITION BY department_id 
        ORDER BY performance_score DESC
    ) AS rank_within_department
FROM employee_performance
ORDER BY department_id, performance_score DESC;

결과:
| employee_id | department_id | performance_score | rank_within_department |
|-------------|---------------|-------------------|------------------------|
| 1 | Sales | 95 | 1 |
| 2 | Sales | 90 | 2 |
| 4 | IT | 92 | 1 |
| 3 | IT | 85 | 2 |

DENSE_RANK()나 ROW_NUMBER()로 변형해 동점 처리 방식을 조정할 수 있습니다.

3. 이동 평균 계산: 추세 부드럽게 보기

일시적 변동을 무시하고 장기 추세를 파악할 때 딱입니다. 이전 2일 평균으로 예시:

쿼리:

SELECT 
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_average_sales
FROM sales_data
ORDER BY sale_date;

(가상 결과: 매일 평균이 부드럽게 계산됨.) 이는 주식 가격이나 방문자 수 분석에 자주 쓰입니다.

4. 현재 행과 이전 행 간의 차이 찾기: 변화율 분석

재무 보고서에서 필수! LAG() 함수로 이전 값을 가져옵니다. monthly_revenue 테이블:

month_year	revenue
2023-01	1000
2023-02	1200
2023-03	1100

쿼리:

SELECT 
    month_year,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY month_year) AS change_from_previous_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month_year)) / LAG(revenue) OVER (ORDER BY month_year) * 100, 2
    ) AS percentage_change
FROM monthly_revenue
ORDER BY month_year;

결과:
| month_year | revenue | change_from_previous_month | percentage_change |
|------------|---------|----------------------------|-------------------|
| 2023-01 | 1000 | NULL | NULL |
| 2023-02 | 1200 | 200 | 20.00 |
| 2023-03 | 1100 | -100 | -8.33 |

추가로 LEAD()를 사용하면 다음 값과 비교할 수 있어 미래 예측에 유용합니다.

추가 팁: 성능 최적화와 베스트 프랙티스

• 인덱스 활용: ORDER BY 열에 인덱스를 걸어 쿼리 속도를 높이세요.
• 프레임 지정: 큰 데이터셋에서는 RANGE 대신 ROWS를 사용해 메모리 부하를 줄이세요.
• 연습 추천: pgAdmin이나 DBeaver에서 샘플 DB(예: Pagila)를 불러와 테스트해보세요.
• 고급 함수: NTILE()로 데이터를 등분하거나, FIRST_VALUE()로 파티션 첫 값 가져오기.

결론: 데이터 분석의 미래, 윈도우 함수

PostgreSQL 윈도우 함수는 SQL의 한계를 넘어 데이터의 컨텍스트를 유지한 분석을 가능하게 합니다. 단순 요약이 아닌, 숨겨진 패턴과 관계를 드러내 비즈니스 의사결정을 돕죠. 중급 개발자라면 이 도구를 필수로 익히세요 – 한 번 익히면 생산성이 폭발적으로 증가할 거예요!