预测天气（6.19）的功能现已加入BigQuery ML。

引言

天气预报是一个复杂的过程，尤其对于居住在墨尔本这种天气变化多端的地区的人来说，他们可能会发现实际天气与网站上所显示的有所不同。

一般的做法是先收集大量关于大气、湿度和风力等方面的信息，然后根据我们已有的大气知识和物理模型来预测未来天气的变化。然而，由于我们对物理模型的理解有限，并且大气性质变化难以预测，因此通过这种方式得到的结果可能并不十分可靠。

在本文中，我们将探讨利用机器学习技术来预测天气的方法。我们希望通过模型分析历史数据，以便准确预测未来温度的变化趋势。

“NOAA天气数据是Google BigQuery中的一个公开可用数据集，由Google员工每天进行维护和更新。我们将利用这些数据以及最新的BigQuery ML技术来探索是否有可能进行预测。”

我们将在BigQuery中准备和预处理数据，并倚赖BigQuery ML作为我们的机器学习模型。所有这些都在BigQuery内部进行，这正是它的魅力所在。因此，您可以期待在BigQuery内部看到许多关于训练数据准备和预处理的查询，以及最终模型使用了BigQuery ML。

BigQuery维护一个数据集，其中包含来自国家海洋和大气管理局（NOAA）的最新天气数据以及世界各地不同站点的大气数据。这个数据集涵盖了从1929年至今的气候数据，并每日进行更新。

数据集名为“bigquery-public-data.noaa_gsod”，每年有一个表。因此，通过BigQuery的语法，访问这些表非常方便，甚至可以使用正则表达式进行访问。在我们的初步研究中发现，如果我们将训练数据集限制在2010年以后，仍然有足够的数据可供模型训练。因此，在创建数据集时，我们将查询相应的表，并将它们命名为“bigquery-public-data.noaa_gsod.gsod201 *”。

每个gsod表的记录都对应于来自站点的观测数据。stn字段显示站点编号，该编号可以引用到站点表中，该表保存了有关站点的信息，包括地理位置、海拔和所属国家。

每个观测都代表着一个特定的日期，其中包括温度（最小值、最大值和平均值）以及大气数据，如风速、降水量和湿度等。我们将使用该站点的观测数据作为我们模型的特征输入。

为了准备训练数据集，我们需要按照以下步骤进行操作。首先，我们要将训练数据限制在感兴趣的地理范围内。其次，我们需要将非浮点字段的值转换为浮点型。第三步是处理缺失值。最后一步是准备过去7天的天气信息和明天的温度标签字段。通过这些操作，我们的模型可以分析过去7天的温度和大气数据，并预测即将到来的一天的温度情况。

如果你能留意到所有的内容都是在SQL中，并且是在BigQuery内部操作，我将非常感激。只需想象一下，在一条记录中查询过去7天的数据，这种做法在BigQuery中被视为一个反模式，因为你的查询将永远运行下去。

地理位置和时间范围 可以改写为 地理区域和时间段

我们将仅限于使用2010年以后的数据，并且仅关注我们感兴趣的地理位置。这一操作非常简单，只需一个连接和一组where子句即可实现。

