如何在BigQuery中基于另一列条件统计值的出现次数

我有如下BigQuery查询语句：

Select ID,Event_Name,MAX(Page_URL) as URL,MAX(Value) as Value from database
Group by ID,Event_Name
Order by Event_Name

执行后输出如下数据：

我希望新增一个Count列，按Event_Name分组统计该分组内各URL的实例数量，期望输出如下：

使用BigQuery的窗口函数COUNT()，通过PARTITION BY Event_Name, URL统计每个Event_Name分组内对应URL的出现次数，修改后的查询语句如下：

SELECT 
  ID,
  Event_Name,
  MAX(Page_URL) AS URL,
  MAX(Value) AS Value,
  COUNT(*) OVER (PARTITION BY Event_Name, MAX(Page_URL)) AS Count
FROM database
GROUP BY ID, Event_Name
ORDER BY Event_Name

如果偏好更直观的逻辑，也可以用子查询先计算各URL的计数，再关联回原数据：

WITH url_counts AS (
  SELECT 
    Event_Name,
    Page_URL,
    COUNT(*) AS Count
  FROM database
  GROUP BY Event_Name, Page_URL
)
SELECT 
  d.ID,
  d.Event_Name,
  MAX(d.Page_URL) AS URL,
  MAX(d.Value) AS Value,
  uc.Count
FROM database d
JOIN url_counts uc 
  ON d.Event_Name = uc.Event_Name 
  AND MAX(d.Page_URL) = uc.Page_URL
GROUP BY d.ID, d.Event_Name, uc.Count
ORDER BY d.Event_Name

说明 #

• 窗口函数方案更简洁高效，直接在分组结果上计算计数；
• 子查询方案逻辑清晰，适合后续需要扩展统计规则的场景。

两种方式均可得到你期望的输出。

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