ROLLUP 扩展生成一系列有总计的分层组,每个分层组都有小计。该层次结构的顺序由 ROLLUP 表达式列表中给定的表达式的顺序确定。该层次结构的顶部是列表中最左侧的项。每个连续项都会沿右侧在该层次结构中向下移动,最右侧的项是最低级别。

语法

单个 ROLLUP 的语法如下所示:

ROLLUP ( { expr_1 | ( expr_1a [, expr_1b ] ...) }
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)
  • 每个 expr 都是确定结果集分组方式的一个表达式。如果采用带圆括号的形式 ( expr_1a, expr_1b, ...),则 expr_1a 和 expr_1b 返回的值组合定义层次结构的单个分组级别。
  • 结果集中返回的聚合的基础级别对应于表达式列表返回的每个唯一值组合。
  • 此外,对于列表中的第一项(expr_1 或 ( expr_1a, expr_1b, ...) 的组合,以指定的为准),将为每个唯一值返回一个小计。对于列表中的第二项(expr_2 或 ( expr_2a, expr_2b, ...) 的组合,以指定的为准),将为第一项的每个分组中的每个唯一值返回一个小计,依此类推。最后,将为整个结果集返回一个总计。
  • 对于小计行,将为小计包含的各项返回 null。

示例

以下示例展示了 GROUPBY 子句中指定的 ROLLUP 扩展:

SELECT select_list FROM ...
GROUP BY [... ,] ROLLUP ( expression_list ) [, ...]
  • select_list 中指定的各项必须也出现在 ROLLUP expression_list 中;或者这些项必须是聚合函数,如 COUNT、SUM、AVG、MIN 或 MAX 等;或者这些项必须是其返回值独立于组中各行的约束或函数(例如,SYSDATE 函数)。
  • GROUP BY 子句可指定多个 ROLLUP 扩展以及多次出现的其他 GROUP BY 扩展和各表达式。
  • 如果您希望输出显示在一个分层或其他有意义的结构中,应使用 ORDER BY 子句。如果未指定 ORDERBY 子句,则无法保证结果集的顺序。
  • 分组级别数或总计数为 n + 1,其中 n 表示 ROLLUP 表达式列表中的项数。带圆括号的列表计为一项。

以下示例将根据 loc 列、dname 列和 job 列的层次结构生成 rollup。

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
ORDER BY 1, 2, 3;

下面是该查询的结果。对于 loc、dname 和 job 的每个唯一组合,都有一个员工数的计数;对于 loc 和 dname 的每个唯一组合以及 loc 的每个唯一值,都有一个小计;并且在最后一行显示了总计。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | OPERATIONS |           |         3
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 BOSTON   | RESEARCH   |           |         5
 BOSTON   |            |           |         8
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 CHICAGO  |            |           |         6
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
 NEW YORK |            |           |         3
          |            |           |        17
(20 rows)

以下查询显示了用圆括号合并 ROLLUP 列表中的项的效果。

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, (dname, job))
ORDER BY 1, 2, 3;

在输出中,您可以看到与上一示例不同的是,没有 loc 和 dname 组合的小计。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 BOSTON   |            |           |         8
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  |            |           |         6
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK |            |           |         3
          |            |           |        17
(16 rows)

如果 ROLLUP 列表中的前两列带圆括号,则小计级别也会不同。

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP ((loc, dname), job)
ORDER BY 1, 2, 3;

对于每个 loc 和 dname 唯一组合,都有一个小计;而对于 loc 的唯一值,则没有小计。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | OPERATIONS |           |         3
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 BOSTON   | RESEARCH   |           |         5
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
          |            |           |        17
(17 rows)