BigQuery SQL去重问题：如何过滤无效重复记录仅保留有效行

原有SQL问题根因 #

• 你当前的INNER JOIN逻辑仅将每组的最大Total值关联到原表，但没有增加过滤条件筛选原表中Total等于分组最大值的行，因此同分组下的所有行都会匹配到分组聚合结果，最终重复返回所有行。
• 额外注意你原SQL中子查询a的表路径是project_id.backup_dataset.capacity_assortment，聚合子查询的表路径是kohls-bda-prd.backup_dataset.capacity_assortment，如果两个项目ID不一致也可能导致数据匹配异常，建议统一使用同一张表路径。

正确查询方案 #

方案1：基于原有JOIN逻辑修正 #

在关联条件中新增Total值匹配逻辑，仅保留有效行：

SELECT a.*
FROM (
    SELECT * 
    FROM `kohls-bda-prd.backup_dataset.capacity_assortment` 
    WHERE DATE(lod_tmst)='2021-11-09'
) a
INNER JOIN (
    SELECT 
        OFC_NBR,
        YEAR,
        DEPT_ID,
        MONTH,
        MAX(Total) AS tot_capc
    FROM `kohls-bda-prd.backup_dataset.capacity_assortment` 
    WHERE DATE(lod_tmst)='2021-11-09'
    GROUP BY OFC_NBR,YEAR,DEPT_ID,MONTH
) b 
ON a.OFC_NBR = b.OFC_NBR 
AND a.YEAR = b.YEAR
AND a.DEPT_ID = b.DEPT_ID
AND a.MONTH = b.MONTH
AND a.Total = b.tot_capc

方案2：更高效的窗口函数写法（推荐） #

仅需一次表扫描即可实现需求，性能更优：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(
            PARTITION BY OFC_NBR, YEAR, DEPT_ID, MONTH 
            ORDER BY Total DESC
        ) AS rn
    FROM `kohls-bda-prd.backup_dataset.capacity_assortment`
    WHERE DATE(lod_tmst)='2021-11-09'
)
SELECT * EXCEPT(rn)
FROM ranked_data
WHERE rn = 1

如果同分组下存在多条Total等于最大值的行需要全部保留，将ROW_NUMBER()替换为RANK()即可

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