如何在BigQuery中按多条件执行DISTINCT COUNT（去重计数）

需求概述 #

现有如下数据：

需要新增Col4列，统计当前行所属ColA分组中，满足ColC>0的唯一ColB数量，期望结果如下：

原代码问题分析 #

你写的BigQuery代码存在几个语法和逻辑错误：

• SELECT子句中DISTINCT ColA和COUNT(...)之间缺少逗号，导致语法报错
• 误用了Excel的单元格引用[@[ColA]]，BigQuery不支持这种写法
• COUNT(DISTINCT ColB IF(...))的写法不符合BigQuery语法，条件判断需要用CASE WHEN

正确实现方案 #

方法一：窗口函数（推荐，代码简洁高效） #

窗口函数可以直接在原表基础上，按ColA分组计算统计值并附加到每一行：

SELECT
  ColA,
  ColB,
  ColC,
  -- 按ColA分组，统计分组内ColC>0的唯一ColB数量
  COUNT(DISTINCT CASE WHEN ColC > 0 THEN ColB END) OVER (PARTITION BY ColA) AS Col4
FROM
  `mi-trial-365509.Trial.MI 17012022`
LIMIT 1000

方法二：预计算分组统计再关联（适合理解基础SQL逻辑） #

先通过CTE计算每个ColA对应的统计值，再和原表关联匹配到每一行：

WITH group_stats AS (
  SELECT
    ColA,
    COUNT(DISTINCT ColB) AS Col4
  FROM
    `mi-trial-365509.Trial.MI 17012022`
  WHERE
    ColC > 0  -- 只统计ColC>0的记录
  GROUP BY
    ColA
)
SELECT
  t.ColA,
  t.ColB,
  t.ColC,
  gs.Col4
FROM
  `mi-trial-365509.Trial.MI 17012022` t
LEFT JOIN
  group_stats gs
ON
  t.ColA = gs.ColA  -- 按ColA关联统计值
LIMIT 1000

逻辑说明 #

• 方法一中，OVER (PARTITION BY ColA)指定按ColA分组，CASE WHEN ColC > 0 THEN ColB END会过滤掉ColC<=0的ColB值，再用COUNT(DISTINCT ...)统计唯一值数量，最终每个分组的统计值会对应到该分组的每一行。
• 方法二中，CTEgroup_stats先算出每个ColA的目标统计值，再通过左连接把统计值匹配到原表的所有行，确保原始数据的每一行都被保留。

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