BigQuery用SQLスニペット

#はじめに

BigQueryでアレをする時どんなクエリを書けばいいんだっけ？ということが良くあるので、残しておきます。
StandardSQLです。
都度追記します。

# 公式ドキュメント

[INFORMATION_SCHEMA](https://cloud.google.com/bigquery/docs/information-schema-intro)
[PARSE_DATE](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#parse_date)
[DATE でサポートされる形式設定要素](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#supported-format-elements-for-date)

# スニペット

## 履歴 関連

### クエリで特定のテーブルやカラムが使われているか調べる

```sql
SELECT
  query
FROM
  region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- リージョンが異なる場合は適宜変更
WHERE
  query LIKE '%XXXXX%'
```

## テーブル定義 関連

### スキーマ定義をText形式で抽出する

```sql
SELECT
  ARRAY_TO_STRING(columns, ',')
FROM (
  SELECT
    ARRAY(
    SELECT
      CONCAT(column_name, ":",
        CASE data_type
          WHEN 'INT64' THEN 'INTEGER'
          WHEN 'FLOAT64' THEN 'FLOAT'
          ELSE data_type END) AS type
    FROM
      `<dataset>.INFORMATION_SCHEMA.COLUMNS`
    WHERE
      table_name = 'all'
    ORDER BY
      ordinal_position) AS columns)
```

### descriptionを抽出する

```sql
SELECT
  table_name,
  option_value
FROM
  `<dataset>.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE
  option_name = 'description'
```

## Date 関連

### 2015/2/6をDate型にする

```sql
WITH
  data AS (
  SELECT
    '2015/2/6' AS date1,
    '2015/02/16' AS date2,
    '15/02/16' AS date3 )
SELECT
  PARSE_DATE('%Y/%m/%d',
    date1),
  PARSE_DATE('%Y/%m/%d',
    date2),
  PARSE_DATE('%y/%m/%d',
    date3) --yは小文字
FROM
  data
```

### Nov 26, 2018 をDate型にする

```sql
WITH
  data AS (
  SELECT
    'Nov 26, 2018' AS date1 )
SELECT
  PARSE_DATE('%b %e, %Y',
    date1)
FROM
  data
```

## 日付別テーブル 関連

### 最新の日付別テーブルのみ参照する

```sql
WITH
  latest AS (
    SELECT
      MAX(_TABLE_SUFFIX) AS table_suffix
    FROM
      `sandbox-suzutatsu-bq.test.sample1_*`
)
SELECT
  *
FROM
  `sandbox-suzutatsu-bq.test.sample1_*`,
  latest
WHERE
  _TABLE_SUFFIX = latest.table_suffix
```

### 日付別テーブルの日付をカラムとして持つ

```sql
SELECT
  PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) as date
FROM
  `sandbox-suzutatsu-bq.test.sample1_*`
```

## 便利クエリ 関連

### クエリ履歴をクエリ費用が高い順に表示

```sql
WITH
  x AS (
  SELECT
    user_email,
    project_id,
    job_id,
    start_time,
    ROUND(total_bytes_billed / POW(1024, 3), 1) AS total_giga_bytes_billed,
    query
  FROM
    region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    total_bytes_billed IS NOT NULL
    -- AND 金額に関する条件
    -- AND 期間に関する条件
    )
SELECT
  user_email,
  project_id,
  job_id,
  DATETIME(start_time, 'Asia/Tokyo') AS datetime,
  total_giga_bytes_billed,
  query
FROM
  x
ORDER BY
  total_giga_bytes_billed DESC
```

### ある日付の期間の分だけ行を返す

```sql
WITH base AS (
SELECT
  GENERATE_DATE_ARRAY(
    DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 MONTH),
    CURRENT_DATE('Asia/Tokyo'),
    INTERVAL 1 DAY) AS date_array
)
SELECT
  date_row
FROM
  base, UNNEST(date_array) AS date_row
```

## UDF（ファンクション）関連

### マスタとして用意したテーブルを使ってAをBに変換する

```sql
CREATE OR REPLACE FUNCTION func.yyyymmdd_to_yearmonth(yyyymmdd STRING)
OPTIONS (description="YYYYMMDDからマスタテーブルで変換後のXX年月を取得する")
 AS ((
    SELECT
      MAX(year_month)
    FROM
      master.xxxxx_master
    WHERE
      start_date <= PARSE_DATE('%Y%m%d', yyyymmdd)))
```