BigQueryで個人的によく使うクエリ関数まとめ

# はじめに

業務でクエリを作成する際、ちょっと時間が空いてると「あれ？どうやるんだっけ？」となりがちなので、ほぼ自分用にまとめておくことにする。
ちょっと特殊な状況もあるため、汎用的なものではないけど、一部のニッチなところには参考になるんじゃないかと思う。

> あと、日を空けて書いたので句読点・ですますがコロコロ変わっています。

# フィルタ

### 結果値に対してフィルタ適用する
`QUALIFY` を使うことでクエリで何やかんやした後の結果値をフィルタリングできる
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
例えば `ROW_NUMBER` でフィルタリングする場合など、サブクエリを作成する必要があるが、`QUALIFY` を使うことで簡単に書けるようになる
```SQL
SELECT
    *
FROM tableName
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatedAt) = 1
```
:warning: 注意！！
Looker Studioで上記のようなクエリを書くとエラーになる（エラー内容も不明瞭で謎）
`WHERE` 句が無いのが問題なので無理やり書くと解消する
```SQL
SELECT
    *
FROM tableName
WHERE true
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatedAt desc) = 1
```



# 型変換

### 数字の文字列を数値にして取得する
```SQL
SELECT 
  SACE_CAST(number as int64) number,
```
CASTでも良いですが、型不正のエラーでクエリが止まると嫌なのでSAFE_CAST使ってます。


# 日付

### 文字列の日付 `yyyy-mm-ddThh:mm:ss.000z` を範囲指定する
```SQL
WHERE TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) >= TIMESTAMP(createDateTime)
```
こういう日付の足し引きも可能
```SQL
current_date() - 30
```

### 文字列の日付 `yyyy-mm-ddThh:mm:ss.000z`（UTC） をJSTの日付型で取得する
9時間足して取得する方法
```SQL
select
    TIMESTAMP_ADD(TIMESTAMP(dateString), INTERVAL 9 HOUR) createdAt,
```
JSTとして取得する方法
※タイムスタンプをデフォルトでUTCにしており、TIMESTAMP型で取得するとUTCになってしまうため、STRING型で取得する場合
```SQL
select
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP(createdAt), 'Asia/Tokyo') AS createdAt,
```

### JSTにして年月 `yyyy-mm` の文字列だけ取得する
```SQL
select
    FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP(createdAt, 'Asia/Tokyo')) createMonth,
```

### JSTにして時間帯 `HH:00` の文字列だけ取得する
```SQL
select
    FORMAT_TIMESTAMP('%H:00', TIMESTAMP(createdAt, 'Asia/Tokyo')) AS timeRange,
```


# JSON

### JSONのデータを展開する
```SQL
SELECT
  JSON_VALUE(user, '$._id') _id,
  JSON_VALUE(user, '$.name') name,
  JSON_VALUE(user, '$.age') age,
```
この例では、 `user` は以下のようなJSON形式のデータが入っている想定です。
```JSON
{
  "_id": "xxxxxx",
  "name": "xxxxxx",
  "age": "20"
}
```

### JSONの配列データをindex指定して取得する
```SQL
SELECT
  JSON_VALUE(user, '$[0]._id') _id,
  JSON_VALUE(user, '$[0].name') name,
  JSON_VALUE(user, '$[0].age') age,
```
この例では、 `user` は以下のようなJSON形式のデータが入っている想定です。
```JSON
{
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ...
}
```

### JSONの配列データを展開してレコードとして扱う

```SQL
WITH extract_json AS (
  SELECT
    createdAt,
    -- userカラムの中のjson配列をネストして展開
    JSON_EXTRACT_ARRAY(user, '$') AS json_data
  FROM
    `tableName`
)

, temp_table as (
  SELECT
    createdAt,
    -- レコードごとに name を抽出
    JSON_EXTRACT_SCALAR(JSON_EXTRACT(json_record, '$.name'), '$') name,
  FROM
    -- ネストされたjsonレコードを個別のレコードに展開
    extract_json, UNNEST(json_data) AS json_record
)

select * from temp_table
```
この例では、 `user` は以下のようなJSON形式のデータが入っている想定です。
```JSON
{
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ...
}
```

# GA関連

### GAのevent_timestampを日付型で取得する
```SQL
select
    TIMESTAMP_MICROS(event_timestamp)
```

### 流入元の取得

```SQL
select
    event_date,
    user_pseudo_id,
    (select value.string_value from unnest(event_params) where key = 'source') source,
from tableName
where event_name = 'first_visit'
```

# その他

### 日付の疑似テーブルを生成する GENERATE_TIMESTAMP_ARRAY
「複数のデータを日付ごとにサマリしてプロットしたいけど、実際のデータレコードには存在しない日があるし、データが無いところは 0 をいれておきたいよー！」
って思ったことないですか？
一時的なデータレコードをマスタ化することで、それをベースにJoinしてサマリできます。
※解りやすくするために分けて書いてますが、実際に使うときはまとめてもOK
```SQL
with date_table as (
  select
    generate_timestamp_array(
      timestamp('2024-01-01'), -- 生成する開始の日付
      timestamp('2024-04-01'), -- 生成する終了の日付
      interval 1 day           -- 生成する間隔(second / minutes / hour / day / month / year)
    ) date_array,
)

, convert_record as (
  select
    _date,
  from date_table, unnest(date_array) _date -- 配列データをunnestでレコードに展開
)

select
  format_timestamp('%Y-%m-%d', _date) date, -- 好きなフォーマットに変換して使う
from convert_record
```
実行すると以下のようになります。
```
date
2024-01-01
2024-01-02
2024-01-03
2024-01-04
2024-01-05
2024-01-06
・・・（以下略）・・・
```

### 日付以外で同じことをしたい GENERATE_ARRAY
簡単に書くとこのようになる。
※GENERATE_ARRAYの引数は 開始, 終了, 間隔
```SQL
select
  num,
from unnest(generate_array(1, 10, 1)) num
```
応用として以下のような使い方も可能。
```SQL
with base as (
  select
    num,
  from unnest(generate_array(1, 10, 1)) num
)

select
  -- LPADで0埋めしてIDの形式に変換する
  concat('XXX-', lpad(cast(num as string), 2, '0')) id
from base
```
実行すると以下のようになる。
```
id
XXX-01
XXX-02
XXX-03
XXX-04
・・・（以下略）・・・
```