建站推广网站排名,中国最好的包装设计公司,博物馆门户网站建设优势,程序员帮人做黑彩网站一、说明 我撰写本文的目的是通过引导您完成一个示例来帮助您了解 TVF 以及如何使用它们#xff0c;该示例解决了时间序列分析中常见的缺失日期问题。 我们将介绍#xff1a; 如何生成日期以填补数据中缺失的空白如何创建 TVF 和参数的使用如何呼叫 TVF我们将考虑扩展我们的日… 一、说明 我撰写本文的目的是通过引导您完成一个示例来帮助您了解 TVF 以及如何使用它们该示例解决了时间序列分析中常见的缺失日期问题。 我们将介绍 如何生成日期以填补数据中缺失的空白如何创建 TVF 和参数的使用如何呼叫 TVF我们将考虑扩展我们的日期生成器以获得更大的灵活性。最后我将分享如何访问我的TVF并向您介绍一个名为BigFunctions的开源项目。 二、简述 在某些情况下数据为零的日期很重要必须在数据集中显示/包含。例如 企业可以从识别零售中没有销售的天数中受益。这些日子受到假期或客户行为变化的影响。查找数据中缺失的日期有助于揭示由系统故障或不完整的数据捕获引起的异常或异常值从而提高数据质量。显示缺少的日期是实现此目标的有用工具。 这些缺失的日期可能会导致分析和可视化问题。因此您需要一个解决方案来确保输出中存在所有日期即使没有相应的数据也是如此。 在本文结束时您将拥有自己的 TVF它可以生成这个...... 从一行代码开始吧 三、缺失日期如何补救 请考虑以下方案您运行了一个查询该查询按日期提供过去四周的总调查响应结果。然后您将结果导入 Google 表格以快速可视化数据。 上图没有突出显示任何缺失的数据;它看起来完全符合预期。即使您选择在 x 轴上显示所有日期您也没有注意到 7 月中缺少的两天也是可以原谅的。 上图是真实数据并真实反应7月缺少两天的数据因而数据较窄。从中可以看出Google的数据填充是天衣无缝的感觉问题是离开Google我们自己是否依然能够处理好这种缺失问题答案是需要经过一番研究。 四、我们如何解决这个问题 为了解决这个问题我创建了我喜欢称之为日期轴的东西。 此日期/周/月列无论需要什么时间段都是与正在分析的数据集分开构建的。这可确保日期是独立的并且不依赖于数据存在。 创建日期轴相当简单尽管如果您经常需要创建一个则很麻烦。 下面是一个简单的示例它生成2023-6-16到 2023-6-19 日之间的日期。
WITH date_axis as (SELECTdates
FROMUNNEST(generate_date_array(2023-06-19,2023-07-16)) as dates
)SELECTdates
FROMdate_axis generate_date_array函数是其中的关键部分但正如函数名称所暗示的那样输出以数组形式返回。因此我们必须在下一步中取消嵌套展平此数组。 日期轴存在于 CTE 中因为我们需要将其视为一个单独的表将我们的实际数据左连接到日期列表。 WITH date_axis as (SELECTdates
FROMUNNEST(generate_date_array(2023-06-19,2023-07-16)) as dates
)SELECTdates,responses as original_responses,ifnull(responses,0) as new_responses
FROMdate_axis as axis
LEFT JOINspreadsheep-20220603.Case_Studies.survey_responses as surveyON axis.dates survey.date 正如您在上面看到的我们在survey_responses表中为 7 月 2 日和 1 日的空值因为这些日期不存在。使用日期轴我们可以轻松发现这些并适当地处理它们在这种情况下空值将替换为0。 重新绘制更新的数据我们现在捕捉到7月初缺乏回复的情况。 五、关于TVF的使用table value Function 5.1 什么是TVF TVF 是表值函数的缩写table value Function。与 UDF用户定义函数非常相似它们允许您指定一系列任务这些任务将在调用自定义函数时运行。 两者之间的区别在于 UDF 为数据集中的每一行返回结果而 TVF 返回整个表。 您可能想知道如果 CTE 方法完美地完成了这项工作那有什么意义。好吧在 TVF 中我们可以扩展日期轴函数的功能和可重用性并简化我们的代码。 有许多创造性和有用的方法来使用 TVF在本文中我们将使用一种方法来生成日期轴。 5.2 创建TVF CREATE OR REPLACE TABLE FUNCTION spreadsheep-20220603.Case_Studies.generate_dates(start_date DATE, end_date DATE)
AS (
SELECTdates
FROMUNNEST(generate_date_array(start_date,end_date)) as dates
) 创建 TVF 既好又容易;从“开始然后是要在项目中保存 TVF 的位置。然后您可以添加参数我们在此示例中添加了两个参数。create or replace table function 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)
)SELECTdates,responses as original_responses,ifnull(responses,0) as new_responses
FROMdate_axis as axis
LEFT JOINspreadsheep-20220603.Case_Studies.survey_responses as surveyON axis.dates survey.date 5.3 在TVF上扩展 到目前为止该函数非常有限因为它只提供日期。如果我们想要一周的开始日期一周从星期日开始或者我们想要过去几年的季度开始和结束日期该怎么办 虽然我们可以将该逻辑添加到调用 TVF 的 CTE 中但让我们在 TVF 中处理它以便在需要时它就在那里。 我的最终版本增加了一些其他可能性具体取决于您是否需要每周、每月或每季度的日期范围。 CREATE OR REPLACE TABLE FUNCTION spreadsheep-20220603.Case_Studies.generate_dates(start_date DATE, end_date DATE)
OPTIONS (descriptionGenerate a table of dates) AS (
(
selectdate,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) 摄影Benjamin Davies on Unsplash 5.4 访问BigFunctions 为了节省一些时间您不必在项目中创建此 TVF;您可以使用公共版本该版本存在于 BigFunctions 开源项目中。 要将 BigFunctions 添加到项目中您可以使用资源管理器添加功能然后“按名称为项目加星标”如下所示。 这些函数在每个区域中都可用在每个数据集中您可以在“例程”下找到generate_dates。试试下面的代码 SELECT * FROM bigfunctions.europe_west2.generate_dates(2022-01-01, 2023-01-01); 有关 BigFunctions 的更多详细信息可以在这里找到其中充满了很棒的自定义函数其中一些甚至使用 Python 来运行各种整洁的东西。如果您在日常角色中使用 BigQuery请查看它。
