PostgreSQL 윈도우 함수: 복잡한 분석을 간단하게 해결하는 강력한 기능

윈도우 함수란?

데이터베이스에서 복잡한 분석 쿼리를 작성해야 할 때, 여러 개의 서브쿼리나 임시 테이블을 사용해 본 경험이 있을 것입니다. 특히 각 행의 데이터와 함께 집계 결과를 동시에 보여줘야 하는 경우에는 더욱 그렇습니다. PostgreSQL의 **윈도우 함수(Window Functions)**는 이러한 복잡한 문제를 간단하게 해결해 줍니다.

윈도우 함수는 SQL:1999 표준에 도입되었으며, PostgreSQL 8.4 버전부터 지원하기 시작했습니다. 이 함수들은 행 집합("윈도우")에 대한 계산을 수행하면서도 각 행의 개별성을 유지한다는 특징이 있습니다. 일반 집계 함수가 여러 행을 하나의 행으로 축소하는 것과 달리, 윈도우 함수는 원래 행을 그대로 유지하면서 추가 계산을 수행합니다.

-- 일반 집계 함수: 여러 행이 하나로 축소됨
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- 윈도우 함수: 각 행을 유지하면서 부서별 평균 급여도 표시
SELECT name, department, salary, 
       AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

윈도우 함수의 기본 구문

윈도우 함수의 기본 구문은 다음과 같습니다:

함수_이름() OVER (
    PARTITION BY 표현식1, 표현식2, ...
    ORDER BY 표현식1 [ASC|DESC], 표현식2 [ASC|DESC], ...
    프레임_절
)

각 요소의 역할:

• 함수_이름(): 사용할 윈도우 함수 (예: SUM(), AVG(), ROW_NUMBER() 등)
• OVER(): 윈도우 함수임을 나타내는 키워드
• PARTITION BY: 데이터를 그룹화하는 방법 (선택 사항)
• ORDER BY: 윈도우 내에서 행의 순서 지정 (선택 사항)
• 프레임_절: 현재 행에 대한 윈도우 프레임 정의 (선택 사항, 예: ROWS BETWEEN)

순위 관련 윈도우 함수

순위 관련 함수는 윈도우 함수 중에서 가장 많이 사용되는 함수들입니다.

ROW_NUMBER()

각 행에 고유한 순차 번호를 할당합니다.

SELECT name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

RANK()

동일한 값에 동일한 순위를 할당하고, 다음 순위는 중복된 순위 수만큼 건너뜁니다.

SELECT name, department, salary,
       RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

DENSE_RANK()

동일한 값에 동일한 순위를 할당하되, 순위에 빈틈이 없게 합니다.

SELECT name, department, salary,
       RANK() OVER (ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

예를 들어, 급여가 100, 90, 90, 80인 경우:

• RANK(): 1, 2, 2, 4
• DENSE_RANK(): 1, 2, 2, 3

PERCENT_RANK()

백분율 순위를 계산합니다 (0~1 사이의 값).

SELECT name, department, salary,
       PERCENT_RANK() OVER (ORDER BY salary) as percentile
FROM employees;

NTILE(n)

결과 집합을 n개의 동일한 크기의 그룹으로 나눕니다.

-- 직원들을 급여 기준으로 4개의 그룹으로 나누기
SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) as salary_quartile
FROM employees;

집계 윈도우 함수

일반 집계 함수(SUM, AVG, COUNT, MIN, MAX 등)도 윈도우 함수로 사용할 수 있습니다.

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) as dept_total_salary,
       AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
       COUNT(*) OVER (PARTITION BY department) as dept_employee_count,
       salary / SUM(salary) OVER (PARTITION BY department) * 100 as salary_percentage
FROM employees;

누적 집계

ORDER BY를 사용하면 누적 집계를 계산할 수 있습니다.

-- 월별 누적 매출
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) as cumulative_revenue
FROM monthly_sales;

오프셋 윈도우 함수

이전 또는 이후 행의 값에 접근할 수 있는 함수들입니다.

LAG()

현재 행보다 앞에 있는 행의 값을 반환합니다.

-- 전월 대비 매출 증가율 계산
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100 as growth_rate
FROM monthly_sales;

LEAD()

현재 행보다 뒤에 있는 행의 값을 반환합니다.

-- 다음 달 매출과 비교
SELECT month, revenue,
       LEAD(revenue) OVER (ORDER BY month) as next_month_revenue
FROM monthly_sales;

FIRST_VALUE()와 LAST_VALUE()

윈도우 내의 첫 번째 값과 마지막 값을 반환합니다.

-- 각 부서에서 가장 높은 급여와 비교
SELECT name, department, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary_in_dept
FROM employees;

PARTITION BY와 ORDER BY 활용

PARTITION BY와 ORDER BY를 함께 사용하면 더 강력한 분석이 가능합니다.

-- 부서별, 연도별 누적 급여 합계
SELECT name, department, year, salary,
       SUM(salary) OVER (PARTITION BY department, year ORDER BY month) as cumulative_salary_by_dept_year
FROM employee_monthly_salaries;

윈도우 프레임 (Window Frame) 지정

윈도우 프레임을 사용하면 현재 행을 기준으로 계산할 행의 범위를 더 세밀하게 지정할 수 있습니다.

