SQL 创建预定数目的桶|极客教程

SQL 创建预定数目的桶，你想把你的数据分别放入到数目固定的桶里面去。例如，你希望把 EMP 表里的员工分别放入到 4 个桶里，结果集应该如下所示。

SQL 创建预定数目的桶问题描述

你想把你的数据分别放入到数目固定的桶里面去。例如，你希望把 EMP 表里的员工分别放入到 4 个桶里，结果集应该如下所示。

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

本实例和 12.7 节中的实例恰好相反。在 12.7 节中的实例里，桶的个数并没有限制，但每个桶的元素个数却是事先定好的。对于本实例而言，你不在乎每个桶里有多少个元素，但需要创建固定数目（数目已知）的桶。

SQL 创建预定数目的桶解决方案

对于那些提供了专有函数帮助我们创建“桶”的数据库而言，很容易解决本问题。dxcbv但是，如果数据库不提供这类函数，则只好为每一行生成一个序号，然后针对该序号和 n 执行模运算以决定把某一行放入哪个桶，此处的 n 代表我们希望创建的桶的个数。针对部分提供了这些函数的数据库，本解决方案将借助窗口函数 NTILE 创建数目固定的桶。NTILE 负责把排好序的集合分别放入到指定数目的桶里去，每一个元素都必然会被分配到某个桶中。这恰好与前面给出的我们所期望的结果集相一致，桶 1 和桶 2 分别有 4 行数据，而桶 3 和桶 4 却只有 3 行。如果数据库不支持 NTILE，也不必担心。本实例的主要目的是创建固定数目的桶，我们没必要执着于一定要把某一行放进哪个桶。
DB2
使用窗口函数 ROW_NUMBER OVER 基于 EMPNO 为每一行生成一个序号，然后针对该序号和 4 执行模运算以创建 4 个桶。

1 select mod(row_number()over(order by empno),4)+1 grp,
2        empno,
3        ename
4   from emp
5  order by 1

Oracle 和 SQL Server
DB2 解决方案也适用于这两种数据库。除此之外，我们还可以使用窗口函数 NTILE 创建 4 个桶（这种做法更简单）。

1  select ntile(4)over(order by empno) grp,
2         empno,
3         ename
4    from emp

MySQL 和 PostgreSQL
使用自连接基于 EMPNO 为每一行生成一个序号，然后针对该序号和 4 执行模运算以创建桶。

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

SQL 创建预定数目的桶扩展知识

DB2
首先使用窗口函数 ROW_NUMBER OVER 基于 EMPNO 为每一行生成一个序号。

select row_number()over(order by empno) grp,
       empno,
       ename
  from emp
　
GRP 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

现在每一行都分配好了序号，下面要调用模运算函数 MOD 创建 4 个桶。

select mod(row_number()over(order by empno),4) grp,
       empno,
       ename
  from emp
　
GRP EMPNO ENAME
--- ----- ------
  1  7369 SMITH
  2  7499 ALLEN
  3  7521 WARD
  0  7566 JONES
  1  7654 MARTIN
  2  7698 BLAKE
  3  7782 CLARK
  0  7788 SCOTT
  1  7839 KING
  2  7844 TURNER
  3  7876 ADAMS
  0  7900 JAMES
  1  7902 FORD
  2  7934 MILLER

最后为 GRP 加 1，这样桶的编号才会从 1 而不是 0 开始，然后还要使用 ORDER BY 基于 GRP 排序。
Oracle 和 SQL Server
NTILE 函数独自完成了全部工作。我们只需要传递一个参数告诉 NTILE 我们想要几个桶，然后等着看结果即可。
MySQL 和 PostgreSQL
首先和 EMP 表生成笛卡儿积，这样每一个 EMPNO 就能够和其他的任意 EMPNO 进行比较了，下面只截取了该笛卡儿积的一部分，因为全部的返回值会有 196 行（14 乘以 14）。

select e.empno,
       e.ename,
       d.empno,
       d.ename
  from emp e, emp d
　