```SQL
SELECT lon, lat, elev, stn, temp,
FROM `bigquery-public-data.noaa_gsod.gsod201*` gsod ,
`bigquery-public-data.noaa_gsod.stations` stns
WHERE stns.usaf = gsod.stn and stns.country='AS'
AND lat > LAT_MIN and lat  LON_MIN and lon  LAT_MIN AND 
lat  LON_MIN AND 
lon   

  
另外一个要注意的事项是，如果你希望对地理范围进行更复杂的限制，而不仅仅是使用我们目前所采用的方框方式，那么你可以考虑查看一下BigQuery GIS。它正在测试阶段，在撰写文本时提供了更多功能。
  
Casting
  
  

  
我们不希望将字符串字段视为分类值，这与我们的预期不符。以下是示例：
  
```SQL
SELECT CAST(elev AS FLOAT64) AS elev, ...
FROM ...
SELECT CAST...
```
请注意，这是一段被截断的代码，无法完整显示。  
另外，我们可以通过以下方法将年、月和日字段组合成日期时间字段：
  
```SQL
SELECT datetime(year, mo, da, 00, 00, 00) AS datetime,
...
FROM ...
SELECT datetime(year, mo , da , 00 , 00) AS datetime,
...
```
改为：
```SQL
SELECT datetime(year, mo , da , '00' , '00' ,'00') AS `datetime`,
...
FROM ...
SELECT datetime(year, mo , da ,'00' ,'00') AS `datetime`,
...
```
  
  

  
当您查看gsod表的描述时，您会发现缺失值以特定值（例如99.9或类似值）表示。为了解决这个问题，我们将编写一个查询来用字段的平均值替换这些缺失值。具体如下：
  
```sql
SELECT IF(temp = 99.9, avg_temp, temp) AS temp2
FROM ...
JOIN (
    SELECT AVG(temp) AS avg_temp
    FROM `bigquery-public-data.noaa_gsod.gsod201*`
    WHERE temp > 99.9
)
```  
  

  
迄今为止，我们已经在感兴趣的时间和地理范围内对数据进行了投射字段，并用平均值替换了缺失值。查询中最关键的部分是将7天的数据保存在一行中。传统数据库中使用self-join来解决这个问题，但在BigQuery中，self-join被认为是一种反模式，并且可能导致引擎运行困难，特别是当涉及多个self-join时。相反地，我们建议使用在线分析处理功能作为解决方案。
  
  
1select temp ,2LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_1,3LEAD(temp, 2 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_2 ,4... ,5LEAD(temp, 7 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_7 ,6LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime ASC) as LABEL ,7...8from ...9where ...select temp ,
2LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_1,
3LEAD(temp, 2 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_2 ,
4... ,
5LEAD(temp, 7 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_7 ,
6LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime ASC) as LABEL ,
7...
8from ...
9where ...
  
lead函数返回后续行的值表达式（temp here）的值。第二个参数是偏移量。作为示例，偏移值1将返回前一天的温度，偏移值2表示两天前的温度值。同样，我们可以按升序对分区进行排序来访问明天的天气（LABEL）。
  

  
stn的分区将指示BigQuery获取具有相同值的元素并对它们进行迭代，以便按照日期时间（按降序排列）的顺序返回与同一站点相关联的连续行，并根据时间对这些行进行排序，最后返回温度。这是BigQuery中实现此功能的方法。
  
  
在这里，我们讨论了使用不同代码模式进行查询。显然，最终的查询是一个非常长的查询，它重复利用上述代码片段来获取特征视图。每个特征视图记录包括连续7天的大气和温度特征以及标记，即第8天的温度。所有字段都非常清晰，并且可以通过BigQuery ML将其输入到线性回归模型中。
  

  
BigQuery ML是BigQuery中最新的机器学习模块，目前仅支持逻辑回归中的分类和线性回归。它旨在为用户提供一种简单快捷的方式来实现他们的第一个模型。这个功能非常有价值，因为训练和验证机器学习网络被抽象成了单个查询。
  

  
训练线性回归模型就像执行以下命令一样轻而易举。
  
```SQL
CREATE or REPLACE MODEL `weather-prediction-dataset.linear_regression`
OPTIONS (
  model_type = linear_reg,
  input_label_cols = [LABEL],
  l2_reg = 0.001,
  max_iterations = 20,
  ls_init_learn_rate = 0.01,
  data_split_method = seq,
  data_split_eval_fraction = 0.2,
data_split_col=ID
)
select ...
from ...
where ...
```
  

  
我们还可以通过运动来展示另一个子集的性能度量指标：
  
```sql
SELECT * 
FROM ML.EVALUATE(
    `weather-prediction-dataset.linear_regression`,
    (
        SELECT ...
        FROM ...
        WHERE ...
    )
)
```

改写为：

```sql
SELECT * 
FROM ML.EVALUATE(
    `weather-prediction-dataset.linear_regression`,
    (
        SELECT ...
        FROM ...
        WHERE ...
    )
)
```  
获得预测结果就像轻而易举的事情一样，只需要从模型中提取即可。
  
1. 从ML.PREDICT中选择所有内容，使用weather-prediction数据集的linear_regression模型，并在from子句中指定相关条件和限制。
2. 使用以下代码进行查询：
```SQL
select * from ML.PREDICT (
    MODEL `weather-prediction=dataset.linear_regression`,
    select ...
    from ...
    where ...
)
```
3. 返回结果为所选内容。  
  

  
在本文中，我们介绍了如何处理天气预报数据，并使用该数据训练线性回归模型。根据天气模型的结果，我们得到了6.9华氏度的均方根误差（RMSE），这表明最简单的线性机器学习模型可以平均预测出约7度的温度。
  

  
  
请长按上方二维码 ▲
  
立即关注「架构师小秘圈」公众号，获取订阅服务。
  
如果这段话给您带来了启发，请帮我点个赞，非常感谢！↓

标签： 数据 模型 字段 温度