sql - 在PostgreSQL中计算移动天气统计信息

我正试图在我的PostgreSQL天气数据表中计算自上次降雨以来的天数以及该事件中每天的降雨量。我一直在尝试用窗口函数来实现这一点，但是范围的限制必须是无限的，这让我在如何继续上有些纠结。
这是我到目前为止的问题：

SELECT
        station_num,
        ob_date,
        rain,
        max(rain) OVER (PARTITION BY station_num ORDER BY ob_date ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as prev_rain_mm,
        '' as days_since_rain --haven't attempted this calculation yet
FROM
        obs_daily_ground_moisture

结果如下：
但我正努力实现这样的目标：
我觉得所有的部分都与窗口函数range&filter和嵌套查询有关，但我不确定如何将它们组合在一起。而且上述数据只是实际数据集的一个子集，整个数据集只有50多万行。

最佳答案

这里的关键是将观测值从第一次出现的rain>0值到下一次出现的rain>0值进行分组。此后，可以使用窗口函数计算所需的列。

select
x.station_num,
x.ob_date,
max(rain) over(partition by station_num,col) prev_rain,
case when rain > 0 then 0
else row_number() over(partition by station_num, col order by ob_date)-1 end days_since_rain
from (select t.*,
      sum(case when rain > 0 then 1 else 0 end) over(partition by station_num order by ob_date) col
      from t) x

Sample Demo