Google Data Studioを使用するために使ったSQL

## SQL

`SELECT`：テーブルの中から必要なデータを抽出する(*で全部)

    SELECT id, cost

`FROM`：データ元

    FROM `test.test_report`

※FROMで長い名前の時など`AS`で名前をつけると便利

    FROM `test.test_report` AS test

`WHERE`：条件

    WHERE DATE(_PARTITIONTIME) BETWEEN "2022-01-01" AND DATE_SUB(current_date('Asia/Tokyo'), INTERVAL 1 DAY)

※よく日付指定でWHERE使います。`2022-01-01`~`昨日まで`

`GROUP BY`：グループ化(Data Studioでディメンションに置くもの)

    GROUP BY id,date,gender

`WITH`：中間テーブル（このSQL文の中で使えるテーブル）

    WITH  test1 AS(
    SELECT * FROM `test.test_report`
    ),
    test2 AS (
    SELECT * FROM `test.test2_report`
    )
    SELECT * FROM test2

※上記のように最後の`SELECT * FROM`を`test2`から`test1`にすると1つ目のテーブルを出力できます。
データを調べる時や`JOIN`する時などSQLが見やすくなるので非常に使いやすいです。

`LEFT JOIN`：テーブルの右に追加される

    LEFT JOIN `test.teste_report` AS test2

`USING`：別のテーブルと カラム名 を紐付ける(Key)

    USING(account_id)

`ON`：別のテーブルと 別のカラム名 で紐付ける(Key)

    ON account_id = test_id

`CAST`：型を変更 `SAFE-CAST`：型変更できない時 null にする。書き方は CAST と同じ
    
    CAST(id AS STRING) AS id

`SUM`：合計

    SUM(cost) AS cost

`MAX`：最大値

    MAX(cost) AS cost

※よく`JOIN`する時はMAXを使ってます(メトリクス)

`UNNEST`：ネストされていたクエリを平たくできる

    UNNEST(action) AS id

`SUBSTRING`：文字の切り取り

    SUBSTRING(id,1,2) AS sub_id

`EXCEPT`：除外する

    SELECT * EXCEPT(id) FROM `test.test_report`

`distinct`：重複削除　

    SELECT DISTINCT id from `test.test_report`

`UNION ALL`：テーブルを統合

    SELECT * FROM `test1`
    UNION ALL
    SELECT * FROM `test2`

`COALESCE`：NULLでない最初の引数を返す

    COALESCE(NULL, 'B', 'C')

※結果は`B`になるが、もし`B`がNULLの場合は`C`となる

`IF`：第1引数が`True`の時は第2引数を、そうでない時には第3引数を返す

    IF( A=B, cost1, cost2) as cost

`IFNULL`：第1引数が NULL でなければ1つ目を、そうでなければ第2引数を返す

    IFNULL(cost, 0) AS cost

※メトリクスになるような`cost`とかは上記のような書き方をよくします。計算させる時に`NULL`であると結果が`NULL`になってしまうので0にして計算させます。

`NULLIF`：第1引数 = 第2引数が`True`の時は`NULL`、そうでない時には第1引数を返す

    NULLIF(cost,0) AS cost

`FORMAT_DATE`：指定した日付の形に変更

    FORMAT_DATE("%d", DATE "2022-01-01")　AS day

※第一引数は[こちら](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#supported_format_elements_for_date)を参考に設定

`ROUND`：数値を指定された小数点以下の桁数に丸める

    ROUND(SUM(cost)/SUM(clicks),0)

※`ROUND`はData Studio内でフィールドを追加し、そのフィールドに記載して使用することが多いです。

`JSON_EXTRACT`:JSONの値をとってこれる

    JSON_EXTRACT(`カラム名`, '$.key名') 

`REGEXP_EXTRACT`:正規表現`r`と一致する value内の部分文字列を返す。一致がない場合、NULL を返す。

    REGEXP_EXTRACT(値, r'正規表現')

`JSON_VALUE`:SON 文字列から値を取得

    JSON_VALUE(カラム名, '$パス') 

※IDが含まれているか確認するために使える

`NET.REG_DOMAIN`:URLのドメインを取得できる(サブドメ含まない)

    NET.REG_DOMAIN(URL)

`NET.HOST`:URLのドメインを取得できる(サブドメ含む)

    NET.HOST(URL)

`SPLIT`:分割

    SPLIT(URL,"?")

※URLの場合、パラメータで分割される
+a `[offset(1)]`を関数の後ろにつけるとパラメータだけ取得できる

#### カスタムクエリ
カスタムクエリを使用して、「期間パラメータを有効」にすると期間設定で指定した最初の日、最終日を使うことができる
しかし、日付の形が違うので下記のようにSQLに組み込めば使える！

    PARSE_DATETIME("%Y%m%d", @DS_START_DATE)
    PARSE_DATETIME("%Y%m%d", @DS_END_DATE)

## 最後に

現状で使っているSQLはこのくらいです。
また新しく覚えたことがあれば、追加していきます。

## 何をGROUP BYにすれば良いのか参考になったサイト

https://web-kaizen.co.jp/2018/09/21/sql-query-data-analysis-tableau/

## 日付を変更する際に参考になったサイト

https://shinkufencer.hateblo.jp/entry/2018/06/23/013400

https://yuji0602.hatenablog.jp/entry/2018/05/29/113803

https://qiita.com/hogeta_/items/1416135863a023a09127#parse_dateformat-date

## 参考文献

https://it-atta.com/basics-of-json-and-basics-of-json_extract/

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions?hl=ja#regexp_extract

https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions#nethost

https://ex-ture.com/blog/2020/12/09/bigquery-parse-url-queryparam-as-keyvalue-struct-date/