PostgreSQL条件连续记录查询及气象数据统计问题

基础表结构 #

Create table  weather_forecast (
    date date,
    temperature decimal,
    avg_humidity decimal,
    avg_dewpoint decimal, 
    avg_barometer decimal,
    avg_windspeed decimal,
    avg_gutspeed decimal,
    avg_direction decimal, 
    rainfall_month decimal, 
    rainfall_year decimal, 
    maxrain_permin decimal, 
    max_temp decimal, 
    min_temp decimal, 
    max_humidity decimal,
    min_humidity decimal, 
    max_pressure decimal, 
    min_pressure decimal, 
    max_winspeed decimal, 
    max_gutspeed decimal, 
    maxheat_index decimal, 
    month int, 
    diff_pressure decimal(7,5) 
);

需求1：获取max_gutspeed超过55mph日期的后续4天全量数据 #

你原有的CTE未正确关联后续日期，这里通过日期范围匹配实现：

WITH trigger_dates AS (
    SELECT date AS trigger_date
    FROM weather_forecast
    WHERE max_gutspeed > 55
)
SELECT wf.*
FROM weather_forecast wf
JOIN trigger_dates td
    ON wf.date BETWEEN td.trigger_date AND (td.trigger_date + INTERVAL '4 days')::date
ORDER BY td.trigger_date, wf.date;

说明： #

• 先筛选出所有触发条件的日期存入trigger_dates
• 通过BETWEEN关联原表，获取触发日期及之后4天的全量数据
• 若无需包含触发日期，可将关联条件改为wf.date > td.trigger_date AND wf.date <= td.trigger_date + INTERVAL '4 days'

需求2：找出气温连续下降天数的最大值和最小值 #

通过窗口函数标记连续下降区间，再统计区间天数：

WITH temp_changes AS (
    SELECT 
        date,
        temperature,
        -- 标记当天气温是否低于前一天：1=下降，0=未下降
        CASE WHEN temperature < LAG(temperature) OVER (ORDER BY date) THEN 1 ELSE 0 END AS is_drop
    FROM weather_forecast
    ORDER BY date
),
continuous_drops AS (
    SELECT 
        is_drop,
        -- 累计非下降次数生成分组ID，将连续下降日期归为同一组
        SUM(CASE WHEN is_drop = 0 THEN 1 ELSE 0 END) OVER (ORDER BY date) AS group_id
    FROM temp_changes
),
drop_periods AS (
    SELECT 
        group_id,
        COUNT(*) AS drop_days
    FROM continuous_drops
    WHERE is_drop = 1
    GROUP BY group_id
)
SELECT 
    COALESCE(MAX(drop_days), 0) AS max_continuous_drop_days,
    COALESCE(MIN(drop_days), 0) AS min_continuous_drop_days
FROM drop_periods;

说明： #

1. temp_changes：用LAG()函数对比当日与前日气温，标记是否下降
2. continuous_drops：通过累计非下降次数生成分组ID，实现连续下降区间的划分
3. drop_periods：统计每个连续下降区间的天数
4. 最后用MAX/MIN计算极值，COALESCE处理无连续下降的情况（返回0）

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