MariaDB 教程 / 用 ROW_NUMBER、RANK 和 DENSE_RANK 计算序号与排名

窗口函数在保留输入结果行的同时,按一组有序记录添加计算。ROW_NUMBER 为每行生成不同序号,RANK 与 DENSE_RANK 则可以让相同排序值共享名次;先决定并列规则,再选择函数。

区分唯一行号与共享名次

客户购买金额相同时,展示列表可能仍需要不同的行号,但金额排名应允许并列。应先为每位客户汇总一笔金额,再对这些客户行计算排名。只更换函数名,不能修正输入本来还是订单明细、或者并列定义不正确的问题。

函数 相同排序值 本例序列
ROW_NUMBER 每行仍得到不同序号 1、2、3、4
RANK 共享名次,后面的名次会跳号 1、1、3、3
DENSE_RANK 共享名次,后面不跳号 1、1、2、2

使用 WITH 与 CTE可以把客户汇总与排名分成不同阶段。相关函数规则见官方 ROW_NUMBER、RANK、DENSE_RANK与 窗口函数概览。

准备购买金额刻意相同的客户样本

以 course_user 连接学习服务器,先核对 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 c32_customers、c32_products、c32_orders、c32_items;如果有同名对象,先检查结构和记录,不要覆盖用途未明的数据。

以下是完整准备代码,应在 MariaDB 客户端中执行。也可按 SQL 文件与 SOURCE 教程保存 UTF-8 文件并使用实际路径加载。确认服务器支持这里的窗口函数写法,不要把 SQL 输入操作系统命令行。

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 为大田。比较结果时使用编号,避免翻译字面量改变样本。

客户 1、2 的已支付商品金额都为 19500.00。客户 3 只有取消订单,客户 4 完全没有订单;本报表明确把这两位的已支付金额设为零。是否包括未购买者、是否排除取消订单,都是排名总体的定义,不是函数自动替您作出的决定。

先汇总客户金额,再比较三个函数

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_no amount_rank dense_amount_rank
1 19500.00 1 1 1
2 19500.00 2 1 1
3 0.00 3 3 2
4 0.00 4 3 2

ROW_NUMBER 的窗口排序除了金额,还加入唯一的 customer_id,让同金额客户之间的顺序有明确规则。两个排名函数则只按金额排序,因而保留金额相同的并列关系。给 RANK 和 DENSE_RANK 也加上唯一客户编号,会使完整排序组合不同,并列就消失了。

RANK 中前两位占据两个位置,下一组从 3 开始;DENSE_RANK 只把不同金额分成第 1、2 组,所以不跳号。判断哪个正确,取决于报表要表达竞赛名次、不同金额档位,还是逐行序号。

只把 ROW_NUMBER 内的客户编号方向从 ASC 改成 DESC,同金额内的行号会反转,但按金额计算的两个名次不应改变。核对后恢复 ASC,确认结果回到表中的顺序。排名是计算结果,不应代替稳定的客户主键。

区分窗口顺序、最终显示顺序与分区

OVER 内的 ORDER BY 决定函数怎样计算,最终 SELECT 的 ORDER BY 决定怎样显示结果。如果两者都重要,应分别写明。只改变最终显示为客户编号降序,可以看到输出顺序改变,但原来计算出的 row_no 仍与对应客户保持一致。

PARTITION BY 决定计算在哪些组内重新开始。它不像 GROUP BY 那样把客户行折叠成一行,而是在保留每位客户的同时,分别为各城市计算序号。以下完整查询把 city 加入已聚合输入,仍使用本节四表:

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;
customer_id goods_total regional_row_no
1 19500.00 1
2 19500.00 1
3 0.00 2
4 0.00 1

客户 1、3 同在首尔分区,序号分别为 1、2。客户 2、4 各自在另一个城市分区中从 1 开始。只移除 PARTITION BY city,就变成全局序号 1、2、3、4;比较后恢复城市分区,确认各城市重新计数。

在外层查询筛选已经计算的排名

同一个 SELECT 的 WHERE 不能直接筛选刚生成的 row_no。把排名结果放进 CTE 或派生表,再由外层 WHERE 检查计算列。这样也能把“客户金额汇总”“排名”“取目标行”三个阶段分别核对。

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,行号分别 1、2,金额名次都为 1。只把外层条件改成 row_no <= 1,得到一行;改为 amount_rank = 1,则保留两位并列第一。比较后恢复 row_no <= 2。

“取两行”“取前两个不同金额档位”和“所有并列第一”是不同需求。应选择对应的计算字段与边界,不要用一个看似叫作排名的列替代所有问题。若先筛掉某些客户再排名,还可能改变参与排名的总体。

同样,直接对原始六条商品明细计算 ROW_NUMBER,会得到明细行序号,而不是四位客户的序号。窗口函数保留的是它实际收到的输入结果行,不能自动判断业务上想排名的对象。

用并列值验证规则与统计人群

测试时应刻意保留相同金额,并检查并列之后的名次。核对客户编号的次级排序只影响唯一行号,金额排名仍按预期共享;再分别检查分区、最终显示顺序与外层过滤,避免同时改动多个条件后无法定位差异。

还应确认取消金额没有进入排名,未购买者是否应被保留为零,以及每位客户的金额是否已经正确汇总。基础聚合有误时,窗口函数只会给错误金额排出一个看起来整齐的顺序。CASE 条件聚合教程解释了本例的状态选择方式。

这些查询不修改原始四表。清理前检查定义与记录,备份应保留的数据,再只删除已确认可丢弃的本节表。下面按子表到父表的顺序执行;DROP TABLE 会删除定义和全部行,普通 ROLLBACK 无法恢复,应与事务实验分开。

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