SQL에서 윈도우 함수(ROW_NUMBER, RANK, DENSE_RANK) 사용법

안녕하세요! 데이터 분석하면 머리가 지끈거리시는 분들 많으시죠? 저도 그랬어요. 그런데 SQL의 강력한 기능인 윈도우 함수를 알고 나서는 신세계를 경험했답니다! 마치 마법처럼 데이터를 정렬하고 순위를 매기는 윈도우 함수는 정말 매력적이에요. 특히 ROW_NUMBER, RANK, DENSE_RANK는 데이터 분석에 없어서는 안 될 존재들이죠. 오늘은 이 세 가지 함수를 중심으로 윈도우 함수의 기초부터 실제 활용 예시까지 차근차근 알아보는 시간을 가져보려고 해요. 궁금하시죠? 자, 그럼 함께 윈도우 함수의 세계로 풍덩 빠져볼까요?

 

 

윈도우 함수란 무엇인가?

데이터 분석에 조금이라도 관심 있으신 분들이라면, SQL은 필수죠! 그중에서도 윈도우 함수는 정말 강력한 도구인데, 처음 접하면 좀 어렵게 느껴질 수도 있어요~ 마치 미로처럼 느껴진다고 할까요? 하지만 걱정 마세요! 제가 쉽고 재미있게 설명해 드릴게요! ^^

윈도우 함수의 개념

윈도우 함수는 기존의 집계 함수(SUM, AVG, COUNT 등)와 비슷해 보이지만, 결정적인 차이점이 하나 있어요. 바로 결과 집합을 그룹으로 묶어서 하나의 값으로 반환하는 대신, 각 행에 대해 계산을 수행하고 그 결과를 해당 행에 추가한다는 점이에요! 마치 마법처럼 각 행에 특별한 정보를 불어넣는 느낌이랄까요? ✨

윈도우 함수 활용 예시

자, 예를 들어볼까요? 100명의 학생이 있는데, 각 학생의 성적 순위를 매기고 싶다고 해봅시다. 기존의 집계 함수로는 전체 평균이나 최고 점수는 구할 수 있지만, 각 학생의 순위를 매기는 건 쉽지 않아요. 이럴 때 바로 윈도우 함수가 빛을 발하는 거죠! 짠~! 윈도우 함수를 사용하면 각 학생의 성적을 기준으로 전체 학생 중에서 몇 등인지 쉽게 계산해서 각 행에 순위를 붙여줄 수 있답니다. 정말 편리하지 않나요? 😊

OVER 절

윈도우 함수는 OVER() 절을 사용해서 정의하는데, 이 OVER() 절 안에는 세 가지 중요한 요소가 들어갈 수 있어요. 마치 마법의 주문을 외우는 것 같죠? 바로 PARTITION BY, ORDER BY, 그리고 ROWS 또는 RANGE 절입니다. 이 세 가지 요소를 잘 조합하면 원하는 결과를 뽑아낼 수 있어요! 💪

OVER 절 구성 요소

• PARTITION BY: 데이터를 여러 그룹으로 나누고 싶을 때 사용해요. 예를 들어, 각 반별로 학생들의 순위를 매기고 싶다면 PARTITION BY 반 처럼 사용하면 되겠죠? 마치 마법의 지팡이로 슥- 나누는 것처럼 말이에요!
• ORDER BY: 윈도우 함수를 적용할 때 기준이 되는 열을 지정해요. 학생들의 순위를 매길 때는 당연히 ORDER BY 성적 이겠죠? 높은 점수부터 낮은 점수 순으로 착착! 정렬해주는 마법 같아요!
• ROWS 또는 RANGE: 윈도우 함수를 계산할 범위를 지정하는 역할을 해요. 현재 행을 기준으로 몇 개의 행을 포함할지, 아니면 특정 값 범위를 포함할지 정할 수 있죠. 예를 들어, 현재 행을 포함해서 앞뒤 2개 행, 총 5개 행의 평균을 구하고 싶다면 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 처럼 사용할 수 있어요! 마치 마법의 돋보기로 원하는 범위만 쏙! 골라내는 것 같지 않나요? 🔍

