BigQuery中排除特定值记录并按appName分组计数的方法

我来帮你搞定这个BigQuery的统计问题！先明确你的核心需求：彻底排除所有曾关联过appName为A的BridgeToken，然后对剩下的有效Token按appName分组统计数量。

先对照你给出的示例数据梳理下：
原表中ffffff1、ffffff3这两个Token都关联过appName A，所以要完全排除；剩下的有效Token是ffffff2、aaaaaa4、bbbbbb2，对应的appName分别是B、C、B，最终统计结果应该是B:2，C:1。

先分析你现有查询的问题 #

你的子查询只过滤了指定日期内关联过A的Token，但如果你的需求是"只要Token关联过A就排除"，这个日期限制会导致漏判；另外主查询没有同步日期条件的话，也会混入其他日期的无效数据，导致结果不准。

正确实现方案 #

根据你的需求，分两种场景给出查询：

场景1：排除所有历史上关联过A的BridgeToken（无日期限制）

用CTE先筛选出从未关联过A的Token，再基于这些Token做统计：

WITH valid_bridge_tokens AS (
  SELECT BridgeToken
  FROM `<DB>`
  GROUP BY BridgeToken
  -- 筛选出从未和A关联的Token：统计关联A的次数为0
  HAVING SUM(IF(appName = 'A', 1, 0)) = 0
)
SELECT 
  COUNT(DISTINCT t.BridgeToken) AS Bridges,  -- 若需统计唯一Token数则保留DISTINCT，统计记录数则去掉
  t.appName
FROM `<DB>` t
JOIN valid_bridge_tokens vt ON t.BridgeToken = vt.BridgeToken
-- 如果需要限定统计的日期范围，在这里加条件
-- WHERE date >= "2018-04-01 00:00:00" AND date < "2018-05-01 00:00:00"
GROUP BY t.appName
ORDER BY Bridges DESC;

场景2：仅排除指定日期范围内关联过A的BridgeToken

如果你的需求是只排除2018年4月期间关联过A的Token，其他时间关联过A的不影响，那么可以调整为：

WITH invalid_bridge_tokens AS (
  SELECT DISTINCT BridgeToken
  FROM `<DB>`
  WHERE appName = 'A'
    AND date >= "2018-04-01 00:00:00" 
    AND date < "2018-05-01 00:00:00"
)
SELECT 
  COUNT(DISTINCT t.BridgeToken) AS Bridges,
  t.appName
FROM `<DB>` t
LEFT JOIN invalid_bridge_tokens it ON t.BridgeToken = it.BridgeToken
WHERE it.BridgeToken IS NULL
  -- 主查询同步日期条件，确保统计的是同一时间范围的数据
  AND date >= "2018-04-01 00:00:00" 
  AND date < "2018-05-01 00:00:00"
GROUP BY t.appName
ORDER BY Bridges DESC;

用你的示例数据测试场景1的查询，会得到符合预期的结果：

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