Search code examples
sql-servergraphsql-server-2019subgraphsql-graph

SQL Server Graph: how identify subgraphs?

I have [Friends] node and [Link] edge. My task is to figure-out which friend to which subgraph(network) belongs. Simple illustration of this: enter image description here

Here is my code how I construct nodes, edges and populate the data:

--  Construct nodes
DROP TABLE IF EXISTS [dbo].[Friend];
CREATE  TABLE [dbo].[Friend] (
    [FriendId] INT IDENTITY PRIMARY KEY
,   [Name] NVARCHAR(256) NOT NULL   
) AS NODE;

INSERT INTO [dbo].[Friend]([Name]) VALUES 
    ('Jon'), ('Rita'), ('Ben'), ('Andrew'), ('Joe'), ('Patrick')
,   ('Mike'), ('Thomas'),   ('Kelly'), ('Lily'), ('Kira'); 

--  Construct edges
DROP TABLE IF EXISTS [dbo].[Link];
CREATE TABLE [dbo].[Link] (
    [LinkDirection] BIT NOT NULL
,   [FriendIdFrom] INT NOT NULL
,   [FriendIdTo] INT NOT NULL
)AS EDGE;

INSERT INTO [dbo].[Link] ($from_id, $to_id, [LinkDirection], [FriendIdFrom], [FriendIdTo]) 
--  Network 1
            SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Rita'
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Ben'
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Andrew'
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Joe'
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Patrick'
--  Network 2
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Thomas'
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Kelly'
--  Network 3
UNION ALL   SELECT [f1].$node_id, [f2].$node_id, 1, [f1].[FriendId], [f2].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Lily' AND [f2].[Name] = 'Kira'

--  To have bi-directional link

--  Network 1
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Rita'
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Ben'
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Jon' AND [f2].[Name] = 'Andrew'
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Joe'
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Andrew' AND [f2].[Name] = 'Patrick'
--  Network 2
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Thomas'
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Mike' AND [f2].[Name] = 'Kelly'
--  Network 3
UNION ALL   SELECT [f2].$node_id, [f1].$node_id, 0, [f2].[FriendId], [f1].[FriendId] FROM [dbo].[Friend] [f1] INNER JOIN [dbo].[Friend] [f2] ON [f1].[Name] = 'Lily' AND [f2].[Name] = 'Kira';

I have code, how to list all possible paths:

SELECT  
    [StartNode] = [f1].[Name]
,   [FinalNode] = LAST_VALUE([f2].[name]) WITHIN GROUP (GRAPH PATH)
,   [Steps] = COUNT([f2].[FriendId]) WITHIN GROUP (GRAPH PATH)
,   [Path] = [f1].[Name] + ' => ' + STRING_AGG([f2].[Name],' => ') WITHIN GROUP (GRAPH PATH) 
,   [Network] = '???'
--, *       
    FROM
        [dbo].[Friend] [f1]
    ,   [dbo].[Friend] FOR PATH [f2]
    ,   [dbo].[Link] FOR PATH [l]
    WHERE 
            MATCH(SHORTEST_PATH(f1(-(l)->f2)+));

