AntDB-Oracle Compatibility Developer's Manual P2–3
Published: 2023-08-29 06:33:56

##### Hierarchy of nodes

LEVEL is a pseudo-column that can be used wherever a column appears in a SELECT command. For each row in the result set, LEVEL returns a non-zero integer value specifying the depth of the node hierarchy represented by that row. The assignment is done in the order that the root node LEVEL is 1 and the direct child of the root node LEVEL is 2.

In the following query the previous query command is modified by adding the LEVEL pseudo-column to the statement. In addition, by using the LEVEL value, the employee names are indented, which further emphasizes the depth of each record in the hierarchy.

```
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
```

The output of this query is as follows.

```
 LEVEL |  employee   | EMPNO | MGR  
-------+-------------+-------+------
     1 | KING        |  7839 |     
     2 |   CLARK     |  7782 | 7839
     2 |   BLAKE     |  7698 | 7839
     2 |   JONES     |  7566 | 7839
     3 |     MILLER  |  7934 | 7782
     3 |     JAMES   |  7900 | 7698
     3 |     TURNER  |  7844 | 7698
     3 |     MARTIN  |  7654 | 7698
     3 |     WARD    |  7521 | 7698
     3 |     ALLEN   |  7499 | 7698
     3 |     FORD    |  7902 | 7566
     3 |     SCOTT   |  7788 | 7566
     4 |       SMITH |  7369 | 7902
     4 |       ADAMS |  7876 | 7788
(14 rows)

```

##### Sort same-level nodes

Nodes at the same level share a parent node and are nodes at the same level. For example, in the above output, employees ALLEN, WARD, MARTIN, TURNER and JAMES are same-level nodes because they are at the same level and have a common parent node BLAKE. JONES, BLAKE and CLARK are also same-level nodes because they are at the same level and have a common parent, KING.

By using the ORDER SIBLINGS BY clause to query the values of the columns, the same-level nodes can appear in ascending or descending order. This enables sorting of the result set. This is a specific use case for the ORDER BY clause and can only be used for hierarchical queries.

By adding the ORDER SIBLINGS BY ename ASC clause, we make further modifications to the previous query.

```
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;
```

The output of the query has now been modified so that the same-level nodes appear in ascending order based on their names. The siblings BLAKE, CLARK and JONES under KING appear in alphabetical order. The sibling nodes ALLEN, JAMES, MARTIN, TURNER and WARD under BLAKE are also in alphabetical order.

```
 LEVEL |  employee   | EMPNO | MGR  
-------+-------------+-------+------
     1 | KING        |  7839 |     
     2 |   BLAKE     |  7698 | 7839
     3 |     ALLEN   |  7499 | 7698
     3 |     JAMES   |  7900 | 7698
     3 |     MARTIN  |  7654 | 7698
     3 |     TURNER  |  7844 | 7698
     3 |     WARD    |  7521 | 7698
     2 |   CLARK     |  7782 | 7839
     3 |     MILLER  |  7934 | 7782
     2 |   JONES     |  7566 | 7839
     3 |     FORD    |  7902 | 7566
     4 |       SMITH |  7369 | 7902
     3 |     SCOTT   |  7788 | 7566
     4 |       ADAMS |  7876 | 7788
(14 rows)
```

In this last example, the WHERE clause is added and the query starts at the 3 root nodes. After the node tree is constructed, the WHERE clause filters out the rows in the tree so that the final result set is formed.

```
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp WHERE mgr IN (7839, 7782, 7902, 7788)
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;
```

The output result set of the query shows the root nodes (LEVEL1) - BLAKE, CLARK and JONES. In addition, rows that do not satisfy the WHERE clause do not appear in the output.

```
 LEVEL | employee  | EMPNO | MGR  
-------+-----------+-------+------
     1 | BLAKE     |  7698 | 7839
     1 | CLARK     |  7782 | 7839
     2 |   MILLER  |  7934 | 7782
     1 | JONES     |  7566 | 7839
     3 |     SMITH |  7369 | 7902
     3 |     ADAMS |  7876 | 7788
(6 rows)
```

#### Multidimensional Analysis

Multidimensional analysis refers to the process of examining data using a combination of dimensions commonly used in data warehouse applications. Dimensions are categories used to classify data, such as time, geographic location, company department, product line, etc. The results associated with a particular set of dimensions are called facts. Facts are usually numbers associated with product sales, profits, volumes, quantities, etc.

In order to get these facts based on a set of dimensions in a relational database system, SQL aggregation is usually used. SQL aggregation basically means grouping data according to some criteria (dimensions) and the result set consists of an aggregation of facts such as counts, sums and averages of the data in each group.

The GROUP BY clause of the SQL SELECT command supports the following commands that simplify the process of generating aggregated results.

- ROLLUP

- CUBE

- GROUPING SETS

##### ROLLUP extensions

ROLLUP is an extension of the GROUP BY statement. The effect is to create subtotals from the most detailed level of aggregation to the total, according to the grouped list specified in the ROLLUP clause. First he calculates the standard total specified in the GROUP BY clause, then he incrementally creates higher level totals by starting from the first column to the right of the multiple columns in the GROUP BY clause list and moving to the left. Eventually, it will calculate the sum.

The syntax of ROLLUP is as follows.

GROUP BY ROLLUP ( *attributeList* )

The number of grouping levels or totals is n + 1, where n indicates the number of items in the ROLLUP expression list. A list with parentheses counts as one item.

If it is Group by ROLLUP(A, B, C), it will first GROUP BY (A, B, C), then GROUP BY (A, B), then GROUP BY (A), and finally GROUP BY the whole table.

In the case of GROUP BY A, ROLLUP(B, C), the GROUP BY clause creates a subtotal at the (2+1=3) aggregation level. That is, at levels (A, B, C), (B,C) and (A).

The ROLLUP specified in the context of the GROUP BY clause is shown below.

SELECT select\_list FROM ...

GROUP BY \[... ,] ROLLUP ( expression\_list ) \[, ...]

The items specified in select\_list must also appear in ROLLUP expression\_list; or they must be aggregate functions such as COUNT, SUM, AVG, MIN, or MAX; or they must be constants or functions whose return values are independent of the individual rows in the group (e.g., the SYSDATE function).

If you want the output to be displayed in a hierarchy or other meaningful structure, you should use the ORDER BY clause. If the ORDER BY clause is not specified, the order of the result set is not guaranteed.

For example:

```
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;
```

Here are the results of the query. The number of employees for each unique combination of loc, dname, and job, and the subtotal for each unique combination of loc and dname, the sum of each unique value of loc, and the last line showing the total.

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

```

If the first two columns in the ROLLUP list are enclosed in parentheses, then the subtotal levels are different.

```
 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;
```

Now there is a subtotal for each unique combination of loc and dname, but no subtotal for the unique value of loc.

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