「時系列分析のための欠落した日付の修正方法」

欠落した日付の修正方法

BigQueryでTVFを使用して、時系列分析のための日付範囲を簡単に生成する方法を学びましょう。

この記事では、TVFの理解と使用方法を説明することで、時系列分析で一般的な欠落した日付の問題に対処する例を通じて、あなたをサポートすることが目標です。

いくつかの状況では、データが存在しない日付は重要であり、データセットに表示/含まれる必要があります。例えば：

• 小売業では、売上がない日を特定することで利益を得ることができます。これらの日は休日や顧客の行動の変化に影響を受けます。
• データの欠落した日付を見つけることで、システムの障害や不完全なデータのキャプチャによる異常や外れ値を明らかにすることで、データの品質を向上させることができます。欠落した日付を表示することは、この目標を達成するための有用なツールです。

これらの欠落した日付は、分析や可視化に問題を引き起こす可能性があります。したがって、対応するデータが存在しない場合でも、すべての日付が出力に存在することを保証するソリューションが必要です。

• 「ChatGPTのリリースはオープンデータの生産に影響を与えているのか？ 研究者が調査し、人気を集めるLLMがStackOverflowのコンテンツの大幅な減少をもたらしていることを検証」
• 自分自身のデータを使用して、要約と質問応答のために生成型AI基盤モデルを使用してください
• 「データの海を航海する：スタートアップが自律的な海洋モニタリングをチャートする」

この記事の最後まで読むと、たった1行のコードで日付範囲を生成することができる独自のTVFを持つようになります！

単一のコード行から！

以下の内容をカバーします：

• データの欠落した箇所を埋めるための日付の生成方法
• TVFを作成する方法とパラメータの使用方法
• TVFを呼び出す方法
• 日付ジェネレータを拡張して柔軟性を向上させる方法を見ていきます。
• 最後に、私のTVFにアクセスする方法と、BigFunctionsというオープンソースプロジェクトについて紹介します。

問題

次のシナリオを考えてみましょう：直近4週間の日付ごとのアンケート回答の結果を提供するクエリを実行しました。その後、結果をGoogleシートに持ち込んでデータを素早く可視化します。

上記のチャートは、欠落したデータを目立たせることはありません。期待どおりに見えます。x軸にすべての日付を表示することを選択しても、7月の2つの欠落した日を見逃すことは許容されます。

この問題を解決するにはどうすればいいのか

TVFのトピックに入る前に、このような問題を解決するための私の方法と、なぜそれをTVFにまとめたのかについて話しましょう。

この問題を解決するために、私は日付軸と呼ぶものを作成します。この日付/週/月などの列は、分析しているデータセットとは別に構築されます。これにより、日付が独立して存在し、データの存在に依存しないことが保証されます。

日付軸の作成は比較的簡単ですが、頻繁に作成する必要がある場合は手間がかかります。

以下は、2023年6月19日から2023年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日にnull値が存在しないため、null値が表示されます。日付軸を使用すると、これを簡単に見つけて適切に処理することができます。このシナリオでは、null値は0に置換されます。

更新されたデータを再プロットすると、7月の初めに回答がないことがわかります。

TVFとは何ですか？

TVFはTable-Valued Functionの略です。UDF(User-Defined Functions)と同様、カスタム関数が呼び出されるたびに実行される一連のタスクを指定することができます。

2つの違いは、UDFはデータセットの各行に対して結果を返し、TVFはテーブル全体を返すことです。

CTEのアプローチで完璧に処理できる場合、なぜTVFが必要なのか疑問に思うかもしれません。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を保存するプロジェクトの場所を指定します。次に、この例では2つのパラメータを追加しました。

start_date DATE, end_date DATE

以下のように、これら2つのパラメータは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

We will continue to update VoAGI; if you have any questions or suggestions, please contact us!