如何在BigQuery中按地理维度分组并保留Geography字段

你遇到的问题是BigQuery不允许直接对GEOGRAPHY类型字段执行GROUP BY操作，但由于h3id和geog是一一对应的（每个H3 ID对应唯一的地理区域），可以通过以下两种方式实现分组同时保留地理字段：

方法一：用聚合函数包裹GEOGRAPHY字段 #

利用ANY_VALUE()这类聚合函数，直接在SELECT中保留与分组键唯一对应的地理字段，绕过GROUP BY对地理类型的限制：

SELECT 
    h3s.h3id, 
    ANY_VALUE(h3s.geog) AS geog,
    MIN(ST_DISTANCE(`carto-os`.carto.H3_CENTER(htsp.h3id), `carto-os`.carto.H3_CENTER(h3s.h3id))) AS min_distance
FROM 
    rgns_h3_geogs_clipped h3s
CROSS JOIN 
    hotspot_centers htsp
GROUP BY 
    h3s.h3id

ANY_VALUE()会返回分组内任意一个值，因为每个h3id对应唯一的geog，所以最终得到的就是正确的地理字段值。

方法二：先聚合再关联原表 #

先按h3id分组计算最小距离，再通过h3id关联原表获取对应的地理字段，逻辑更直观：

WITH min_distances AS (
    SELECT 
        h3s.h3id, 
        MIN(ST_DISTANCE(`carto-os`.carto.H3_CENTER(htsp.h3id), `carto-os`.carto.H3_CENTER(h3s.h3id))) AS min_distance
    FROM 
        rgns_h3_geogs_clipped h3s
    CROSS JOIN 
        hotspot_centers htsp
    GROUP BY 
        h3s.h3id
)
SELECT 
    md.h3id, 
    h3s.geog, 
    md.min_distance
FROM min_distances md
JOIN rgns_h3_geogs_clipped h3s ON md.h3id = h3s.h3id

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