다양한 윈도우 함수

윈도우 함수의 종류는 정말 다양해요! 순위를 구하는 ROW_NUMBER(), RANK(), DENSE_RANK() 부터, 누적 합계를 구하는 SUM() OVER(), 이동 평균을 구하는 AVG() OVER(), 심지어는 현재 행의 값과 이전 행의 값을 비교하는 LAG()와 LEAD() 함수까지! 정말 없는 게 없죠? 😄

윈도우 함수의 활용

이렇게 다양한 윈도우 함수를 활용하면 복잡한 데이터 분석 작업도 훨씬 간단하게 처리할 수 있어요! 마치 마법사가 된 기분이랄까요? 🎩✨ 앞으로 다양한 윈도우 함수들을 하나씩 자세히 알아보면서 데이터 분석 마법사가 되어보자고요! 😉

 

ROW_NUMBER 함수의 이해와 활용

자, 이제 본격적으로 ROW_NUMBER 함수에 대해 파헤쳐 볼까요? 이 함수, 생각보다 엄청 유용하고 재밌어요! 마법처럼 순위를 뿅! 하고 매겨주거든요. 마치 마라톤 결승선을 통과하는 선수들에게 1등, 2등, 3등 번호표를 붙여주는 것과 같답니다!

ROW_NUMBER 함수의 역할

ROW_NUMBER 함수는 말 그대로 각 행에 고유한 순번을 부여하는 역할을 해요. 쿼리 결과 집합에서 특정 기준에 따라 순위를 매기고 싶을 때 정말 유용하죠. “윈도우 함수”라는 이름이 붙은 이유는 마치 창문처럼 특정 범위(윈도우)를 지정해서 그 안에서 순위를 계산하기 때문이에요. 전체 데이터를 대상으로 순위를 매기는 것과는 조금 다르다는 점, 꼭 기억해 두세요! 😉

ROW_NUMBER 함수의 활용 예시

예를 들어, 온라인 쇼핑몰에서 각 상품 카테고리별로 판매 순위를 매기고 싶다고 생각해 봐요. ‘옷’, ‘신발’, ‘가방’ 등 다양한 카테고리가 있고, 각 카테고리 안에서 판매량 순으로 1위부터 10위까지 상품을 뽑아내고 싶을 때 ROW_NUMBER 함수가 딱이죠!

ROW_NUMBER 함수의 작동 방식

자, 그럼 실제 쿼리를 통해 ROW_NUMBER 함수의 작동 방식을 좀 더 자세히 살펴볼까요? 아래 예시는 각 부서별로 연봉 순위를 매기는 쿼리입니다.

SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_within_department
FROM
    employees;

여기서 PARTITION BY 절은 윈도우를 정의하는 부분이에요. PARTITION BY department는 각 부서별로 윈도우를 나눈다는 뜻이죠. 즉, ‘영업부’, ‘개발부’, ‘마케팅부’ 등 각 부서 내에서 순위를 따로 계산하게 됩니다. 만약 PARTITION BY 절을 생략하면 전체 직원을 대상으로 순위가 매겨지겠죠?

ORDER BY 절은 순위를 매기는 기준을 정의하는 부분이에요. ORDER BY salary DESC는 연봉을 기준으로 내림차순으로 순위를 매긴다는 뜻입니다. 즉, 연봉이 가장 높은 사람이 1위, 그다음으로 높은 사람이 2위, 이런 식으로 순위가 정해지는 거죠.

ROW_NUMBER() 함수는 이렇게 정의된 윈도우 내에서 각 행에 순번을 부여합니다. 같은 연봉을 받는 직원이 여러 명 있다고 해도 ROW_NUMBER 함수는 중복 없이 1, 2, 3… 순으로 고유한 순번을 부여해요. 이 점이 바로 ROW_NUMBER 함수의 핵심적인 특징 중 하나랍니다!

고연봉자 찾기 예시

만약 특정 부서에서 상위 3명의 고연봉자를 찾고 싶다면 어떻게 해야 할까요? ROW_NUMBER 함수를 활용하면 아주 간단하게 해결할 수 있어요! 아래 쿼리를 보시죠.

