윈도우함수(Window Function)로 중복 닉네임 처리하기

서론 (들어가기 앞서)

기획자님 : 상용 DB에 중복 닉네임이 존재합니다. 중복된 닉네임을 뒤에 가입한 순서대로 숫자 001, 002, 003 등으로 붙여주세요.

얼마 전, 회사에서 중복 닉네임을 일괄 변경하는 작업에 대한 요청이 있었습니다. 이미 기존에 중복된 닉네임에 가입 순서를 기준으로 001, 002, 003 뒤에 숫자를 붙여서 수정하는 것이 요구사항이었습니다. 물론 실제로는 이것보다 친절하고 자세하게 설명해주셨죠. 순간 고민이 됐습니다. 쿼리로 한 번에 처리할 순 없을까? 🤔 찾아 보니, 윈도우 함수 내 순위함수 기능을 사용하면 쉽게 값을 정해줄 수 있었습니다 🥹 그래서 이번 시간에는 윈도우함수의 순위함수를 사용하여, 위 요구사항에 만족하는 쿼리를 작성하는 방법에 대해 함께 알아보도록 하겠습니다.

본론

바로 정의부터 들어가보시죠.

윈도우 함수 (Window Function)

윈도우 함수는 행과 행 간을 비교, 연산, 정의하기 위한 함수입니다. 행가 행을 비교한다는 말이 무슨 의미일까요? 보통 일반적인 SQL 쿼리는 한 행을 계산한 후 그 결과를 출력하는데, 각 행의 결과는 독립적으로 계산합니다. 이 때 독립적으로 계산된다라는 의미는 한 행의 계산 결과가 다른 행에 영향을 주지 않고, 한 행이 다른 행의 값을 참조하지도 않는다는 의미입니다.

일반적인 SQL 쿼리

사실 한 행의 결과가 다른 행에 결과에 영향을 주지 않는 건 당연한 것 아닌가요? 살짝 머리가 아파옵니다. 위 설명을 이해하기 위해, 저희가 익숙한 SQL 쿼리와 비교해봅시다. 아래와 같은 쿼리가 있습니다. 전체 직원의 이름과 월급을 조회하는 쿼리입니다.

SELECT employee_name, salary FROM employees;

이 쿼리는 직원의 이름과 급여를 각각의 행에서 그대로 출력합니다. 각 행은 다른 행의 영향을 받지 않고 그 자체로 독립적으로 처리됩니다.

employee_name | salary
--------------|-------
Alice         | 5000
Bob           | 3000
Charlie       | 7000

여기서 각 직원의 급여는 그저 해당 행에만 존재하고, 다른 직원의 급여와는 무관하게 독립적으로 표시됩니다.

윈도우 함수

그렇다면 윈도우 함수 중 하나인 집계 함수와 비교해보면 어떨까요? 총합을 계산하기 위해 자주 사용하는 집계 함수인 COUNT()를 예시로 살펴봅시다. COUNT 함수에서도, 각 행을 독립된 행으로 봅니다. 그러나 각 행의 개별 값을 모아서 하나의 값으로 만드는 연산을 수행하죠. 예를 들어, 부서별 급여의 총합을 계산할 때, 모든 직원의 급여를 하나의 값으로 합산하여 결과를 내기 때문에 행 간 비교나 참조 없이 독립적으로 계산되지 않는다고 말할 수 있습니다.

COUNT(*)
------------
3

마찬가지로 ROW_NUMBER() 같은 SQL 윈도우 함수를 추가로 예로 들어보겠습니다. 윈도우 함수는 각 행을 처리할 때, 다른 행과 비교하거나 참조합니다. 각 행을 기준으로 누적 합계를 계산할 때, 그 행이 나오기 전까지의 모든 행을 참조해서 값을 만듭니다.

SELECT employee_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;

이 쿼리는 급여를 기준으로 순위를 매기는 작업입니다. 각 행은 다른 행과 비교되어 순위가 매겨지며, 독립적으로 처리되지 않습니다. 예를 들어, "Charlie"의 급여가 다른 행들의 급여와 비교되기 때문에 그 순위가 결정됩니다.

employee_name | salary | rank
--------------|--------|-----
Charlie       | 7000   | 1
Alice         | 5000   | 2
Bob           | 3000   | 3

여기서 순위(rank)는 다른 행과의 비교를 통해 결정되므로, 각 행은 더 이상 독립적으로 계산되지 않는 것이죠.

문법 설명

자, 윈도우 함수가 일반적인 쿼리와 어떤 차이점이 있는지 알아보았으니, 기본적인 문법에 대해서 살펴봅시다.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절]) FROM 테이블명;

윈도우 함수의 기본 문법은 위와 같습니다.

• 윈도우 함수: SUM, AVG, ROW_NUMBER, RANK 등 다양한 집계 및 순위 관련 함수를 사용할 수 있습니다.
• PARTITION BY: 데이터를 특정 그룹(파티션)으로 나누는 기준을 지정합니다. 이 기준에 따라 데이터가 그룹별로 나뉩니다.
• ORDER BY: 각 파티션 내에서 데이터를 정렬하는 기준을 지정합니다.

주요 윈도우 함수들

순위함수 : RANK, DENSE_RANK, ROW_NUMBER

SELECT id, nickname, 
       ROW_NUMBER() OVER (PARTITION BY nickname ORDER BY id) AS row_num
FROM users;

• ROW_NUMBER : 대표적인 순위 함수입니다. 동일한 값이라도 고유한 순위를 부여합니다. 이 때 기준은 OVER 절의 결과에 따릅니다.
• PARTITION BY nickname: nickname과 같은 특정 필드를 기준으로 데이터를 그룹화할 때 사용합니다.
• ORDER BY id: 각 그룹 내에서 id 순서대로 정렬하여 순번을 매깁니다.

