Google BigQuery中SQL排除特定字母开头字符串失效问题解决

你的问题出在多数组交叉UNNEST导致的笛卡尔积，以及过滤逻辑的定位偏差：

为什么计数反而增加？ #

你当前的SQL对description_localized、claims_localized、cpc三个数组做了交叉展开，这会生成大量笛卡尔积行。比如一个专利有2个英文描述、3个英文权利要求、4个CPC代码，就会产生2×3×4=24行数据。

添加SUBSTRING(cpc_unnest.code,1,1) <> 'C'时，你只是过滤掉了CPC代码以C开头的单行数据，但如果某个专利同时有C开头和非C开头的CPC代码，非C的行依然会被保留。如果你的数据中，大部分包含C开头CPC的专利同时拥有更多非C开头的CPC代码，那么过滤后保留的行数反而可能超过原来包含C行的总数，导致计数上升。

正确的过滤方式分两种场景： #

场景1：只想排除CPC代码为C开头的行（保留其他行） #

如果这确实是你的需求，当前语法本身没问题，计数变化是数据分布导致的正常结果。你可以通过分组统计验证数据分布：

-- 统计不同CPC首字母对应的行数
SELECT SUBSTRING(cpc_unnest.code,1,1) AS cpc_first_char, COUNT(*)
FROM `patents-public-data.patents.publications`,
unnest(description_localized) as description_unnest,
unnest(claims_localized) as claims_unnest,
unnest(cpc) as cpc_unnest
WHERE description_unnest.language IN ('en','EN')
AND claims_unnest.language IN ('en','EN')
AND publication_date > 19900101
GROUP BY cpc_first_char
ORDER BY COUNT(*) DESC;

查看C开头的行数和其他非C开头的行数总和，就能明确计数变化的原因。

场景2：排除所有包含至少一个C开头CPC的专利（整个专利不统计） #

这应该是你真正的需求，此时需要先过滤专利，再做UNNEST，避免笛卡尔积干扰：

SELECT COUNT(*)
FROM `patents-public-data.patents.publications`,
UNNEST(description_localized) AS description_unnest,
UNNEST(claims_localized) AS claims_unnest
WHERE description_unnest.language IN ('en','EN')
AND claims_unnest.language IN ('en','EN')
AND publication_date > 19900101
AND NOT EXISTS (
  SELECT 1 FROM UNNEST(cpc) c
  WHERE SUBSTRING(c.code,1,1) = 'C'
);

这种写法先判断专利是否包含C开头的CPC，若包含则整个专利被排除，不会生成任何行，计数会符合预期减少。

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