全部产品
云市场

ROLLUP

更新时间:2020-03-23 20:04:51

ROLLUP 函数是聚合函数,它是 GROUP BY 语句的简单扩展。在数据统计和报表生成过程中,它可以为每个分组返回一个小计,同时为所有分组返回总计,效率比 GROUP BYUNION 组合方法高。

ROLLUP 函数的执行很简单,以下是它的执行顺序:

  • 根据参数指定的列从右到左递减分组;
  • 对每个分组小计,再对所有分组合计;
  • 按照 ORDER BY col1 (,col2,col3,col4 ...) 排序。

如果 ROLLUP 参数为 N 个,则相当于 N+1GROUP BY 分组的 UNION 的结合。

在涉及分组统计的任务中使用 ROLLUP 函数非常高效。例如,沿着时间或地理等层次维度进行小计,您只需要查询 ROLLUP(y, m, day)ROLLUP(country, state, city)。数据仓库管理员使用 ROLLUP 函数可以简化和加快汇总表的维护。

语法

  1. SELECT GROUP BY ROLLUP(col1 [,col2...])

参数

参数 说明
col1 进行分组操作的列名。列数指的是数据库中的行数。

示例

创建表 group_test,并向里面插入数据,执行以下语句:

  1. CREATE TABLE group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
  2. INSERT INTO group_test VALUES (10, 'Coding', 'Bruce', 1000);
  3. INSERT INTO group_test VALUES (10, 'Programmer', 'Clair', 1000);
  4. INSERT INTO group_test VALUES (20, 'Coding', 'Jason', 2000);
  5. INSERT INTO group_test VALUES (20, 'Programmer', 'Joey', 2000);
  6. INSERT INTO group_test VALUES (30, 'Coding', 'Rebecca', 3000);
  7. INSERT INTO group_test VALUES (30, 'Programmer', 'Rex', 3000);
  8. INSERT INTO group_test VALUES (40, 'Coding', 'Samuel', 4000);
  9. INSERT INTO group_test VALUES (40, 'Programmer', 'Susy', 4000);
  10. COMMIT;

group_id 进行 GROUP BY 分组操作,执行以下语句:

  1. SELECT group_id, SUM(salary) FROM group_test GROUP BY group_id;

查询结果如下:

  1. +----------+-------------+
  2. | GROUP_ID | SUM(SALARY) |
  3. +----------+-------------+
  4. | 10 | 2000 |
  5. | 20 | 4000 |
  6. | 30 | 6000 |
  7. | 40 | 8000 |
  8. +----------+-------------+

group_id 使用 ROLLUP 函数进行分组,同时求总计。执行以下语句:

  1. SELECT group_id, SUM(salary) FROM group_test GROUP BY ROLLUP (group_id);

查询结果如下:

  1. +----------+-------------+
  2. | GROUP_ID | SUM(SALARY) |
  3. +----------+-------------+
  4. | 10 | 2000 |
  5. | 20 | 4000 |
  6. | 30 | 6000 |
  7. | 40 | 8000 |
  8. | NULL | 20000 |
  9. +----------+-------------+

group_id 列和 job 列使用 ROLLUP 函数进行分组,同时求总计。执行以下语句:

  1. SELECT group_id, job, SUM(salary) FROM group_test GROUP BY ROLLUP (group_id, job);

查询结果如下:

  1. +----------+------------+-------------+
  2. | GROUP_ID | JOB | SUM(SALARY) |
  3. +----------+------------+-------------+
  4. | 10 | Coding | 1000 |
  5. | 10 | Programmer | 1000 |
  6. | 10 | NULL | 2000 |
  7. | 20 | Coding | 2000 |
  8. | 20 | Programmer | 2000 |
  9. | 20 | NULL | 4000 |
  10. | 30 | Coding | 3000 |
  11. | 30 | Programmer | 3000 |
  12. | 30 | NULL | 6000 |
  13. | 40 | Coding | 4000 |
  14. | 40 | Programmer | 4000 |
  15. | 40 | NULL | 8000 |
  16. | NULL | NULL | 20000 |
  17. +----------+------------+-------------+

上面的 SQL 语句用 GROUP BYUNION 组合方法替换,执行以下语句:

  1. SELECT group_id, job, SUM(salary) FROM group_test GROUP BY group_id, job
  2. UNION ALL
  3. SELECT group_id, NULL, SUM(salary) FROM group_test GROUP BY group_id
  4. UNION ALL
  5. SELECT NULL, NULL, SUM(salary) FROM group_test ORDER BY 1, 2;

查询结果如下:

  1. +----------+------------+-------------+
  2. | GROUP_ID | JOB | SUM(SALARY) |
  3. +----------+------------+-------------+
  4. | 10 | Coding | 1000 |
  5. | 10 | Programmer | 1000 |
  6. | 10 | NULL | 2000 |
  7. | 20 | Coding | 2000 |
  8. | 20 | Programmer | 2000 |
  9. | 20 | NULL | 4000 |
  10. | 30 | Coding | 3000 |
  11. | 30 | Programmer | 3000 |
  12. | 30 | NULL | 6000 |
  13. | 40 | Coding | 4000 |
  14. | 40 | Programmer | 4000 |
  15. | 40 | NULL | 8000 |
  16. | NULL | NULL | 20000 |
  17. +----------+------------+-------------+

输出结果和 ROLLUP 函数一样,但是 ROLLUP 函数更加简洁、高效。