如何按category_id分组统计去重user_id的数量？（BigQuery）

背景与数据 #

使用的数据库为BigQuery，语法与多数数据库通用。现有数据表t的结构及数据如下：

user_id | date | category_id 
----------------------------
1       | xx   | 10
2       | xx   | 10
2       | xx   | 10
3       | xx   | 10
3       | xx   | 10
3       | xx   | 10
1       | xx   | 11
2       | xx   | 12

需求 #

按category_id分组，统计每组内去重后的user_id数量，预期输出：

category_id | distinct_user_count
---------------------------------
10          | 3
11          | 1
12          | 1

两种SQL查询对比 #

正确查询（得到预期结果） #

SELECT 
  category_id,
  count(distinct user_id) AS distinct_user_count
FROM t
GROUP BY category_id

错误查询（返回意外结果） #

查询语句：

SELECT 
  category_id,
  count(user_id) over(partition by category_id) AS distinct_user_count
FROM t
GROUP BY category_id, user_id

意外输出：

category_id | distinct_user_count
---------------------------------
10          | 1
11          | 1
12          | 1

错误原因分析 #

第二种查询先按category_id和user_id分组，这一步已经把每个分类下的用户做了去重，每组仅保留一条记录（比如分类10下会生成3条分组记录：(10,1)、(10,2)、(10,3)）。之后用窗口函数count(user_id) over(partition by category_id)时，是在分组后的结果集上计算，但此时每个用户对应的分组行里只有一条非空的user_id，count(user_id)对单条记录统计的结果就是1，因此最终每个分类下的每行都显示1。

如果想用窗口函数实现需求，可以先对用户去重，再统计分类下的用户数：

SELECT 
  category_id,
  COUNT(*) OVER(PARTITION BY category_id) AS distinct_user_count
FROM (
  SELECT DISTINCT category_id, user_id
  FROM t
)
GROUP BY category_id, user_id, distinct_user_count

不过更简洁高效的写法还是直接使用第一种count(distinct)的分组查询。

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