Как подсчитать глубину в иерархии с условием в PostgreSQL?

Question

У меня есть дерево сотрудников. Мне необходимо:

подсчитать на каком уровне находится конкретный сотрудник. Это я сделал при помощи следующего запроса:

WITH RECURSIVE parents AS (
 SELECT id, manager_id
 FROM Employees
 WHERE id = 9
UNION
 SELECT op.id, op.manager_id
 FROM Employees op
 JOIN parents p ON op.id = p.manager_id
)
SELECT COUNT(*)
FROM parents

Необходимо подсчитать "Звездный" уровень сотрудника (уровень +1 к уровню добавляется только при skill_id.is_star == True).

Пример: Для Employee 9 уровень будет равен 9, а звездный уровень - 4 (Сотрудники под номерами 2,4,7,9)

Демо данные:

create table Employees(
    id INT,
    name VARCHAR,
    manager_id INT,
    skill_id INT
    
);

 create table Skills(
    id INT,
    name VARCHAR,
    is_star BOOL
);

INSERT INTO Employees
    (id, name, manager_id, skill_id)
VALUES
    (1, 'Employee 1', NULL, 1),
    (2, 'Employee 2', 1, 2),
    (3, 'Employee 3', 2, 3),
    (4, 'Employee 4', 3, 4),
    (5, 'Employee 5', 4, 5),
    (6, 'Employee 6', 5, 1),
    (7, 'Employee 7', 6, 2),
    (8, 'Employee 8', 7, 3),
    (9, 'Employee 9', 8, 4)
    ;

INSERT INTO Skills
    (id, name, is_star)
VALUES
    (1, 'Skill 1', FALSE),
    (2, 'Skill 2', TRUE),
    (3, 'Skill 3', FALSE),
    (4, 'Skill 4', TRUE),
    (5, 'Skill 5', FALSE)
    ;

Может кто-нибудь помочь, пожалуйста?

Answer 1

Как-то так:

WITH RECURSIVE parents AS (
  SELECT e.id
       , e.manager_id
       , 1 AS level
       , CAST(s.is_star AS INTEGER) AS star_level
    FROM employees AS e
         INNER JOIN skills AS s
            ON e.skill_id = s.id
   WHERE manager_id IS NULL
UNION ALL
  SELECT e.id
       , e.manager_id
       , p.level + 1 AS level
       , p.star_level + CAST(s.is_star AS INTEGER) AS star_level
    FROM employees AS e
         INNER JOIN skills AS s
            ON e.skill_id = s.id
         INNER JOIN parents AS p
            ON e.manager_id = p.id
   WHERE e.manager_id = p.id
)
SELECT *
  FROM parents
;

Результат:

 id | manager_id | level | star_level 
----+------------+-------+------------
  1 |       NULL |     1 |          0
  2 |          1 |     2 |          1
  3 |          2 |     3 |          1
  4 |          3 |     4 |          2
  5 |          4 |     5 |          2
  6 |          5 |     6 |          2
  7 |          6 |     7 |          3
  8 |          7 |     8 |          3
  9 |          8 |     9 |          4
(9 rows)

Фидель.

БЛОГ НА HUSL

Как подсчитать глубину в иерархии с условием в PostgreSQL?

Ответы (1 шт):