如何在BigQuery中基于person_id与activity组合新建计数列combination_count

你可以使用BigQuery的窗口函数实现需求，核心是在分区的同时指定排序规则，实现同组合下的累计计数。
假设你的原始表名为your_table，包含可标识行生成顺序的字段event_time（可替换为你表中实际的排序字段，如日志ID、行号等），查询语句如下：

SELECT
  person_id,
  activity,
  ROW_NUMBER() OVER(PARTITION BY person_id, activity ORDER BY event_time ASC) AS combination_count
FROM `your_project.your_dataset.your_table`

逻辑说明 #

• PARTITION BY person_id, activity 会将person_id和activity取值完全相同的行划分到同一个分区
• ORDER BY event_time ASC 会给同一分区内的行按时间先后排序
• ROW_NUMBER() 会给分区内排序后的行依次生成1、2、3...的序号，正好对应你需要的累计出现次数
  如果需要将结果写入原表作为新列，可以使用如下语句覆盖原表或生成新表：

-- 覆盖原表添加新列
CREATE OR REPLACE TABLE `your_project.your_dataset.your_table` AS
SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY person_id, activity ORDER BY event_time ASC) AS combination_count
FROM `your_project.your_dataset.your_table`

执行后即可得到你示例中展示的输出结果，该方案支持任意数据量的表执行，不需要逐行过滤处理。

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