Столбец выбора Postgresql с минимальным значением иерархических родителей

Наличие такого стола

                       Table "public.access_level"
  Column   |  Type   | Modifiers | Storage  | Stats target | Description
-----------+---------+-----------+----------+--------------+-------------
 uid       | uuid    | not null  | plain    |              |
 parent_id | integer | not null  | plain    |              |
 child_id  | integer | not null  | plain    |              |
 level     | integer | not null  | plain    |              |
 entity    | text    |           | extended |              |

и такие строки (удален столбец uid)

 parent_id | child_id | level | entity
-----------+----------+-------+--------
        11 |       22 |     4 | a
        22 |       33 |     5 | a
        33 |       44 |     6 | a
        11 |       22 |     7 | b
        22 |       33 |     4 | b
        33 |       44 |     5 | b

Мне нужен вывод, который возвращает значение level для каждой строки на основе минимального значения level родителей, отличающихся от каждого entity. Вот мой желаемый результат:

 parent_id | child_id | level | entity
-----------+----------+-------+--------
        11 |       22 |     4 | a
        22 |       33 |     4 | a
        33 |       44 |     4 | a
        11 |       22 |     7 | b
        22 |       33 |     4 | b
        33 |       44 |     4 | b

Желателен рекурсивный подход, поскольку глубина иерархии не фиксирована.

Примечание.(parent_id,child_id,entity) уникален в таблице.

Фактически parent_id и child_id являются пользователями. родитель дает дочернему элементу уровень доступа к объекту. Затем дочерний пользователь может предоставить уровень доступа другому своему дочернему пользователю. В какой-то момент родитель родителя может изменить уровень доступа своего дочернего элемента. теперь все более глубокие дети должны иметь уровень доступа не выше этого. Его нельзя реализовать с помощью триггера для обновления соответствующих строк, поскольку родительский элемент родителя может откатить изменения.

Сценарий:

  • 11,22,7,b означает, что пользователь-11 дает пользователю-22 сущность level из 7 в b.
  • Теперь пользователь-22 в какой-то момент дает пользователю-33 уровень 5 для объекта b.
  • затем пользователь-33 дает пользователю-44 уровень 5 для b объекта.
  • Важно: пользователь-22 изменяет уровень доступа b на 4 для пользователя-33, что вы и видите. в таблице примеров
  • уровень доступа от пользователя-33 до пользователя-44 для объекта b должен оставаться 5 в таблице

Но мне нужен запрос, который вернет 4 для этого столбца, как если бы я делал это рекурсивно для всех дочерних элементов пользователя-22, которые получили уровень более 4.

Благодарность


postgresql sql postgresql-11
person Soheil Armin    schedule 18.12.2019    source источник
comment
Похоже, вам нужен раздел: min(level) over (partition by parent_id). Вы можете добавить больше столбцов в раздел, представьте себе это как group by.   -  person Viorel    schedule 18.12.2019
comment
@Viorel, это невозможно путем разделения. подумайте, что у каждой сущности есть дерево, для каждого узла дерева я хочу вернуть минимум level, проходящего дерево до корня   -  person Soheil Armin    schedule 18.12.2019
comment
Этот столбец уровня выглядит так, как будто он уже является результатом рекурсивного запроса, и вы можете разбить его по сущности. Связь между данными и желаемым результатом мне не очень ясна, почему родитель 33 для сущности b имеет уровень 4, тогда как родитель 11 для сущности b имеет уровень 7?   -  person Viorel    schedule 18.12.2019
comment
@Viorel, обновил мой ответ еще некоторыми пояснениями.   -  person Soheil Armin    schedule 18.12.2019

Ответы (1)


arrow_upward
1
arrow_downward

Начальная часть рекурсивного запроса находит корни (узлы без родителей), в рекурсивной части мы просто выбираем меньший уровень для сущности:

with recursive cte as (
    select parent_id, child_id, level, entity
    from access_level t
    where not exists (
        select from access_level l
        where l.child_id = t.parent_id)
union all
    select t.parent_id, t.child_id, least(c.level, t.level), t.entity
    from cte c
    join access_level t
    on t.parent_id = c.child_id and t.entity = c.entity
)
select *
from cte
order by entity, parent_id

Db‹>скрипка.

person klin    schedule 18.12.2019