BigQuery如何筛选id对应所有value均为'p'的记录

原有SQL错误原因 #

你的SQL存在两个核心问题：

• 内层子查询提前过滤了value = 'p'的行，相当于直接丢弃了同id下非p的记录，统计得到的count(distinct value)只要该id存在至少1个p就会等于1，导致不符合要求的id也被纳入筛选范围
• 外层查询没有加value过滤逻辑，就算内层id筛选正确，也会带出同id下非p的行

方案1：子查询筛选符合条件的id #

先通过分组确认该id下所有行的value均为p，再关联原表取全量符合要求的行：

SELECT id, value
FROM `ggg.tt.table`
WHERE id IN (
  SELECT id
  FROM `ggg.tt.table`
  GROUP BY id
  -- 确保该id下没有任何非p的value
  HAVING COUNTIF(TRIM(value) != 'p') = 0
)

执行后返回结果与预期完全一致：id=2的1行、id=6的2行p。

方案2：窗口函数实现（大表性能更优） #

数据量较大时用窗口函数仅需单次扫表，性能优于子查询方案：

SELECT id, value
FROM (
  SELECT
    id,
    value,
    -- 按id分组统计该分组下非p值的数量
    COUNTIF(TRIM(value) != 'p') OVER(PARTITION BY id) AS non_p_count
  FROM `ggg.tt.table`
)
WHERE non_p_count = 0

如果后续需要每个id仅返回一行去重结果，在外层SELECT后加DISTINCT即可。

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