Google BigQuery SQL：如何筛选仅感染病毒群体并统计数量

看起来你的查询遇到了两个核心问题：逻辑漏洞导致结果包含真菌感染用户，以及NOT IN子查询的性能问题引发崩溃，还有个容易被忽略的表名拼写错误。我来一步步帮你修复：

首先修正低级错误 #

你的查询里表名拼写不一致：dieases 和 dieseases，这会导致数据库找不到正确的表，或者扫描错误的数据集，先统一改成正确的表名（比如diseases）。

优化查询逻辑与性能 #

原查询用NOT IN子查询在大数据量下性能极差，很容易超时崩溃，而且如果子查询返回NULL值，NOT IN会直接返回空结果。推荐两种更可靠的方案：

方案1：用LEFT JOIN排除真菌感染用户 #

这种方式通过左连接匹配存在真菌感染记录的用户，然后过滤掉这些用户，性能比NOT IN好很多：

-- 假设342,2345,9282是病毒对应的illness编码，其他illness属于真菌
SELECT DISTINCT d1.name
FROM `diseases` d1
LEFT JOIN `diseases` d2 
  ON d1.name = d2.name 
  AND d2.illness NOT IN (342, 2345, 9282) -- 匹配有真菌感染记录的用户
WHERE d1.illness IN (342, 2345, 9282) -- 确保用户至少有一条病毒感染记录
AND d2.name IS NULL; -- 排除存在真菌感染记录的用户

如果你的病原体是通过campaign_id区分的（病毒对应987,457,1283,2212），可以改成：

SELECT DISTINCT d1.name
FROM `diseases` d1
LEFT JOIN `diseases` d2 
  ON d1.name = d2.name 
  AND d2.campaign_id NOT IN (987, 457, 1283, 2212) -- 匹配真菌相关的campaign记录
WHERE d1.campaign_id IN (987, 457, 1283, 2212)
AND d2.name IS NULL;

方案2：用GROUP BY + HAVING确保仅感染病毒 #

这种方式通过分组统计用户的所有记录，确保只存在病毒相关的illness：

SELECT name
FROM `diseases`
GROUP BY name
HAVING 
  SUM(CASE WHEN illness IN (342, 2345, 9282) THEN 1 ELSE 0 END) > 0 -- 至少有一条病毒记录
  AND SUM(CASE WHEN illness NOT IN (342, 2345, 9282) THEN 1 ELSE 0 END) = 0; -- 没有任何真菌记录

提升查询性能的关键 #

为了避免查询崩溃，建议给相关字段建立索引：

-- 针对illness筛选的索引
CREATE INDEX idx_diseases_name_illness ON `diseases`(name, illness);
-- 针对campaign_id筛选的索引
CREATE INDEX idx_diseases_name_campaign ON `diseases`(name, campaign_id);

索引会让数据库快速定位目标记录，大幅减少查询时间。

逻辑验证 #

执行查询后，可以随机抽取几个结果用户，检查他们的所有记录是否确实只有病毒相关的illness，确保没有混入真菌感染的用户。

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