用户层级多设施重复数据的SQL去重及聚合计算校验需求

我来帮你搞定这个用户层级的重复数据去重和聚合校验问题～先理清楚场景：

现有数据集结构为：独立用户拥有多个设施，每个设施包含多个账户，每个账户对应多笔持仓。发现重复案例：user_ID='A'的facility_ID='1'（含account_ID='A'、'B'）与facility_ID='2'（含account_ID='C'、'D'）的账户数量、持仓金额总和及每笔持仓金额完全一致。需在用户层级实现重复数据去重，并完成聚合计算校验。

1. 第一步：识别重复的设施 #

要去重首先得精准定位哪些设施是重复的。我们可以通过计算每个设施的核心特征（账户数量、持仓总金额、所有持仓金额的有序集合），然后给同一用户下特征完全一致的设施标记排名，排名大于1的就是重复项。

用SQL实现的话是这样：

WITH facility_features AS (
    SELECT
        user_id,
        facility_id,
        -- 统计该设施下的账户数量
        COUNT(DISTINCT account_id) AS account_count,
        -- 计算该设施的持仓总金额
        SUM(holding_amount) AS total_holding,
        -- 把所有持仓金额按顺序聚合为数组，确保每笔持仓都完全匹配
        ARRAY_AGG(holding_amount ORDER BY holding_amount) AS holding_amounts
    FROM
        holdings
    JOIN accounts ON holdings.account_id = accounts.account_id
    JOIN facilities ON accounts.facility_id = facilities.facility_id
    GROUP BY
        user_id, facility_id
),
duplicate_facilities AS (
    SELECT
        user_id,
        facility_id,
        -- 同一用户下特征相同的设施，按ID排序后标记排名
        ROW_NUMBER() OVER (PARTITION BY user_id, account_count, total_holding, holding_amounts ORDER BY facility_id) AS duplicate_rank
    FROM
        facility_features
)
-- 筛选出所有重复的设施（排名>1的）
SELECT * FROM duplicate_facilities WHERE duplicate_rank > 1;

2. 第二步：去重后做用户层级的聚合校验 #

识别出重复设施后，我们只保留每个重复组里的第一个设施（你也可以根据需求保留最早/最晚创建的），然后做用户层级的聚合计算，和原始数据对比来验证去重是否正确。

SQL代码如下：

WITH facility_features AS (
    SELECT
        user_id,
        facility_id,
        COUNT(DISTINCT account_id) AS account_count,
        SUM(holding_amount) AS total_holding,
        ARRAY_AGG(holding_amount ORDER BY holding_amount) AS holding_amounts
    FROM
        holdings
    JOIN accounts ON holdings.account_id = accounts.account_id
    JOIN facilities ON accounts.facility_id = facilities.facility_id
    GROUP BY
        user_id, facility_id
),
unique_facilities AS (
    SELECT
        user_id,
        facility_id,
        account_count,
        total_holding
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY user_id, account_count, total_holding, holding_amounts ORDER BY facility_id) AS duplicate_rank
        FROM facility_features
    ) sub_query
    -- 只保留每个重复组里的第一个设施
    WHERE duplicate_rank = 1
)
-- 计算用户层级的聚合指标，用于校验
SELECT
    user_id,
    COUNT(facility_id) AS unique_facility_count,
    SUM(account_count) AS total_unique_accounts,
    SUM(total_holding) AS total_unique_holdings
FROM unique_facilities
GROUP BY user_id;

拿用户A的案例来说：原始数据里有2个重复设施，去重后unique_facility_count会变成1，total_unique_accounts从4变成2，total_unique_holdings会是原始总持仓的一半，这样就能直观验证去重结果是否符合预期。

3. 一些实用的注意事项 #

• 如果你的数据库不支持数组类型，可以把ARRAY_AGG换成STRING_AGG(holding_amount::TEXT, ',' ORDER BY holding_amount)，用有序拼接的字符串来匹配持仓集合。
• 要是想保留特定的设施（比如最早创建的），可以把ORDER BY facility_id改成按设施的创建时间字段排序（比如ORDER BY facility_created_at）。
• 校验阶段一定要对比去重前后的聚合数据，确保只是剔除了重复项，没有丢失有效数据。

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