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

### サンプルデータ
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です。
```SQL:変数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`形式で日付が記載されているため、日付の形式を変換しなくてはいけません。

##### 解決策
その際使用するのが、日付の形式を変換できる
```SQL:
FORMAT_TIMESTAMP("%Y%m%d", {変数名});
```
です。
現在の日付を取得してくる関数
```SQL:
CURRENT_DATE('Asia/Tokyo')
```
が`2022/10/04`のとき、
- `%Y` は `2022` を
- `%m` は `10` を
- `%d` は `04` を
それぞれ指定します。
`%Y%m%d`と記載することで、`2022-10-04`を`20221004`に変換できるのです。

つまりまとめるとこんな感じ…
```SQL:「昨日の日付」を『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`に格納するデータを関数で定義しています。

ちなみに
```SQL:
FORMAT_TIMESTAMP("%Y%m%d", {変数名});
```
で定義したデータはString型で返ってくるようです。

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

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

実際にクエリ上で空のテーブルにInsertする指示を出して確認しました。
```SQL:
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です。

あいまい検索は
```SQL:LIKE検索
WHERE ad_name LIKE '%丸の内%'
```
>（例）ad_name内に「丸の内」を含んでいるデータを取得

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

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

##### 解決策

ARRAYの各要素を文字列連結できる。
```SQL:
SELECT ARRAY_TO_STRING(['a','b','c'], '_')
```
を使用することにしました。
これを実行すると
|result|
|:--|
|a_b_c|

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

これをⅠで頑張って作成した、`yyyymmdd`形式の***昨日の日付***が格納されている
`yesterday_yyyymmdd`と`%`や`-`を連結させてやれば、結果としては
```SQL:
WHERE ad_name LIKE '%-20221003-%'
```
というWHERE句を意味するクエリ文が関数を使って記載できるはずです。
`declere`と`set`を記載するとこんな感じ。
```SQL:
--文字列連結の結果を格納
declare date_in_adname string;
--文字列連結させる
set date_in_adname = ARRAY_TO_STRING(['%',yesterday_yyyymmdd,'%'], '_')
```

よって、Ⅰ、Ⅱで得た検証結果を全部まとめると、かなり大変ですがこんなクエリ文になりました。
```SQL:
--①変数「昨日の日付」を定義
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`を元にシンプルなクエリ文

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


参考
---
https://zenn.dev/persona/articles/4fc3bf8464b29d

https://itips.krsw.biz/bigquery-how-to-get-yyyymm-format-data-from-date-column/

https://www.codeemall.info/posts/bq-string-func/