用于图表的SQL查询

Graph can be represented using vertex and edge table .

可以使用顶点和边表来表示图。

Vertex holds nodes details and edge holds the relationship .

顶点保存节点细节，边缘保持关系。

    VERTEX                        EDGE
    ------                       ----------------------------
     ID                           ID    |  Source   | Target
    ------                       ----------------------------
     A                            1        A            B
     B                            2        A            B
     C                            3        B            C
     D                            4        B            D
     E                            5        D            B
                                  6        A            D
                                  7        B            A
                                  8        E            A

Graph :

图表：

I am trying to get direct neighbor of each node using SQL query .

我试图使用SQL查询获得每个节点的直接邻居。

 OUTPUT
---------------------------
ID    | COUNT
---------------------------
 A        3
 B        3
 C        1
 D        2
 E        1

Explanation :

说明：

A has connection to E , D , B

A与E，D，B连接

B has connection to C, A, D

B与C，A，D连接

5 个解决方案

#1

To add this variant:

要添加此变体：

select source, count(distinct target) 
from
    (select  source, target 
    from edge e_out
    union all
    select  target, source -- note the switch of src/trgt columns
    from edge e_in)
group by source
order by source;

#2

Please check following SQL query giving the exact result you are looking for

请检查以下SQL查询，提供您要查找的确切结果

select
    Vertex.ID,
    Count(Neighbour) as "Count"
from GraphVertex as Vertex
left join (
select
    v.ID,
    e.Target as Neighbour
from GraphVertex as v
inner join GraphEdge as e
    on v.ID = e.Source

union

select
    v.ID,
    e.Source as Neighbour
from GraphVertex as v
inner join GraphEdge as e
    on v.ID = e.Target
) as Neighbours
    on Vertex.ID = Neighbours.ID
group by Vertex.ID
order by Vertex.ID;

#3

although you have named your tables as Vertex and Edge all solutions provided here seem to be SQLScript solutions instead of a Graph language solution.

虽然您已将表命名为Vertex和Edge，但此处提供的所有解决方案似乎都是SQLScript解决方案而不是Graph语言解决方案。

I tried to create a Graph database solution for your request, but I had a few difficulties that I had to overcome.

我试图为您的请求创建一个Graph数据库解决方案，但我遇到了一些必须克服的困难。

First, since I am new to Graph I could not find a direct solution. You know Edge's are directed records. But your question wants total number of neighbors in both directions. So I had to dublicate the rows by cross-changing the source and target columns.

首先，因为我是Graph的新手，所以找不到直接的解决方案。你知道Edge是有针对性的记录。但是你的问题想要两个方向的邻居总数。所以我不得不通过交叉更改源列和目标列来对行进行公开。

Then I used following procedure

然后我用了以下程序

CREATE PROCEDURE graphProcedureSample2(in ID varchar(2), out cnt int)
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
    Graph g = Graph("A00077387", "SAMPLEGRAPHWORKSPACE");
    cnt = 0;
    Vertex v = Vertex(:g, :ID);
    Multiset<Vertex> neighbors = NEIGHBORS (:g, :v, 1, 1);
    Multiset<Edge> edges = EDGES (:g, :v, :neighbors);
    cnt = INT(COUNT(:neighbors));
END;

But above code works only for a single vertex.

但上面的代码只适用于单个顶点。

I had to create HANA database cursor and loop through all vertices then call this SP for each.

我必须创建HANA数据库游标并遍历所有顶点，然后为每个顶点调用此SP。

I have stored the results in a temp table and got the result you requested by querying this temp table.

我已将结果存储在临时表中，并通过查询此临时表获得您请求的结果。

I hope it helps,

我希望它有所帮助，

#4

You can use the following query:

您可以使用以下查询：

SELECT CASE WHEN Source < Target THEN Source ELSE Target END AS Node1,
       CASE WHEN Source >= Target THEN Source ELSE Target END AS Node2
FROM EDGE

to get:

要得到：

Node1   Node2
-------------
A       B
A       B
B       C
B       D
B       D
A       D
A       B
A       E

This is a simplified, stripped down, version of the EDGE table, that contains only node connection information.

这是EDGE表的简化版本，仅包含节点连接信息。

You can further strip down using DISTINCT:

您可以使用DISTINCT进一步删除：

SELECT DISTINCT
       CASE WHEN Source < Target THEN Source ELSE Target END AS Node1,
       CASE WHEN Source >= Target THEN Source ELSE Target END AS Node2
FROM EDGE

so that duplicates are removed:

以便删除重复项：

Node1   Node2
-------------
A       B
A       D
A       E
B       C
B       D

You can now wrap the above query in a CTE, unpivot and then count:

您现在可以将上述查询包装在CTE中，取消忽略然后计数：

;WITH SimpleEDGE AS (
    SELECT DISTINCT
           CASE WHEN Source < Target THEN Source ELSE Target END AS Node1,
           CASE WHEN Source >= Target THEN Source ELSE Target END AS Node2
    FROM EDGE
)
SELECT Node, COUNT(*) AS cnt
FROM (
    SELECT Node1 AS Node
    FROM SimpleEDGE

    UNION ALL

    SELECT Node2 AS Node
    FROM SimpleEDGE
) AS t
GROUP BY Node

Output:

输出：

Node    cnt
-----------
A       3
B       3
C       1
D       2
E       1

#5

select a, count(*) as connections from
(
    select a, b from
    (
        select Source a, Target b from EDGE
        union
        select Target a, Source b from EDGE
    ) group by a,b
) group by a

#1