MySQL NTILE 怎样把结果分成固定桶

MySQL 可以用 NTILE(4) OVER (ORDER BY score DESC, id ASC)，把排序后的结果行尽量均匀地分进四个桶。桶号从 1 开始；行数不能整除桶数时，前面的桶多一行。这里固定的是分桶数量参数，不是分数区间，也不是每桶严格相同的人数。

如果我要做一份“按得分把名单划成四层”的报告，我会先问能否拆开同分记录。允许拆开、又希望各层人数接近，才适合 NTILE；若要求同分必须同档，我会选别的规则。下面基于 MySQL 8.4，示例未在本文环境执行，列出的分配是按样例推导的预期结果。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/window-function-descriptions.html

先分清要均分人数，还是均分数值

“四档”可能是两种完全不同的需求：一是把十个人尽量平均分成四组；二是把分数划进固定阈值区间。NTILE 解决前者；后者通常用明确的 CASE 条件。它不会保证每组的总分相同，也不是随机分组。

我会把这两个问题写在报告口径里：参与分桶的是哪些行？排序靠什么字段？例如只选本月已完成考核的名单，和对全部名单分桶后只展示本月记录，桶号含义并不相同。名单范围、窗口排序和桶数参数共同决定桶号。

我会把唯一键写进窗口排序

下面在隔离测试库中创建专用样例。表名已存在时请停下确认，不要删除已有对象。分数故意放入一对并列值，用来暴露“同分可能跨桶”的取舍。

-- 在隔离测试库中创建专用样例；不要覆盖已有同名表
CREATE TABLE ntile_score_demo (
  id INT NOT NULL PRIMARY KEY,
  dept CHAR(1) NOT NULL,
  score INT NOT NULL
);

-- 唯一键用来确定并列分数的次序
INSERT INTO ntile_score_demo (id, dept, score) VALUES
(1,'A',100), (2,'A',96), (3,'A',90), (4,'A',90),
(5,'A',84), (6,'A',80), (7,'B',75), (8,'B',70),
(9,'B',65), (10,'B',60);

-- 窗口内排序决定桶号，查询末尾排序决定展示次序
SELECT id, dept, score,
       NTILE(4) OVER (ORDER BY score DESC, id ASC) AS bucket_no
FROM ntile_score_demo
ORDER BY score DESC, id ASC;

十行分四桶，人数的预期分布为 3、3、2、2：第一桶包含 id 1、2、3；第二桶包含 4、5、6；第三桶包含 7、8；第四桶包含 9、10。因此两个 90 分会跨越第一、第二桶。唯一键只是让拆分结果可确定，不会把同分绑定在一起。

对一般行数 m 和桶数 n，可先算每桶基础人数 q=m DIV n，再把余数 r=m MOD n 对应的额外行分给前 r 个桶。这是理解桶大小的算术，不建议用简单的“行号乘桶数再除行数”替代 NTILE；余数的摆放未必相同。

如果只写 score 降序，并列记录之间没有稳定次序，我不会承诺哪一个 90 分一定进入第一桶。补上在本次结果集中真正唯一的键后，同一份数据才有确定的完整顺序；连接导致 id 重复时，还需补足真正唯一的组合键。

各部门分别分桶，就明确分区

若报告需求改成“每个部门内部三层”，我会保留同样的排序规则，增加分区。全局分桶与部门内部排名不应混用同一个字段名称而不注明口径。

-- 每个部门独立计算三个桶；桶号在分区内重新开始
SELECT id, dept, score,
       NTILE(3) OVER (
         PARTITION BY dept
         ORDER BY score DESC, id ASC
       ) AS dept_bucket
FROM ntile_score_demo
ORDER BY dept, score DESC, id ASC;

A 部门六行的预期人数是 2、2、2，B 部门四行则是 2、1、1。“B 部门第一桶”不是全局前三分之一，也不保证与 A 部门第一桶的分数相近。分区字段定义比较范围；唯一键约束窗口排序；外层排序只定义展示顺序。

