基于IDA与IDB关联关系的分组及指定日期IDA计数查询需求

解决思路 #

这个问题的核心是找出IDB的连通分组——也就是所有通过IDA间接关联的IDB要归为同一组，这属于典型的图连通分量问题，用SQL的递归CTE就能解决。之后按分组统计指定日期的唯一IDA数量，再把统计结果关联回每个IDA即可。

完整实现（以PostgreSQL为例） #

假设你的数据表名为ida_idb_rel，包含IDA、IDB、asofdate三个字段。

1. 递归生成IDB连通分组 #

先用递归CTE把所有间接关联的IDB归为同一组：

WITH RECURSIVE idb_groups AS (
    -- 初始化：每个IDB先作为单独分组
    SELECT 
        IDB AS group_id,
        IDB AS member_id
    FROM ida_idb_rel
    GROUP BY IDB
    UNION
    -- 递归扩展：把通过IDA关联的其他IDB加入当前分组
    SELECT 
        g.group_id,
        r.IDB AS member_id
    FROM idb_groups g
    JOIN ida_idb_rel r1 ON g.member_id = r1.IDB
    JOIN ida_idb_rel r ON r1.IDA = r.IDA
    WHERE r.IDB NOT IN (SELECT member_id FROM idb_groups WHERE group_id = g.group_id)
),
-- 去重，确保每个IDB只属于一个分组（取最小的group_id作为分组标识）
unique_idb_groups AS (
    SELECT 
        member_id AS IDB,
        MIN(group_id) AS group_id
    FROM idb_groups
    GROUP BY member_id
)

2. 统计分组内指定日期的唯一IDA数 #

基于上面的分组，统计2024-12-13当天的唯一IDA数量：

, group_ida_stats AS (
    SELECT 
        ug.group_id,
        COUNT(DISTINCT r.IDA) AS target_ida_count
    FROM unique_idb_groups ug
    JOIN ida_idb_rel r ON ug.IDB = r.IDB
    WHERE r.asofdate = '2024-12-13'
    GROUP BY ug.group_id
)

3. 关联回原表，给每个IDA绑定统计结果 #

最后把统计数关联到每个IDA，确保每个IDA只返回一条结果：

SELECT 
    DISTINCT r.IDA,
    gs.target_ida_count
FROM ida_idb_rel r
JOIN unique_idb_groups ug ON r.IDB = ug.IDB
JOIN group_ida_stats gs ON ug.group_id = gs.group_id;

整合后的完整SQL #

WITH RECURSIVE idb_groups AS (
    SELECT 
        IDB AS group_id,
        IDB AS member_id
    FROM ida_idb_rel
    GROUP BY IDB
    UNION
    SELECT 
        g.group_id,
        r.IDB AS member_id
    FROM idb_groups g
    JOIN ida_idb_rel r1 ON g.member_id = r1.IDB
    JOIN ida_idb_rel r ON r1.IDA = r.IDA
    WHERE r.IDB NOT IN (SELECT member_id FROM idb_groups WHERE group_id = g.group_id)
),
unique_idb_groups AS (
    SELECT 
        member_id AS IDB,
        MIN(group_id) AS group_id
    FROM idb_groups
    GROUP BY member_id
),
group_ida_stats AS (
    SELECT 
        ug.group_id,
        COUNT(DISTINCT r.IDA) AS target_ida_count
    FROM unique_idb_groups ug
    JOIN ida_idb_rel r ON ug.IDB = r.IDB
    WHERE r.asofdate = '2024-12-13'
    GROUP BY ug.group_id
)
SELECT 
    DISTINCT r.IDA,
    gs.target_ida_count
FROM ida_idb_rel r
JOIN unique_idb_groups ug ON r.IDB = ug.IDB
JOIN group_ida_stats gs ON ug.group_id = gs.group_id;

适配其他数据库的注意事项 #

• MySQL 8.0+：语法和PostgreSQL基本一致，支持WITH RECURSIVE
• SQL Server：同样支持WITH RECURSIVE，但子查询里的NOT IN可以换成NOT EXISTS提升性能
• Oracle：不支持WITH RECURSIVE，需要用CONNECT BY语法来实现连通分量查找，逻辑类似但语法不同

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