如何在BigQuery中筛选键组合相同但TS与EFF_DT均不同的表记录

针对你需要筛选SK和NO相同，且TS与EFF_DT均不同的记录（排除仅TS不同、EFF_DT相同的组），这里提供两种高效的BigQuery实现方式：

方法一：窗口函数分组统计（推荐百万级数据使用） #

通过窗口函数先统计每个SK+NO组合下不同EFF_DT的数量，保留数量大于1的组的所有记录——这类组中必然存在TS和EFF_DT均不同的记录，符合筛选要求。

WITH grouped_stats AS (
  SELECT
    *,
    -- 统计当前SK+NO组内不同EFF_DT的数量
    COUNT(DISTINCT EFF_DT) OVER (PARTITION BY SK, NO) AS distinct_eff_dt_count
  FROM
    `你的项目ID.数据集名称.表名称` -- 替换为你的实际表路径
)
SELECT
  SK, NO, NAME, DOB, TS, EFF_DT, SOURCE
FROM
  grouped_stats
WHERE
  distinct_eff_dt_count > 1;

方法二：EXISTS子查询（逻辑更直观） #

直接检查每条记录是否存在同SK+NO的其他记录，满足TS不同且EFF_DT不同，存在则保留该记录。

SELECT
  t1.*
FROM
  `你的项目ID.数据集名称.表名称` t1
WHERE
  EXISTS (
    SELECT 1
    FROM `你的项目ID.数据集名称.表名称` t2
    WHERE
      t2.SK = t1.SK
      AND t2.NO = t1.NO
      AND t2.TS != t1.TS
      AND t2.EFF_DT != t1.EFF_DT
  );

方案说明 #

• 百万级数据优先选方法一：窗口函数只需对表做一次全量扫描，性能更优；
• 方法二逻辑更直白，但对于大数据量，关联查询的开销会高于窗口函数。

测试你的示例数据时，两种方法都会返回SK=123的两条记录，排除SK=456的两条记录，完全符合期望输出。

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