如何用SQL窗口函数找出各类天气事件的最近两次发生记录？

你的基础SQL已经正确计算了每个事件与前一同类型事件的间隔天数，接下来只需要筛选出每种天气类型中间隔最小的那一组记录即可。这里提供两种简洁的实现方式，你可以根据自己的习惯选择：

方法一：CTE+聚合关联 #

WITH event_intervals AS (
    SELECT 
        type, 
        day, 
        day - LAG(day, 1) OVER (PARTITION BY type ORDER BY day) AS days_since_previous
    FROM weather
)
SELECT 
    ei.type, 
    ei.day, 
    ei.days_since_previous
FROM event_intervals ei
JOIN (
    SELECT 
        type, 
        MIN(days_since_previous) AS min_interval
    FROM event_intervals
    WHERE days_since_previous > 0  -- 过滤掉无前置事件的首条记录
    GROUP BY type
) mi ON ei.type = mi.type AND ei.days_since_previous = mi.min_interval;

方法二：窗口函数直接筛选 #

SELECT type, day, days_since_previous
FROM (
    SELECT 
        type, 
        day, 
        day - LAG(day, 1) OVER (PARTITION BY type ORDER BY day) AS days_since_previous,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY (day - LAG(day, 1) OVER (PARTITION BY type ORDER BY day)) ASC) AS rn
    FROM weather
) sub
WHERE rn = 1 AND days_since_previous > 0;

逻辑说明： #

1. 第一步（CTE或子查询阶段）：和你原本的SQL逻辑一致，计算每个事件与前一个同类型事件的间隔天数days_since_previous。
2. 第二步：
   • 方法一中，先按天气类型分组，找出每组的最小间隔天数，再通过关联匹配到对应的具体事件记录。
   • 方法二中，利用ROW_NUMBER()窗口函数，按天气类型分组后，以间隔天数升序排序，直接取每组的第一条（即间隔最小的那条记录）。
3. 加上days_since_previous > 0的条件，是为了排除每种类型的第一条事件（它没有前置事件，间隔为0，不属于我们要找的"两次事件"范畴）。

执行上述任意一种SQL，就能得到你期望的结果：

type day days_since_previous
rain 61 3
hurricane 178 43
thunderstorm 238 16

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