MySQL 创建预定数目的桶

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

把数据变为固定数目的桶。
例如，把表EMP中的员工编组为4桶。
其结果集应该如下所示:

±-----±------±-------+
| grp | empno | ename |
±-----±------±-------+
| 1 | 7566 | JONES |
| 1 | 7788 | SCOTT |
| 1 | 7900 | JAMES |
| 2 | 7369 | SMITH |
| 2 | 7654 | MARTIN |
| 2 | 7839 | KING |
| 2 | 7902 | FORD |
| 3 | 7499 | ALLEN |
| 3 | 7698 | BLAKE |
| 3 | 7844 | TURNER |
| 3 | 7934 | MILLER |
| 4 | 7521 | WARD |
| 4 | 7782 | CLARK |
| 4 | 7876 | ADAMS |
±-----±------±-------+

二.解决方案

MySQL 8.0开始支持窗口函数，ntile直接提供了创建"桶"的函数，这个就很简单了。

如果不使用ntile函数，也可以给每行分等级，然后在表达式中使用等级对n的模(n是要创建的同属)，以确定该行落入哪个桶内。

2.1 临时表方法

select  mod(count(*),4) +1 as grp,
        e.empno,
        e.ename
  from  emp e, emp d
 where  e.empno >= d.empno
 group  by e.empno,e.ename
 order  by 1;

测试记录:

mysql> select  mod(count(*),4) +1 as grp,
    ->         e.empno,
    ->         e.ename
    ->   from  emp e, emp d
    ->  where  e.empno >= d.empno
    ->  group  by e.empno,e.ename
    ->  order  by 1;
+------+-------+--------+
| grp  | empno | ename  |
+------+-------+--------+
|    1 |  7566 | JONES  |
|    1 |  7788 | SCOTT  |
|    1 |  7900 | JAMES  |
|    2 |  7369 | SMITH  |
|    2 |  7654 | MARTIN |
|    2 |  7839 | KING   |
|    2 |  7902 | FORD   |
|    3 |  7499 | ALLEN  |
|    3 |  7698 | BLAKE  |
|    3 |  7844 | TURNER |
|    3 |  7934 | MILLER |
|    4 |  7521 | WARD   |
|    4 |  7782 | CLARK  |
|    4 |  7876 | ADAMS  |
+------+-------+--------+
14 rows in set (0.00 sec)

2.2 MySQL 8.0 窗口函数方法

select  mod(row_number() over w, 4) + 1 as grp,
        empno,
        ename
  from  emp
 window w as (order by empno)
 order  by 1;

测试记录:

mysql> select  mod(row_number() over w, 4) + 1 as grp,
    ->         empno,
    ->         ename
    ->   from  emp
    ->  window w as (order by empno)
    ->  order  by 1;
+------+-------+--------+
| grp  | empno | ename  |
+------+-------+--------+
|    1 |  7566 | JONES  |
|    1 |  7788 | SCOTT  |
|    1 |  7900 | JAMES  |
|    2 |  7369 | SMITH  |
|    2 |  7654 | MARTIN |
|    2 |  7839 | KING   |
|    2 |  7902 | FORD   |
|    3 |  7499 | ALLEN  |
|    3 |  7698 | BLAKE  |
|    3 |  7844 | TURNER |
|    3 |  7934 | MILLER |
|    4 |  7521 | WARD   |
|    4 |  7782 | CLARK  |
|    4 |  7876 | ADAMS  |
+------+-------+--------+
14 rows in set (0.00 sec)

2.3 MySQL 8.0 ntile函数方法

select  ntile(4) over w as 'grp',
        empno,
        ename
  from  emp
window  w as (order by empno)
;

测试记录:

mysql> select  ntile(4) over w as 'grp',
    ->         empno,
    ->         ename
    ->   from  emp
    -> window  w as (order by empno)
    -> ;
+------+-------+--------+
| grp  | empno | ename  |
+------+-------+--------+
|    1 |  7369 | SMITH  |
|    1 |  7499 | ALLEN  |
|    1 |  7521 | WARD   |
|    1 |  7566 | JONES  |
|    2 |  7654 | MARTIN |
|    2 |  7698 | BLAKE  |
|    2 |  7782 | CLARK  |
|    2 |  7788 | SCOTT  |
|    3 |  7839 | KING   |
|    3 |  7844 | TURNER |
|    3 |  7876 | ADAMS  |
|    4 |  7900 | JAMES  |
|    4 |  7902 | FORD   |
|    4 |  7934 | MILLER |
+------+-------+--------+
14 rows in set (0.00 sec)