[SQL] 순위와 관련된 함수(윈도우 함수 알아보기 - 1)

SQL의 순위 관련 함수들

안녕하세요! 오늘은 SQL에서 데이터를 순위별로 나눌 때 자주 사용하는 함수들에 대해 알아보려고 해요.

데이터 분석이나 보고서를 작성할 때 순위 매기기가 얼마나 중요한지 잘 아시죠?

그렇다면, 다양한 순위 관련 함수들을 알아보고 그 사용 예시를 살펴봅시다!

1. ROW_NUMBER()

ROW_NUMBER() 함수는 각 행에 고유한 순번을 매겨줍니다. 예를 들어, 직원들의 목록을 정리할 때 각 직원에게 번호를 매길 수 있겠죠?

예제 코드

SELECT 
  employee_name,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM 
  employees;

예제 테이블

employee_name	hire_date
John	2022-01-10
Alice	2022-01-15
Bob	2022-01-12

출력 결과

employee_name	hire_date	row_num
John	2022-01-10	1
Bob	2022-01-12	2
Alice	2022-01-15	3

위 예시에서는 직원들을 입사 날짜 순으로 정렬하고, row_num이라는 열에 순번을 매깁니다. 첫 번째로 입사한 직원은 1번, 두 번째는 2번... 이런 식으로요!

2. RANK()

RANK() 함수는 동일한 값이 있을 때 동일한 순위를 부여합니다. 다만, 다음 순위는 건너뛰게 돼요.

예를 들어, 점수가 같은 학생들에게 동일한 순위를 매길 때 유용하죠.

예제 코드

SELECT 
  student_name,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM 
  students;

예제 테이블

student_name	score
John	90
Alice	95
Bob	90

출력 결과

student_name	score	rank
Alice	95	1
John	90	2
Bob	90	2

여기서, 점수가 높은 순서대로 학생들에게 순위를 매깁니다. 만약 1등이 2명 있다면, 그 다음 순위는 3등이 됩니다.

3. DENSE_RANK()

DENSE_RANK() 함수도 동일한 값에는 동일한 순위를 부여하지만, 다음 순위는 건너뛰지 않습니다. 이를 통해 연속된 순위를 매길 수 있어요.

예제 코드

SELECT 
  student_name,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM 
  students;

출력 결과

student_name	score	dense_rank
Alice	95	1
John	90	2
Bob	90	2

이 예시에서는 RANK()와 비슷하지만, 1등이 2명일 경우 다음 순위는 3등이 아닌 2등이 됩니다.

4. NTILE(n)

NTILE(n) 함수는 결과 집합을 n개의 그룹으로 나누고, 각 그룹에 순위를 매깁니다. 예를 들어, 상위 25%, 50%, 75%, 100%로 나누고 싶을 때 유용해요.

예제 코드

SELECT 
  product_name,
  NTILE(4) OVER (ORDER BY sales DESC) AS quartile
FROM 
  products;

예제 테이블

product_name	sales
Product A	500
Product B	300
Product C	700
Product D	200

출력 결과

product_name	sales	quartile
Product C	700	1
Product A	500	2
Product B	300	3
Product D	200	4

여기서는 판매량을 기준으로 제품을 4개의 그룹으로 나누고, 각 그룹에 순위를 부여합니다. 이로써 가장 많이 팔린 제품부터 순서대로 1, 2, 3, 4로 나뉘게 됩니다.

5. PERCENT_RANK()

PERCENT_RANK() 함수는 각 행의 상대적인 순위를 0과 1 사이의 백분율로 나타냅니다. 첫 번째 행은 항상 0의 값을 갖고, 마지막 행은 1에 가까운 값을 가집니다.

예제 코드

SELECT 
  employee_name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank
FROM 
  employees;

예제 테이블

employee_name	salary
John	4000
Alice	5000
Bob	3000

출력 결과

employee_name	salary	percent_rank
Bob	3000	0.0
John	4000	0.5
Alice	5000	1.0

이 예시에서는 직원들의 급여를 기준으로 백분율 순위를 계산합니다. 예를 들어, 가장 낮은 급여를 받는 직원은 0에 가까운 값을, 가장 높은 급여를 받는 직원은 1에 가까운 값을 가지게 되죠.

6. CUME_DIST()

CUME_DIST() 함수는 특정 값 이하의 값이 전체에서 차지하는 누적 비율을 계산합니다. 전체 데이터에서 특정 값 이하의 데이터가 얼마나 차지하는지를 나타낼 때 유용합니다.

예제 코드

SELECT 
  product_name,
  sales,
  CUME_DIST() OVER (ORDER BY sales DESC) AS cumulative_distribution
FROM 
  products;

예제 테이블

product_name	sales
Product A	500
Product B	300
Product C	700
Product D	200

출력 결과

product_name	sales	cumulative_distribution
Product C	700	1.0
Product A	500	0.75
Product B	300	0.5
Product D	200	0.25

이 예시에서는 판매량을 기준으로 누적 분포를 계산합니다. 가장 많이 팔린 제품부터 가장 적게 팔린 제품까지의 누적 비율을 알 수 있습니다.

7. RATIO_TO_REPORT

RATIO_TO_REPORT는 Oracle SQL에서 제공되는 분석 함수로, 특정 그룹 내에서 각 행의 값을 해당 그룹 전체 합계 대비 백분율로 계산할 때 사용됩니다.

이 함수는 특히 데이터가 여러 범주나 그룹으로 나뉘어 있을 때, 각 항목이 그룹 내에서 얼마나 큰 비중을 차지하는지 분석할 때 유용합니다.

예제 코드

아래는 sales_data라는 테이블에서 제품별 매출 비중을 계산하는 예제입니다:

SELECT product_name, sales,
    RATIO_TO_REPORT(sales) OVER () AS sales_ratio 
FROM sales_data;

예제 테이블

product_name	sales
Product A	500
Product B	300
Product C	700
Product D	200

출력 결과

위 쿼리를 실행하면 각 제품이 전체 매출에서 차지하는 비중을 백분율로 나타낸 결과를 얻을 수 있습니다.

product_name	sales	sales_ratio
Product A	500	0.2941
Product B	300	0.1765
Product C	700	0.4118
Product D	200	0.1176

 

 

마무리하며...

이렇게 SQL에서 순위와 관련된 다양한 함수들을 살펴보았습니다.

이 함수들을 활용해서 직접 작성해보셔야 본인만의 개념이 적립될 것입니다!

궁금한 점이 있으면 언제든지 댓글로 남겨주세요. 😊