SQL：修正ID与secondary ID匹配问题并按唯一ID汇总expenses

你的需求核心是合并同一客户的多组标识（ID/secondary ID），统一采用首次出现的标识，再按年度汇总支出。由于数据量达5000万条，方案需兼顾逻辑正确性与性能。以下是适配主流SQL数据库（MySQL 8.0+/Oracle/SQL Server）的实现步骤：

1. 核心思路 #

• 提取所有唯一的ID-secondary ID组合及首次出现日期，建立标识映射
• 递归关联所有属于同一客户的标识，形成客户簇
• 为每个客户簇选取首次出现的ID和secondary ID作为统一标识
• 将原始数据与统一标识关联，最终按统一标识+年度汇总支出

2. 完整SQL代码 #

-- 假设表名为expense_data，根据实际情况修改
WITH entity_mapping AS (
    -- 提取所有唯一的ID-secondary ID组合及首次出现日期
    SELECT 
        ID,
        secondary_ID,
        -- 日期转换函数按需替换：
        -- MySQL: STR_TO_DATE(date, '%b%Y')
        -- Oracle: TO_DATE(date, 'monYYYY')
        -- SQL Server: CONVERT(DATE, date, 100)
        MIN(TO_DATE(date, 'monYYYY')) AS first_appearance_date
    FROM expense_data
    GROUP BY ID, secondary_ID
),
recursive_clusters AS (
    -- 递归构建客户簇：从最早出现的标识组合开始，关联所有相关ID/secondary ID
    SELECT 
        ID,
        secondary_ID,
        first_appearance_date,
        CONCAT(ID, '-', secondary_ID) AS cluster_id
    FROM entity_mapping
    WHERE first_appearance_date = (SELECT MIN(first_appearance_date) FROM entity_mapping)
    
    UNION ALL
    
    SELECT 
        em.ID,
        em.secondary_ID,
        em.first_appearance_date,
        rc.cluster_id
    FROM entity_mapping em
    JOIN recursive_clusters rc 
        ON em.ID = rc.ID OR em.secondary_ID = rc.secondary_ID
    WHERE NOT EXISTS (
        SELECT 1 FROM recursive_clusters rc2 WHERE rc2.ID = em.ID AND rc2.secondary_ID = em.secondary_ID
    )
),
cluster_unified_ids AS (
    -- 为每个簇选取首次出现的ID和secondary ID作为统一标识
    SELECT 
        cluster_id,
        FIRST_VALUE(ID) OVER (PARTITION BY cluster_id ORDER BY first_appearance_date) AS unified_ID,
        FIRST_VALUE(secondary_ID) OVER (PARTITION BY cluster_id ORDER BY first_appearance_date) AS unified_secondary_ID
    FROM recursive_clusters
),
unified_expense_data AS (
    -- 将原始数据与统一标识关联，并提取年份
    SELECT 
        ed.*,
        cui.unified_ID,
        cui.unified_secondary_ID,
        EXTRACT(YEAR FROM TO_DATE(ed.date, 'monYYYY')) AS expense_year
    FROM expense_data ed
    JOIN cluster_unified_ids cui 
        ON ed.ID = cui.ID OR ed.secondary_ID = cui.secondary_ID
)
-- 最终按统一标识+年度汇总总支出
SELECT 
    unified_ID,
    unified_secondary_ID,
    expense_year,
    SUM(expenses) AS total_expenses
FROM unified_expense_data
GROUP BY unified_ID, unified_secondary_ID, expense_year
ORDER BY unified_ID, expense_year;

3. 性能优化建议 #

• 为ID、secondary_ID、date字段建立联合索引，大幅提升关联和分组效率
• 先抽取小批量数据（如1000条）测试SQL逻辑，确认正确后再执行全量查询
• 如果递归CTE性能不佳，可改用迭代式的UNION ALL多次关联，或借助临时表存储中间结果
• 若数据库支持，开启并行查询加速大数据量处理

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