用BigQuery SQL处理缺失值、窗口函数和嵌套查询
照片：Todd DiemeronUnsplash
我们的数据和分析团队最近收到的一个要求是以问题的形式出现的。"每家商店年初至今的上市文章数量是多少，这个数字是如何演变的？"
找到年初至今的文章总数并不是一项复杂的分析任务。然而，当我们想展示这个数字如何随时间增长时，问题就开始了。
其原因是源数据集中的缺失值。换句话说，我们没有数据集中 每个日期的记录 ，而数据集中有每个特定商店的新上市文章的条目。
这就是挑战开始的地方。
首先，我们需要弄清楚如何填充每个商店的缺失日期。在这一步之后，需要对每个日期和商店组合的缺失值进行向前填补。最后，作为最后一步，必须计算出运行的总数。
在花了一些时间研究和挖掘BigQuery SQL教程后，我们能够找到一个简单的解决方案。
现在，我们将与你分享我们的实施方法。
如果你使用BigQuery，该解决方案有几个步骤，或者说--最好是--几个嵌套的查询了。）
问题解释：从源头到目标
让我们以可视化的形式展示这个问题**，即源数据的外观和预期的结果是什么**。
对于图形化呈现，我们使用Looker在时间序列图上显示源记录的样本和目标结果。
源数据集中的记录和预期结果的时间序列展示 [图片来自作者]
从上面图片的第一部分**（源数据集**）可以看出，在选定的日期范围内，我们有缺失的日期和每个店铺级别的相应数值。
因此，我们将解决方案分为三个步骤，以实现目标结果，并计算措施new_article_count对日期article_online_since_date和每个分区商店的运行总数。
自下而上的实施方法
首先，通过以下查询，我们能够创建假的输入数据集。
查询的结果是。
有了ListedArticlePerShop这个输入表，我们就可以开始研究自下而上的解决方案，计算每个店铺在一段时间内的运行总量。
第1步：填补每个分区（商店）缺失的日期范围
BigQuery SQL提供了一个整洁的数组函数GENERATE_DATE_ARAY，你可以指定以下输入[1]。
- start_date - 必须是一个日期
- end_date - 必须是一个日期
- INT64_expr - 一个确定用于生成日期的增量的参数；这个参数的默认值是一天
- date_part - 必须是DAY, WEEK, MONTH, QUARTER或YEAR。
通过GENERATE_ARRAY函数，我们能够创建一个具有每个车间完整日期范围的表格。
查询的结果如下。
在成功完成步骤#1后，我们现在可以将新创建的查询加入到输入表ListedArticlesPerShop。
步骤#2：将有完整日期范围的表加入到输入表中 的日期范围。
这一步很简单，因为任务是。
- 使用LEFT JOIN类型连接这两个表，并且
- 从每个表中选择相应的属性；从table_a中选择ascending_date和shop，从table**_b**中选择new_article_count（现在别名为number_of_listed_articles）。
在成功完成这部分任务后，我们现在可以计算运行总数了。
第3步：计算一段时间内和每个分区（商店）的运行总量
我们使用顶部查询中的窗口函数来计算运行总数。
SUM (number_of_listed_articles) OVER (partition BY shop) ORDER BY (ascending_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
现在，让我们把这一切包起来，呈现出顶级查询。
而最终的查询结果如下。
通过这最后一步，我们成功地完成了我们的任务。）
总结
在这篇文章中，我们展示了如何填补缺失的数值，并计算出特定指标在不同时期和每个特定分区的演变模式，只使用BigQuery SQL。
我们还介绍了如何结合不同的BigQuery函数：窗口和数组函数，以解决复杂的分析任务，并提供所需的数据洞察力。
最后，我们希望你会喜欢我们的教程，并在你的使用案例中找到它的用途。）
参考文献。
[1] BigQuery SQL 文档，访问。2022年6月3日，cloud.google.com/bigquery/do…
BigQuery SQL。有缺失日期的数据集上运行总数的演变最初发表在Medium上的Towards Data Science，在那里人们通过强调和回应这个故事来继续对话。
