I'm trying to calculate the days since last rain and the amount of rain in that event for each day in my PostgreSQL table of weather data. I've been trying to achieve this with window functions but the limitation of ranges having to be unbounded has left me a bit stuck on how to proceed.
Here's the query I have so far:
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
The key here is to group the observations starting from the first occurrence of rain>0 value to the next occurrence of rain>0 value. Thereafter you can use window functions to calculate the needed columns.
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