MariaDB 강좌 / 윈도 함수로 순번과 순위 계산하기

윈도 함수는 원래 결과 행을 유지하면서 정렬 순서·그룹에 따른 계산값을 덧붙입니다. ROW_NUMBER는 행마다 다른 순번을, RANK와 DENSE_RANK는 같은 기준값에 같은 순위를 주므로 동점 처리 규칙을 먼저 정해야 합니다.

순번과 순위의 차이 정하기

고객별 구매 금액이 같아도 화면의 행 번호는 각각 달라야 할 수 있고, 구매 순위는 공동 순위여야 할 수 있습니다. 동점을 처리하는 요구가 다른데 함수 이름만 바꾸면 안 됩니다. 집계로 고객당 금액을 먼저 만든 뒤 그 고객 행에 순위를 붙이겠습니다. 집계와 순위 단계를 나눌 때는 WITH로 공통 테이블 식 만들고 쿼리 나누기를 사용하고, 같은 행 순서에서 누적·이전값을 계산하는 경우는 윈도 함수로 누적 합계와 이전·다음 행 비교하기와 비교합니다.

동점 고객이 있는 데이터 준비하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c32_customers, c32_products, c32_orders, c32_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE c32_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c32_order_customer FOREIGN KEY (customer_id)
  REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c32_item_order FOREIGN KEY (order_id)
  REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
  REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c32_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

paid 상품 금액 합계는 고객 1과 2가 각각 19500.00으로 같습니다. 고객 3은 취소 주문만, 고객 4는 주문이 없어 둘 다 paid 금액을 0으로 정의합니다. 주문 없는 고객도 포함할지, 취소 금액을 뺄지는 이 순위의 업무 정책입니다.

고객별 금액을 만든 뒤 세 함수를 비교하기

WITH customer_totals AS (
 SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid'
  THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
 FROM c32_customers c LEFT JOIN c32_orders o
  ON o.customer_id=c.customer_id
 LEFT JOIN c32_items i ON i.order_id=o.order_id
 GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
 ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
 RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
 DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;

ROW_NUMBER에는 customer_id를 보조 기준으로 넣어 같은 금액에서 누가 먼저인지 정했습니다. RANK와 DENSE_RANK에는 금액만 넣어 공동 순위를 유지합니다. 순위 함수에도 고객 번호를 넣으면 금액이 같아도 다른 정렬 조합이 되어 동점이 사라집니다. ROW_NUMBER 문법과 윈도 함수 개요를 참고하세요.

OVER의 정렬과 최종 결과 정렬 구분하기

OVER(ORDER BY ...)는 함수가 순번·순위를 계산할 기준이며, 최종 SELECT의 ORDER BY는 화면에 출력할 행 순서입니다. OVER에 정렬이 있다는 이유로 최종 출력 순서까지 보장된다고 가정하지 않습니다. 필요하면 두 기준을 모두 명시합니다.

PARTITION BY는 윈도 계산을 다시 시작할 그룹을 지정합니다. 지역별 순위라면 city를 집계 결과에 포함하고 PARTITION BY city를 검토할 수 있습니다. GROUP BY처럼 고객 행을 줄이는 것이 아니라 각 그룹 안에서 계산 범위를 정한다는 점이 다릅니다.

다음 전체 조회는 같은 고객별 금액을 지역별로 나누어 순번을 다시 시작합니다. 기존 c32 실습 테이블을 그대로 사용하며 최종 결과는 고객 번호로 정렬합니다.

WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
       COALESCE(SUM(CASE WHEN o.status='paid'
         THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
       ROW_NUMBER() OVER (PARTITION BY city
         ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;

고객 1·3은 같은 서울 그룹이므로 순번이 1·2입니다. 고객 2·4는 서로 다른 지역의 첫 행이어서 각각 1입니다. PARTITION BY를 제거하면 전체 고객 네 명에 대한 순번이 됩니다. 비교 후 city 기준을 되돌리세요.

상위 순위 필터는 바깥 조회에서 적용하기

같은 SELECT의 WHERE에서 방금 만든 row_no를 바로 사용할 수 없습니다. CTE나 파생 테이블로 순위 결과를 한 번 감싼 뒤 바깥 WHERE에서 row_no <= 2처럼 제한합니다. 상위 두 행과 공동 1위 전체는 다른 요구이므로 ROW_NUMBER와 RANK 중 어떤 값을 필터할지도 정해야 합니다.

상품 상세 행에 바로 순위를 붙이면 고객당 한 번의 순위가 아니라 상세 행의 순위가 됩니다. 고객별 금액 합계를 먼저 만드는 단계와 순위 부여 단계를 구분하면 행 단위가 바뀌는 실수를 줄일 수 있습니다.

WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
       COALESCE(SUM(CASE WHEN o.status='paid'
         THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
 SELECT customer_id,customer_name,goods_total,
        ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
        RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
 FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;

결과는 고객 1·2이며 row_no는 1·2, amount_rank는 둘 다 1입니다. 같은 쿼리에서 조건만 row_no <= 1로 바꾸면 한 행, amount_rank = 1로 바꾸면 공동 1위 두 행이 남습니다. 두 결과의 요구 사항이 다르므로 “상위 한 행”과 “1위 전체”를 구분하고 비교 후 row_no <= 2로 되돌립니다.

동점·보조 기준·대상 집합 확인하기

금액이 같은 고객을 일부러 넣어 공동 순위와 다음 순위를 비교합니다. 고객 번호 방향을 바꿨을 때 ROW_NUMBER만 동점 안에서 바뀌는지, 취소·미구매 고객을 포함한 정책이 맞는지도 확인하세요. 순위 숫자는 저장된 고유 고객 번호를 대체하는 식별 키가 아닙니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;