MySQL 创建预定数目的桶

野性酷女 2022-10-26 12:52 75阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb