GoogleBigQuery/GoogleScripts技术咨询：如何过滤特定ReviewRound记录

一、Google BigQuery实现方案（优先推荐） #

方案1：CTE子查询标记目标ID #

先筛选出所有包含ReviewRound="b"的Unique ID，再排除这些ID下的ReviewRound="a"记录，逻辑清晰易读：

WITH id_with_b AS (
  SELECT DISTINCT unique_id
  FROM `your_project.your_dataset.your_table`
  WHERE ReviewRound = 'b'
)
SELECT *
FROM `your_project.your_dataset.your_table` t
WHERE NOT (t.unique_id IN (SELECT unique_id FROM id_with_b) AND t.ReviewRound = 'a');

方案2：窗口函数（大数据量更高效） #

通过窗口函数直接在每条记录上标记该ID是否存在"b"轮评审，避免额外子查询开销，适合百万级以上数据场景：

SELECT *
FROM (
  SELECT *,
         MAX(CASE WHEN ReviewRound = 'b' THEN 1 ELSE 0 END) OVER (PARTITION BY unique_id) AS has_b_round
  FROM `your_project.your_dataset.your_table`
)
WHERE NOT (has_b_round = 1 AND ReviewRound = 'a');

注意：请替换your_project.your_dataset.your_table为你的实际项目、数据集和表名；两种方案都会保留未经历"b"轮的所有记录，同时精准移除有"b"轮的ID下所有"a"轮记录。

二、Google Apps Script过滤方案（BigQuery不可行时备选） #

如果无法通过BigQuery直接处理，可通过Apps Script读取数据（示例以Google Sheets为例）并完成过滤：

function filterReviewRecords() {
  // 1. 读取源数据（此处假设数据存储在Google Sheets中）
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("源数据");
  const allData = sourceSheet.getDataRange().getValues();
  const header = allData[0];
  const records = allData.slice(1); // 移除表头，仅保留数据行

  // 2. 收集所有包含"b"轮评审的Unique ID
  const idsWithBRound = new Set();
  const uniqueIdColIndex = header.indexOf("unique_id"); // 替换为你的实际列名
  const reviewRoundColIndex = header.indexOf("ReviewRound");

  records.forEach(record => {
    const currentId = record[uniqueIdColIndex];
    const currentRound = record[reviewRoundColIndex];
    if (currentRound === "b") {
      idsWithBRound.add(currentId);
    }
  });

  // 3. 执行核心过滤逻辑
  const filteredRecords = records.filter(record => {
    const currentId = record[uniqueIdColIndex];
    const currentRound = record[reviewRoundColIndex];
    // 保留规则：ID不在b轮列表中，或者ID在列表中但当前轮不是a
    return !idsWithBRound.has(currentId) || currentRound !== "a";
  });

  // 4. 将结果写入新工作表（可按需调整输出位置）
  const outputSheet = sourceSheet.getParent().getSheetByName("过滤后数据") || sourceSheet.getParent().insertSheet("过滤后数据");
  outputSheet.clear();
  outputSheet.appendRow(header); // 写入表头
  filteredRecords.forEach(row => outputSheet.appendRow(row));

  Logger.log(`过滤完成：原记录数${records.length}，过滤后记录数${filteredRecords.length}`);
}

说明：

• 如果数据来自BigQuery，可改用Apps Script的BigQuery服务直接查询源数据后处理；
• 请根据实际列名调整unique_id和ReviewRound的列索引；
• 执行脚本前需确保已启用对应权限（如Google Sheets或BigQuery权限）。

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