AntDB-Oracle Compatibility Developer's Manual P2–4
Published: 2023-08-29 06:42:42

##### CUBE extensions

The CUBE extension is similar to the ROLLUP extension. However, unlike ROLLUP, which generates groups and produces a hierarchy based on a left-to-right list of items in the ROLLUP expression list, CUBE generates groups and subtotals based on each alignment of all items in the CUBE expression list. As a result, the result set contains more rows compared to ROLLUP executed on the same expression list.

CUBE(A, B, C), on the other hand, will first GROUPBY (A, B, C), then (A, B), (A, C), (A), (B, C), (B), (C), and finally GROUPBY the whole table. There are 2's of N combinations.

For example:

The following query produces a multidimensional dataset based on the permutation of the loc, dname, and job columns.

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

Here are the results of the query. For each combination of loc, dname and job, there is a count of the number of employees, for each combination of loc and dname, for each combination of loc and job, and for each combination of dname and job, there is a subtotal for each. unique value for loc, for each unique value for dname, for each unique value for job, and the total shown on the last row.

```
  LOC     |   DNAME    |    JOB    | employees 
----------+------------+-----------+-------------
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 CHICAGO  |            | CLERK     |         1
 CHICAGO  |            | MANAGER   |         1
 CHICAGO  |            | SALESMAN  |         4
 CHICAGO  |            |           |         6
 DALLAS   | RESEARCH   | ANALYST   |         2
 DALLAS   | RESEARCH   | CLERK     |         2
 DALLAS   | RESEARCH   | MANAGER   |         1
 DALLAS   | RESEARCH   |           |         5
 DALLAS   |            | ANALYST   |         2
 DALLAS   |            | CLERK     |         2
 DALLAS   |            | MANAGER   |         1
 DALLAS   |            |           |         5
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
 NEW YORK |            | CLERK     |         1
 NEW YORK |            | MANAGER   |         1
 NEW YORK |            | PRESIDENT |         1
 NEW YORK |            |           |         3
          | ACCOUNTING | CLERK     |         1
          | ACCOUNTING | MANAGER   |         1
          | ACCOUNTING | PRESIDENT |         1
          | ACCOUNTING |           |         3
          | RESEARCH   | ANALYST   |         2
          | RESEARCH   | CLERK     |         2
          | RESEARCH   | MANAGER   |         1
          | RESEARCH   |           |         5
          | SALES      | CLERK     |         1
          | SALES      | MANAGER   |         1
          | SALES      | SALESMAN  |         4
          | SALES      |           |         6
          |            | ANALYST   |         2
          |            | CLERK     |         4
          |            | MANAGER   |         3
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
          |            |           |        14
(42 rows)
```