BigQuery中Case语句同一列多条件搭配Count Distinct的问题解决

现有SQL的问题分析 #

你的SQL返回0的核心原因是逻辑矛盾：

case when (month >= 202207 and month <= 202301) and col1 in ('A','B','C','D','E','F') and (col1 ='Z') then month end

同一行的col1不可能同时属于('A','B','C','D','E','F')又等于'Z'，所以CASE语句永远返回NULL，而COUNT(DISTINCT NULL)的结果就是0。

需求拆解与正确实现 #

从你的数据和期望输出来看，实际需求是：统计同时存在A/B/C/D/E/F类记录和Z类记录的用户，在202207-202301时间段内的所有去重月份数。

以下是两种可行的实现方式：

方式一：先筛选有效用户，再统计月份 #

WITH valid_users AS (
  -- 筛选同时有A-F记录和Z记录的用户
  SELECT cus
  FROM your_table
  WHERE month BETWEEN 202207 AND 202301
  GROUP BY cus
  HAVING 
    SUM(CASE WHEN col1 IN ('A','B','C','D','E','F') THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN col1 = 'Z' THEN 1 ELSE 0 END) > 0
)
-- 对有效用户统计去重月份数
SELECT 
  t.cus,
  COUNT(DISTINCT t.month) AS count_distinct_month
FROM your_table t
JOIN valid_users vu ON t.cus = vu.cus
WHERE t.month BETWEEN 202207 AND 202301
GROUP BY t.cus

方式二：用窗口函数一次完成统计 #

SELECT 
  cus,
  COUNT(DISTINCT month) AS count_distinct_month
FROM (
  SELECT 
    *,
    -- 标记用户是否有A-F记录
    SUM(CASE WHEN col1 IN ('A','B','C','D','E','F') THEN 1 ELSE 0 END) OVER (PARTITION BY cus) AS has_target,
    -- 标记用户是否有Z记录
    SUM(CASE WHEN col1 = 'Z' THEN 1 ELSE 0 END) OVER (PARTITION BY cus) AS has_z
  FROM your_table
  WHERE month BETWEEN 202207 AND 202301
)
WHERE has_target > 0 AND has_z > 0
GROUP BY cus

结果验证 #

执行上述任意SQL后，会得到你期望的输出：

cus count_distinct_month
1   1
2   3

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