MariaDB講座 / ROW_NUMBER・RANK・DENSE_RANKの違いを比べる

MariaDB講座

ウィンドウ関数は入力の行を残しながら、その集合に計算を加えます。ROW_NUMBERは各行に異なる番号、RANKとDENSE_RANKは同じ値へ同順位を付けられるため、同点をどう扱うかで選びます。

連番と同順位を分ける

関数	同点の扱い	並びの例
ROW_NUMBER	全行へ異なる番号	1,2,3,4
RANK	同順位の後に欠番	1,1,3,3
DENSE_RANK	同順位の後も連続	1,1,2,2

まず一顧客一行の合計を作ってから順位を計算します。明細行のまま関数名だけを変えても顧客順位にはなりません。CTEで集計と順位を分け、累計でも同じ入力単位を意識します。

同額顧客を意図して用意する

course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下はこの記事専用の四表を用意する完全な例です。UTF-8ファイルでの実行もできます。同名表がある場合は定義と行を保存して用途を調べ、確認なしに置き換えません。

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);

文字列は原文の韓国語を保っています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、IDと数値で結果を照合します。paidは101・102・103、104はcanceledです。

顧客1・2はpaid金額19500ずつ、3はキャンセルのみ、4は注文なしです。このレポートは後の二人も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;

顧客1～4のrow_noは1・2・3・4、amount_rankは1・1・3・3、dense_amount_rankは1・1・2・2です。ROW_NUMBERにはcustomer_idを加えて同額内も確定します。一方RANKへ一意なIDを加えると組が異なり、意図した同順位を失います。

計算順・表示順・区切りを分ける

OVER内ORDER BYは計算順、最後のORDER BYは表示順です。両方必要なら両方書きます。PARTITION BYは計算を再開する区切りで、GROUP BYのように行をまとめる操作ではありません。

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は同じソウルなのでregional_row_noが1・2、別地域の2・4は各1です。PARTITION BY cityだけを外して全体連番と比較し、元へ戻します。地域列は集計の入力へ含める必要があります。

順位の絞り込みは外側で行う

同じSELECTのWHEREで作成直後のrow_noは一般に使えません。CTEへ順位を置いて外側で選びます。

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;

row_no <= 2は顧客1・2です。条件だけをrow_no <= 1にすると一行、amount_rank = 1にすると同点二人になります。「二行」「一位全員」は違う要求です。比較後は元の条件へ戻します。生成順位は報告の値であり、安定した顧客IDの代わりではありません。

同点と集計対象を照合する

IDの補助方向だけを逆転すると、ROW_NUMBERの同額内の順番が変わり、金額だけの順位は保たれます。非購入者とキャンセルの方針を確認し、ウィンドウ順だけで表示順を推測しません。

再実習では実際のDB・表定義・保存行を調べ、必要なデータをバックアップしてから、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。

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