PostgreSQL存储过程实现3小时气象预报数据日聚合

嘿，Fabio，新年快乐！你这个需求其实不用游标就能搞定——PostgreSQL的窗口函数和聚合函数完全能处理这些非标准分组逻辑，而且效率比游标高多了。咱们一步步来解决你的问题：

核心需求拆解与实现思路 #

先明确你提到的两个特殊需求的正确处理方式：

1. 每日Top2气象描述：先统计单日每个描述的出现次数，再筛选次数最多的前2个并拼接成字符串
2. 风向计算：不能直接对角度取平均（环形数据直接平均会出错，比如10°和350°的平均应该是0°而非180°），气象领域的标准做法是矢量平均法

完整实现函数 #

我把你的草稿修改成了可直接运行的版本，每部分都加了逻辑说明：

CREATE OR REPLACE FUNCTION meteo_forecast_daily()
RETURNS TABLE (
    forecasting_date DATE,
    temperature NUMERIC(5,2),
    pressure NUMERIC(6,2),
    description VARCHAR(50),
    w_speed NUMERIC(4,2),
    w_dir NUMERIC(6,2)
) AS $$
BEGIN
    RETURN QUERY
    -- CTE 1: 统计每日每个气象描述的出现次数并排名
    WITH daily_desc_ranked AS (
        SELECT
            m.forecasting_time::date AS forecasting_date,
            m.desc AS weather_desc,
            COUNT(*) AS occurrence,
            -- 按出现次数降序排名，次数相同则并列
            RANK() OVER (PARTITION BY m.forecasting_time::date ORDER BY COUNT(*) DESC) AS rank
        FROM meteo_forecast_last_update m
        WHERE m.forecasting_time > NOW()
        GROUP BY forecasting_date, weather_desc
    ),
    -- CTE 2: 筛选每日Top2描述并拼接成字符串
    daily_top2_desc AS (
        SELECT
            forecasting_date,
            STRING_AGG(weather_desc, ', ' ORDER BY occurrence DESC) AS top_desc
        FROM daily_desc_ranked
        WHERE rank <= 2
        GROUP BY forecasting_date
    ),
    -- CTE 3: 计算风向的矢量分量（解决环形数据平均问题）
    daily_wind_vector AS (
        SELECT
            m.forecasting_time::date AS forecasting_date,
            -- u分量：东西方向（sin值）
            AVG(m.w_sp * SIN(RADIANS(m.w_dir))) AS avg_u,
            -- v分量：南北方向（cos值）
            AVG(m.w_sp * COS(RADIANS(m.w_dir))) AS avg_v,
            AVG(m.w_sp) AS avg_w_speed
        FROM meteo_forecast_last_update m
        WHERE m.forecasting_time > NOW()
        GROUP BY forecasting_date
    )
    -- 关联所有结果，生成最终日度数据
    SELECT
        dt.forecasting_date,
        ROUND(AVG(m.temperature)::NUMERIC, 2) AS temperature,
        ROUND(AVG(m.pressure)::NUMERIC, 2) AS pressure,
        dt.top_desc AS description,
        ROUND(dwv.avg_w_speed::NUMERIC, 2) AS w_speed,
        -- 将平均矢量转换为风向角度，处理负角度转为0-360范围
        ROUND(
            CASE
                WHEN degrees(ATAN2(dwv.avg_u, dwv.avg_v)) < 0 THEN degrees(ATAN2(dwv.avg_u, dwv.avg_v)) + 360
                ELSE degrees(ATAN2(dwv.avg_u, dwv.avg_v))
            END::NUMERIC, 3
        ) AS w_dir
    FROM meteo_forecast_last_update m
    JOIN daily_top2_desc dt ON m.forecasting_time::date = dt.forecasting_date
    JOIN daily_wind_vector dwv ON m.forecasting_time::date = dwv.forecasting_date
    WHERE m.forecasting_time > NOW()
    GROUP BY dt.forecasting_date, dt.top_desc, dwv.avg_u, dwv.avg_v, dwv.avg_w_speed
    ORDER BY dt.forecasting_date;
END;
$$ LANGUAGE plpgsql;

关键逻辑说明 #

1. Top2描述处理：

   • 用RANK()窗口函数给每日的气象描述按出现次数排名，次数相同会并列（比如你的示例中clear和snow都是3次，会并列第1）
   • 用STRING_AGG()将排名前2的描述拼接成逗号分隔的字符串，按出现次数降序排列

2. 风向矢量平均：

   • 先将风向角度转为弧度，计算风的东西（u）和南北（v）分量
   • 对分量取平均值后，用ATAN2()转换回角度，再处理负角度（加360转为0-360的标准风向范围）

3. 为什么不用游标：
   游标适合逐行处理的场景，但这里的分组、统计、聚合逻辑用CTE（公共表表达式）和关联查询就能高效完成，性能比游标好很多，代码也更简洁易维护。

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