如何用SQL创建视图实现多对多关联的客户礼品卡网络聚合？

需求分析 #

我们需要将客户与礼品卡的多对多关联表，聚合为连通组件形式：所有通过礼品卡互相关联的客户归为一组，同时列出该组关联的所有礼品卡账户号。

核心思路 #

这本质是图论中的连通分量问题：把客户和礼品卡视为节点，"客户使用礼品卡"的关系视为边，我们需要找出所有连通的节点集合，再按客户组聚合对应的客户ID和礼品卡号。

分数据库实现方案 #

假设原表名为customer_giftcard，字段为customer_id（客户ID）和gift_card_acct（礼品卡账户号）。

MySQL 实现 #

WITH RECURSIVE cte AS (
    -- 锚点：初始化客户和礼品卡节点，以客户ID作为初始组标识
    SELECT 
        customer_id AS node,
        customer_id AS group_id,
        'customer' AS type
    FROM customer_giftcard
    UNION
    SELECT 
        gift_card_acct AS node,
        customer_id AS group_id,
        'giftcard' AS type
    FROM customer_giftcard
    UNION
    -- 递归扩展：通过礼品卡关联其他客户，通过客户关联其他礼品卡
    SELECT 
        CASE WHEN c.type = 'customer' THEN g.gift_card_acct ELSE c2.customer_id END AS node,
        c.group_id,
        CASE WHEN c.type = 'customer' THEN 'giftcard' ELSE 'customer' END AS type
    FROM cte c
    LEFT JOIN customer_giftcard g ON c.type = 'customer' AND c.node = g.customer_id
    LEFT JOIN customer_giftcard c2 ON c.type = 'giftcard' AND c.node = c2.gift_card_acct
    WHERE (g.gift_card_acct IS NOT NULL OR c2.customer_id IS NOT NULL)
        AND CASE WHEN c.type = 'customer' THEN g.gift_card_acct ELSE c2.customer_id END NOT IN (SELECT node FROM cte WHERE group_id = c.group_id)
),
-- 统一每个客户的组标识（取组内最小的客户ID）
customer_groups AS (
    SELECT 
        customer_id,
        MIN(group_id) AS group_id
    FROM cte
    WHERE type = 'customer'
    GROUP BY customer_id
)
-- 创建视图
CREATE VIEW customer_giftcard_network AS
SELECT 
    GROUP_CONCAT(DISTINCT c.customer_id ORDER BY c.customer_id SEPARATOR ', ') AS `Customer IDs`,
    GROUP_CONCAT(DISTINCT g.gift_card_acct ORDER BY g.gift_card_acct SEPARATOR ';') AS `Gift Cards Acct Numbers`
FROM customer_groups cg
JOIN customer_giftcard c ON cg.customer_id = c.customer_id
JOIN customer_giftcard g ON cg.customer_id = g.customer_id
GROUP BY cg.group_id;

PostgreSQL 实现 #

WITH RECURSIVE cte AS (
    SELECT 
        customer_id AS node,
        customer_id AS group_id,
        'customer'::text AS type
    FROM customer_giftcard
    UNION
    SELECT 
        gift_card_acct AS node,
        customer_id AS group_id,
        'giftcard'::text AS type
    FROM customer_giftcard
    UNION
    SELECT 
        CASE WHEN c.type = 'customer' THEN g.gift_card_acct ELSE c2.customer_id END AS node,
        c.group_id,
        CASE WHEN c.type = 'customer' THEN 'giftcard' ELSE 'customer' END AS type
    FROM cte c
    LEFT JOIN customer_giftcard g ON c.type = 'customer' AND c.node = g.customer_id
    LEFT JOIN customer_giftcard c2 ON c.type = 'giftcard' AND c.node = c2.gift_card_acct
    WHERE (g.gift_card_acct IS NOT NULL OR c2.customer_id IS NOT NULL)
        AND CASE WHEN c.type = 'customer' THEN g.gift_card_acct ELSE c2.customer_id END NOT IN (SELECT node FROM cte WHERE group_id = c.group_id)
),
customer_groups AS (
    SELECT 
        customer_id,
        MIN(group_id) AS group_id
    FROM cte
    WHERE type = 'customer'
    GROUP BY customer_id
)
CREATE VIEW customer_giftcard_network AS
SELECT 
    STRING_AGG(DISTINCT c.customer_id, ', ' ORDER BY c.customer_id) AS "Customer IDs",
    STRING_AGG(DISTINCT g.gift_card_acct, ';' ORDER BY g.gift_card_acct) AS "Gift Cards Acct Numbers"
FROM customer_groups cg
JOIN customer_giftcard c ON cg.customer_id = c.customer_id
JOIN customer_giftcard g ON cg.customer_id = g.customer_id
GROUP BY cg.group_id;

SQL Server 实现（2017及以上版本） #

WITH RECURSIVE cte AS (
    SELECT 
        customer_id AS node,
        customer_id AS group_id,
        'customer' AS type
    FROM customer_giftcard
    UNION
    SELECT 
        gift_card_acct AS node,
        customer_id AS group_id,
        'giftcard' AS type
    FROM customer_giftcard
    UNION
    SELECT 
        CASE WHEN c.type = 'customer' THEN g.gift_card_acct ELSE c2.customer_id END AS node,
        c.group_id,
        CASE WHEN c.type = 'customer' THEN 'giftcard' ELSE 'customer' END AS type
    FROM cte c
    LEFT JOIN customer_giftcard g ON c.type = 'customer' AND c.node = g.customer_id
    LEFT JOIN customer_giftcard c2 ON c.type = 'giftcard' AND c.node = c2.gift_card_acct
    WHERE (g.gift_card_acct IS NOT NULL OR c2.customer_id IS NOT NULL)
        AND CASE WHEN c.type = 'customer' THEN g.gift_card_acct ELSE c2.customer_id END NOT IN (SELECT node FROM cte WHERE group_id = c.group_id)
),
customer_groups AS (
    SELECT 
        customer_id,
        MIN(group_id) AS group_id
    FROM cte
    WHERE type = 'customer'
    GROUP BY customer_id
)
CREATE VIEW customer_giftcard_network AS
SELECT 
    STRING_AGG(DISTINCT c.customer_id, ', ') WITHIN GROUP (ORDER BY c.customer_id) AS [Customer IDs],
    STRING_AGG(DISTINCT g.gift_card_acct, ';') WITHIN GROUP (ORDER BY g.gift_card_acct) AS [Gift Cards Acct Numbers]
FROM customer_groups cg
JOIN customer_giftcard c ON cg.customer_id = c.customer_id
JOIN customer_giftcard g ON cg.customer_id = g.customer_id
GROUP BY cg.group_id;

关键说明 #

1. 递归CTE的作用：遍历所有连通的客户和礼品卡节点，为每个连通组件分配统一的组标识。
2. 去重处理：聚合时使用DISTINCT避免重复的客户ID或礼品卡号（比如同一客户多次使用同一张卡的场景）。
3. 排序控制：聚合时指定ORDER BY保证输出字符串的顺序稳定，与示例格式一致。

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