참고로 위 세 함수는 기본적으로 순위를 매기는 데, 사용됩니다. 그렇지만 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위를 비웁니다. RANK의 경우에는 예를 들어 동일한 값이 있는 경우 순위는 1,1,3,4,4,6 이런식인 셈입니다. DENSE_RANK는 RANK와 작동법은 동일하지만, 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않는다는 차이가 있습니다. 동일한 값이 있는 경우 순위는 1,1,2,3,3,4 이런 식입니다.

일반 집계 함수 : SUM, MAX, MIN, AVG, COUNT

모든 주문 내역에서 customer_id 별로 구매 금액의 누적 합계를 구하는 쿼리입니다.

SELECT order_id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

• PARTITION BY customer_id: 고객별로 데이터를 그룹화합니다.
• ORDER BY order_date: 각 고객의 주문 날짜 순서로 정렬하여 누적 구매 금액을 계산합니다.

그룹 내 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD

이 함수들은 현재 행을 기준으로 이전 또는 다음 행의 값을 참조하거나, 그룹 내 첫 번째 또는 마지막 값을 반환하는 함수입니다.
이전 주문의 금액을 참조하여, 쿼리를 작성해보겠습니다.

SELECT order_id, customer_id, amount,
       LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_amount
FROM orders;

• LAG(amount, 1, 0): 현재 행의 바로 이전 행에 있는 amount 값을 반환하고, 이전 행이 없으면 0을 반환합니다.
• PARTITION BY customer_id: 고객별로 데이터를 그룹화하여 각 고객의 주문 내역을 기준으로 비교합니다.

그룹 내 비율 함수 : RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE

이 함수들은 현재 행이 그룹 내에서 차지하는 비율이나 순위 등을 계산하는 데 사용됩니다.

SELECT student_id, score,
       PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROM students;

• PERCENT_RANK(): 각 학생의 점수가 전체에서 차지하는 상대적 순위를 비율로 계산합니다.
• ORDER BY score DESC: 높은 점수를 기준으로 순위를 계산합니다.

실제 적용

기획자님 : 상용 DB에 중복 닉네임이 존재합니다. 중복된 닉네임을 뒤에 가입한 순서대로 숫자 002, 003 등으로 붙여주세요.

자, 이제 위 요구사항을 다시 살펴보겠습니다. 중복된 닉네임을 조회하려면 PARTITION BY nickname을 해주면, 닉네임을 기준으로 그룹화 할 수 있을 것입니다. 또한 동일한 순서도 순서에 차등을 두기 위해 순위 함수인 ROW_NUMBER 를 사용할 수 있습니다.

SELECT id, nickname,
    ROW_NUMBER() OVER (PARTITION BY nickname ORDER BY id) as row_num
FROM users 

이렇게하면 중복된 닉네임을 가입 순서대로 순서를 부여할 수 있습니다. 추가로 002, 003 등으로 만들기 위해, LPAD라는 SQL에서 문자열을 왼쪽(L)에서부터 지정한 길이만큼 채우는(PAD) 함수를 사용해보겠습니다. 문법은 아래와 같습니다.

LPAD(string, length, pad_string)

• string: 원래 문자열 (채워야 할 대상).
• length: 최종적으로 만들어질 문자열의 길이.
• pad_string: 원래 문자열의 왼쪽에 추가할 문자.

실제로는 아래와 같이 사용해볼 수 있습니다.

LPAD([닉네임], 3, '0')

마지막으로 조회된 결과를 바로 업데이트 할 수 있도록 WITH 구문을 사용하여 처리해보겠습니다. WITH 구문을 사용하면, 조회된 데이터를 공통 테이블 표현식(CTE)로 정의하고, 그 데이터를 다른 쿼리에서 활용할 수 있습니다. ROW_NUMBER()와 같은 윈도우 함수로 조회된 데이터를 바로 업데이트하려면, WITH 구문에서 ROW_NUMBER()의 결과를 계산한 뒤, 이를 이용하여 UPDATE 쿼리에서 사용할 수 있습니다.

WITH CTE AS (
  SELECT id, nickname,
         ROW_NUMBER() OVER (PARTITION BY nickname ORDER BY id) as row_num
  FROM users 
)

이렇게 값을 CTE라는 테이블에 저장한 후, 저장된 값을 실제 테이블에 업데이트하기 위해, inner join으로 기존 테이블과 묶어주겠습니다.

UPDATE users u
JOIN CTE c ON u.id = c.id
SET u.nickname = CONCAT(c.nickname, LPAD(c.row_num, 3, '0'))
WHERE c.row_num != 1;

가장 먼저 등록된 유저는 숫자를 붙이지 않아야 하기 때문에 c.row_num != 1 조건을 추가해줬습니다. 또한 002, 003을 닉네임 뒤에 추가하기 위해, concat 함수를 같이 사용해주었습니다. 이제, 원하는 요구사항을 만족시키는 결과를 도출해낼 수 있습니다. 실행해야 할 쿼리는 아래와 같습니다.

WITH CTE AS (
  SELECT id, nickname,
         ROW_NUMBER() OVER (PARTITION BY nickname ORDER BY id) as row_num
  FROM users 
)

UPDATE users u
JOIN CTE c ON u.id = c.id
SET u.nickname = CONCAT(c.nickname, LPAD(c.row_num, 3, '0'))
WHERE c.row_num != 1;

이상으로 긴 글 읽어주셔서 감사합니다!