BigQuery物化视图不支持LEFT JOIN，如何实现等效效果？

针对你需要保留所有GPS点位（包括不在MyTable3多边形内的记录）的需求，这里提供三种可行的替代方案：

方案1：用UNION ALL模拟LEFT JOIN（推荐） #

将结果拆分为两部分合并：匹配到多边形的记录，加上未匹配到多边形的记录（area字段设为NULL），以此绕过物化视图对LEFT JOIN的限制。

CREATE MATERIALIZED VIEW MY_TABLE
  PARTITION BY TIMESTAMP_TRUNC(date, DAY)
  CLUSTER BY node_address
  OPTIONS (enable_refresh = true)
AS (
  -- 匹配到多边形的记录
  SELECT
    date,
    personName,
    personId,
    area
  FROM
    MyTable1 AS t1
  INNER JOIN
    MyTable2 AS t2
  ON
    t1.uuid = t2.uuid
  INNER JOIN
    MyTable3 AS polygons
  ON
    ST_CONTAINS(polygons.geoJSON, t1.gps_coordinates)
  WHERE
    type = "person"
  
  UNION ALL
  
  -- 未匹配到任何多边形的记录，area设为NULL
  SELECT
    date,
    personName,
    personId,
    NULL AS area
  FROM
    MyTable1 AS t1
  INNER JOIN
    MyTable2 AS t2
  ON
    t1.uuid = t2.uuid
  WHERE
    type = "person"
    AND NOT EXISTS (
      SELECT 1
      FROM MyTable3 AS polygons
      WHERE ST_CONTAINS(polygons.geoJSON, t1.gps_coordinates)
    )
)

注意：如果MyTable3数据量较大，可为geoJSON字段添加空间索引来优化ST_CONTAINS的执行效率。

方案2：分层构建（物化视图+普通视图） #

先创建存储基础关联数据的物化视图，再基于它创建普通视图实现LEFT JOIN逻辑。

步骤1：创建基础物化视图 #

CREATE MATERIALIZED VIEW BASE_DATA
  PARTITION BY TIMESTAMP_TRUNC(date, DAY)
  CLUSTER BY node_address
  OPTIONS (enable_refresh = true)
AS (
  SELECT
    date,
    personName,
    personId,
    gps_coordinates,
    node_address
  FROM
    MyTable1 AS t1
  INNER JOIN
    MyTable2 AS t2
  ON
    t1.uuid = t2.uuid
  WHERE
    type = "person"
)

步骤2：创建关联多边形的普通视图 #

CREATE VIEW MY_TABLE AS
SELECT
  date,
  personName,
  personId,
  polygons.area
FROM
  BASE_DATA
LEFT JOIN
  MyTable3 AS polygons
ON
  ST_CONTAINS(polygons.geoJSON, BASE_DATA.gps_coordinates)

缺点：普通视图为实时计算，每次查询都会执行LEFT JOIN和空间函数，数据量大时查询性能可能不如纯物化视图。

方案3：定时刷新普通表模拟物化视图 #

通过定时任务（比如Cloud Scheduler）定期执行含LEFT JOIN逻辑的SQL，刷新普通表来模拟物化视图的自动更新效果。

示例SQL脚本（可作为定时查询或嵌入Cloud Function）：

-- 全量刷新：先清空目标表
TRUNCATE TABLE MY_TABLE;

-- 插入包含LEFT JOIN逻辑的数据
INSERT INTO MY_TABLE
SELECT
  date,
  personName,
  personId,
  area
FROM
  MyTable1 AS t1
INNER JOIN
  MyTable2 AS t2
ON
  t1.uuid = t2.uuid
LEFT JOIN
  MyTable3 AS polygons
ON
  ST_CONTAINS(polygons.geoJSON, t1.gps_coordinates)
WHERE
  type = "person"

优点：完全自定义逻辑，不受物化视图限制；缺点：需额外配置定时任务，增量更新需自行实现（比如用MERGE对比时间戳）。

内容的提问来源于stack exchange，提问作者Rui Bras Fernandes