But, I have big struggle how to add network identifier here(unique number, text, doesn't matters) :( Maybe someone has previous experience, and can help?

Expected result is:

---------------------------------------------------------------------------------
|StartNode   | FinalNode  | Steps  | Path                               |Network|
|------------| -----------| -------| -----------------------------------|-------|
|Jon         | Rita       | 1      | Jon => Rita                        |1      |
|Jon         | Ben        | 1      | Jon => Ben                         |1      |
|Jon         | Andrew     | 1      | Jon => Andrew                      |1      |
|Rita        | Jon        | 1      | Rita => Jon                        |1      |
|Ben         | Jon        | 1      | Ben => Jon                         |1      |
|Andrew      | Jon        | 1      | Andrew => Jon                      |1      |
|Andrew      | Joe        | 1      | Andrew => Joe                      |1      |
|Andrew      | Patrick    | 1      | Andrew => Patrick                  |1      |
|Joe         | Andrew     | 1      | Joe => Andrew                      |1      |
|Patrick     | Andrew     | 1      | Patrick => Andrew                  |1      |
|Mike        | Thomas     | 1      | Mike => Thomas                     |2      |
|Mike        | Kelly      | 1      | Mike => Kelly                      |2      |
|Thomas      | Mike       | 1      | Thomas => Mike                     |2      |
|Kelly       | Mike       | 1      | Kelly => Mike                      |2      |
|Lily        | Kira       | 1      | Lily => Kira                       |3      |
|Kira        | Lily       | 1      | Kira => Lily                       |3      |
|Jon         | Jon        | 2      | Jon => Andrew => Jon               |1      |
|Jon         | Joe        | 2      | Jon => Andrew => Joe               |1      |
|Jon         | Patrick    | 2      | Jon => Andrew => Patrick           |1      |
|Rita        | Rita       | 2      | Rita => Jon => Rita                |1      |
|Rita        | Ben        | 2      | Rita => Jon => Ben                 |1      |
|Rita        | Andrew     | 2      | Rita => Jon => Andrew              |1      |
|Ben         | Rita       | 2      | Ben => Jon => Rita                 |1      |
|Ben         | Ben        | 2      | Ben => Jon => Ben                  |1      |
|Ben         | Andrew     | 2      | Ben => Jon => Andrew               |1      |
|Andrew      | Rita       | 2      | Andrew => Jon => Rita              |1      |
|Andrew      | Ben        | 2      | Andrew => Jon => Ben               |1      |
|Andrew      | Andrew     | 2      | Andrew => Jon => Andrew            |1      |
|Joe         | Jon        | 2      | Joe => Andrew => Jon               |1      |
|Joe         | Joe        | 2      | Joe => Andrew => Joe               |1      |
|Joe         | Patrick    | 2      | Joe => Andrew => Patrick           |1      |
|Patrick     | Jon        | 2      | Patrick => Andrew => Jon           |1      |
|Patrick     | Joe        | 2      | Patrick => Andrew => Joe           |1      |
|Patrick     | Patrick    | 2      | Patrick => Andrew => Patrick       |1      |
|Mike        | Mike       | 2      | Mike => Thomas => Mike             |2      |
|Thomas      | Thomas     | 2      | Thomas => Mike => Thomas           |2      |
|Thomas      | Kelly      | 2      | Thomas => Mike => Kelly            |2      |
|Kelly       | Thomas     | 2      | Kelly => Mike => Thomas            |2      |
|Kelly       | Kelly      | 2      | Kelly => Mike => Kelly             |2      |
|Lily        | Lily       | 2      | Lily => Kira => Lily               |3      |
|Kira        | Kira       | 2      | Kira => Lily => Kira               |3      |
|Rita        | Joe        | 3      | Rita => Jon => Andrew => Joe       |1      |
|Rita        | Patrick    | 3      | Rita => Jon => Andrew => Patrick   |1      |
|Ben         | Joe        | 3      | Ben => Jon => Andrew => Joe        |1      |
|Ben         | Patrick    | 3      | Ben => Jon => Andrew => Patrick    |1      |
|Joe         | Rita       | 3      | Joe => Andrew => Jon => Rita       |1      |
|Joe         | Ben        | 3      | Joe => Andrew => Jon => Ben        |1      |
|Patrick     | Rita       | 3      | Patrick => Andrew => Jon => Rita   |1      |
|Patrick     | Ben        | 3      | Patrick => Andrew => Jon => Ben    |1      |
---------------------------------------------------------------------------------

Shortly saying, I need way how to give these networks unique names, and map with persons.

Solution 
  • select *, 
    min([FinalNode]) over(partition by [StartNode]) as NetworkOf,
    ----in case of duplicate names, also use partition by StartNodeId instead of [StartNode]... 
    min(FinalFriendId) over(partition by [StartNode]) as NetworkId 
from
(
SELECT  
    [StartNode] = [f1].[Name]
,   [FinalNode] = LAST_VALUE([f2].[name]) WITHIN GROUP (GRAPH PATH)
,   [Steps] = COUNT([f2].[FriendId]) WITHIN GROUP (GRAPH PATH)
,   [Path] = [f1].[Name] + ' => ' + STRING_AGG([f2].[Name],' => ') WITHIN GROUP (GRAPH PATH) 
,   FinalFriendId = LAST_VALUE(f2.FriendId) WITHIN GROUP (GRAPH PATH) 
     
    FROM
        [dbo].[Friend] [f1]
    ,   [dbo].[Friend] FOR PATH [f2]
    ,   [dbo].[Link] FOR PATH [l]
    WHERE 
            MATCH(SHORTEST_PATH(f1(-(l)->f2)+))
) as src;