SQL Server: как получить данные из низшей иерархии в таблице иерархий?

ProgramBox

SQL Server: как получить данные из низшей иерархии в таблице иерархий?

Post author:admin
Запись опубликована:19 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server #sql-server-2005 #tsql #sql-server-2008

#sql #sql-сервер #sql-server-2005 #tsql #sql-server-2008

Вопрос:

У меня есть иерархическая таблица, подобная этой

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EX_TABLE](
    [PARENT_OBJ] [nvarchar](255) NOT NULL,
    [PARENT_OBJ_TYPE] [nvarchar](64) NOT NULL,
    [DESCEN_OBJ] [nvarchar](255) NOT NULL,
    [DESCEN_OBJ_TYPE] [nvarchar](64) NOT NULL,
    [DESCEN_OBJ_USAGE] [nvarchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'batch_name', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'print', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'batch_run_id', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'db_name', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'repo_name', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'WF_Batch_name_1', N'WF', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'table_attr', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'WF_Batch_name_1', N'WF', N'DF_Batch_name_1', N'DF', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'WF_Batch_name_1', N'WF', N'DF_Batch_name_1_2', N'DF', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'TABLE_1', N'Table', N'Source')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'Query', N'Transform', N'Transform')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'sysdate', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'TABLE_2', N'Table', N'Target')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'TABLE_2', N'Table', N'Key')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'DS_NAME', N'Ds', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'Key', N'Trans', N'Trans')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'TABLE_1', N'Table', N'Source')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'sysdate', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'TABLE_3', N'Table', N'Target')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'Key', N'Trans', N'Trans')

Идея состоит в том, чтобы пройти по дереву вверх от DF к WF и к пакету. В этом примере Batch_name_1 имеет много дочерних объектов (DESCEN_OBJ), но интересен только дочерний элемент WF ( WF_Batch_name_1 ). WF_Batch_name_1 также имеет дочерние объекты (DESCEN_OBJ), которые являются объектами DF ( DF_Batch_name_1 , DF_Batch_name_1_2 ).

DF_Batch_name_1 и DF_Batch_name_1_2 также имеют дочерние объекты, но меня интересуют только table объекты (из DESCEN_OBJ_TYPE), которые имеют DESCEN_OBJ_USAGE в качестве цели.

Batch всегда является самым высоким элементом в этом дереве, а DF — самым низким, но между этими двумя могут быть и другие элементы. Убедитесь, что это только подмножество реальных данных.

Итак, как я могу запросить различное количество целевых таблиц (DESCEN_OBJ_USAGE) (DESCEN_OBJ_TYPE) для Batch_name_1 (PARENT_OBJ). В этом случае результат должен быть равен 2 (с реальными данными это не дает правильного результата, когда у меня много пакетов (самый высокий элемент в иерархии), поэтому необходим переход вверх к пакетам).:

 SELECT COUNT(distinct descen_obj) as dobj FROM EX_TABLE
WHERE DESCEN_OBJ_TYPE = 'Table' and DESCEN_OBJ_USAGE = 'Target'

Иерархия представлена в поле DESCEN_OBJ. Можно ли этого достичь с помощью одного запроса (рекурсивный CTE?) без временных таблиц? Любые советы по этому поводу были бы весьма признательны!

Ответ №1:

Я не уверен, что понимаю ваш вопрос, но, возможно, вы ищете это?

 with tree as (
   select parent_obj,
          parent_obj_type,
          descen_obj, 
          descen_obj_usage,
          descen_obj_type,
          1 as level
   from ex_table
   where parent_obj = 'Batch_name_1'

   union all

   select e.parent_obj,
          e.parent_obj_type,
          e.descen_obj,
          e.descen_obj_usage,
          e.descen_obj_type,
          t.level   1
   from ex_table e
      join tree t on e.parent_obj = t.descen_obj
) 
select *
from tree
where descen_obj_type = 'Table' 
  and descen_obj_usage = 'Target'

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

1. Я отредактировал свой ответ, дайте мне знать, если это то, что вы ищете (он возвращает то, что вы ожидаете, с вашими образцами данных)

2. Большое вам спасибо! Изменив значение last select на это значение select COUNT (distinct descen_obj) как c, я получаю именно то, что хочу!!!

Ответ №2:

Если вы хотите получить все дочерние элементы DF для Batch_name_1, и их можно идентифицировать через [PARENT_OBJ_TYPE] = ‘DF’, и между ними неизвестное количество уровней / элементов, тогда я мог бы предложить использовать некоторые временные таблицы, подобные этой:

 -- Creating a temporary table where we will store all found members from the lowest level
CREATE TABLE #DF (
    [DESCEN_OBJ_TYPE] [nvarchar](64) NOT NULL,
    [DESCEN_OBJ_USAGE] [nvarchar](20) NULL
)

CREATE TABLE #DESCEN (
    [DESCEN_OBJ] [nvarchar](255) NOT NULL
)

-- First we get the initial top layer
SELECT *
INTO #PARENTS
FROM [dbo].[EX_TABLE]
WHERE [PARENT_OBJ] = 'Batch_name_1'

-- Loop running as long as there are children
WHILE EXISTS (SELECT * FROM #PARENTS)
BEGIN

    -- Storing away the DF levelled members
    INSERT INTO #DF ([DESCEN_OBJ_TYPE],[DESCEN_OBJ_USAGE])
    SELECT [DESCEN_OBJ_TYPE],[DESCEN_OBJ_USAGE]
    FROM #PARENTS WHERE [PARENT_OBJ_TYPE] = 'DF'

    INSERT INTO #DESCEN ([DESCEN_OBJ])
    SELECT DISTINCT [DESCEN_OBJ]
    FROM #PARENT WHERE [PARENT_OBJ_TYPE] <> 'DF'

    -- Clearing the parents table since we are going to fill it with the next layer
    TRUNCATE TABLE #PARENTS
    INSERT INTO #PARENTS
    SELECT ex.*
    FROM #DESCEN de
    INNER JOIN [dbo].[EX_TABLE] ex ON ex.[PARENT_OBJ] = de.[DESCEN_OBJ]

    TRUNCATE TABLE #DESCEN

END

-- Finally outputting
SELECT DISTINCT * FROM #DF

DROP TABLE #PARENTS
DROP TABLE #DESCEN
DROP TABLE #DF

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

Я не тестировал код, и в нем может быть какая-то ошибка, но я надеюсь, что вы понимаете концепцию.

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

Ответ №3:

Это то, о чем вы говорите?

 -- flatten hierarchy while keeping top-level ID
WITH all_descendants(top_parent_obj, middle_obj, descen_obj) AS (
    SELECT parent_obj, descen_obj, descen_obj
      FROM dbo.EX_TABLE
     WHERE PARENT_OBJ_TYPE = 'Batch'
     UNION ALL
    SELECT p.top_parent_obj, c.DESCEN_OBJ, c.DESCEN_OBJ
      FROM all_descendants p
     INNER JOIN dbo.EX_TABLE c
             ON p.middle_obj = c.PARENT_OBJ
)
-- show distinct usages by top-level ID
SELECT d.top_parent_obj, o.DESCEN_OBJ_USAGE
  FROM all_descendants d
 INNER JOIN dbo.EX_TABLE o
         ON d.descen_obj = o.DESCEN_OBJ
 GROUP BY d.top_parent_obj, o.DESCEN_OBJ_USAGE

Результат таков:

 Batch_name_1    NULL
Batch_name_1    Key
Batch_name_1    Source
Batch_name_1    Target
Batch_name_1    Trans
Batch_name_1    Transform