BigQuery使用SQL PIVOT统计邮件营销活动用户数据报错如何解决

• 语法适配错误：你当前使用的方括号[]包裹枚举值是SQL Server等少数数据库的专属写法，你使用的数据库不支持该语法，因此会把[opened]识别为字段名而非status的枚举值，触发未识别报错
• SELECT子句逻辑错误：PIVOT执行后会将status字段的枚举值转换为新列，user_id、status两个字段在转换后已经不存在，你在SELECT中指定这两个字段会引发连锁报错
• 缺少正确的分组维度：你需要按活动维度统计，PIVOT默认会按SELECT中保留的非聚合、非转换字段分组，原查询没有正确指定分组维度

方案1：修正PIVOT语法（适配不同数据库） #

适配BigQuery/MySQL 8.0+/PostgreSQL写法 #

SELECT 
  campaign_name,
  opened,
  clicked
FROM `table`
PIVOT(
  COUNT(DISTINCT user_id)
  FOR status IN (
    'opened' AS opened,
    'clicked' AS clicked
  )
) AS PivotTable

适配SQL Server写法 #

SELECT 
  campaign_name,
  [opened],
  [clicked]
FROM (
  SELECT user_id, campaign_name, status FROM [table]
) AS src
PIVOT(
  COUNT(DISTINCT user_id)
  FOR status IN ([opened], [clicked])
) AS PivotTable

方案2：通用条件聚合适配所有数据库 #

如果你不确定当前使用的数据库是否支持PIVOT语法，可以用更通用的条件聚合写法实现相同效果，不存在语法兼容问题：

SELECT
  campaign_name AS `Campaign Name`,
  COUNT(DISTINCT CASE WHEN status = 'opened' THEN user_id END) AS Opened,
  COUNT(DISTINCT CASE WHEN status = 'clicked' THEN user_id END) AS Clicked
FROM `table`
GROUP BY campaign_name

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