私事ですが、料理づくりを自主練中です。テキストとして「きょうの料理」を購入して、そこから気に入った料理を作っています。もちろん、外食したり、忙しかったり、単に気分が乗らなかったり、毎日作るわけではありません。すると、料理自主練のログはおおよそ、このようなデータになります。（Google スプレッドシートに記入している実物です）
2024年9月からはじめた取り組みですのでレコード数が少ないのはご愛嬌として、このデータの特徴としては「日付が飛び飛び」というところにあります。このデータが一定期間蓄積した状態を以下とします。このテーブルをcook_masterテーブルと呼ぶことにします。
上記のテーブルを眺めていたところ、「さて、自分は何曜日によく料理を作るんだろう」と疑問に思い、分析してみたくなったとします。
結構、困ります。^^;
分子にあたる「料理をした曜日ごとの回数」は上記のテーブルから分かりますが、分母にあたる「何曜日が何回あったか？」が分からず、今知りたい指標である、曜日ごとの「料理をした日数÷日付の個数」がわからないからです。それを解決する一つの手法がこのブログ記事で紹介する「日付の配列」を利用した方法です。BigQuery上でSQLを使っています。
解法の全体像
解法の全体像としては、「9月1日から9月30日のテーブル」（date_rangeテーブルと呼ぶことにします）を別途用意し、そのテーブルを左側とします。そして、上記図1の「cook_masterテーブル」を右側として、2つのテーブルを左外部結合をします。結合キーは日付です。すると、結合済みのテーブルは次のようになります。（途中で切れてますが、date_sep列の値は実際は2024-9-30まであります）
このテーブルがあれば、以下の3つの処理を経て、課題を解決できそうです。
- data_sep列の値を関数で曜日に変換する
- dish列を、値があれば1、nullは0に変換する
- 曜日をグループ化し、値を「平均」で集計する
では、順序だてて解説していきましょう。
「日付の配列」を作る
BigQueryにGENERATE_DATE_ARRAYという関数があります。この関数を利用すると、任意の開始日から任意の終了日まで、任意の間隔で日付の配列を作成することができます。例えば2024年9月1日～9月30日までの連続した日付の配列を作成するには以下のSQL文を実行すればよいです。
見ただけで理解できるほどシンプルですが、念の為、GENERATE_DATE_ARRAY関数の引数について説明すると、以下の通りです。
- 赤の下線：開始日
- 水色の下線：終了日
- 緑の下線：どのようなステップにするか。上記では、「1」「日」としています。実は1日の場合はこちらの第3引数は省略可能です。
紫色枠に着目すると、スクショの関連で途中で切れていますが、GENERATE_DATE_ARRAY関数がうまいこと機能し、9月1日～30日の日付が取得できています。ただ、関数名にARRAYとあるように、今、日付の値は配列に入っています。
配列をフラット化して、1レコード1日付を取り出す
配列のままでは使いづらいので、UNNEST関数で配列をフラット化します。以下のSQL文でいけます。このテーブルが、結合のとき左側になるdate_rangeテーブルです。
２つのテーブルを左外部結合する
2つのテーブルを左外部結合します。9月の全部の日付を含めたいので、date_rangeテーブルを左側にします。cook_masterテーブルはWITH区の最初の塊でUNION ALLを利用して作成しています。図5のSQL文を実行した結果は図2のとおりとなります。
日付を曜日に、値を1と0に変換する
日付(date_sep列）を曜日に、値(dish列）を、値があれが1、nulなら0に変換します。図6の下半分が結果です。参考までに変換前の値も同時に掲載しています。赤枠が変換前、水色枠が変換後です。
曜日をグループ化し、値を「平均」で集計する
ここまでくれば、もう解決したようなものです。以下の曜日をグループ化し、値を「平均」で集計します。値の名前は、avg_cook_rateとします。SQL文は図7のとおりです。
結果は図8のとおりです。月曜日の100%（月曜日は毎回）から、日曜日の20%（5回の日曜日のうち1回だけ）まで、どのような頻度で料理をしているかが分かりました。
まとめ
BigQueryに用意されているGENERATE_DATE_ARRAY関数って使えるね！ということをお伝えするための記事でした。この記事では2024年9月を日付範囲としました。1ヵ月位であれば手作業でdate_rangeテーブルを作れるかもしれません。が、もし、1ヵ月、2ヵ月、あるいは1年といった長い期間だったら無理ですね。
そんなとき、この関数の存在を知っているととても助かることがあると思います。
宣伝
（ここから宣伝です）この記事をここまで読んでいただいた方は、もれなく、BigQuery(SQL)って、やっぱりできた方がいいな！！と思っていただいたんじゃないかと思います。
そういえば、SQLについて、学習用の良い動画講座があるんですよ。たくさんのレビューを頂き、5点満点中4.6点と、お陰様で評判も良いようです。よろしければどうぞ。（画像をクリックするとUdemy.comにジャンプします。）
え？SQLは本で勉強したい？なるほど、では、こちらをどうぞ。このブログ記事のテーマであるGENERATE_DATE_ARRAY関数についても触れています。
