BigQuery使用ST_CLUSTERDBSCAN聚类百万行数据内存超额问题咨询

你当前报错的核心原因是OVER()窗口没有指定分区键，全量百万行数据会被加载到同一个计算节点做DBSCAN计算，而DBSCAN的时间和内存复杂度接近O(n²)，超过单节点内存上限。针对百万行级别的地理坐标聚类，可行方案如下：

方案1：空间分区后分治聚类（最常用，精度损失最小） #

核心思路是把全量地理数据按空间格网切分成多个小分区，每个分区单独聚类后再处理边界问题，避免单节点加载全量数据。
操作步骤：

• 用ST_SnapToGrid将坐标对齐到大于DBSCAN搜索半径2倍的格网（你当前搜索半径为2000米，格网可设为4000米），生成分区键
• 对格网边缘2000米范围内的点，额外分配到相邻格网的分区参与计算，避免跨格网的同一聚类被拆分
• 按格网分区后在窗口内执行聚类，最后合并去重得到全局聚类ID
  参考代码：

WITH gridded_points AS (
  SELECT 
    point,
    ST_SnapToGrid(point, 4000) AS grid_cell,
    -- 判断是否为格网边缘点，需要跨分区计算
    ST_DWithin(point, ST_Boundary(ST_SnapToGrid(point, 4000)), 2000) AS is_edge_point
  FROM mytable
),
-- 给边缘点生成相邻格网的分区映射
partitioned_points AS (
  SELECT point, grid_cell FROM gridded_points
  UNION ALL
  SELECT 
    p.point,
    ST_SnapToGrid(ST_Translate(p.point, dx, dy), 4000) AS grid_cell
  FROM gridded_points p
  CROSS JOIN UNNEST([-4000, 0, 4000]) dx
  CROSS JOIN UNNEST([-4000, 0, 4000]) dy
  WHERE p.is_edge_point = TRUE
    AND (dx != 0 OR dy != 0)
),
-- 单格网内执行聚类
single_grid_clustered AS (
  SELECT 
    point,
    ST_CLUSTERDBSCAN(point, 2000, 200) OVER (PARTITION BY grid_cell) AS local_cluster_id,
    grid_cell
  FROM partitioned_points
)
-- 去重生成全局唯一聚类ID
SELECT DISTINCT
  point,
  FARM_FINGERPRINT(CONCAT(ST_ASGEOJSON(grid_cell), local_cluster_id)) AS global_cluster_id
FROM single_grid_clustered
WHERE local_cluster_id IS NOT NULL

方案2：采样预聚类后全量回溯（性能最高，适合精度要求宽松场景） #

• 先从全量数据中随机采样10%~20%的点，执行全量DBSCAN得到所有聚类的核心点集合
• 对全量未采样的点，通过ST_DWithin匹配最近的核心点，分配对应聚类ID即可
  内存占用仅为全量聚类的1/5~1/10，适合数据分布集中、对聚类边界精度要求不高的场景。

方案3：调整参数降低计算复杂度 #

• 你当前使用的min_points参数为200，适当调大该参数可以减少核心点数量，直接降低DBSCAN的内存开销
• 如果业务允许，适当增大搜索半径也可以减少聚类总数，降低计算压力

方案4：启用BigQuery批量执行模式 #

如果不需要修改聚类逻辑，可以在BigQuery查询设置中开启批量执行模式，平台会为大查询分配更多计算资源，仅需接受更长的排队等待时间即可。

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