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
※第一引数はこちらを参考に設定
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にすれば良いのか参考になったサイト
日付を変更する際に参考になったサイト
参考文献