How to implement summary statistics in an organizational structure with Oracle

For the general data model, there is usually a fact table, a number of dimension tables, through the fact table and dimension table connection, to achieve different levels of query rollup.

The problem is that for the organizational structure, all data is stored in a table, and the level of organizational structure is dynamic. So, in this case, how to achieve the summary of employee wages? A more interesting question is: How to count the total wages of employees and all of their employees, for example, the CEO's total salary is the sum of the total salary of the company's employees, including himself.

Oracle introduced an extended operator that is specifically designed to handle this situation, which is connect_by_root. When a column name is decorated with Connect_by_root, Oracle returns the value of the column corresponding to the root node. For example, when start with is last_name = ' King ', the value of Connect_by_root last_name for all rows returned will be ' king '. At this point, the cumulative of all rows is the sum of the data for ' King '. When the start with clause is not specified, Oracle loops through each node sequentially, so we can have a group by on the returned result for last_name, so we get all the last_name corresponding rollup salaries.

The following is an example of an Oracle document.

The following example returns the last name of each employee in department and each manager

Above that employee in the hierarchy, the number of levels between manager and employee,

and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"FROM employeesWHERE LEVEL > 1 and department_id = 110CONNECT BY PRIOR employee_id = manager_idORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee　　　　　　　 Manager　　　　　　　　　　　 Pathlen Path--------------- --------------- ---------- ------------------------------Gietz　　　　　　　　　　 Higgins　　　　　　　　　　　　　　　　　 1 /Higgins/GietzGietz　　　　　　　　　　 King　　　　　　　　　　　　　　　　　　　　 3 /King/Kochhar/Higgins/GietzGietz　　　　　　　　　　 Kochhar　　　　　　　　　　　　　　　　　 2 /Kochhar/Higgins/GietzHiggins　　　　　　　　 King　　　　　　　　　　　　　　　　　　　　 2 /King/Kochhar/HigginsHiggins　　　　　　　　 Kochhar　　　　　　　　　　　　　　　　　 1 /Kochhar/HigginsThe following example uses a GROUP BY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:

SELECT name, SUM(salary) "Total_Salary" FROM (SELECT CONNECT_BY_ROOT last_name as name, SalaryFROM employeesWHERE department_id = 110CONNECT BY PRIOR employee_id = manager_id)GROUP BY nameORDER BY name, "Total_Salary";
NAME　　　　　　　　　　　　　　　　　　　　　 Total_Salary------------------------- ------------Gietz　　　　　　　　　　　　　　　　　　　　　　　　　　　　 8300Higgins　　　　　　　　　　　　　　　　　　　　　　　　　 20300King　　　　　　　　　　　　　　　　　　　　　　　　　　　　 20300Kochhar　　　　　　　　　　　　　　　　　　　　　　　　　 20300