如何在Google BigQuery中基于重复类别值创建SQL分组？

要实现将记录按「A起始、D结束，中间可包含B/C」的规则分组，核心是通过窗口函数标记每个分组的起始点，再基于分组ID聚合数据，具体步骤如下：

基础实现方案 #

假设你的表名为your_project.your_dataset.your_table，时间戳字段为time_stamp，类别字段为category，可以用以下SQL完成分组：

WITH grouped_data AS (
  SELECT
    time_stamp,
    category,
    -- 每次遇到A就生成新的分组ID
    SUM(CASE WHEN category = 'A' THEN 1 ELSE 0 END) OVER (ORDER BY time_stamp) AS group_id
  FROM
    `your_project.your_dataset.your_table`
)
SELECT
  group_id,
  MIN(time_stamp) AS 分组起始时间,
  MAX(time_stamp) AS 分组结束时间,
  -- 将分组内的记录按时间顺序整理成数组
  ARRAY_AGG(STRUCT(time_stamp, category) ORDER BY time_stamp) AS 分组内记录
FROM
  grouped_data
GROUP BY
  group_id
ORDER BY
  group_id;

逻辑说明 #

1. 标记分组ID：通过SUM() OVER (ORDER BY time_stamp)窗口函数，每遇到一条category='A'的记录，分组计数器就加1，后续所有行直到下一个A出现前，都会共享同一个group_id。
2. 聚合分组数据：按group_id分组后，用MIN()/MAX()获取分组的起止时间，ARRAY_AGG()将分组内的所有记录按时间顺序整理，方便查看完整分组内容。

进阶过滤方案（可选） #

如果数据中存在「A之后未出现D就遇到下一个A」的无效分组，可以添加过滤逻辑，只保留包含D的有效分组：

WITH grouped_data AS (
  SELECT
    time_stamp,
    category,
    SUM(CASE WHEN category = 'A' THEN 1 ELSE 0 END) OVER (ORDER BY time_stamp) AS group_id
  FROM
    `your_project.your_dataset.your_table`
),
group_validation AS (
  SELECT
    *,
    -- 检查当前分组是否包含D
    MAX(CASE WHEN category = 'D' THEN 1 ELSE 0 END) OVER (PARTITION BY group_id) AS has_d
  FROM
    grouped_data
)
SELECT
  group_id,
  MIN(time_stamp) AS 分组起始时间,
  MAX(time_stamp) AS 分组结束时间,
  ARRAY_AGG(STRUCT(time_stamp, category) ORDER BY time_stamp) AS 分组内记录
FROM
  group_validation
WHERE
  has_d = 1 -- 只保留包含D的有效分组
GROUP BY
  group_id
ORDER BY
  group_id;

针对你提供的示例数据，运行上述SQL会得到两个符合要求的分组：

• 分组1：从1:10-A到1:15-D，包含中间的B记录
• 分组2：从2:05-A到2:10-D

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