即使窗口已经写 ORDER BY，我仍会在最终查询末尾写展示用的 ORDER BY。前者服务于函数计算，不能拿它代替结果集的显示顺序约定。

取某一桶时，把筛选放到外层

我更喜欢先在 CTE 里给名单标好桶号，再由外层筛选。这样读 SQL 的人能分清“哪些行参与分桶”和“只展示哪个桶”。窗口函数不能直接放在同一层 WHERE 中使用。

-- 内层先对完整样例名单分桶，外层只展示第一桶
WITH bucketed AS (
  SELECT id, dept, score,
         NTILE(4) OVER (ORDER BY score DESC, id ASC) AS bucket_no
  FROM ntile_score_demo
)
SELECT id, dept, score, bucket_no
FROM bucketed
WHERE bucket_no = 1
ORDER BY score DESC, id ASC;

此查询预期保留 id 1、2、3。若把 WHERE dept = 'A' 放在 CTE 内，是对 A 部门这六行重新进行四桶分配；放在 CTE 外，则是对全局桶号不变的结果只展示 A 部门。两种查询都可能合法，但业务口径不同。

另一个实用的读者核对方式是统计每桶人数。不要直接在包含原始名单的同一层 GROUP BY 桶号，先把窗口结果作为独立结果集：

-- 保持分桶定义一致，再汇总已经生成的桶号
WITH bucketed AS (
  SELECT NTILE(4) OVER (ORDER BY score DESC, id ASC) AS bucket_no
  FROM ntile_score_demo
)
SELECT bucket_no, COUNT(*) AS members
FROM bucketed
GROUP BY bucket_no
ORDER BY bucket_no;

同分同档或永久分组，不选动态分桶

如果同分必须同档，我不会为了维持人数相等而偷偷加唯一键拆分。DENSE_RANK 可以按分数给相同名次，但它产生的是不同值的名次，不保证只有四档或人数接近；固定四档且同分同档，需要业务制定阈值及边界规则。使用排名函数保留同分语义时，也不要把唯一 id 加进其按分数比较的窗口，否则会改变“并列”的定义。

另一个容易误会的点是成员稳定性：NTILE 每次根据当前参与行集分桶。数据新增、删除、得分变化或筛选范围变化，都可能移动原记录的桶号。若我要保存一个长期不变的分组，我会把分配结果和口径版本另行存储，而不是每次报表重新算后称为永久分组。

唯一键能稳定同一行集里的并列顺序，不能冻结变化数据的分桶成员。需要多次导出一致时，应固定数据快照或业务批次，而不是仅固定最终展示排序。

按需求选写法，不按函数名字选

桶数大于行数，会补出空桶吗？

不会生成额外结果行。例如两行使用 NTILE(4)，只会在这两行上出现桶号 1、2。若展示必须列齐四个桶，包括人数为零的桶，应另用完整桶号集合做外连接统计。

桶数可以为 0、负数或 NULL 吗？

应用层应要求有效正整数并拒绝这些输入，不把它们当作“禁用分桶”的开关。本例固定使用字面量，动态桶数应先检查边界，再通过所用驱动支持的参数绑定形式传入，避免拼接未经检查的输入。

没有 ORDER BY 还能用吗？

语法允许的无序分桶不能满足这里按分数分层的需求。我会明确写出窗口排序，并在有并列值时补足稳定次序；否则即使人数看起来接近，也不能保证名单分配符合报告口径。

我的选择标准很简单：人数均衡优先并允许拆分同值，使用 NTILE；阈值、并列或成员永久性优先，就不要强行让它承担这些规则。

官方参考：NTILE 与排名函数 https://dev.mysql.com/doc/refman/8.4/en/window-function-descriptions.html；分区、窗口排序与 WHERE 的位置 https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html。