如何仅可视化层次结构中节点的后代，祖先和自身? [英] How to visualize only the descendants, ancestors, and self of a node in a hierarchy?

问题描述

在另一个问题中，我问有关可视化的问题SQL Server数据库中的表中存储的层次结构数据.我找到了一种使用 GraphViz 可视化整个层次结构的方法，并且在T-SQL和Powershell中有一些功能. /p>

我想使用像这样的可视化来调试使用类似数据的应用程序.可视化所有内容对于小型示例层次结构来说都很好.但是在成千上万的层次结构中，这是压倒性的.

调试应用程序时，通常只查看与给定节点相关的一小部分节点.目前，对于给定节点而言，与我无关的唯一相关节点是后代和祖先，以及节点本身.

因此，我想要一种仅可视化层次结构中的节点的方法，这些节点是给定节点的后代，祖先或自身.

以下语句按照链接的问题创建示例数据库和表.

CREATE DATABASE HierarchyTest;
GO

USE HierarchyTest;
GO

CREATE TABLE NodeHierarchy (
  PK_NodeID INT NOT NULL
    CONSTRAINT PK_NodeHierarchy PRIMARY KEY,
  FK_ParentNodeID INT NULL
    CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY
      REFERENCES NodeHierarchy(PK_NodeID),
  Name NVARCHAR(255) NOT NULL
);

以下语句使用国家，城市和场所的层次结构的修改版本填充表.英国现在是根节点，并且有更多的节点可以代表著名的英语场所.

INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name)
VALUES
  (1, 18, N'Scotland'),
  (2, 1, N'Glasgow'),
  (3, 1, N'Edinburgh'),
  (4, 1, N'St Andrews'),
  (5, 2, N'The Barrowlands'),
  (6, 2, N'The Cathouse'),
  (7, 2, N'Carling Academy'),
  (8, 2, N'SECC'),
  (9, 2, N'King Tut''s Wah-Wah Hut'),
  (10, 3, N'Henry''s Cellar Bar'),
  (11, 3, N'The Bongo Club'),
  (12, 3, N'Sneaky Pete''s'),
  (13, 3, N'The Picture House'),
  (14, 3, N'Potterrow'),
  (15, 4, N'Aikman''s'),
  (16, 4, N'The Union'),
  (17, 4, N'Castle Sands'),
  (18, NULL, N'United Kingdom'),
  (19, 15, N'Upstairs'),
  (20, 15, N'Downstairs'),
  (21, 16, N'Venue 1'),
  (22, 16, N'Venue 2'),
  (23, 18, N'England'),
  (24, 23, N'Manchester'),
  (25, 24, N'Apollo Theatre'),
  (26, 18, N'Liverpool'),
  (27, 26, N'Cavern Club');

下图是链接的问题中列出的Powershell脚本generate-graph.ps1的输出.如果Stack Overflow尺寸缩小的版本看起来丑陋，请查看全尺寸图片

我只想看看圣安德鲁斯的后代与祖先之间的关系.该图包含许多与这些关系无关的信息，因此较难阅读.当我将层次结构扩展到覆盖全球城市和场所的数千个节点时，完整的可视化几乎变得毫无用处.

在 Freemind 中，我画了一个粗略的示意图代替查看:

如何仅提取与圣安德鲁斯相关的数据，以便将其提供给GraphViz?

解决方案

我不认为您在这里使用工会，这是一种更简单的方法:

declare @nodeid int, @parentID int
select @nodeid = PK_NodeID, @parentID = FK_ParentNodeID
    from NodeHierarchy where name = 'St Andrews'

select PK_NodeID, FK_ParentNodeID, Name
    from NodeHierarchy 
    where PK_NodeID in (@nodeid, @parentID)
    or FK_ParentNodeID = @nodeid

当然，您可以将其放在表函数中以使其通用.

In another question I asked about visualizing hierarchical data stored in a table in an SQL Server database. I found a way to visualize the whole hierarchy using GraphViz, with some plumbing in T-SQL and Powershell.

I want to use a visualization like this to debug an application that uses similar data. Visualizing everything is fine for the small example hierarchy. But in a hierarchy of thousands, this is overwhelming.

When I debug my application, I usually look at only a small set of nodes related to a given node. Currently, the only related nodes that matter to me for a given node are the descendants and the ancestors, as well as the node itself.

So, I want a way to visualize only the nodes in the hierarchy that are the descendants, ancestors, or self of a given node.

The following statements create the example database and table as in the linked question.

CREATE DATABASE HierarchyTest;
GO

USE HierarchyTest;
GO

CREATE TABLE NodeHierarchy (
  PK_NodeID INT NOT NULL
    CONSTRAINT PK_NodeHierarchy PRIMARY KEY,
  FK_ParentNodeID INT NULL
    CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY
      REFERENCES NodeHierarchy(PK_NodeID),
  Name NVARCHAR(255) NOT NULL
);

The following statement populates the table with a modified version of the hierarchy of countries, cities, and venues. The United Kingdom is now the root node, and there are more nodes to represent famous English venues.

INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name)
VALUES
  (1, 18, N'Scotland'),
  (2, 1, N'Glasgow'),
  (3, 1, N'Edinburgh'),
  (4, 1, N'St Andrews'),
  (5, 2, N'The Barrowlands'),
  (6, 2, N'The Cathouse'),
  (7, 2, N'Carling Academy'),
  (8, 2, N'SECC'),
  (9, 2, N'King Tut''s Wah-Wah Hut'),
  (10, 3, N'Henry''s Cellar Bar'),
  (11, 3, N'The Bongo Club'),
  (12, 3, N'Sneaky Pete''s'),
  (13, 3, N'The Picture House'),
  (14, 3, N'Potterrow'),
  (15, 4, N'Aikman''s'),
  (16, 4, N'The Union'),
  (17, 4, N'Castle Sands'),
  (18, NULL, N'United Kingdom'),
  (19, 15, N'Upstairs'),
  (20, 15, N'Downstairs'),
  (21, 16, N'Venue 1'),
  (22, 16, N'Venue 2'),
  (23, 18, N'England'),
  (24, 23, N'Manchester'),
  (25, 24, N'Apollo Theatre'),
  (26, 18, N'Liverpool'),
  (27, 26, N'Cavern Club');

The following image is the output of Powershell script generate-graph.ps1 listed in the linked question. If the Stack Overflow reduced-size version looks ugly, look at the full-size image.

I want to see only how St Andrews' descendants and ancestors relate to it. The diagram contains a lot of information irrelevant to these relationships, and so is harder to read. When I scale my hierarchy up to thousands of nodes covering cities and venues globally, a full visualization becomes almost useless.

In Freemind I drew a crude diagram of what I would like to see instead:

How do I extract only the data that is relevant to St Andrews so I can give it to GraphViz?

解决方案

I don't think you heed to use union here, it is a way more simple:

declare @nodeid int, @parentID int
select @nodeid = PK_NodeID, @parentID = FK_ParentNodeID
    from NodeHierarchy where name = 'St Andrews'

select PK_NodeID, FK_ParentNodeID, Name
    from NodeHierarchy 
    where PK_NodeID in (@nodeid, @parentID)
    or FK_ParentNodeID = @nodeid

Of course, you can put it in a table function to make it general.

这篇关于如何仅可视化层次结构中节点的后代，祖先和自身?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！