BigQuery中Full Outer Join与Group By的分组问题及优化咨询

正确实现Receipts分组的SQL代码 #

针对需求，我们可以通过拆分逻辑替代直接使用Full Outer Join，确保Receipts按Trip的时间条件正确聚合，同时处理无对应Trip的Receipts分组：

WITH filtered_trips AS (
  SELECT
    source_id,
    pickup_time,
    drop_time,
    -- 保留你需要的其他Trips字段
    run_date
  FROM
    `your-project.your-dataset.trips`
  WHERE
    run_date = '2024-05-20' -- 替换为目标日期
),
filtered_receipts AS (
  SELECT
    fk_id,
    created_at,
    -- 保留你需要的其他Receipts字段
    run_date
  FROM
    `your-project.your-dataset.receipts`
  WHERE
    run_date = '2024-05-20' -- 替换为目标日期
),
trip_related_receipts AS (
  SELECT
    t.source_id AS trip_id,
    t.pickup_time,
    t.drop_time,
    -- 按Trip维度聚合符合时间条件的Receipts
    ARRAY_AGG(STRUCT(r.created_at, r.fk_id /* 补充其他需要的Receipt字段 */)) AS receipts_array
  FROM
    filtered_trips t
  LEFT JOIN
    filtered_receipts r
  ON
    t.source_id = r.fk_id
    -- 处理drop_time为null的场景：用极晚时间确保所有created_at都符合条件
    AND r.created_at BETWEEN t.pickup_time AND COALESCE(t.drop_time, TIMESTAMP('9999-12-31'))
  GROUP BY
    t.source_id, t.pickup_time, t.drop_time /* 所有非聚合的Trips字段 */
),
no_trip_receipts AS (
  SELECT
    NULL AS trip_id,
    NULL AS pickup_time,
    NULL AS drop_time,
    -- 对无对应Trip的Receipts按fk_id分组聚合
    ARRAY_AGG(STRUCT(r.created_at, r.fk_id /* 补充其他需要的Receipt字段 */)) AS receipts_array
  FROM
    filtered_receipts r
  WHERE
    NOT EXISTS (
      SELECT 1 FROM filtered_trips t WHERE t.source_id = r.fk_id
    )
  GROUP BY
    r.fk_id
)

-- 合并两部分结果
SELECT * FROM trip_related_receipts
UNION ALL
SELECT * FROM no_trip_receipts

关键逻辑说明 #

1. 预过滤数据：先用CTE筛选指定run_date的数据集，减少后续关联计算的数据量，避免全表扫描。
2. 时间条件关联：在Join时直接判断Receipt的created_at是否在Trip的时间范围内，COALESCE处理drop_time为null的情况，确保这类Trip的所有关联Receipt都被聚合。
3. 拆分分组逻辑：将关联Trip的Receipts和无对应Trip的Receipts分开处理，再用UNION ALL合并，比直接用Full Outer Join更清晰，且能避免分组逻辑混乱。

高效查询优化方案 #

• 利用分区表特性：如果你的Trips和Receipts表是按run_date分区的，BigQuery会自动只扫描目标日期的分区，查询速度会大幅提升。
• 设置集群字段：对Trips按source_id、Receipts按fk_id设置集群，能优化关联操作的性能，减少数据 shuffle。
• 避免Full Outer Join滥用：Full Join会带来较高的计算开销，尤其是数据量大时。拆分逻辑的方式不仅逻辑更清晰，还能降低BigQuery的资源消耗。
• 精简聚合字段：只将需要的Receipt字段加入聚合数组，避免不必要的数据传输和存储，提升查询效率。

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