-- 3개월 이동 평균 계산
SELECT month, revenue,
       AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg_3months
FROM monthly_sales;

프레임 지정 옵션:

• ROWS: 물리적 행 수 기준
• RANGE: 값의 범위 기준
• BETWEEN start AND end: 시작과 끝 지점 지정
  • UNBOUNDED PRECEDING: 파티션의 첫 행부터
  • n PRECEDING: 현재 행에서 n행 이전까지
  • CURRENT ROW: 현재 행
  • n FOLLOWING: 현재 행에서 n행 이후까지
  • UNBOUNDED FOLLOWING: 파티션의 마지막 행까지

실제 비즈니스 시나리오 예제

예제 1: 전자상거래 사이트의 고객 분석

-- 고객별 구매 금액 순위 및 백분위 계산
SELECT 
    customer_id, 
    order_date, 
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) as total_spent,
    RANK() OVER (PARTITION BY EXTRACT(YEAR FROM order_date) ORDER BY amount DESC) as yearly_rank,
    NTILE(5) OVER (ORDER BY SUM(amount) OVER (PARTITION BY customer_id) DESC) as customer_segment
FROM orders;

예제 2: 주식 시장 데이터 분석

-- 주식 가격의 이동 평균 및 전일 대비 변화율 계산
SELECT 
    stock_symbol, 
    date, 
    price,
    AVG(price) OVER (PARTITION BY stock_symbol ORDER BY date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as moving_avg_10days,
    (price - LAG(price) OVER (PARTITION BY stock_symbol ORDER BY date)) / LAG(price) OVER (PARTITION BY stock_symbol ORDER BY date) * 100 as daily_change_pct
FROM stock_prices;

예제 3: 매장별 판매 실적 분석

-- 매장별, 월별 판매 실적 및 전년 동월 대비 성장률
SELECT 
    store_id, 
    year, 
    month, 
    sales,
    LAG(sales, 12) OVER (PARTITION BY store_id ORDER BY year, month) as sales_prev_year,
    (sales - LAG(sales, 12) OVER (PARTITION BY store_id ORDER BY year, month)) / LAG(sales, 12) OVER (PARTITION BY store_id ORDER BY year, month) * 100 as yoy_growth_pct
FROM monthly_store_sales;

성능 최적화 팁

윈도우 함수는 강력하지만, 대용량 데이터셋에서는 성능 이슈가 발생할 수 있습니다. 다음은 성능 최적화를 위한 팁입니다:

1. 인덱스 활용: PARTITION BY와 ORDER BY에 사용되는 컬럼에 인덱스를 생성하세요.
2. 파티션 크기 제한: 가능하면 파티션 크기를 제한하여 계산량을 줄이세요.
3. 불필요한 정렬 피하기: 같은 정렬 기준을 사용하는 여러 윈도우 함수가 있다면, 윈도우 별칭을 사용하세요.

-- 윈도우 별칭 사용
SELECT name, department, salary,
       RANK() OVER w as rank,
       DENSE_RANK() OVER w as dense_rank,
       ROW_NUMBER() OVER w as row_num
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

1. 필요한 경우에만 프레임 사용: 프레임 지정은 추가 계산을 필요로 하므로, 꼭 필요한 경우에만 사용하세요.

자주 묻는 질문

Q: 윈도우 함수와 GROUP BY의 차이점은 무엇인가요?

A: GROUP BY는 여러 행을 하나로 축소하지만, 윈도우 함수는 모든 행을 유지합니다. 윈도우 함수를 사용하면 개별 행 데이터와 집계 데이터를 동시에 볼 수 있습니다.

Q: PostgreSQL의 어떤 버전부터 윈도우 함수를 지원하나요?

A: PostgreSQL 8.4 버전부터 기본적인 윈도우 함수를 지원하기 시작했으며, 이후 버전에서 점진적으로 기능이 추가되었습니다.

Q: 윈도우 함수를 WHERE 절에서 사용할 수 있나요?

A: 아니요, 윈도우 함수는 WHERE 절에서 직접 사용할 수 없습니다. 서브쿼리나 CTE(Common Table Expression)를 사용해야 합니다.

Q: 윈도우 함수와 일반 집계 함수를 함께 사용할 수 있나요?

A: 네, 동일한 쿼리에서 GROUP BY를 사용한 일반 집계와 윈도우 함수를 함께 사용할 수 있습니다.

결론

PostgreSQL 윈도우 함수는 복잡한 분석 작업을 간단하게 수행할 수 있는 강력한 도구입니다. 순위 지정, 누적 집계, 이동 평균 등 다양한 분석 요구사항을 효율적으로 처리할 수 있으며, 서브쿼리나 임시 테이블 없이도 깔끔한 코드로 복잡한 로직을 구현할 수 있습니다.

윈도우 함수를 마스터하면 데이터 분석의 효율성과 코드 가독성이 크게 향상됩니다. 이 블로그에서 소개한 기본 개념과 예제를 바탕으로 직접 쿼리를 작성해 보면서 윈도우 함수의 강력함을 경험해 보세요.

참고 자료:

• PostgreSQL 공식 문서 - 윈도우 함수
• PostgreSQL 13 관련 윈도우 함수 업데이트