BigQuery报错'Unrecognized name: reached_ind'问题排查求助

为什么reached_ind不被识别？ #

你在子查询members里既没有选取reached_ind字段，分组时也没把它纳入分组条件，外层查询自然找不到这个列。而且当前统计逻辑也有问题：你要统计邮件渠道成功联系的客户数，但子查询里的COUNT(DISTINCT member_id)没有区分是否成功联系，窗口函数的ORDER BY month_of_the_order也多余（已经按月份分区，排序不影响结果）。

修正后的SQL代码 #

方案一：先过滤成功记录再分组（更高效）

如果只需要统计成功联系的情况，先通过WHERE过滤掉未成功的记录，再分组统计：

SELECT 
  month_of_the_order,
  channel,
  -- 仅当渠道为email时显示统计数，其他渠道显示NULL
  CASE WHEN channel = 'email' THEN distinct_num_calls END as email_success_count
FROM
  (SELECT 
    EXTRACT(MONTH FROM order_date) AS month_of_the_order,
    COUNT(DISTINCT member_id) AS distinct_num_calls,
    channel
  FROM
    `quiz-one-382819.coding_project.cvs`
  WHERE reached_ind = 1  -- 先筛选成功联系的记录
  GROUP BY 
    month_of_the_order, 
    channel       
  ) members

方案二：保留所有状态，按需统计

如果需要保留所有渠道和联系状态的数据，再单独统计邮件成功的总数，可以在子查询中加入reached_ind并分组：

SELECT 
  month_of_the_order,
  channel,
  -- 按月份统计该月所有邮件成功联系的客户总数
  SUM(CASE WHEN reached_ind = 1 AND channel = 'email' THEN distinct_num_calls ELSE 0 END) 
  OVER(PARTITION BY month_of_the_order) as email_success_count
FROM
  (SELECT 
    EXTRACT(MONTH FROM order_date) AS month_of_the_order,
    COUNT(DISTINCT member_id) AS distinct_num_calls,
    channel,
    reached_ind  -- 子查询中包含该字段
  FROM
    `quiz-one-382819.coding_project.cvs`             
  GROUP BY 
    month_of_the_order, 
    channel,
    reached_ind  -- 分组时加入该字段
  ) members

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