samuelf samuelf - 1 month ago 11
SQL Question

Calculate moving weather stats in PostgreSQL

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:

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


This results in the following:

postgres result

but I'm trying to achieve something more like this:

needed postgres result (edited manually)

I feel like all the pieces are there in regards to window functions range & filter and nested queries but I'm not sure how to pull it all together. Also the above data is just a subset of the actual dataset, the entire dataset is just over half a million rows.

vkp vkp
Answer

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

Sample Demo

Comments