Bigquery 「yyyymmdd形式」の『昨日』の日付を含んでいるデータのみ取得したい #SQL

サンプルデータ

Bigqueryのテーブル内に下記のようなテーブルがあります。

ad_name
1-20221001-xxx
2-20221002-yyy
3-20221003-zzz

→ad_nameの構成としては
{No.}-{日付(yyyymmdd形式)}-{文字列}

やりたいこと

・当日起点でad_name内に「昨日」の日付が含まれているアドのデータのみ抽出したい

例えば本日が2022/10/4だったとして、広告名が
「3-20221003-zzz」
のアドのデータのみ取得したい。

方針

Ⅰ.昨日の日付をクエリで「変数」として定義する
Ⅱ.変数に格納されている日付を元に、WHERE句でLIKE検索する

Ⅰ.昨日の日付をクエリで「変数」として定義する

Bigqueryで変数を定義する際は
declareとsetを使用すればOKです。

変数yesterdayを定義

-- 変数「昨日の日付」を定義
declare yesterday date;

-- 昨日の日付を取得し、変数『yesterday』に格納
set yesterday = DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY);

問題

ただし、これだと変数yesterdayには2022-10-03というようにyyyy-mm-dd形式で結果が格納されてしまいます。
今回ad_nameには「20221003」のようにyyyymmdd形式で日付が記載されているため、日付の形式を変換しなくてはいけません。

解決策

その際使用するのが、日付の形式を変換できる

FORMAT_TIMESTAMP("%Y%m%d", {変数名});

です。
現在の日付を取得してくる関数

CURRENT_DATE('Asia/Tokyo')

が2022/10/04のとき、

%Y は 2022 を
%m は 10 を
%d は 04 を
それぞれ指定します。
%Y%m%dと記載することで、2022-10-04を20221004に変換できるのです。

つまりまとめるとこんな感じ…

「昨日の日付」を『yyyymmdd』形式で取得し変数格納するクエリ

--変数「昨日の日付」を定義
declare yesterday date;
--変数「昨日の日付yyyymmdd形式」（FORMAT_TIMESTAMP関数はデフォルトでstring型となる）を定義
declare yesterday_yyyymmdd string;

--昨日の日付を取得し、変数『yesterday』に格納
set yesterday = DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY);
--変数『yesterday』をYYYYMMDD形式に変換し、変数『yesterday_yyyymmdd』に格納
set yesterday_yyyymmdd = FORMAT_TIMESTAMP("%Y%m%d", yesterday);

⇧
前半で変数名とデータ型をそれぞれ宣言し、
後半でyesterdayとyesterday_yyyymmddに格納するデータを関数で定義しています。

ちなみに

FORMAT_TIMESTAMP("%Y%m%d", {変数名});

で定義したデータはString型で返ってくるようです。

SQLで変数を定義するdeclareでは、変数名だけでなくデータ型も指定して宣言する必要があるのですが、最初はデータ型がわからず迷走していました。

そのため一度テストとして空のテーブルを作成し、結果をテーブルに格納してスキーマを確認することでデータ型を調べることにしました。

実際にクエリ上で空のテーブルにInsertする指示を出して確認しました。

INSERT 
INTO `Insert先のテーブル名` (yesterday, yesterday_yyyymmdd) 
VALUES (yesterday, yesterday_yyyymmdd) 
;

『yesterday』, 『yesterday_yyyymmdd』というカラムに上記で作成した変数のyesterday, yesterday_yyyymmddのValue（値）をInsertしています。
結果はこんな感じ⇩（今日が2022/10/04という前提）

yesterday	yesterday_yyyymmdd
2022-10-03	20221003
date	string

※最後のレコードはスキーマを表しています。

ようやくこれでyyyymmdd形式としての日付を手に入れられそうです。
長かったですがこれでまだ前半…。

Ⅱ.変数に格納されている日付を元に、WHERE句でLIKE検索する

あとは上記で手に入れたyyyymmdd形式の日付データを元に、WHERE句でad_nameを指定してあいまい検索すればOKです。

あいまい検索は

LIKE検索

WHERE ad_name LIKE '%丸の内%'

（例）ad_name内に「丸の内」を含んでいるデータを取得

のように記載すれば抽出可能でした。
ん…？関数ってどうやって右辺に書いたらいいんだ…？

問題

LIKE検索の条件には、任意の0文字以上の文字列として扱われる%を記載して部分一致の条件を記載するのがこれまで僕がやっていた方法でした。
ただこれ、直書きの定数ではなく、クエリ文で作成した変数に対して記載できるのでしょうか…？

解決策

ARRAYの各要素を文字列連結できる。

SELECT ARRAY_TO_STRING(['a','b','c'], '_')

を使用することにしました。
これを実行すると

result
a_b_c

というような結果になります。

これをⅠで頑張って作成した、yyyymmdd形式の昨日の日付が格納されている
yesterday_yyyymmddと%や-を連結させてやれば、結果としては

WHERE ad_name LIKE '%-20221003-%'

というWHERE句を意味するクエリ文が関数を使って記載できるはずです。
declereとsetを記載するとこんな感じ。

--文字列連結の結果を格納
declare date_in_adname string;
--文字列連結させる
set date_in_adname = ARRAY_TO_STRING(['%',yesterday_yyyymmdd,'%'], '_')

よって、Ⅰ、Ⅱで得た検証結果を全部まとめると、かなり大変ですがこんなクエリ文になりました。

--①変数「昨日の日付」を定義
declare yesterday date;
--②変数「昨日の日付yyyymmdd形式」（FORMAT_TIMESTAMP関数はデフォルトでstring型となる）を定義
declare yesterday_yyyymmdd string;
--③文字列連結の結果を格納
declare date_in_adname string;


--①昨日の日付を取得し、変数『yesterday』に格納
set yesterday = DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY);
--②変数『yesterday』をYYYYMMDD形式に変換し、変数『yesterday_yyyymmdd』に格納
set yesterday_yyyymmdd = FORMAT_TIMESTAMP("%Y%m%d", yesterday);
--③文字列連結させる
set date_in_adname = ARRAY_TO_STRING(['%',yesterday_yyyymmdd,'%'], '-');

SELECT *
FROM `{テーブル名}`
WHERE ad_name LIKE date_in_adname
;

前半：変数名を宣言
中盤：変数に格納するデータを作成
後半：最終的に作成した条件date_in_adnameを元にシンプルなクエリ文

これで思い通りの結果が得られました！！
長かったです！
初心者なものでして、もっと良い方法をご存知の方がいらっしゃいましたらお気軽にコメント頂けると幸いです…！

Bigquery 「yyyymmdd形式」の『昨日』の日付を含んでいるデータのみ取得したい

サンプルデータ

やりたいこと

方針

Ⅰ.昨日の日付をクエリで「変数」として定義する

問題

解決策

Ⅱ.変数に格納されている日付を元に、WHERE句でLIKE検索する

問題

解決策

参考