SELECT *
FROM (
    SELECT
        department,
        employee_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_within_department
    FROM
        employees
) ranked_employees
WHERE rank_within_department <= 3;

서브쿼리를 사용해서 ROW_NUMBER 함수로 순위를 매긴 결과를 ranked_employees라는 임시 테이블로 만들고, WHERE 절에서 rank_within_department가 3 이하인 행만 선택하면 각 부서별 상위 3명의 직원 정보를 얻을 수 있답니다. 정말 간단하죠? 😊

ROW_NUMBER 함수의 다양한 활용

ROW_NUMBER 함수는 이처럼 순위를 매기는 것 외에도 다양한 상황에서 활용될 수 있어요. 예를 들어, 특정 기준을 만족하는 가장 최근 데이터를 가져오거나, 중복된 데이터를 제거할 때도 유용하게 사용할 수 있죠. 데이터 분석이나 레포트 생성 시 정말 빛을 발하는 함수랍니다! ✨

ROW_NUMBER 함수, 이제 어느 정도 이해가 되셨나요? 다음에는 RANK 함수와 DENSE_RANK 함수의 차이점에 대해 알아볼 거예요. 기대해 주세요! 😉

 

RANK와 DENSE_RANK 함수의 차이점

자, 이제 드디어 RANK 함수와 DENSE_RANK 함수의 차이점에 대해 알아볼 시간이에요! 두 함수 모두 순위를 매기는 함수이지만, 미묘하지만 중요한 차이점이 숨어있답니다. 마치 쌍둥이처럼 비슷해 보이지만 자세히 보면 다른 매력을 가진 친구들이랄까요? 😄 자, 그럼 본격적으로 RANK와 DENSE_RANK 함수를 파헤쳐 보도록 할게요!

RANK 함수

RANK 함수는 같은 값을 가진 행에 대해 동일한 순위를 부여하고, 다음 순위는 동일한 값을 가진 행의 개수만큼 건너뛰어요. 마치 1등이 두 명이면 2등 없이 바로 3등으로 넘어가는 것과 같죠! 😲 예를 들어, 점수가 100점인 학생이 두 명이면 둘 다 1등이고, 그다음 95점인 학생은 3등이 되는 거예요.

DENSE_RANK 함수

반면에 DENSE_RANK 함수는 같은 값을 가진 행에 대해 동일한 순위를 부여하지만, 다음 순위는 바로 다음 숫자로 이어진답니다. 즉, 1등이 두 명이라도 그다음은 2등이에요! 점수가 100점인 학생이 두 명이면 둘 다 1등이고, 그다음 95점인 학생은 2등이 되는 거죠. 아, RANK 함수와는 조금 다르죠? 🤔

이러한 차이점 때문에 RANK 함수는 순위 사이에 빈틈이 생길 수 있지만, DENSE_RANK 함수는 항상 연속적인 순위를 부여한답니다. ‘등수’라는 개념에 익숙한 우리에게는 DENSE_RANK 함수가 더 직관적으로 느껴질 수도 있겠네요! 😊

예시

자, 그럼 예시를 통해 좀 더 자세히 알아볼까요? 학생들의 시험 점수를 기준으로 RANK 함수와 DENSE_RANK 함수를 적용해 보겠습니다.

학생	점수	RANK	DENSE_RANK
A	100	1	1
B	100	1	1
C	95	3	2
D	90	4	3
E	85	5	4
F	85	5	4
G	80	7	5

표에서 볼 수 있듯이, A와 B 학생은 둘 다 100점으로 동점이기 때문에 RANK 함수와 DENSE_RANK 함수 모두 1등으로 처리되었어요. 하지만 그 다음 순위부터 차이가 발생하는데, RANK 함수의 경우 C 학생은 3등으로, D 학생은 4등으로 순위가 매겨졌죠. 1등이 두 명이었기 때문에 2등은 건너뛰고 바로 3등으로 넘어간 거예요! 마치 계단을 두 칸씩 뛰어넘는 것 같지 않나요? 😜

