基于关联ref合并用户并统计其合并资产总量

核心需求回顾 #

• 同一人可拥有多个用户账号，通过ref1/ref2关联（两者无重叠，值互不交叉）。
• 合并所有同属一个关联组的用户资产，统计总数及资产ID列表。
• 若资产对应的用户不在用户表中，将资产owner填入ref2，同时统计其资产。

实现SQL #

WITH user_association AS (
    -- 建立用户与关联标识的映射：分别关联ref1和ref2
    SELECT
        user_id,
        ref1 AS assoc_ref,
        ref1,
        ref2
    FROM users
    WHERE ref1 IS NOT NULL
    UNION ALL
    SELECT
        user_id,
        ref2 AS assoc_ref,
        ref1,
        ref2
    FROM users
    WHERE ref2 IS NOT NULL
),
user_group AS (
    -- 统一关联组的ref1/ref2标识，确保同组用户共享同一组标识
    SELECT
        user_id,
        assoc_ref,
        MAX(ref1) OVER (PARTITION BY assoc_ref) AS group_ref1,
        MAX(ref2) OVER (PARTITION BY assoc_ref) AS group_ref2
    FROM user_association
)
SELECT
    -- 填充结果的ref1：仅当组来自ref1时赋值
    CASE WHEN ug.group_ref1 IS NOT NULL THEN ug.group_ref1 END AS ref1,
    -- 填充结果的ref2：组来自ref2时赋值，缺失用户则用asset.owner
    CASE 
        WHEN ug.group_ref2 IS NOT NULL THEN ug.group_ref2
        ELSE a.owner::TEXT 
    END AS ref2,
    COUNT(a.asset_id) AS asset_count,
    ARRAY_AGG(a.asset_id ORDER BY asset_id) AS asset_ids
FROM assets a
LEFT JOIN user_group ug ON a.owner = ug.user_id
GROUP BY 
    COALESCE(ug.assoc_ref, a.owner::TEXT),
    ug.group_ref1,
    ug.group_ref2
ORDER BY COALESCE(ug.assoc_ref, a.owner::TEXT);

方案说明 #

1. user_association CTE：将每个用户的ref1和ref2分别映射为关联键assoc_ref，确保共享同一ref的用户归为同一组。
2. user_group CTE：通过窗口函数MAX()统一每个关联组的ref1/ref2值，避免同组用户出现多个标识。
3. 主查询：
   • 关联资产表与用户组映射，处理用户缺失的边界场景（将owner赋值给ref2）。
   • 按关联组分组，统计资产总数并聚合资产ID列表。

可行性说明 #

该方案基于标准SQL语法，支持PostgreSQL、MySQL 8.0+、SQL Server等主流数据库，完全在数据库层实现需求，无需额外代码处理。

内容的提问来源于stack exchange，提问作者Bjørn Bråthen