본문 바로가기
카테고리 없음

SQL 윈도우 함수: 순위, 집계, 행순서, 비율 함수 활용

by moment-love 2025. 2. 2.

 

SQL 윈도우 함수: 순위, 집계, 행순서, 비율 함수 활용

 

SQL의 윈도우 함수(Window Functions)는 행(row) 단위로 데이터를 처리하면서도 전체 데이터 집합에서 누적 값, 순위, 비율 계산 등을 수행할 수 있도록 도와줍니다.

 

일반적인 GROUP BY와 달리 개별 행을 유지한 상태에서 집계 연산을 수행할 수 있기 때문에, 데이터 분석과 보고서 작성에서 매우 유용합니다.

 

이번 글에서는 순위 함수, 집계 함수, 행 순서 함수, 비율 함수를 샘플 테이블과 함께 살펴보겠습니다.

1. 순위 함수: 데이터 순위 계산

 

순위 함수는 특정 열을 기준으로 정렬된 데이터에 순위를 부여하는 함수입니다.

주요 함수로는 RANK, DENSE_RANK, ROW_NUMBER가 있습니다.

 

SELECT EmployeeName, Department, Salary,  
       RANK() OVER(ORDER BY Salary DESC) AS Rank,  
       DENSE_RANK() OVER(ORDER BY Salary DESC) AS DenseRank,  
       ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNumber  
FROM Employees;

 

이 쿼리는 직원들의 급여(Salary)를 기준으로 내림차순 정렬한 후 각 직원에게 순위를 부여합니다.

 

EmployeeName Department Salary RANK DENSE_RANK ROW_NUMBER
Alice Sales 7000 1 1 1
Bob IT 7000 1 1 2
Jane Marketing 6000 3 2 3
John Sales 5000 4 3 4
Tom HR 4500 5 4 5

 

✅ RANK() → 중복 순위 발생 시 건너뛴 순위 부여 (1, 1, 3, 4, 5)


✅ DENSE_RANK() → 중복 순위 발생해도 연속 순위 부여 (1, 1, 2, 3, 4)


✅ ROW_NUMBER() → 중복 순위 없이 일렬 번호 부여 (1, 2, 3, 4, 5)

 

2. 집계 함수: 누적 합계 및 평균 계산

 

집계 함수는 특정 윈도우 범위 내에서 누적 값, 평균, 개수 등을 계산하는 데 사용됩니다.

 

SELECT EmployeeName, Department, Salary,  
       SUM(Salary) OVER(PARTITION BY Department) AS TotalSalary,  
       AVG(Salary) OVER(PARTITION BY Department) AS AvgSalary,  
       COUNT(*) OVER(PARTITION BY Department) AS EmpCount  
FROM Employees;

 

이 쿼리는 부서(Department)별 총 급여, 평균 급여, 직원 수를 계산합니다.

 

EmployeeName EmployeeName Salary TotalSalary AvgSalary EmpCount
Alice Sales 7000 12000 6000 2
John Sales 5000 12000 6000 2
Jane Marketing 6000 6000 6000 1
Bob IT 7000 7000 7000 1
Tom HR 4500 4500 4500 1

 

✅ SUM() → 부서별 총 급여 계산


✅ AVG() → 부서별 평균 급여 계산


✅ COUNT() → 부서별 직원 수 계산

3. 행 순서 함수: 특정 행과의 비교

 

행 순서 함수는 이전 또는 이후 행의 값을 조회하는 함수로, 특정 행과 비교 분석을 할 때 유용합니다.

 

SELECT EmployeeName, Department, Salary,  
       LAG(Salary, 1, 0) OVER(ORDER BY Salary DESC) AS PrevSalary,  
       LEAD(Salary, 1, 0) OVER(ORDER BY Salary DESC) AS NextSalary,  
       FIRST_VALUE(Salary) OVER(ORDER BY Salary DESC) AS TopSalary,  
       LAST_VALUE(Salary) OVER(ORDER BY Salary DESC ROWS BETWEEN UNBOUNDED 
       PRECEDING AND UNBOUNDED FOLLOWING) AS LastSalary  
FROM Employees;

 

 

EmployeeName Salary PrevSalary NextSalary TopSalary LastSalary
Alice 7000 0 7000 7000 4500
Bob 7000 7000 6000 7000 4500
Jane 6000 7000 5000 7000 4500
John 5000 6000 4500 7000 4500
Tom 4500 5000 0 7000 4500

 

✅ LAG() → 이전 행의 값을 가져옴 (이전 값이 없으면 기본값 0 반환)


✅ LEAD() → 다음 행의 값을 가져옴 (다음 값이 없으면 기본값 0 반환)


✅ FIRST_VALUE() → 전체 데이터에서 첫 번째 값 조회


✅ LAST_VALUE() → 전체 데이터에서 마지막 값 조회

 

4. 비율 함수: 누적 분포 및 백분율 계산

 

비율 함수는 전체 데이터 집합에서 특정 행이 차지하는 비율 및 순위 값을 계산하는 데 사용됩니다.

 

SELECT EmployeeName, Salary,  
       CUME_DIST() OVER(ORDER BY Salary) AS CumulativeDist,  
       PERCENT_RANK() OVER(ORDER BY Salary) AS PercentRank,  
       NTILE(4) OVER(ORDER BY Salary DESC) AS Quartile,  
       RATIO_TO_REPORT(Salary) OVER() AS SalaryRatio  
FROM Employees;

 

 

EmployeeName Salary CumulativeDist PercentRank Quartile SalaryRatio
Tom 4500 0.2 0.0 4 0.17
John 5000 0.4 0.25 3 0.21
Jane 6000 0.6 0.5 2 0.26
Alice 7000 1.0 1.0 1 0.35
Bob 7000 1.0 1.0 1 0.35

 

✅ CUME_DIST() → 해당 행이 전체 데이터에서 차지하는 누적 분포 비율 계산


✅ PERCENT_RANK() → 전체 데이터에서 상대적 순위 백분율 계산


✅ NTILE(n) → 데이터를 n개 구간으로 나누어 그룹화


✅ RATIO_TO_REPORT() → 전체 합계 대비 특정 값의 비율 계산

5. 결론

 

✅ 순위 함수 → 데이터의 순위를 계산하는 데 사용 (RANK, DENSE_RANK, ROW_NUMBER)


✅ 집계 함수 → 특정 그룹의 합계 및 평균을 구하는 데 사용 (SUM, AVG, COUNT)


✅ 행 순서 함수 → 이전/이후 행과 비교 (LAG, LEAD, FIRST_VALUE, LAST_VALUE)


✅ 비율 함수 → 백분율과 누적 비율을 계산 (CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT)

 

SQLD 자격증을 준비하면서 윈도우 함수의 개념과 차이점, 실행 순서를 정확히 이해하고 실습해 보세요!

반응형