반면 DENSE_RANK 함수에서는 C 학생이 2등, D 학생이 3등으로 순위가 매겨졌어요. 동점이 있더라도 다음 순위는 바로 다음 숫자로 이어지는 것을 확인할 수 있죠? 마치 계단을 한 칸씩 차근차근 올라가는 것과 같네요. 😉

E와 F 학생의 경우에도 RANK 함수는 5등으로 동일하게 처리하고 다음 순위인 G 학생은 7등으로 건너뛰었지만, DENSE_RANK 함수는 E와 F 학생에게 4등을 부여하고 G 학생에게는 바로 다음 순위인 5등을 부여했어요. 이처럼 두 함수는 동점 처리 방식에서 차이가 발생하고, 이는 결과적으로 순위의 흐름과 해석에 영향을 미친답니다.

자, 이제 RANK 함수와 DENSE_RANK 함수의 차이점이 명확하게 이해되셨나요? 😊 두 함수 모두 데이터 분석에 유용하게 활용될 수 있지만, 어떤 함수를 사용할지는 분석 목적과 데이터의 특성에 따라 결정해야 해요. 만약 촘촘한 순위를 원한다면 DENSE_RANK 함수를, 순위 사이의 간격을 통해 동점자의 수를 암시적으로 표현하고 싶다면 RANK 함수를 사용하는 것이 좋겠죠?

실제 데이터 분석 상황에서는 데이터의 양이 훨씬 많고 복잡할 수 있으니, 두 함수의 특징을 잘 이해하고 적절하게 활용하는 것이 중요해요! 다음에는 윈도우 함수를 사용한 실제 데이터 분석 예시를 살펴보면서 더욱 흥미로운 분석의 세계로 빠져보도록 할게요! 기대해 주세요~! ✨

 

윈도우 함수를 사용한 실제 데이터 분석 예시

자, 이제 드디어! 윈도우 함수를 사용해서 실제 데이터 분석을 어떻게 하는지 알아볼 시간이에요~! 지금까지 ROW_NUMBER, RANK, DENSE_RANK 함수에 대해 배웠으니, 이 친구들을 실전에서 어떻게 활용하는지 궁금하시죠? ^^ 그 궁금증, 제가 확실하게 풀어드릴게요!

가장 먼저, 온라인 쇼핑몰 데이터를 예시로 들어볼게요. 쇼핑몰 데이터는 정말 다양한 분석에 활용될 수 있는데, 윈도우 함수를 사용하면 고객 분석, 상품 분석, 매출 분석 등 다양한 관점에서 데이터를 깊이 있게 파헤칠 수 있답니다!

1. 고객별 구매 순위 분석

고객별로 누적 구매 금액을 계산하고, 누적 구매 금액 순위를 매겨보는 건 어떨까요? 이를 통해 VIP 고객을 선별하고, 타겟 마케팅을 진행할 수 있겠죠? ROW_NUMBER 함수를 사용하면 쉽게 구현할 수 있어요. 고객 ID를 기준으로 파티션을 나누고, 누적 구매 금액을 기준으로 순위를 매기면 끝! 1등부터 100등까지 VIP 고객에게 특별 할인 쿠폰을 제공하는 이벤트를 기획할 수도 있겠네요~?!

SELECT
    customer_id,
    cumulative_purchase_amount,
    ROW_NUMBER() OVER (ORDER BY cumulative_purchase_amount DESC) as purchase_rank
FROM
    customer_purchase_summary;

2. 월별 매출 순위 분석

이번에는 월별 매출 순위를 분석해 볼까요? 전년 동월 대비 매출 성장률을 계산하고, DENSE_RANK 함수를 사용하여 순위를 매겨보면 어떨까요? 성장률이 동일한 월이 여러 개 있더라도, 동일한 순위를 부여해서 좀 더 보기 쉽게 만들 수 있어요. 예를 들어 2023년 1월 매출이 전년 동월 대비 20% 성장했는데, 2022년 5월 매출도 전년 동월 대비 20% 성장했다면? 둘 다 1등! ^^ 이렇게 하면 월별 매출 변동 추이를 한눈에 파악하고, 경영 전략을 수립하는 데 도움이 될 거예요!

