Сгладить Иерархию Списка Смежности До Списка Всех Путей

У меня есть таблица, которая хранит иерархическую информацию, используя модель списка смежности. (использует самореферентный ключ-пример ниже. Эта таблица может выглядеть знакомо):

category_id name                 parent
----------- -------------------- -----------
1           ELECTRONICS          NULL
2           TELEVISIONS          1
3           TUBE                 2
4           LCD                  2
5           PLASMA               2
6           PORTABLE ELECTRONICS 1
7           MP3 PLAYERS          6
8           FLASH                7
9           CD PLAYERS           6
10          2 WAY RADIOS         6

Каков наилучший метод "сгладить" вышеприведенные данные в нечто подобное? 

category_id lvl1        lvl2        lvl3        lvl4
----------- ----------- ----------- ----------- -----------
1           1           NULL        NULL        NULL
2           1           2           NULL        NULL
6           1           6           NULL        NULL
3           1           2           3           NULL
4           1           2           4           NULL
5           1           2           5           NULL
7           1           6           7           NULL
9           1           6           9           NULL
10          1           6           10          NULL
8           1           6           7           8

Каждая строка-это один "путь" через иерархию, за исключением строки для каждого узла (а не только для каждого листового узла). Столбец category_id представляет текущий node и столбцы " lvl " являются его предками. Значение для текущего узла также должно находиться в самом дальнем правом столбце lvl. Значение в столбце lvl1 всегда будет представлять корневой узел, значения в столбце lvl2 всегда будут представлять прямые потомки lvl1 и т. д.

Если возможно, метод для генерации этого вывода будет в SQL и будет работать для n-уровневых иерархий.

4 8
sql hierarchy flatten

4 ответа:

Для выполнения многоуровневых запросов по простому списку смежности неизменно требуется самосоединение слева. Легко сделать таблицу, выровненную по правому краю: 

SELECT category.category_id,
    ancestor4.category_id AS lvl4,
    ancestor3.category_id AS lvl3,
    ancestor2.category_id AS lvl2,
    ancestor1.category_id AS lvl1
FROM categories AS category
    LEFT JOIN categories AS ancestor1 ON ancestor1.category_id=category.category_id
    LEFT JOIN categories AS ancestor2 ON ancestor2.category_id=ancestor1.parent
    LEFT JOIN categories AS ancestor3 ON ancestor3.category_id=ancestor2.parent
    LEFT JOIN categories AS ancestor4 ON ancestor4.category_id=ancestor3.parent;

Выровнять его по левому краю, как в вашем примере, немного сложнее. Это приходит на ум: 

SELECT category.category_id,
    ancestor1.category_id AS lvl1,
    ancestor2.category_id AS lvl2,
    ancestor3.category_id AS lvl3,
    ancestor4.category_id AS lvl4
FROM categories AS category
    LEFT JOIN categories AS ancestor1 ON ancestor1.parent IS NULL
    LEFT JOIN categories AS ancestor2 ON ancestor1.category_id<>category.category_id AND ancestor2.parent=ancestor1.category_id
    LEFT JOIN categories AS ancestor3 ON ancestor2.category_id<>category.category_id AND ancestor3.parent=ancestor2.category_id
    LEFT JOIN categories AS ancestor4 ON ancestor3.category_id<>category.category_id AND ancestor4.parent=ancestor3.category_id
WHERE
    ancestor1.category_id=category.category_id OR
    ancestor2.category_id=category.category_id OR
    ancestor3.category_id=category.category_id OR
    ancestor4.category_id=category.category_id;

Будет работать для n-уровневых иерархий.

Извините, запросы произвольной глубины невозможны в модели списка смежности. Если вы часто выполняете такие запросы, вам следует изменить схему на одну из других моделей хранения иерархической информации : полное отношение смежности (хранение всех отношений предок-потомок), материализованный путь или вложенные множества.

Если категории не перемещаются много (что обычно имеет место для магазина, подобного вашему примеру), я бы склонялся к вложенным наборам.

11

Как уже упоминалось, SQL не имеет чистого способа реализации таблиц с динамически изменяющимся числом столбцов. Единственные два решения, которые я использовал раньше:: 1. Фиксированное число Самосоединяется, давая фиксированное число столбцов (согласно BobInce) 2. Сгенерируйте результаты в виде строки в одном столбце

Второй изначально звучит гротескно; хранение идентификаторов в виде строки?! Но когда выходные данные форматируются как XML или что-то еще, люди, кажется, не так сильно возражают.

Равным образом, это имеет очень большое значение. мало пользы, если вы затем хотите присоединиться к результатам в SQL. Если результат должен быть предоставлен приложению, он может быть очень подходящим. Лично, однако, я предпочитаю делать выравнивание в приложении, а не SQL


Я застрял здесь на 10-дюймовом экране без доступа к SQL, поэтому я не могу дать тестируемый код, но основной метод будет использовать рекурсию каким-то образом;
- Рекурсивная скалярная функция может сделать это
- MS SQL может сделать это с помощью рекурсивного с утверждение (более эффективное)

Скалярная функция (что-то вроде):

CREATE FUNCTION getGraphWalk(@child_id INT)
RETURNS VARCHAR(4000)
AS
BEGIN

  DECLARE @graph VARCHAR(4000)

  -- This step assumes each child only has one parent
  SELECT
    @graph = dbo.getGraphWalk(parent_id)
  FROM
    mapping_table
  WHERE
    category_id = @child_id
    AND parent_id IS NOT NULL

  IF (@graph  IS NULL)
    SET @graph = CAST(@child_id AS VARCHAR(16))
  ELSE
    SET @graph = @graph + ',' + CAST(@child_id AS VARCHAR(16))

  RETURN @graph

END


SELECT
  category_id                         AS [category_id],
  dbo.getGraphWalk(category_id)       AS [graph_path]
FROM
  mapping_table
ORDER BY
  category_id

Я уже давно не использую рекурсивный С, но я дам синтаксис идти, хотя у меня нет SQL здесь, чтобы проверить что-нибудь:)

Рекурсивно с 

WITH
  result (
    category_id,
    graph_path
  )
AS
(
  SELECT
    category_id,
    CAST(category_id AS VARCHAR(4000))
  FROM
    mapping_table
  WHERE
    parent_id IS NULL

  UNION ALL

  SELECT
    mapping_table.category_id,
    CAST(result.graph_path + ',' + CAST(mapping_table.category_id AS VARCHAR(16)) AS VARCHAR(4000))
  FROM
    result
  INNER JOIN
    mapping_table
      ON result.category_id = mapping_table.parent_id
)

SELECT
  *
FROM
  result
ORDER BY
  category_id


EDIT-вывод для обоих одинаков: 

1   '1'
2   '1,2'
3   '1,2,3'
4   '1,2,4'
5   '1,2,5'
6   '1,6'
7   '1,6,7'
8   '1,6,7,8'
9   '1,6,9'
8

Обход дерева произвольной глубины обычно включает рекурсивный процедурный код, если только вы не используете специальные возможности некоторых СУБД.

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

Если вы используете вложенные наборы, левый порядковый номер предоставит вам порядок посещения узлов.

1

Фактически может быть сделано с помощью динамического SQL в рамках процедуры stores. Затем вы становитесь ограничены тем, что может быть сделано с помощью хранимой процедуры. Очевидно, что это становится проблемой, чтобы выполнить результаты во временную таблицу, не зная, сколько столбцов ожидать. Однако, если цель состоит в том, чтобы вывести на веб-страницу или другой пользовательский интерфейс, то это может стоить усилий...

0