SQL 创建固定大小的数据桶|极客教程

SQL 创建固定大小的数据桶，你想把数据放入若干个大小固定的桶（bucket）里，每个桶的元素个数是事先定好的。桶的个数可能是不确定的，但你希望确保每个桶有 5 个元素。例如，你希望基于 EMPNO 值为 EMP 表里的员工分组，一组最多 5 人，结果集显示如下。

SQL 创建固定大小的数据桶问题描述

你想把数据放入若干个大小固定的桶（bucket）里，每个桶的元素个数是事先定好的。桶的个数可能是不确定的，但你希望确保每个桶有 5 个元素。例如，你希望基于 EMPNO 值为 EMP 表里的员工分组，一组最多 5 人，结果集显示如下。

GRP      EMPNO ENAME
--- ---------- -------
  1       7369 SMITH
  1       7499 ALLEN
  1       7521 WARD
  1       7566 JONES
  1       7654 MARTIN
  2       7698 BLAKE
  2       7782 CLARK
  2       7788 SCOTT
  2       7839 KING
  2       7844 TURNER
  3       7876 ADAMS
  3       7900 JAMES
  3       7902 FORD
  3       7934 MILLER

SQL 创建固定大小的数据桶解决方案

对于提供了排名函数的数据库而言，很容易解决本问题。在为每一行数据生成了一个序号之后，创建含有 5 个元素的桶的问题就变成了简单的除法问题。做过除法之后，我们只须针对商值向上取整即可。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER，基于 EMPNO 为每个员工生成一个序号。然后用该序号除以 5 即可实现分组（SQL Server 需要调用 CEILING 函数，而不是 CEIL 函数）。

1 select ceil(row_number()over(order by empno)/5.0) grp,
2        empno,
3        ename
4   from emp

PostgreSQL 和 MySQL
使用标量子查询为每个 EMPNO 生成一个序号，然后用该序号除以 5 以创建分组。

1  select ceil(rnk/5.0) as grp,
2         empno, ename
3    from (
4  select e.empno, e.ename,
5         (select count(*) from emp d
6           where e.empno < d.empno)+1 as rnk
7    from emp e
8         ) x
9   order by grp

SQL 创建固定大小的数据桶扩展知识

DB2、Oracle 和 SQL Server
按照 EMPNO 排序后，窗口函数 ROW_NUMBER OVER 为每一行分配了一个排名或“行号”。

select row_number()over(order by empno) rn,
       empno,
       ename
  from emp
　
RN      EMPNO ENAME
-- ---------- ----------
 1       7369 SMITH
 2       7499 ALLEN
 3       7521 WARD
 4       7566 JONES
 5       7654 MARTIN
 6       7698 BLAKE
 7       7782 CLARK
 8       7788 SCOTT
 9       7839 KING
10       7844 TURNER
11       7876 ADAMS
12       7900 JAMES
13       7902 FORD
14       7934 MILLER

将 ROW_NUMBER OVER 函数的返回值除以 5 之后，下一步要调用函数 CEIL（或 CEILING）。理论上，除以 5，会把每 5 行数据划入一组。也就是说，会有 5 个值小于或等于 1，同时会有 5 个值大于 1 但小于或等于 2，剩下的一组则大于 2 但小于或等于 3（这一组由最后的 4 行数据构成，因为 EMP 表一共包含 14 行数据，并非 5 的整数倍）。
CEIL 函数将返回大于参数值的最小的整数。我们需要这样做，因为每一组的编号都是一个整数值。除法计算的结果以及 CEIL 函数的返回值显示如下。你可以按照从左到右、从 RN 到 DIVISION 再到 GRP 的顺序试着做一下运算。

select row_number()over(order by empno) rn,
       row_number()over(order by empno)/5.0 division,
       ceil(row_number()over(order by empno)/5.0) grp,
       empno,
       ename
  from emp
　
RN   DIVISION GRP EMPNO ENAME
-- ---------- --- ----- ----------
 1         .2   1  7369 SMITH
 2         .4   1  7499 ALLEN
 3         .6   1  7521 WARD
 4         .8   1  7566 JONES
 5          1   1  7654 MARTIN
 6        1.2   2  7698 BLAKE
 7        1.4   2  7782 CLARK
 8        1.6   2  7788 SCOTT
 9        1.8   2  7839 KING
10          2   2  7844 TURNER
11        2.2   3  7876 ADAMS
12        2.4   3  7900 JAMES
13        2.6   3  7902 FORD
14        2.8   3  7934 MILLER

PostgreSQL 和 MySQL
首先借助标量子查询基于 EMPNO 为每一行生成一个序号。

select (select count(*) from emp d
         where e.empno < d.empno)+1 as rnk,
       e.empno, e.ename
  from emp e
 order by 1
　
RNK      EMPNO ENAME
--- ---------- ----------
  1       7934 MILLER
  2       7902 FORD
  3       7900 JAMES
  4       7876 ADAMS
  5       7844 TURNER
  6       7839 KING
  7       7788 SCOTT
  8       7782 CLARK
  9       7698 BLAKE
 10       7654 MARTIN
 11       7566 JONES
 12       7521 WARD
 13       7499 ALLEN
 14       7369 SMITH

上述 RNK 值除以 5 之后，下一步要调用函数 CEIL。理论上，除以 5 会把每 5 行数据划入一组。也就是说，会有 5 个值小于或等于 1，同时会有 5 个值大于 1 但小于或等于 2，剩下的一组则大于 2 但小于或等于 3（这一组由最后的 4 行数据构成，因为 EMP 表一共包含 14 行数据，并非 5 的整数倍）。除法计算的结果以及 CEIL 函数的返回值显示如下。你可以按照从左到右、从 RNK 到 DIVISION 再到 GRP 的顺序试着做一下运算。

select rnk,
       rnk/5.0 as division,
       ceil(rnk/5.0) as grp,
       empno, ename
  from (
select e.empno, e.ename,
       (select count(*) from emp d
         where e.empno < d.empno)+1 as rnk
  from emp e
       ) x
 order by 1
　
RNK   DIVISION GRP EMPNO ENAME
--- ---------- --- ----- -------
  1         .2   1  7934 MILLER
  2         .4   1  7902 FORD
  3         .6   1  7900 JAMES
  4         .8   1  7876 ADAMS
  5          1   1  7844 TURNER
  6        1.2   2  7839 KING
  7        1.4   2  7788 SCOTT
  8        1.6   2  7782 CLARK
  9        1.8   2  7698 BLAKE
 10          2   2  7654 MARTIN
 11        2.2   3  7566 JONES