如何在 Google BigQuery 中进行低通滤波-育师

原文：towardsdatascience.com/how-to-low-pass-filter-in-google-bigquery-3eefa082b497

当处理时间序列数据时，应用滤波以移除噪声可能很重要。这个故事展示了如何在 SQL/BigQuery 中实现低通滤波器，这在改进机器学习特征时可能很有用。

时间序列数据的滤波是数据科学中最有用的预处理工具之一。实际上，数据几乎总是信号和噪声的组合，其中噪声不仅由缺乏周期性定义，而且也不代表感兴趣的信息。例如，想象一下零售店的每日访问量。如果您对季节性变化如何影响访问量感兴趣，您可能对由于工作日变化而产生的短期模式不感兴趣（周六的总体访问量可能比周一高，但这不是您感兴趣的）。

时间序列滤波是您数据的清洁工具

尽管这可能在数据中看起来像是一个小问题，但噪声或不相关信息（如短期访问模式）确实会增加您的特征复杂度，从而影响您的模型。如果不移除这些噪声，您应该相应地调整模型复杂度和训练数据量，以避免过拟合。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/5ec370f706ab5c1a45a79e1f3354b82e.png

图 1：表示快速和慢速振荡信号混合的合成数据。蓝色信号代表潜在的噪声时间序列特征，而红色信号代表表示感兴趣的季节性信息的滤波版本。

这就是滤波发挥作用的地方。类似于如何从训练集中过滤掉异常值或从特征集中过滤掉不那么重要的指标，时间序列滤波从时间序列特征中移除噪声。简而言之：时间序列滤波是您数据的清洁工具。应用时间序列滤波将限制您的数据仅反映您感兴趣的频率（或时间模式），从而产生更干净的信号，这将增强您后续的统计或机器学习模型（见图 1 的合成示例）。

什么是滤波器？

本故事的范围不包括对滤波器是什么以及它是如何工作的详细说明（这通常是一个非常复杂的话题）。然而，从高层次来看，滤波可以被视为通过应用另一个信号（也称为核或滤波函数）来修改输入信号。

由于滤波器的主要目的是将数据限制在某种模式（从而移除表示噪声的模式），因此确保核的设计方式与值得保留的信号（感兴趣的信号）产生共鸣是至关重要的。在成功定义核之后，原始输入信号与核的组合将提供信号的清洁版本（称为卷积）。

需要强调的是，这种信号和核之间的组合既可以作为窗口函数应用，也可以通过傅里叶变换实现。由于傅里叶变换的实现需要复数，而截至目前 Google BigQuery 尚未原生支持复数，因此这个故事将专注于使用窗口函数的第一种方法。

简而言之：时间序列滤波器也可以被视为仅仅是输入信号与预定义核的时域乘积。

为什么为机器学习过滤特征？

如上所述，允许机器学习模型的输入特征中有大量噪声是不推荐的。噪声会损害数据质量，从而对结果模型产生直接影响（垃圾输入，垃圾输出）。

然而，即使附加的时间序列模式不是严格意义上的噪声，而是代表不同信息的混合，出于许多原因，将信息分离到不同的特征中也是一种好的做法：

特征重要性：分离允许单独针对时间序列信息调查特征重要性和模型可解释性。日变化可能比季节性更重要或更不重要。但如果没有将信息拆分为单独的特征，那么调查将具有挑战性。
敏感损失函数：确保模型学习它应该学习的内容。如果目标是训练一个预测零售市场季节性影响的模型，您可能希望从特征中移除任何日变化。否则，损失函数可能会对日变化敏感，并基于错误信息更新权重。
过拟合：尽管提供更多特征或增加模型复杂性以处理复杂输入特征非常容易，但首先关注特征工程以防止过拟合是一种好的做法。

示例用例：预测餐厅访问

为了提供一个过滤器的实际应用案例，我们将使用案例来构建一个预测餐厅访问的模型（图 2）。当考虑美国的这个案例时，某些食品类型的消费可能与季节性温度有关。因此，天气数据可能是餐厅访问的潜在预测因子（在许多其他特征中）。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/02d35e9b8d5045d6e534fd3aedd67963.png

图 2:展示了一个预测餐厅访问量的潜在产品的示意图。

然而，天气数据包括长期（气候）和短期（天气）数据。根据你的详细建模方法，在数据中应用过滤并去除短期模式可能是有益的。

在 Google BigQuery 中实现窗口过滤

既然我们已经定义了一个潜在的使用案例，我们将一步一步地介绍如何在 Google BigQuery 中获取数据、定义过滤函数以及应用过滤。

1. 获取天气数据

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/cd0899a1cfd8227b983212f4fddf2b25.png

图 3:此图显示了 2022 年纽约市中央公园的平均每日温度（摄氏度）。数据已从全球历史气候网络导出，并按照以下查询进行处理。

第一步是获取天气数据。你可以获取任何你感兴趣的数据。为了简单起见，这里我们将获取 BigQuery 中公开可用的天气数据（见以下代码）。为此，我们定义了获取天气数据的时间范围和位置。以下查询显示了获取这些数据的简便性，图 3 绘制了 2022 年纽约市中央公园的平均温度。请注意，以下代码的详细说明超出了本文的范围，因为它“只是”获取示例数据。

