如何修复时间序列分析中的缺失日期
修复时间序列分析中的缺失日期 (How to fix missing dates in time series analysis)
学习如何在BigQuery中使用TVF轻松生成时间序列分析所需的日期范围。
我写这篇文章的目标是通过一个例子帮助你理解TVF以及如何使用它们来解决时间序列分析中常见的缺失日期问题。
在某些情况下，零数据日期是重要的，必须显示/包含在数据集中。例如：
- 零销售额的日期对于零售业务有益。这些日期受到假期或客户行为变化的影响。
- 找到数据中的缺失日期可以通过揭示由系统故障或不完整数据捕捉引起的异常或离群值来提高数据质量。显示缺失日期是实现此目标的有用工具。
这些缺失日期可能会对分析和可视化产生问题。因此，您需要一种解决方案，确保输出中包含所有日期，即使没有相应的数据。
通过本文的最后，你将拥有自己的TVF，可以生成这个…
只需一行代码！
我们将涵盖：
- 如何生成日期以填补数据中的空缺
- 如何创建TVF以及参数的使用
- 如何调用TVF
- 我们将扩展我们的日期生成器以获得更大的灵活性。
- 最后，我将介绍如何访问我的TVF，并向您介绍一个名为BigFunctions的开源项目。
问题
考虑以下情况：您运行了一个查询，提供了过去四周按日期分组的总调查响应结果。然后，您将结果导入Google Sheets以快速可视化数据。
上面的图表没有突出显示任何缺失的数据；它看起来完全符合预期。即使您选择在x轴上显示所有日期，您也可能没有注意到七月份的两天缺失。
我们如何解决这个问题
在我们讨论TVF主题之前，让我们谈谈解决这个问题的方法以及为什么我将其捆绑成一个TVF。
为了解决这个问题，我创建了一个我喜欢称之为日期轴的内容。这个日期/周/月的列是独立于您正在分析的数据集而构建的。这确保日期是独立的，不依赖于数据的存在。
创建日期轴相对简单，尽管如果您经常需要创建日期轴，这可能是一项繁琐的任务。
下面是一个简单的例子，它生成2023年6月19日至7月16日之间的日期。
WITH date_axis as (SELECT datesFROM UNNEST(generate_date_array("2023-06-19","2023-07-16")) as dates)SELECT datesFROM date_axis
generate_date_array函数是关键部分，但正如函数名所示，输出以数组形式返回。因此，我们必须将该数组解嵌套（展平）以进行下一步操作。
日期轴存在于一个CTE中，因为我们需要将其视为一个单独的表，以便将实际数据左连接到日期列表。
WITH date_axis as (SELECT datesFROM UNNEST(generate_date_array("2023-06-19","2023-07-16")) as dates)SELECT dates, responses as original_responses, ifnull(responses,0) as new_responsesFROM date_axis as axisLEFT JOIN `spreadsheep-20220603.Case_Studies.survey_responses` as survey ON axis.dates = survey.date
如上所示，我们的survey_responses表中7月1日和7月2日的值为空，因为这些日期不存在。使用日期轴，我们可以轻松地发现这些并进行适当处理，在这种情况下，空值将被替换为0。
重新绘制我们的更新数据，我们现在捕捉到了7月初缺乏响应的情况。
什么是TVF？
TVF是Table-Valued Function的缩写。与UDF（User-Defined Function）类似，它们允许您指定一系列任务，在调用自定义函数时运行。
两者之间的区别在于，UDF为数据集中的每一行返回一个结果，而TVF返回整个表。
您可能会想知道，如果CTE方法完美地完成了工作，那么TVF的意义何在。好吧，在TVF中，我们可以扩展日期轴函数的功能和可重用性，简化我们的代码。
有许多创造性和有用的方法可以使用TVF，在本文中，我们将使用其中一种来生成日期轴。
创建TVF
CREATE OR REPLACE TABLE FUNCTION `spreadsheep-20220603.Case_Studies.generate_dates`(start_date DATE, end_date DATE)AS (SELECT datesFROM UNNEST(generate_date_array(start_date,end_date)) as dates)创建TVF非常简单；以create or replace table function开始，然后指定您要保存TVF的项目位置。然后，您可以添加参数，我们在此示例中添加了两个参数。
start_date DATE, end_date DATE
如下所示，这两个参数替换了我们添加到generate_date_array函数中的静态值。
unnest(generate_date_array(start_date,end_date)) as dates
创建TVF后，您可以像调用表格一样调用新函数。请注意，我在FROM子句的末尾添加了括号，以指定TVF要使用的值，起始日期为7月1日，结束日期为7月7日。
SELECT dates FROM `spreadsheep-20220603.Case_Studies.generate_dates`("2023-07-01", "2023-07-07")
现在，我们可以更新原始查询以使用新的TVF。
WITH date_axis as (SELECT dates FROM `spreadsheep-20220603.Case_Studies.generate_dates`("2023-06-19", "2023-07-16"))SELECT dates, responses as original_responses, ifnull(responses,0) as new_responsesFROM date_axis as axisLEFT JOIN `spreadsheep-20220603.Case_Studies.survey_responses` as survey ON axis.dates = survey.date对 TVF 进行扩展
到目前为止，这个函数的功能还相当有限，因为它只提供日期。如果我们想要以星期日为周开始日期，或者想要最近几年的季度开始和结束日期呢？
虽然我们可以将这个逻辑添加到调用 TVF 的 CTE 中，但让我们在 TVF 中处理它，这样每当我们需要时就可以使用它。
我的最终版本根据您需要的是每周、每月还是每季度的日期范围，添加了一些其他可能性。
CREATE OR REPLACE TABLE FUNCTION `spreadsheep-20220603.Case_Studies.generate_dates`(start_date DATE, end_date DATE)OPTIONS (description="生成日期表") AS ((select date, format_date("%a", date) as day_of_week, date_trunc(date, week(monday)) as week_start_monday, date_trunc(date, week(monday)) + 6 as week_end_monday, date_trunc(date, week(sunday)) as week_start_sunday, date_trunc(date, week(sunday)) + 6 as week_end_sunday, date_trunc(date, month) as month_start, date_add(date_trunc(date, month), interval 1 month) - 1 as month_end, date_trunc(date, quarter) as quarter_start, date_add(date_trunc(date, quarter), interval 1 quarter) - 1 as quarter_end,from unnest( generate_date_array( start_date, end_date )) as date));这样我们就可以得到文章开头看到的输出，一个查询语句就可以生成多年的日期，以及它们的周、月和季度部分。
作为一个额外的好处，我们创建的这个函数不会查询任何实际数据。这意味着它完全免费运行，而且速度非常快。
即使从 1820 年到现在生成日期也只需要 1 秒钟。
SELECT * FROM `spreadsheep-20220603.Case_Studies.generate_dates`("1820-07-01","2023-07-15")
访问我的 TVF
为了节省时间，您不必在项目中创建这个 TVF；您可以使用公共版本，它存在于 BigFunctions 开源项目中。
要将 BigFunctions 添加到您的项目中，您可以使用资源管理器的添加功能，然后按下面的示例 “按名称标记一个项目”。
这些函数可在每个区域中使用，并且在每个数据集中，您将在 “例程” 下找到 generate_dates。尝试一下下面的代码吧！
SELECT * FROM `bigfunctions.europe_west2.generate_dates`("2022-01-01", "2023-01-01");关于 BigFunctions 的更多详细信息可以在这里找到，其中包含了很多很棒的自定义函数，其中一些甚至使用 Python 运行各种有趣的东西。如果您在日常工作中使用 BigQuery，请查看一下。
这篇文章就到此结束了。如果您有任何问题，请随时评论，我会尽快回答。
我经常为 BigQuery 和 Looker Studio 写文章。如果您感兴趣，请考虑在 VoAGI 上关注我，以获取更多内容！
除非另有说明，所有图片均为作者所拍。
保持优雅，朋友们！Tom
