按多条件筛选原表数据插入新表的SQL实现问题

需要从original_table筛选符合以下规则的记录写入空表new_table：

• 行程时长duration_sec不低于30秒
• 记录的出发站start_station_id满足：以该站为起点的有效行程（时长≥30秒）数至少100次
• 记录的到达站end_station_id满足：以该站为终点的有效行程（时长≥30秒）数至少100次

你之前的实现存在两个核心错误，导致结果不符合预期：

1. 临时表统计逻辑错误：统计合格出发站时，HAVING子句错误加入了COUNT(end_station_id)>=100的判断，这个条件统计的是当前分组内到达站的记录数，和「该站作为出发站的行程量」无关；同理统计到达站的临时表也错误混入了出发站的计数条件，直接导致筛选出的站点列表本身不符合要求。
2. 关联逻辑冗余：将站点ID封装为数组再通过UNNEST+笛卡尔积的方式关联，属于不必要的复杂操作，既容易出错，也会增加计算开销。

以下两种写法都可以在BigQuery中直接运行，逻辑清晰且性能更好：

方案1：CTE分维度统计（可读性高） #

先分别统计合格的出发站、到达站列表，再通过EXISTS匹配过滤原表数据：

INSERT INTO `dataset.new_table`
WITH valid_start_stations AS (
  SELECT start_station_id
  FROM `dataset.original_table`
  WHERE duration_sec >= 30
  GROUP BY start_station_id
  HAVING COUNT(1) >= 100
),
valid_end_stations AS (
  SELECT end_station_id
  FROM `dataset.original_table`
  WHERE duration_sec >= 30
  GROUP BY end_station_id
  HAVING COUNT(1) >= 100
)
SELECT a.*
FROM `dataset.original_table` a
WHERE a.duration_sec >= 30
AND EXISTS (
  SELECT 1 FROM valid_start_stations vs 
  WHERE vs.start_station_id = a.start_station_id
)
AND EXISTS (
  SELECT 1 FROM valid_end_stations ve 
  WHERE ve.end_station_id = a.end_station_id
);

方案2：窗口函数单次计算（性能更优） #

通过窗口函数直接在原表上计算每个站点的出发、到达行程量，一次过滤完成，不需要多表关联：

INSERT INTO `dataset.new_table`
WITH trip_with_station_cnt AS (
  SELECT
    *,
    COUNT(1) OVER (PARTITION BY start_station_id) AS start_total,
    COUNT(1) OVER (PARTITION BY end_station_id) AS end_total
  FROM `dataset.original_table`
  WHERE duration_sec >= 30
)
SELECT * EXCEPT(start_total, end_total)
FROM trip_with_station_cnt
WHERE start_total >= 100
AND end_total >= 100;

注意：两种方案的统计基准都是时长≥30秒的有效行程，和需求规则完全一致，不会出现统计口径偏差。

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