Oracle соединяется с предыдущим вместе с max() по разделу, чтобы удалить повторяющееся поддерево

Я пытаюсь удалить повторяющееся поддерево, возвращаемое предложением «подключиться по предыдущему». Я хочу проверить узел верхнего уровня иерархии дерева, где пользователь может ввести дочерние идентификаторы, которые уже являются частью поддерева. Взгляните на следующий пример:

SELECT * FROM (
With test_hierarchy as(
       SELECT 'a' parent, 'b' child FROM dual UNION ALL
       SELECT 'b','c' FROM dual UNION ALL
       SELECT 'd','e' FROM dual UNION ALL
       SELECT 'e','f' FROM dual UNION ALL
       SELECT 'f','g' FROM dual UNION ALL
       SELECT 'f','h' FROM dual)
SELECT
    parent,
    child,
    CONNECT_BY_ROOT child AS init_child,
    LEVEL,
    CONNECT_BY_ISLEAF,
    MAX(LEVEL) OVER(
        PARTITION BY parent
    ) AS max_level
FROM
    test_hierarchy
WHERE
    CONNECT_BY_ISLEAF = 1
START WITH
    child IN (
        'c', 'b', 'e', 'f', 'h', 'g'
    )
CONNECT BY
    PRIOR parent = child);

Этот запрос возвращает результат в виде:

P C I      LEVEL CONNECT_BY_ISLEAF  MAX_LEVEL
- - - ---------- ----------------- ----------
a b b          1                 1          2
a b c          2                 1          2
d e g          3                 1          3
d e f          2                 1          3
d e h          3                 1          3
d e e          1                 1          3

Я хочу вернуть только те узлы верхнего уровня с level = max_level. т.е. мой запрос должен возвращать результат как:

P C I      LEVEL CONNECT_BY_ISLEAF  MAX_LEVEL
- - - ---------- ----------------- ----------
a b c          2                 1          2
d e g          3                 1          3
d e h          3                 1          3

Если я попытаюсь отфильтровать результаты, используя предложение WHERE как «WHERE level = max_level», Oracle жалуется:

ORA-01788: CONNECT BY clause required in this query block
01788. 00000 -  "CONNECT BY clause required in this query block"

Пожалуйста, дайте мне знать, если у вас есть идея о том, как это сделать. Спасибо,


sql database oracle partition-by connect-by
person user613114    schedule 31.01.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Оберните свой рекурсивный запрос в другой CTE и отфильтруйте его:

WITH 
  test_hierarchy AS (
    SELECT 'a' parent, 'b' child FROM dual UNION ALL
    SELECT 'b','c' FROM dual UNION ALL
    SELECT 'd','e' FROM dual UNION ALL
    SELECT 'e','f' FROM dual UNION ALL
    SELECT 'f','g' FROM dual UNION ALL
    SELECT 'f','h' FROM dual
  ),
  recursion AS (
    SELECT
      parent,
      child,
      CONNECT_BY_ROOT child AS init_child,
      LEVEL AS lvl,
      CONNECT_BY_ISLEAF AS isleaf,
      MAX(LEVEL) OVER(
        PARTITION BY parent
      ) AS max_level
    FROM
      test_hierarchy
    START WITH child IN ('c', 'b', 'e', 'f', 'h', 'g')
    CONNECT BY PRIOR parent = child
  )
SELECT *
FROM recursion
WHERE isleaf = 1 AND lvl = max_level
person Lukas Eder    schedule 31.01.2019

arrow_upward
1
arrow_downward

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

Один из вариантов заключается в том, чтобы ограничить START WITH только узлами выход.

Это можно сделать, исключив все узлы, которые являются родительскими узлами:

START WITH
    child IN ( 'c', 'b', 'e', 'f', 'h', 'g') and
    child not in (select parent from test_hierarchy)

Окончательный запрос имеет более высокую производительность из-за ограниченного списка START, и вам не нужна WINDOWS SORT, чтобы получить максимальный уровень:

With test_hierarchy as(
       SELECT 'a' parent, 'b' child FROM dual UNION ALL
       SELECT 'b','c' FROM dual UNION ALL
       SELECT 'd','e' FROM dual UNION ALL
       SELECT 'e','f' FROM dual UNION ALL
       SELECT 'f','g' FROM dual UNION ALL
       SELECT 'f','h' FROM dual)
SELECT
    parent,
    child,
    CONNECT_BY_ROOT child AS init_child,
    LEVEL,
    CONNECT_BY_ISLEAF
FROM
    test_hierarchy
WHERE
    CONNECT_BY_ISLEAF = 1 
START WITH
    child IN ( 'c', 'b', 'e', 'f', 'h', 'g') and
    child not in (select parent from test_hierarchy)
CONNECT BY
    PRIOR parent = child;

P C I      LEVEL CONNECT_BY_ISLEAF
- - - ---------- -----------------
a b c          2                 1
d e g          3                 1
d e h          3                 1
person Marmite Bomber    schedule 31.01.2019
comment
Marmite, спасибо за ваш эффективный запрос. Он имеет немного лучшую производительность по сравнению с моим подходом к аналитической функции. Поэтому я буду использовать ваш запрос в своем решении. Ваше здоровье! Я по-прежнему оставлю запрос @Lukas Eder в качестве принятого ответа, поскольку он ответил именно на то, что было задано в вопросе :) - person user613114; 01.02.2019
comment
@user613114 user613114 спасибо, у меня нет проблем с ответом на что-то, немного отличающееся от того, что вы спросили;) - person Marmite Bomber; 01.02.2019