如何使用标准SQL借助递归CTE模拟图查询MATCH模式匹配功能

完全可以通过标准SQL的递归CTE实现图模式匹配能力，无需依赖SQL Server专属的MATCH子句，核心思路是先标准化图数据建模，再基于递归逻辑封装通用查询能力，具体实现步骤如下：

第一步：统一图数据基础建模 #

先将所有节点、边数据抽象到统一结构中，避免为不同实体单独建表，为通用匹配做底层支撑：

-- 统一节点表：存储所有类型的图节点
CREATE TABLE graph_nodes (
    node_id BIGINT PRIMARY KEY,
    node_type VARCHAR(50) NOT NULL, -- 节点类型，比如Person/Supplier/Product/Category
    properties JSON NOT NULL -- 存储节点所有属性，比如name/companyName等
);

-- 统一边表：存储所有类型的关联关系
CREATE TABLE graph_edges (
    edge_id BIGINT PRIMARY KEY,
    from_node_id BIGINT NOT NULL REFERENCES graph_nodes(node_id),
    to_node_id BIGINT NOT NULL REFERENCES graph_nodes(node_id),
    edge_type VARCHAR(50) NOT NULL, -- 关系类型，比如friend/supply/belong_to_category
    properties JSON NOT NULL -- 存储关系属性
);

第二步：递归CTE实现通用路径匹配逻辑 #

递归逻辑分为三个核心部分：

1. 锚点成员：匹配路径的起始节点，过滤节点类型
2. 递归成员：按传入的方向、边类型、节点类型要求，逐次匹配后续节点和边
3. 终止条件：路径长度等于传入的模式要求长度

通用匹配逻辑示例：

WITH RECURSIVE graph_path AS (
    -- 锚点：匹配起始节点
    SELECT 
        1 AS path_step,
        ARRAY[gn.node_id] AS node_ids,
        ARRAY[gn.node_type] AS node_types,
        ARRAY[]::BIGINT[] AS edge_ids,
        ARRAY[]::VARCHAR[] AS edge_types,
        gn.properties AS start_node_props
    FROM graph_nodes gn
    WHERE gn.node_type = :start_node_type -- 传入参数：起始节点类型
    UNION ALL
    -- 递归：逐段匹配路径
    SELECT
        gp.path_step + 1 AS path_step,
        gp.node_ids || next_node.node_id AS node_ids,
        gp.node_types || next_node.node_type AS node_types,
        gp.edge_ids || ge.edge_id AS edge_ids,
        gp.edge_types || ge.edge_type AS edge_types,
        gp.start_node_props
    FROM graph_path gp
    JOIN graph_edges ge 
        ON CASE :direction_rule[gp.path_step] -- 传入参数：每一步的边方向（OUT/IN/BOTH）
            WHEN 'OUT' THEN ge.from_node_id = gp.node_ids[array_upper(gp.node_ids,1)]
            WHEN 'IN' THEN ge.to_node_id = gp.node_ids[array_upper(gp.node_ids,1)]
            WHEN 'BOTH' THEN ge.from_node_id = gp.node_ids[array_upper(gp.node_ids,1)] OR ge.to_node_id = gp.node_ids[array_upper(gp.node_ids,1)]
        END
    JOIN graph_nodes next_node
        ON next_node.node_id = CASE :direction_rule[gp.path_step]
            WHEN 'OUT' THEN ge.to_node_id
            WHEN 'IN' THEN ge.from_node_id
            WHEN 'BOTH' THEN CASE WHEN ge.from_node_id = gp.node_ids[array_upper(gp.node_ids,1)] THEN ge.to_node_id ELSE ge.from_node_id END
        END
    WHERE 
        ge.edge_type = :edge_type_rule[gp.path_step] -- 传入参数：每一步的边类型要求
        AND next_node.node_type = :next_node_type_rule[gp.path_step] -- 传入参数：每一步的节点类型要求
        AND gp.path_step < :total_path_length -- 传入参数：路径总边数
)
-- 取匹配完成的完整路径
SELECT * FROM graph_path WHERE path_step = :total_path_length;

第三步：封装通用MATCH表函数 #

将上述递归逻辑封装为表函数，即可实现你需要的MATCH_TABLE_FUNCTION能力，支持传入节点类型、关系方向、关系类型等参数自动完成匹配，无需手动写JOIN逻辑，PostgreSQL语法示例如下：

CREATE OR REPLACE FUNCTION match_graph(
    start_node_type VARCHAR,
    edge_rules JSON, -- 数组参数：每一段边的类型、方向
    node_rules JSON -- 数组参数：每一个后续节点的类型
)
RETURNS TABLE (
    path_node_ids BIGINT[],
    path_node_props JSON[],
    path_edge_ids BIGINT[],
    path_edge_props JSON[]
) AS $$
-- 上述递归CTE逻辑嵌入此处，替换对应参数即可
$$ LANGUAGE sql;

第四步：需求场景验证 #

场景1：查找两位有共同好友的用户 #

匹配模式为Person1-(friend)->Person0<-(friend)-Person2，调用函数参数如下：

• 起始节点类型：Person
• 边规则：[{"type":"friend","direction":"OUT"}, {"type":"friend","direction":"IN"}]
• 节点规则：["Person", "Person"]
  返回结果中第1个节点为Person1、第2个节点为共同好友Person0、第3个节点为Person2，过滤掉Person1和Person2为同一人的结果即可完成需求。

场景2：查询每个供应商供应的食品所属分类 #

匹配模式为Supplier-->(:Product)-->(c:Category)，调用函数参数如下：

• 起始节点类型：Supplier
• 边规则：[{"type":"supply","direction":"OUT"}, {"type":"belong_to_category","direction":"OUT"}]
• 节点规则：["Product", "Category"]
  聚合查询即可得到对应结果：

SELECT 
    (path_node_props[1]->>'companyName') AS Company,
    ARRAY_AGG(DISTINCT path_node_props[3]->>'categoryName') AS Categories
FROM match_graph('Supplier', '[{"type":"supply","direction":"OUT"}, {"type":"belong_to_category","direction":"OUT"}]'::json, '["Product", "Category"]'::json)
GROUP BY Company;

性能优化建议 #

• 固定长度的路径匹配可以分支优化为非递归的多表关联逻辑，查询性能更高
• 为node_type、edge_type、from_node_id、to_node_id建联合索引，可以大幅提升查询速度
• 支持可变长度路径匹配时，只需新增最大、最小路径长度参数，在递归终止条件中加入对应判断即可

内容的提问来源于stack exchange，提问作者David542