DECLARE input_year INT64 DEFAULT2022;--time DECLARE location STRING DEFAULT"NY Central Park";--name DECLARE geom GEOGRAPHY DEFAULT ST_GEOGPOINT(-73.974282,40.771464);--location WITH--Fetch weather stations that have&gt;360dayswithtemperature recordedinthe US.stations_with_data AS(SELECTid,COUNT(DISTINCT CASE WHEN element="TMAX"THEN date ELSE NULL END)tmax_date_n FROM `bigquery-public-data.ghcn_d.ghcnd_stations` JOIN `bigquery-public-data.ghcn_d.ghcnd_*` USING(id)WHERE qflag IS NULL AND element IN('TMIN','TMAX')AND _TABLE_SUFFIX=CAST(input_year AS STRING)AND value IS NOT NULL GROUP BYidHAVING(tmax_date_n&gt;=360)),--calculate the linear distance between the location of interest(NYC Central Park)andeach weather station.calc_distance AS(SELECT s.id,location,s.state,ST_DISTANCE(geom,ST_GEOGPOINT(s.longitude,s.latitude))AS distance FROM `bigquery-public-data.ghcn_d.ghcnd_stations` s JOIN stations_with_data USING(id)WHERE ST_DWITHIN(geom,ST_GEOGPOINT(s.longitude,s.latitude),10000)),--pick closest weather station fetch_closest AS(SELECT location,ARRAY_AGG(STRUCT(id,state,distance)ORDER BY distance LIMIT1)station FROM calc_distance GROUP BY1),--Unnest stationandtransform distance to KM unnest_closest AS(SELECT location,id,CAST(distance/1000AS INT64)distance_km FROM fetch_closest,UNNEST(station)),--fetch weather data onlyforthe closest station get_weather AS(SELECT location,date,IF(element='TMIN',value,NULL)AS tmin,IF(element='TMAX',value,NULL)AS tmax FROM `bigquery-public-data.ghcn_d.ghcnd_*` JOIN unnest_closest USING(id)WHERE qflag IS NULL AND element IN('TMIN','TMAX')AND _TABLE_SUFFIX=CAST(input_year AS STRING))--take average betweenmin()andmax()anddivide by10to get average daily temperatureinCelsius SELECT location,date,CAST((MAX(tmin)+MAX(tmax))/2AS INT64)/10AS avg_temp FROM get_weather GROUP BY location,date

尽管这段代码相当长，但它只是获取了一些我们想要过滤的数据（这里：2022 年纽约市中央公园附近气象站的数据）并对这些数据进行了一些转换（创建每日平均温度）。如果你有自己的数据想要过滤，你可以忽略这段代码，继续处理你的数据。

2. 定义过滤函数

现在我们有了想要过滤的数据，我们需要定义核/过滤函数。不幸的是，BigQuery 原生不支持复数，这使得使用傅里叶变换和逆傅里叶变换实现更有效的过滤变得非常复杂。然而，正如我们之前所学的，使用实数窗口函数实现过滤是可能的。因此，我们将定义一个汉宁窗形式的核，如下所示：

CREATE TEMP FUNCTION HANN(x float64)AS(0.5*(1-COS((2*ACOS(-1)*x)/(28-1))));

此代码定义了一个临时函数，称为"Hann"，它接受一个输入值 x 并将其转换为长度为 28 的汉宁窗口表示。如果您对不同的滤波器长度感兴趣，或者想对此输入进行参数化，您必须在这里调整28。其余的数学遵循汉宁窗口的公式（从Wikipedia获取）。

3. 应用过滤器

现在我们有了想要过滤的数据，并且已经定义了过滤器函数（上面的核），我们可以将这些全部放在一起，创建一个窗口函数，该函数将核与数据相乘，类似于移动平均（只是由汉宁函数加权）。图 4 显示了当汉宁窗口应用于天气数据时下面代码的结果，这将导致一条平滑的线，代表更长期的天气趋势。

--define hann gaussianwith28window length CREATE TEMP FUNCTION HANN(x float64)AS(0.5*(1-COS((2*ACOS(-1)*x)/(28-1))));WITH--fetch the weather dataasshown before weather_data AS(SELECT location,date,avg_temp FROM `your_project.your_dataset.your_weather` GROUP BY location,date,distance_km),--create kernel/filterfunctionandstandardise set_kernel AS(SELECT HANN(k)/SUM(HANN(k))OVER()AS kernel,ROW_NUMBER()OVER()AS idx FROM UNNEST(GENERATE_ARRAY(0,27))AS k),--add row number per date toapplywindow function later add_row_number AS(SELECT date,avg_temp,ROW_NUMBER()OVER(ORDER BY date)idx FROM weather_data GROUP BY date,avg_temp),--applya window function(of length28)to every single date across previousandfollowing dates apply_window_function AS(SELECT date,ARRAY_AGG(STRUCT(date AS t,avg_temp))OVER w1 AS v FROM add_row_number WINDOW w1 AS(ORDER BY idx ROWS BETWEEN14PRECEDING AND13FOLLOWING)),--unnest that huge vector created by the window functionforsubsequent multiplication unnest_vector AS(SELECT date,t,avg_temp,ROW_NUMBER()OVER(PARTITION BY date ORDER BY t)AS idx FROM agg,UNNEST(v))--final multiplication of each windowwithkernel toapplythe filteringandsumup to get the final data SELECT date,SUM(avg_temp*COALESCE(kernel,0))AS smooth_line FROM t LEFT JOIN set_kernel USING(idx)GROUP BY date