按结果划分的SQL组- Salesforce营销云

Question

我正在寻找所有重复记录，然后选择所有重复减去最古老的记录从每一组，以便我可以删除重复和保存一个唯一的记录。

当我运行这个查询时，我得到了我想要的结果。留给我一个独特的电子邮件地址和最古老的创建日期.

SELECT 
    EmailAddress,
    MIN(CreatedDate)
FROM [_ListSubscribers]
WHERE EmailAddress IN
    (
        SELECT EmailAddress
        FROM _ListSubscribers
        GROUP BY EmailAddress
        HAVING COUNT(EmailAddress) > 1
    )
GROUP BY EmailAddress

当我将SubscriberKey添加到查询中时， 的结果加倍！为什么？我只想看到SubscriberKey绑定到我找到的在子查询中有最古老日期的EmailAddress。

SELECT 
    EmailAddress,
    SubscriberKey,
    MIN(CreatedDate)
FROM [_ListSubscribers]
WHERE EmailAddress IN
    (
        SELECT EmailAddress
        FROM _ListSubscribers
        GROUP BY EmailAddress
        HAVING COUNT(EmailAddress) > 1
    )
GROUP BY EmailAddress, SubscriberKey

Answer 1

您得到了多个记录，因为您是按SubscriberKey分组的。您需要通过EmailAddress和CreatedDate进行匹配。尝试执行一个子查询并将其加入到原来的表中。

select 
[_ListSubscribers].EmailAddress,
[_ListSubscribers].SubscriberKey,
[_ListSubscribers].CreatedDate,
from
(
SELECT 
    EmailAddress,
    MIN(CreatedDate) as CreatedDate
    FROM [_ListSubscribers]
    GROUP BY EmailAddress, SubscriberKey
    Having count(EmailAddress)>1
) SubTbl
inner join
[_ListSubscribers] on
[_ListSubscribers].EmailAddress = SubTbl.EmailAddress
and
[_ListSubscribers].CreatedDate = SubTbl.CreatedDate

Answer 2

，我正在寻找所有重复的记录，然后选择所有重复，减去每组中最古老的记录，这样我就可以删除重复记录并保存一个唯一的记录。

使用ROW_NUMBER()

select l.*
from (select l.*,
             row_number() over (partition by EmailAddress order by CreatedDate desc) as seqnum
      from _ListSubscribers l
     ) l
where seqnum > 1;

但是，如果要删除除最新记录之外的所有记录，可以使用：

delete from _ListSubscribers
    where CreatedDate < (select max(CreatedDate)
                         from _ListSubscribers l2
                         where l2.EmailAddress = _ListSubscribers.EmailAddress
                        );

如果您想要最古老的记录，您将使用min()而不是max()翻转逻辑。