はじめに
年度や月次、週次といった時系列単位でデータ分析をしたいことがあると思います。
この場合に時系列データを扱いやすくするために、連続した日付を持つカレンダーテーブルを活用します。
カレンダーテーブルを作成するには、UNNEST関数とGENERATE_DATE_ARRAY関数の2つを用います。
UNNEST関数
UNNEST関数では、配列（ARRAY）を取得し、ARRAY に含まれる各要素を 1 行にしたテーブルを返します。
つまり、フィールドに入っている複数データを分解して１行ずつ展開されることになります。
GENERATE_DATE_ARRAY関数
GENERATE_DATE_ARRAY関数は日付の配列を返します。
関数としては、次の形をとります。
” GENERATE_DATE_ARRAY (start_date, end_date [ , date_part ] ) “start_dateからend_dateまでの日付の配列データが抽出されます。
例として、次のクエリを実行します。
” GENERATE_DATE_ARRAY ('2023-06-01', '2023-06-05') ”実行した結果、以下のような結果になります。
[ "2023-06-01", "2023-06-02", "2023-06-03", "2023-06-04", "2023-06-05" ]これは、１つのフィールドに日付データが複数入っていることになります。
カレンダーテーブル
上記で説明した2つの関数を用いてカレンダーテーブルを作成します。
上記の結果にUNNEST関数を用いると、日付のデータを１列に展開することができます。
ここで、Magiqで日付を扱う際の注意事項として、「 /*@type date*/ 」を日付の後に加える必要があります。
クエリ上で日付と文字列を明示的に区別するために、「 /*@type date*/ 」を追加します。
SELECT ＊
FROM
UNNEST(GENERATE_DATE_ARRAY('2023-06-01' /*@type date*/, '2023-06-05' /*@type date*/)上記のSQLを実行すると以下のような結果になります。
1 2023-06-01
2 2023-06-02
3 2023-06-03
4 2023-06-04
5 2023-06-05上記の結果を踏まえて、日付を変更することでカレンダーテーブルを作成することができます。
また、end_dateの部分にDATE_ADD関数を加えると、一年後までの日付を指定して羅列することができます。
カレンダーテーブルの作成
最後に、Magiqでカレンダーテーブルを作成する手順について説明します。
基本的なSQL文は上記で説明したとおりです。
まず、主テーブルから関数を選択します。
関数ビルダで、UNNEST関数を選択し、引数にGENERATE_DATE_ARRAY関数を選択します。
GENERATE_DATE_ARRAY関数の引数１に最初の日付、引数２に最後の日付を入力します。
この時、引数で日付を選択することで「 /*@type date*/ 」が自動的に追加されます。
最後に、取得フィールドに＊を加えて、SQL文は完成となります。
結果は以下のようになります。
| 1 | 2023-06-21 |
| 2 | 2023-06-22 |
| 3 | 2023-06-23 |
| 4 | 2023-06-24 |
| 5 | 2023-06-25 |
| 6 | 2023-06-26 |
| 7 | 2023-06-27 |
| 8 | 2023-06-28 |
Magiqでのカレンダーテーブルの作成はこのようになります。
まとめ
カレンダーテーブルの作成では、GENERATE_DATE_ARRAY関数で日付のデータを作成し、UNNEST関数で日付を羅列したデータにします。
GENERATE_DATE_ARRAY関数で日付の調整を行うことと、Magiq特有の「 /*@type date*/ 」を付け加えることに注意して作成してみてください。