BigQuery技术问题：合并相似项求和并转为行式输出

问题背景 #

现有数据表test 2 campaign table，结构及数据如下：

需要完成三个统计需求并以行形式输出结果：

• 统计values匹配like%或%love%的行的count总和，命名为Top2
• 统计values匹配dislike%或%hate%的行的count总和，命名为Bottom2
• 统计values匹配%neutral%的行的count总和，命名为Neutral

原WIP查询返回全null且以列展示结果，需修正实现需求。

原查询错误分析 #

1. 模糊匹配语法错误：使用=进行模糊匹配，而BigQuery中模糊匹配需要用LIKE关键字，=仅匹配完全相等的字符串，导致所有CASE条件不触发，sum结果为null。
2. 输出结构不符合要求：原查询以多列形式展示统计值，未满足行输出的需求。

修正后的SQL查询 #

方案一：先聚合再转行 #

WITH aggregated_stats AS (
  SELECT
    SUM(CASE WHEN values LIKE 'like%' OR values LIKE '%love%' THEN count ELSE 0 END) AS Top2,
    SUM(CASE WHEN values LIKE 'dislike%' OR values LIKE '%hate%' THEN count ELSE 0 END) AS Bottom2,
    SUM(CASE WHEN values LIKE '%neutral%' THEN count ELSE 0 END) AS Neutral
  FROM `test 2 campaign table`
)
SELECT 'Top2' AS values, Top2 AS count FROM aggregated_stats
UNION ALL
SELECT 'Bottom2' AS values, Bottom2 AS count FROM aggregated_stats
UNION ALL
SELECT 'Neutral' AS values, Neutral AS count FROM aggregated_stats

方案二：直接按类别聚合（更简洁） #

SELECT
  CASE
    WHEN values LIKE 'like%' OR values LIKE '%love%' THEN 'Top2'
    WHEN values LIKE 'dislike%' OR values LIKE '%hate%' THEN 'Bottom2'
    WHEN values LIKE '%neutral%' THEN 'Neutral'
  END AS values,
  SUM(count) AS count
FROM `test 2 campaign table`
GROUP BY values
HAVING values IS NOT NULL

执行结果 #

运行上述任一查询，将得到符合期望的输出：

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