使用SELECT DISTINCT仍存在重复ID，寻求SQL去重解决方案

为什么SELECT DISTINCT没生效？ #

DISTINCT是对所有选中列的组合去重，不是单独针对ID列。如果同一个ID对应不同的createdatetime、groupid或其他列值，这些行会被判定为不同记录，所以ID仍会重复出现。

方案1：用窗口函数保留每个ID的唯一行（推荐） #

如果同一个ID有多条记录，你可以指定保留某一条（比如最新创建的记录），用ROW_NUMBER()窗口函数实现：

,signal as (
    SELECT *
    FROM (
        SELECT
            rtw.identifiersk
            ,rtw.createdatetime
            ,lcd.personid
            ,pi.ID
            ,lmd.groupid
            ,lmd.groupname
            ,rtw.typecode AS "TC"
            ,lmmf.date
            ,CASE 
                WHEN rtw.typecode LIKE 'PRO%' THEN 'Prospect'
                WHEN rtw.typecode LIKE 'CA' THEN  'Cali'
                WHEN rtw.typecode LIKE '%CT%' OR a.description LIKE '%team%' THEN 'Conn'
            END AS Type
            -- 按ID分组，按创建时间倒序排，给每个ID的行编号
            ,ROW_NUMBER() OVER (PARTITION BY pi.ID ORDER BY rtw.createdatetime DESC) AS rn
        FROM traditional_winner rtw
        INNER JOIN milestone_fact lmmf ON lmmf.identifierdimsk = rtw.identifierdimsk
        INNER JOIN milestone_dim lmd ON lmd.milestonesk = lmmf.milestonesk
        LEFT JOIN attributes a ON a.identifierdimsk = rtw.identifierdimsk
        LEFT JOIN identifier_dim lid ON lid.identifierdimsk = rtw.identifierdimsk
        LEFT JOIN client_dim lcd ON lcd.identifierdimsk = rtw.identifierdimsk
        LEFT JOIN (
            SELECT party_id as personid, padditional_id as ID 
            FROM identifier_current pi
            WHERE pi.party_id = 'AccountId'
              AND party_id IS NOT NULL 
              AND party_id <> '' 
            GROUP BY party_id, padditional_id
        ) as pi ON lcd.personid = pi.personid
        -- 把日期条件移到WHERE，避免LEFT JOIN变成INNER JOIN的效果
        WHERE lmmf.date >= '2022-10-25'
    ) t
    -- 只保留每个ID的第一条记录（这里是最新的）
    WHERE rn = 1
)

注：原SQL里有两处笔误已修正：

• 子查询别名是pi，原代码写的pai.ID改为pi.ID
• 引用la.description但无对应表，改为a.description（对应LEFT JOIN的attributes a）

方案2：按ID分组聚合其他列 #

如果不需要保留所有列的细节，可通过GROUP BY聚合其他列，确保每个ID只出现一次：

,signal as (
    SELECT
        MAX(rtw.identifiersk) AS identifiersk
        ,MAX(rtw.createdatetime) AS createdatetime
        ,MAX(lcd.personid) AS personid
        ,pi.ID
        ,MAX(lmd.groupid) AS groupid
        ,MAX(lmd.groupname) AS groupname
        ,MAX(rtw.typecode) AS "TC"
        ,MAX(lmmf.date) AS date
        ,MAX(CASE 
            WHEN rtw.typecode LIKE 'PRO%' THEN 'Prospect'
            WHEN rtw.typecode LIKE 'CA' THEN  'Cali'
            WHEN rtw.typecode LIKE '%CT%' OR a.description LIKE '%team%' THEN 'Conn'
        END) AS Type
    FROM traditional_winner rtw
    INNER JOIN milestone_fact lmmf ON lmmf.identifierdimsk = rtw.identifierdimsk
    INNER JOIN milestone_dim lmd ON lmd.milestonesk = lmmf.milestonesk
    LEFT JOIN attributes a ON a.identifierdimsk = rtw.identifierdimsk
    LEFT JOIN identifier_dim lid ON lid.identifierdimsk = rtw.identifierdimsk
    LEFT JOIN client_dim lcd ON lcd.identifierdimsk = rtw.identifierdimsk
    LEFT JOIN (
        SELECT party_id as personid, padditional_id as ID 
        FROM identifier_current pi
        WHERE pi.party_id = 'AccountId'
          AND party_id IS NOT NULL 
          AND party_id <> '' 
        GROUP BY party_id, padditional_id
    ) as pi ON lcd.personid = pi.personid
    WHERE lmmf.date >= '2022-10-25'
    -- 按ID分组，强制每个ID只返回一行
    GROUP BY pi.ID
)

额外排查点 #

• 确认identifier_current表的party_id = 'AccountId'是否正确？通常party_id应该是具体账号ID值，而非字符串'AccountId'，笔误会导致子查询返回空，进而引发后续重复问题
• 检查traditional_winner与关联表的关联逻辑，是否因多对多关联（比如一个ID对应多个里程碑记录）导致重复行

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