BigQuery SQL如何按列值前缀分组统计TEST/CONTROL数据

你的查询返回6行结果、统计不符合预期，由三处写法错误导致：

• 匹配语法错误：=是精确匹配操作符，q11="TEST*"只会匹配值完全等于字符串TEST*的记录，无法匹配前缀为TEST的内容。SQL中前缀匹配需要用LIKE操作符，搭配多字符通配符%（你使用的*不是SQL标准通配符，不会生效）。
• 分组逻辑错误：语句中写了GROUP BY q11，会按照Q11列原始的6个不同取值分别聚合，自然返回6行。要实现两类聚合，需要按照归类后的TEST/CONTROL分组，而非原始Q11字段。
• 计数逻辑错误：COUNT()会统计所有非NULL值，你在CASE表达式中写了ELSE 0，不符合匹配条件的记录会因为返回0被计入统计，导致计数结果错误。

如果需要输出2行统计结果（每行对应一个分组），使用如下语句：

SELECT
  CASE
    WHEN q11 LIKE 'TEST%' THEN 'TEST'
    WHEN q11 LIKE 'CONTROL%' THEN 'CONTROL'
  END AS experiment_group,
  COUNT(1) AS valid_response_count
FROM
  `paid-poc-analytics-0ee3.study_brandlift.response_values`
WHERE
  question_wording = 'Affinity'
  AND VALUES IN ('Love it', 'Like it')
  AND (q11 LIKE 'TEST%' OR q11 LIKE 'CONTROL%')
GROUP BY
  experiment_group

执行后返回结果示例：

如果你需要将两个分组的统计结果作为两列输出（仅返回1行），可以使用如下写法：

SELECT
  COUNT(CASE WHEN q11 LIKE 'TEST%' THEN 1 END) AS TEST,
  COUNT(CASE WHEN q11 LIKE 'CONTROL%' THEN 1 END) AS CONTROL
FROM
  `paid-poc-analytics-0ee3.study_brandlift.response_values`
WHERE
  question_wording = 'Affinity'
  AND VALUES IN ('Love it', 'Like it')

注意：上述CASE表达式没有写ELSE分支，不符合匹配条件的记录会返回NULL，COUNT会自动忽略NULL值，保证统计结果准确。

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