SQL查询异常求助：期望单条观测结果却输出多条

问题描述 #

执行SQL查询时，期望仅返回包含date_pulled、score、did三列的单条记录，但实际输出两条记录。推测问题由time字段存在重复的最新时间戳导致。

示例数据 #

id did  rid date_pulled             s created_at              date                    time          
1  123  123 2020-05-07 20:50:49.155 1 2020-05-07 20:50:49.155 2021-03-19 00:09:36.199 2023-04-11 07:48:18.692 
1  123  123 2020-05-07 20:50:49.155 1 2020-05-07 20:50:49.155 2021-03-19 00:09:36.199 2023-04-19 02:26:26.836
2  123  123 2020-05-07 20:50:49.818 2 2020-05-07 20:50:49.818 2021-03-19 00:09:36.199 2023-04-19 02:26:26.836
1  123  123 2020-05-07 20:50:49.155 1 2020-05-07 20:50:49.155 2021-03-19 00:09:36.199 2023-04-21 11:54:11.132
2  123  123 2020-05-07 20:50:49.818 2 2020-05-07 20:50:49.818 2021-03-19 00:09:36.199 2023-04-21 11:54:11.132

原SQL代码 #

select  date_pulled, score, did
from (
    select *,  max(time) over (partition by did) max_time
    from grs_datalake_prod_distill_lexis_nexis_db.credit_report_detail
)
where max_time = time

问题原因 #

原SQL通过max(time) over (partition by did)获取每个did的最新时间戳，但同一个did+最新time下存在多条不同记录（比如示例中did=123的最新time对应两条id不同、score不同的记录），因此过滤后仍会返回多条结果。

解决方案 #

方案1：使用ROW_NUMBER()确保仅返回单条记录 #

通过ROW_NUMBER()按did分区，先按time倒序取最新记录，再按辅助字段（如id）排序确保唯一，最后筛选行号为1的记录：

SELECT date_pulled, score, did
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY did ORDER BY time DESC, id ASC) AS rn
    FROM grs_datalake_prod_distill_lexis_nexis_db.credit_report_detail
) t
WHERE rn = 1

• 若需要选择score更大/更小的记录，可调整ORDER BY中的id ASC为score DESC或其他规则。

方案2：若允许去重（仅当相同did+最新time的date_pulled/score存在重复时适用） #

在原查询基础上添加DISTINCT关键字去重：

SELECT DISTINCT date_pulled, score, did
FROM (
    SELECT *,
           MAX(time) OVER (PARTITION BY did) AS max_time
    FROM grs_datalake_prod_distill_lexis_nexis_db.credit_report_detail
) t
WHERE max_time = time

注意：如果相同did+最新time的记录中date_pulled或score存在差异，该方案仍会返回多条记录，因此方案1更通用可靠。

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