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)
);
If the maximum gust speed increases from 55mph, I need to fetch the details for the next 4 days. I am able to go this far only:
with t1 as(
select max_gutspeed, date
from weather_forecast
where max_gutspeed >55 )
, t2 as (
select date, row_number() over () as rn
from weather_forecast)
select distinct(rn), t1.date, t1.max_gutspeed
from t1
inner join t2 on t1.date = t2.date
order by t2.rn asc`
Also, how do I find the maximum and minimum number of days when temperature dropped?
I tried doing it with the temp tables but i am stuck and don't know the next steps.
You can construct a daterange out of your date and an interval of 4 days after that. Then list everything that's on a "date" contained <@ in ANY of those ranges. online demo
select *
from weather_forecast t
where date <@ ANY(
select daterange(date, (date+'4 days'::interval)::date, '[]')
from weather_forecast
where max_gutspeed >55 )
order by t.rn asc;