Подсчет данных в иерархической структуре?

Question

[pvgax]

Добрый день. Есть задача, реализовать хранение структуры компании в БД. СУБД MySql 8.0.33

Структура

• Отдел по обслуживанию клиентов (11 сотрудников)
  
  • Отдел продаж (4 сотрудника)
    • Отдел оптовых продаж (2 сотрудника)
      
    • Отдел розничных продаж (2 сотрудника)
      
    
    
  • Отдел логистики (7 сотрудников)
    • Склад (2 сотрудника)
      
    • Отдел доставки (5 сотрудников)
      
    
    
  
  
• Производственный отдел (7 сотрудников)
  • Инженерный отдел (4 сотрудника)
    
  • Отдел проверки качества (2 сотрудник)
    
  • Отдел закупок (1 сотрудник)
    
  
  
• Бухгалтерия (2 сотрудника)
  

Для этого реализовал 2 таблицы:
1) Departments хранит отделы и ИД родительского отдела

Таблица Departments

Х

CREATE TABLE `departments` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NameDepartment` longtext,
  `ParentDepartmentID` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

2) Сотрудники отделов

Таблица Employee

CREATE TABLE `employees` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `DepartmentID` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

Для того, чтобы вывести инфу об отделах использовал рекурсивные запросы. Но у меня не получается подсчитать сотрудников для каждого уровня. Получалось только самого нижнего уровня, данные которые вносятся вручную, либо на самых верхних уровнях общее количество подсчитать.
Использовать агрегатные функции внутри рекурсивного запроса нельзя, а как подсчитать для каждого уровня количество сотрудников - не понимаю.

Что у меня получилось:
1) Вывести количество сотрудников на нижних уровнях иерархии

Запрос 1

WITH RECURSIVE cte AS(
	SELECT d.ID, d.NameDepartment, d.ParentDepartmentID, e.ID AS Emp_ID
	FROM departments d
	INNER JOIN employees e ON e.DepartmentID = d.ID
	UNION ALL
	SELECT d.ID, d.NameDepartment, d.ParentDepartmentID, cte.Emp_ID
	FROM cte 
	INNER JOIN departments d ON cte.ID = d.ParentDepartmentID
)
SELECT cte.ID, cte.NameDepartment, COUNT(cte.Emp_ID) AS Emp_Count
FROM cte
GROUP BY cte.ID, cte.NameDepartment;

2) Вывести количество отделов на каждом уровне включая самые верхние. Например,
"Отдел по обслуживанию клиентов состоит из 7 отделов включая себя".

Запрос 2

WITH RECURSIVE cte 
AS(
	SELECT d.ID, d.NameDepartment, d.ParentDepartmentID, e.ID AS Emp_ID
    FROM departments d
		INNER JOIN employees e ON e.ID = d.ID
	UNION ALL
    SELECT d.ID, d.NameDepartment, d.ParentDepartmentID, cte.Emp_ID
    FROM cte 
		INNER JOIN departments d ON cte.ParentDepartmentID = d.ID
        )
SELECT NameDepartment, COUNT(*)  AS COUNT FROM cte
group by NameDepartment

Answer 1

[Rsa97]

В рекурсии добавлять уровень, в основном запросе группировать по уровню.

SELECT ..., 1 AS `level`
UNION
SELECT ..., `cte`.`level` + 1

Comments

[pvgax]

Возможно я не правильно выразил суть задачи. Но этот вариант не подошел, т.к. считает сотрудников на нижних уровнях или я неправильно написал запрос. Мне нужно подсчитать на каждом уровне для каждого отдела.

Т.е. Отдел по обслуживанию клиентов (11 сотрудников) = (Отдел продаж (4) + Отдел Логистики (7))
Отдел Продаж = Отдел оптовых продаж (2) + Отдел розничных продаж (2)
Отдел Логистики = Склад(2) + Отдел Доставки (5)

WITH RECURSIVE cte AS(
  SELECT d.ID, d.NameDepartment, d.ParentDepartmentID, e.ID AS Emp_ID, 1 AS Level
  FROM departments d
  INNER JOIN employees e ON e.DepartmentID = d.ID
  UNION ALL
  SELECT d.ID, d.NameDepartment, d.ParentDepartmentID, cte.Emp_ID, cte.Level + 1
  FROM cte 
  INNER JOIN departments d ON cte.ID = d.ParentDepartmentID
)
SELECT id, count(*) AS Emp_Count
FROM cte
GROUP BY id, level;

[Rsa97]

pvgax, Тогда надо тянуть родителей сквозь рекурсию

WITH RECURSIVE `cte` AS (
  SELECT `d`.`ID`, `d`.`ID` AS `root_id`
    FROM `departments` AS `d`
  UNION
  SELECT `d`.`ID`, `cte`.`root_id`
    FROM `cte`
    JOIN `departments` as `d` ON `d`.`ParentDepartmentID` = `cte`.`ID`
)
SELECT `root_id`, COUNT(*)
  FROM `cte`
  JOIN `employees` AS `e` ON `e`.`DepartmentID` = `cte`.`ID`
  GROUP BY `cte`.`root_id`

[pvgax]

Rsa97, огромное спасибо! Это правильное решение.

Answer 2

[Сергей Горностаев]

В PostgreSQL я делал так с использованием nested set:

SELECT name, cp.p_count FROM catalog_category AS cc
INNER JOIN LATERAL (
  SELECT cc.id AS id, SUM(products) AS p_count FROM (
    SELECT cc.id AS parent_id, category_id, COUNT(id) AS products
    FROM catalog_product
    WHERE category_id IN (
      SELECT id FROM catalog_category
      WHERE lft <= cc.rght AND lft >= cc.lft AND tree_id = cc.tree_id)
      GROUP BY category_id
    ) AS sub_cс
  GROUP BY parent_id
) AS cp
USING(id) ORDER BY name;

Может поможет чем-нибудь.