基于数组列rel_id分组，获取每个网络下最近2个站点的SQL求助

我从空间数据库通过CTE获取了指定范围内的有轨电车站点，这些站点的rel_id是数组类型（包含1个或多个网络ID）。现在需要从这些可达站点里，按每个网络ID分组，返回每个poi_id对应的cost_m最低的2个站点。当前的CTE查询如下：

with con as (
    select 
        stops.id as stop_id,
        stops.vertex_id,
        stops.network_type,
        stops.rel_id,
        pois.id as poi_id,
        pois.geom as poi_geom,
        stops.geom as geom,
        min(st_distance(stops.geom, pois.geom))/(1.3*60) as cost_m
    from pois inner join stops on st_dwithin(stops.geom, pois.geom, 20*60/1.4)
    where pois."name" = 'Esselunga' and stops.vertex_id is not null and 'tram' = any(network_type)
    group by stop_id, vertex_id, network_type, rel_id, poi_id, poi_geom, stops.geom)
select 
    *
from con;

完全可以用SQL实现需求，无需修改rel_id的列类型或数据组织方式，核心是拆分数组并使用窗口函数筛选：

WITH con AS (
    SELECT 
        stops.id AS stop_id,
        stops.vertex_id,
        stops.network_type,
        -- 将数组类型的rel_id拆分为单个网络ID
        unnest(stops.rel_id) AS single_rel_id,
        pois.id AS poi_id,
        pois.geom AS poi_geom,
        stops.geom AS stop_geom,
        MIN(st_distance(stops.geom, pois.geom))/(1.3*60) AS cost_m
    FROM pois 
    INNER JOIN stops ON st_dwithin(stops.geom, pois.geom, 20*60/1.4)
    WHERE pois."name" = 'Esselunga' 
      AND stops.vertex_id IS NOT NULL 
      AND 'tram' = ANY(network_type)
    GROUP BY stop_id, vertex_id, network_type, stops.rel_id, poi_id, poi_geom, stops.geom
),
ranked_stops AS (
    SELECT 
        *,
        -- 按POI和单个网络ID分组，按cost_m升序排名
        ROW_NUMBER() OVER (PARTITION BY poi_id, single_rel_id ORDER BY cost_m ASC) AS rn
    FROM con
)
SELECT 
    stop_id,
    vertex_id,
    network_type,
    single_rel_id AS rel_id,
    poi_id,
    poi_geom,
    stop_geom AS geom,
    cost_m
FROM ranked_stops
WHERE rn <= 2; -- 保留每个分组下cost_m最低的2个站点

关键步骤说明 #

• 拆分数组：用unnest(stops.rel_id)把数组类型的rel_id拆分成多行，每个行对应一个独立的网络ID，实现按单个网络ID分组的基础。
• 排名筛选：通过ROW_NUMBER()窗口函数，以poi_id和拆分后的single_rel_id作为分组维度，按cost_m升序给记录排名，最后筛选出排名≤2的结果，就是每个POI对应每个网络ID的最低2个站点。

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