俺的BigQueryのSQLテンプレート

完全に自分で使う用の記事です。
もしBigQueryで迷える子羊がいたら、その助けになればと思っています。

クエリ

ログを期間で絞り込む

SELECT
  ワイルドカードは使わずに必ず必要なカラムだけに絞り込む
FROM
  大量にレコードがあるログテーブルを指定
WHERE
  actions.created_at BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP() -- 例: 30日

JSON内の値を取得する

必ずSTRING型で取得される

JSON_EXTRACT_SCALAR(data, '$.xxxxx')

日付を日で丸める(JST)

Dailyで集計する時に使う
2019-10-23 15:00:00.000 UTC -> 2019-10-24

DATE(created_at, 'Asia/Tokyo')

日付を週で丸める(JST)

Weeklyで集計する時に使う
2019-10-23 15:00:00.000 UTC -> 2019-10-18
2019-10-24 15:00:00.000 UTC -> 2019-10-18
2019-10-25 15:00:00.000 UTC -> 2019-10-25

DATE_TRUNC(DATE(created_at, 'Asia/Tokyo'), WEEK)

日付を月で丸める(JST)

Monthlyで集計する時に使う
2019-10-23 15:00:00.000 UTC -> 2019-10-01
2019-10-23 15:00:00.000 UTC -> 2019-10-01
2019-10-24 15:00:00.000 UTC -> 2019-10-01

DATE_TRUNC(DATE(created_at, 'Asia/Tokyo'), MONTH)

日付を年で丸める(JST)

Monthlyで集計する時に使う
2019-10-23 15:00:00.000 UTC -> 2019-01-01
2019-10-23 15:00:00.000 UTC -> 2019-01-01
2020-10-24 15:00:00.000 UTC -> 2020-01-01

DATE_TRUNC(DATE(created_at, 'Asia/Tokyo'), YEAR)

移動平均

# 7日移動平均
AVG(src) OVER (ORDER BY date DESC ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

JSONの配列を展開する

CREATE TEMPORARY FUNCTION str_to_array(s STRING) RETURNS ARRAY<STRUCT<b STRING>> LANGUAGE js AS "return JSON.parse(s);";

WITH
sources AS (
  SELECT
    str_to_array(JSON_EXTRACT(data, "$")) as b
  FROM
    xxx.sample
)

SELECT fb.b FROM sources, sources.b as fb

関数

よく使う日付フォーマット

下記は例なので、自分でよく使うのを定義しておくと楽

CREATE OR REPLACE FUNCTION `xxx.date_format`(date DATE) AS ((
  SELECT FORMAT("%s (%s)",
    FORMAT_DATE("%y/%m/%d", date),
    CASE EXTRACT(DAYOFWEEK FROM date)
      WHEN 1 THEN "日"
      WHEN 2 THEN "月"
      WHEN 3 THEN "火"
      WHEN 4 THEN "水"
      WHEN 5 THEN "木"
      WHEN 6 THEN "金"
      WHEN 7 THEN "土"
      ELSE ""
    END
  )
));

SELECT
  xxx.date_format(date)
FROM
  hogehoge

中央値を出す

CREATE OR REPLACE FUNCTION `xxx.median`(arr ANY TYPE) AS ((
  SELECT IF (
    MOD(ARRAY_LENGTH(arr), 2) = 0,
    (arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2) - 1)] + arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]) / 2,
    arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]
  )
  FROM (SELECT ARRAY_AGG(x ORDER BY x) AS arr FROM UNNEST(arr) AS x)
));

SELECT
  xxx.median(ARRAY_AGG(xxxx)) AS median
FROM
  hogehoge

Firestoreのdocument_nameからドキュメントIDを取得する

CREATE OR REPLACE FUNCTION `xxx.get_doc_id`(dn STRING) AS ((
  SELECT REGEXP_EXTRACT(dn, r"[^/]+$")
));

SELECT
  xxx.get_doc_id(ua) AS median
FROM
  hogehoge