CODEWITHSUNDEEP
sqlgoogle-bigquery
Macterror
Macterror

Reputation: 431

Bigquery - finding records with 3 consecutive days

Using the NOAA Global Surface Summary of the Day Weather Data on BigQuery, I am trying to find the percent of weather stations that had 4 consecutive days of hail=1 in the state of Kansas and in the year 2013. A weather station is defined as concat(stn, wban)

Here is the query I built so far:

#standardSQL
select hail, concat(year, mo, da) as date, concat(a.stn, a.wban) as station, b.state
from `bigquery-public-data.noaa_gsod.gsod*` a
join `bigquery-public-data.noaa_gsod.stations` b
on a.stn=b.usaf AND a.wban=b.wban
where _TABLE_SUFFIX = '2013' and country = 'US' and state = 'KS'
order by date;

It joins it to the stations table so I can only pick Kansas as my state, but after researching how to get consecutive days I came up short. I know I will probably another join to make this work. Any help is appreciated

Thanks!

Upvotes: 0

Views: 1046

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Here is the strategy:

  • Count the number of days of hail in 4 days using window functions with a windowing clause.
  • Summarize at the station level to count the number of consecutive days.
  • Summarize to get the proportion.

I don't think there are any such stations, but the query looks like:

select avg(case when has_hail_4 > 0 then 1.0 else 0 end)
from (SELECT station, max(hail_4) as has_hail_4
      from (select hail,
                   concat(g.year, g.mo, g.da) as date, concat(g.stn, g.wban) as station, s.state,
                   SUM(CASE WHEN hail = '1' THEN 1 else 0 END) OVER
                       (partition by g.stn, g.wban ORDER BY g.year, g.mo, g.da ROWS BETWEEN CURRENT ROW and 3 FOLLOWING) as hail_4
            from `bigquery-public-data.noaa_gsod.gsod*` g join
                 `bigquery-public-data.noaa_gsod.stations` s
                 on g.stn = s.usaf AND g.wban = s.wban
            where _TABLE_SUFFIX = '2013' and s.country = 'US' and s.state = 'KS'
           ) s 
      group by station
     ) s;

Upvotes: 3

Related Questions