Postgresql嵌套SELECT查询如何聚合并整理多行气象预警数据

你可以用DISTINCT ON先按「区域+预警事件」分组取每个分组内最新过期的生效预警，再用条件聚合把同一区域的不同预警字段合并为一行，不需要单独创建多个子视图，完整SQL如下：

WITH latest_alerts AS (
    -- 第一步：筛选所有生效中的预警，按区域+事件分组取最新过期的那条
    SELECT DISTINCT ON (areadesc, event)
        areadesc,
        event,
        effective,
        expires,
        severity
    FROM country
    WHERE 
        expires >= now() 
        AND now() >= effective
        AND event IN ('Thunderstorms', 'Wind', 'Rain') -- 你需要关注的预警类型
    ORDER BY 
        areadesc, 
        event, 
        expires DESC -- 最新过期的排在最前，DISTINCT ON会取第一条
)
-- 第二步：按区域聚合，把不同预警的字段合并为一行，同时关联空间表取几何数据
SELECT
    a.areadesc,
    -- 雷暴预警相关字段
    MAX(a.severity) FILTER (WHERE a.event = 'Thunderstorms') AS thunderstorms_severity,
    MAX(a.effective) FILTER (WHERE a.event = 'Thunderstorms') AS thunderstorms_effective,
    MAX(a.expires) FILTER (WHERE a.event = 'Thunderstorms') AS thunderstorms_expires,
    -- 大风预警相关字段
    MAX(a.severity) FILTER (WHERE a.event = 'Wind') AS wind_severity,
    MAX(a.effective) FILTER (WHERE a.event = 'Wind') AS wind_effective,
    MAX(a.expires) FILTER (WHERE a.event = 'Wind') AS wind_expires,
    -- 降雨预警相关字段
    MAX(a.severity) FILTER (WHERE a.event = 'Rain') AS rain_severity,
    MAX(a.effective) FILTER (WHERE a.event = 'Rain') AS rain_effective,
    MAX(a.expires) FILTER (WHERE a.event = 'Rain') AS rain_expires,
    d.geom
FROM latest_alerts a
LEFT JOIN country_districts d ON a.areadesc::text = d.name_en::text
GROUP BY a.areadesc, d.geom
ORDER BY a.areadesc;

方案说明 #

• 用CTE（WITH子句）代替多个独立视图，一次性完成最新预警的筛选，后续新增预警类型只需要在IN条件和聚合字段部分补充对应配置即可
• FILTER是PostgreSQL特有的条件聚合语法，比CASE WHEN写法更简洁，能在聚合时仅处理符合条件的行
• 每个区域的所有有效预警会合并为一行数据，发布到Geoserver后不会出现多层叠加的问题，也可以直接在Leaflet端按不同预警等级做样式渲染
• 如果需要保留无任何有效预警的区域，只需要把FROM latest_alerts a改成FROM country_districts d，再左连latest_alerts a即可

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