如何筛选含特定子串且排除含另一特定子串的数据行？

核心思路 #

BigQuery 支持直接在条件中使用 NOT CONTAINS_SUBSTR() 排除包含指定子串的记录，无需新增额外WITH子表即可区分仅接种麻疹、仅接种脊髓灰质炎和两者均接种的记录。

方案1：保留现有WITH结构，统计仅麻疹接种数据 #

如果想维持当前代码结构，只需在WHERE条件中添加排除脊髓灰质炎疫苗的逻辑：

WITH measles_only_table AS
(SELECT
*
FROM
---- 替换为你的原始表名
WHERE
CONTAINS_SUBSTR(vaccine, "MEASLES") 
AND NOT CONTAINS_SUBSTR(vaccine, "POLIO") -- 排除接种过脊髓灰质炎的记录
AND date_of_report>="2023-05-01"
)
SELECT
region,
COUNT(number) as total_measles_only,
COUNTIF(serious="NO") as non_serious_total, -- 修正拼写错误：seious → serious
COUNTIF(serious="YES") as serious_total
FROM
measles_only_table
GROUP BY
region

方案2：无需WITH子表，一次性统计全部分类（更高效） #

若希望直接输出所有维度的统计结果，可在主查询中用COUNTIF嵌套条件，完全避免额外子表：

SELECT
region,
-- 仅接种麻疹
COUNTIF(CONTAINS_SUBSTR(vaccine, "MEASLES") AND NOT CONTAINS_SUBSTR(vaccine, "POLIO") AND date_of_report>="2023-05-01") as total_measles_only,
-- 仅接种脊髓灰质炎
COUNTIF(CONTAINS_SUBSTR(vaccine, "POLIO") AND NOT CONTAINS_SUBSTR(vaccine, "MEASLES") AND date_of_report>="2023-05-01") as total_polio_only,
-- 两者均接种
COUNTIF(CONTAINS_SUBSTR(vaccine, "MEASLES") AND CONTAINS_SUBSTR(vaccine, "POLIO") AND date_of_report>="2023-05-01") as total_both,
-- 仅麻疹的严重/非严重统计
COUNTIF(CONTAINS_SUBSTR(vaccine, "MEASLES") AND NOT CONTAINS_SUBSTR(vaccine, "POLIO") AND date_of_report>="2023-05-01" AND serious="NO") as measles_non_serious,
COUNTIF(CONTAINS_SUBSTR(vaccine, "MEASLES") AND NOT CONTAINS_SUBSTR(vaccine, "POLIO") AND date_of_report>="2023-05-01" AND serious="YES") as measles_serious,
-- 仅脊髓灰质炎的严重/非严重统计
COUNTIF(CONTAINS_SUBSTR(vaccine, "POLIO") AND NOT CONTAINS_SUBSTR(vaccine, "MEASLES") AND date_of_report>="2023-05-01" AND serious="NO") as polio_non_serious,
COUNTIF(CONTAINS_SUBSTR(vaccine, "POLIO") AND NOT CONTAINS_SUBSTR(vaccine, "MEASLES") AND date_of_report>="2023-05-01" AND serious="YES") as polio_serious
FROM
---- 替换为你的原始表名
GROUP BY
region

关键说明 #

• NOT CONTAINS_SUBSTR() 是BigQuery支持的合法语法，可直接用于排除目标子串
• 方案2避免了多次子查询，执行效率更高，且能一次性输出所有需要的统计维度
• 注意修正原代码中的拼写错误：seious → serious

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