WITH MonthlySales AS (
    SELECT
        strftime('%Y-%m', order_date) AS sales_month,
        SUM(order_amount) AS total_sales
    FROM
        orders
    GROUP BY
        sales_month
),
PreviousYearSales AS (
    SELECT
        strftime('%Y-%m', date(order_date, '+1 year')) AS sales_month,
        SUM(order_amount) AS total_sales
    FROM
        orders
    WHERE strftime('%Y', order_date) = '2022'
    GROUP BY
        sales_month
)
SELECT
    ms.sales_month,
    ms.total_sales,
    pys.total_sales AS previous_year_sales,
    (ms.total_sales - pys.total_sales) * 100.0 / pys.total_sales AS growth_rate,
    DENSE_RANK() OVER (ORDER BY (ms.total_sales - pys.total_sales) * 100.0 / pys.total_sales DESC) AS sales_rank
FROM
    MonthlySales ms
LEFT JOIN
    PreviousYearSales pys ON ms.sales_month = pys.sales_month
WHERE strftime('%Y', ms.sales_month) = '2023';

3. 상품별 판매 순위 분석 (카테고리별)

각 상품 카테고리별로 상품 판매 순위를 분석해보는 것도 유용해요. RANK 함수를 사용하면 판매량이 동일한 상품에 대해 동일한 순위를 부여할 수 있죠. 예를 들어, ‘의류’ 카테고리에서 티셔츠와 바지가 각각 100개씩 팔렸다면 둘 다 1등! 이렇게 하면 어떤 상품이 각 카테고리에서 인기가 많은지 쉽게 파악할 수 있답니다. 인기 상품을 파악해서 마케팅 전략에 활용하거나, 재고 관리를 효율적으로 할 수 있겠죠?!

SELECT
    product_name,
    category,
    sales_quantity,
    RANK() OVER (PARTITION BY category ORDER BY sales_quantity DESC) AS sales_rank
FROM
    product_sales;

4. 고객 세그먼트 분류

고객의 구매 횟수를 기준으로 RFM 분석 등을 활용하여 고객을 여러 세그먼트로 분류하고 싶다고 가정해 보죠. 예를 들어, 상위 10% 고객은 VIP, 다음 20%는 우수 고객, 나머지는 일반 고객으로 분류하는 거예요. NTILE 함수를 사용하면 전체 고객을 10개의 그룹으로 나누고, 각 고객이 어떤 그룹에 속하는지 쉽게 확인할 수 있어요. 각 그룹에 맞는 맞춤형 마케팅 전략을 수립하면 마케팅 효율을 극대화할 수 있을 거예요!

SELECT
    customer_id,
    purchase_count,
    NTILE(10) OVER (ORDER BY purchase_count DESC) AS customer_segment
FROM
    customer_purchase_history;

이 외에도 윈도우 함수는 정말 다양한 분석에 활용될 수 있어요! 이동 평균, 누적 합계, 전년 동기 대비 성장률 계산 등등… 데이터 분석의 세계는 무궁무진하니까요! 윈도우 함수를 잘 활용하면 데이터 분석 능력을 한 단계 업그레이드할 수 있을 거예요! 다양한 윈도우 함수를 활용해서 여러분의 데이터 분석 실력을 마음껏 뽐내보세요! 화이팅!!

 

자, 이렇게 ROW_NUMBER, RANK, DENSE_RANK 함수까지 윈도우 함수 삼총사를 모두 살펴봤어요! 어때요, 이제 좀 친해진 것 같나요? 처음엔 낯설었던 개념들이 이제는 여러분의 데이터 분석 능력에 날개를 달아줄 강력한 도구로 느껴지길 바라요.

복잡한 순위 계산이나 데이터 그룹화 작업도 이제 윈도우 함수를 활용하면 훨씬 쉽고 효율적으로 처리할 수 있답니다. 데이터 분석, 이제 윈도우 함수와 함께라면 두렵지 않아요! 앞으로 여러분의 데이터 분석 여정에 윈도우 함수가 든든한 동반자가 되어줄 거예요.

더 많은 데이터 분석 팁을 원한다면 언제든 다시 찾아와 주세요! 함께 성장하는 기쁨을 나누고 싶어요.