EMPNO ENAME           EMPNO ENAME
----- ---------- ---------- ---------
 7369 SMITH            7369 SMITH
 7369 SMITH            7499 ALLEN
 7369 SMITH            7521 WARD
 7369 SMITH            7566 JONES
 7369 SMITH            7654 MARTIN
 7369 SMITH            7698 BLAKE
 7369 SMITH            7782 CLARK
 7369 SMITH            7788 SCOTT
 7369 SMITH            7839 KING
 7369 SMITH            7844 TURNER
 7369 SMITH            7876 ADAMS
 7369 SMITH            7900 JAMES
 7369 SMITH            7902 FORD
 7369 SMITH            7934 MILLER
 …

从上述结果集中可以看到，SMITH 的 EMPNO 会和 EMP 表中每一个 EMPNO 进行比较（每个员工的 EMPNO 都可以和所有其他员工的 EMPNO 进行比较）。下一步要限定笛卡儿积的结果，只有那些 EMPNO 大于或等于其他 EMPNO 的行才会被保留下来。部分结果集如下所示（因为总共有 105 行）。

select e.empno,
       e.ename,
       d.empno,
       d.ename
  from emp e, emp d
 where e.empno >= d.empno
　
EMPNO ENAME           EMPNO ENAME
----- ---------- ---------- ----------
 7934 MILLER           7934 MILLER
 7934 MILLER           7902 FORD
 7934 MILLER           7900 JAMES
 7934 MILLER           7876 ADAMS
 7934 MILLER           7844 TURNER
 7934 MILLER           7839 KING
 7934 MILLER           7788 SCOTT
 7934 MILLER           7782 CLARK
 7934 MILLER           7698 BLAKE
 7934 MILLER           7654 MARTIN
 7934 MILLER           7566 JONES
 7934 MILLER           7521 WARD
 7934 MILLER           7499 ALLEN
 7934 MILLER           7369 SMITH
 ...
 7499 ALLEN            7499 ALLEN
 7499 ALLEN            7369 SMITH
 7369 SMITH            7369 SMITH

以上输出结果并非全部的结果集，它只包括来自 EMP E 表的 MILLER、ALLEN 和 SMITH。我只是想告诉你 WHERE 子句会怎样限制笛卡儿积的结果。因为 WHERE 子句的 EMPNO 过滤条件是“大于或等于”，这意味着每个员工至少有一行查询结果，因为每个 EMPNO 都等于它自身。但是，为什么 SMITH 只有 1 行（在结果集的左边），ALLEN 有 2 行，而 MILLER 则有 14 行呢？原因就在于 WHERE 子句里那个关于 EMPNO 的复合条件：“大于或等于”。对于 SMITH 而言，没有比 7369 小的 EMPNO，因此只有一行数据返回。对于 ALLEN 而言，很显然他的 EMPNO 等于其自身（因此返回了该行数据），但 7499 也大于 7369（SMITH 的 EMPNO），因此会返回两行数据。对于 MILLER 而言，他的 EMPNO 7934 大于 EMP 表中所有其他 EMPNO（并且显然也等于其自身），因此会返回 14 行数据。
现在我们可以比较每一个 EMPNO，并且决定哪些大于其他的值。在自连接查询里使用聚合函数 COUNT 返回一个更具表现力的结果集。

select count(*) 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       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

现在每一行都有了一个序号，先对 GRP 和 4 执行模运算，然后再加上 1，这样就创建出了 4 个桶（加 1 是为了让桶从 1 开始，而不是 0）。针对 GRP 使用 ORDER BY 子句进行排序，这样就能以合适的顺序输出最终结果。

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       7900 JAMES
  1       7566 JONES
  1       7788 SCOTT
  2       7369 SMITH
  2       7902 FORD
  2       7654 MARTIN
  2       7839 KING
  3       7499 ALLEN
  3       7698 BLAKE
  3       7934 MILLER
  3       7844 TURNER
  4       7521 WARD
  4       7782 CLARK
  4       7876 ADAMS