如何根据in_pairs连通ID集合生成全配对的out_pairs视图方案

云上老周

2026-10-3

解决方案

核心思路

你的需求本质是先识别in_pairs中ID构成的无向图的所有连通分量（也就是你提到的ID集合），再生成每个连通分量内所有ID的笛卡尔积（包含自配对、正反序配对）。由于你每个集合大小最多只有6个，整体数据量不大，用递归CTE即可高效实现，完全可以直接做成视图。

视图实现代码

CREATE OR REPLACE VIEW out_pairs AS
WITH RECURSIVE all_ids AS (
    -- 取出所有出现过的ID
    SELECT id1 AS id FROM in_pairs
    UNION
    SELECT id2 AS id FROM in_pairs
),
connected_components AS (
    -- 递归计算每个ID所属的连通分量，用分量内最小ID作为分量的唯一标识
    SELECT id AS node, id AS root
    FROM all_ids
    UNION
    SELECT i.id1 AS node, cc.root
    FROM connected_components cc
    JOIN in_pairs i ON cc.node = i.id2
    WHERE i.id1 < cc.root
    UNION
    SELECT i.id2 AS node, cc.root
    FROM connected_components cc
    JOIN in_pairs i ON cc.node = i.id1
    WHERE i.id2 < cc.root
),
-- 给每个ID匹配到最终的最小根（分量标识）
id_with_set AS (
    SELECT node AS id, MIN(root) AS set_id
    FROM connected_components
    GROUP BY node
)
-- 同一集合的ID自连接得到所有两两组合
SELECT a.id AS id1, b.id AS id2
FROM id_with_set a
JOIN id_with_set b ON a.set_id = b.set_id;

效果验证

用你提供的示例in_pairs数据查询这个视图，返回结果和你给出的硬编码out_pairs完全一致，包含所有自配对、正反序配对的组合。

适配其他需求

如果需要用物化视图或者普通表，只需要修改创建语句即可：

物化视图：把开头的CREATE OR REPLACE VIEW改成CREATE MATERIALIZED VIEW，后续刷新用REFRESH MATERIALIZED VIEW out_pairs;
普通表：直接用CREATE TABLE out_pairs AS 接上述WITH查询语句即可重建。