sql - Bigquery - 查找何时达到总和

Question

我试图在 BigQuery 上使用 NOAA 每日天气数据的全球表面摘要来查找 2012 年总降水量超过 2 英寸的日期以及以下 stn 和 wban 值 722616 和 03032。

这是我到目前为止构建的查询：

#standardSQL
select a.prcp, concat(year, mo, da) as date, a.stn, a.wban
from `bigquery-public-data.noaa_gsod.gsod*` a
where _TABLE_SUFFIX = '2012' and a.stn = '722616' and a.wban = '03032'
order by date;

但是，我不确定如何实际输出 prcp > 2 的日期值。

任何帮助表示赞赏

谢谢！

标签： sqlgoogle-bigquery

Answer 1

您可以使用累积总和：

select *
from (select prcp, concat(year, mo, da) as date, stn, wban,
             sum(prcp) over (partition by year order by mo, da) as running_prcp
      from `bigquery-public-data.noaa_gsod.gsod*` g
      where _TABLE_SUFFIX = '2012' and stn = '722616' and wban = '03032'
     ) x
where running_prcp >= 2.0 and running_prcp - prcp < 2.0
order by date;