为何两个SQL查询返回记录数不同？已定位问题原因

问题背景 #

测试两种用于识别某业务单元内每个订阅者最新发送记录的查询时，发现返回结果不一致：查询2比查询1多返回约3000条记录。已确认EventDate和SubscriberKey无空值，且不存在重复的SubscriberKey。

对比的查询语句 #

查询1 #

SELECT top 250000
    sent.SubscriberKey,
    sent.EventDate as LatestSend
FROM
    ent._Sent as sent
WHERE 
    sent.OYBAccountID = '[id]'
    AND sent.EventDate = (
        SELECT MAX (latest.EventDate) 
        FROM ent._Sent as latest 
        WHERE latest.SubscriberKey = sent.SubscriberKey
    )
ORDER BY LatestSend ASC

查询2 #

SELECT top 250000
    sent.SubscriberKey,
    MAX(sent.EventDate) as LatestSend
FROM
    ent._Sent as sent
WHERE 
    sent.OYBAccountID = '[id]'
GROUP BY
    sent.SubscriberKey
ORDER BY LatestSend ASC

差异原因 #

核心问题是查询1的子查询中缺失OYBAccountID的关联条件：

• 查询2逻辑直接：先筛选出当前业务单元的所有发送记录，再按SubscriberKey分组，取每个订阅者在该单元内的最新发送日期，因此能覆盖该业务单元内的所有订阅者。
• 查询1的子查询未限制OYBAccountID，会去整个ent._Sent表中查找该订阅者的全局最新发送日期。如果某个订阅者在其他业务单元有比当前单元内记录更新的发送记录，那么当前单元内的EventDate就不等于这个全局最大值，这条记录会被过滤；只有当订阅者在当前单元的发送记录就是他的全局最新记录时，才会被查询1返回。这就导致查询1漏掉了部分订阅者，记录数比查询2少。

修正后的查询1 #

在子查询中加入OYBAccountID的过滤条件，确保子查询只在当前业务单元内找最新记录：

SELECT top 250000
    sent.SubscriberKey,
    sent.EventDate as LatestSend
FROM
    ent._Sent as sent
WHERE 
    sent.OYBAccountID = '[id]'
    AND sent.EventDate = (
        SELECT MAX (latest.EventDate) 
        FROM ent._Sent as latest 
        WHERE latest.SubscriberKey = sent.SubscriberKey
        AND latest.OYBAccountID = sent.OYBAccountID -- 新增此条件
    )
ORDER BY LatestSend ASC

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