📌

良く使うBigQueryの関数まとめ

2022/05/08に公開1

はじめに

  • 本記事は、社内の非エンジニア向けの BigQuery SQL入門ドキュメント の BigQueryの関数編 を公開したものです
  • 良く使う関数はデータや分析の特性によって異なるので、あくまでも独断と偏見に基づく関数セレクションです
    • カテゴリ内の関数の並び順はアルファベット順ではなく、個人的に良く使っていると思う順です
  • 慣れてきたら BigQuery の公式リファレンス を確認することをおすすめします
  • MySQL や PostgreSQL の利用経験はあるが BigQueryは初めてという社内メンバーをメインターゲットにしているので、そことの違いを補足しています
  • 分析関数はBigQuery固有のものでもないので、他サイト等の解説を参考ください

https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts
https://resanaplaza.com/2021/10/17/【ひたすら図で説明】一番やさしい-sql-window-関数(分/
https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5

変換関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions

CAST

CAST(expression AS typename [format_clause])
  • typenameへの型変換を行う
  • format_clause でフォマットすることも可能

CASTで良く使うデータ型

種類
数値型 ● INT64(INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT)
● NUMERIC(DECIMAL)
● BIGNUMERIC(BIGDECIMAL)
● FLOAT64
文字列型 ● STRING
日付型 ● DATE
日時型 ● DATETIME
時刻型 ● TIME

CAST 用 の FORMAT句

SELECT
  FORMAT_DATE('%F %A %a.', DATE '2008-12-25') AS format_date,
  CAST(DATE '2008-12-25' AS STRING FORMAT 'YYYY-MM-DD DAY DY.') AS cast_format
format_date cast_format
2008-12-25 Thursday Thu. 2008-12-25 THURSDAY THU.

日付関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions

CURRENT_DATE

CURRENT_DATE([time_zone])
  • 指定したタイムゾーンの現在日付を返す
  • MySQLとPostgreSQLにもある関数
  • MySQLで良く使われる CURDATE は使えない

SELECT CURRENT_DATE('Asia/Tokyo')

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)
  • 指定した粒度まで日付を切り詰める
  • 日別データを、週、月、年で集計する際に活用できる非常に便利な関数
  • PostgreSQLにもある関数

SELECT
  DATE_TRUNC('2008-12-25', YEAR) AS year,
  DATE_TRUNC('2008-12-25', MONTH) AS month,
  DATE_TRUNC('2008-12-25', WEEK) as week,  -- 週初日曜日
  DATE_TRUNC('2008-12-25', WEEK(MONDAY)) as week_monday -- 週初月曜日
year month week week_monday
2008-01-01 2008-12-01 2008-12-21 2008-12-22

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)
  • 指定した時間間隔を DATE に追加する
  • MySQLの + interval 1 day や PostgreSQLの + cast('1 days' as INTERVAL) は使えないが、日数だけであれば +/- 演算子が利用可能
  • MySQLにもある関数

SELECT
  DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY) AS tomorrow,
  DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -1 DAY) AS yesterday,
  CURRENT_DATE('Asia/Tokyo') + 1 AS tomorrow2

DATE_SUB

SELECT
  DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY) AS yesterday,
  DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 52 WEEK) AS weeks_52_ago, -- 前年同曜日
  CURRENT_DATE('Asia/Tokyo') - 1 AS yesterday2

EXTRACT

EXTRACT(part FROM date_expression)
  • 指定した日付の一部に対応する値が返す(part で単位を年、月、日などが指定できる)
  • MySQLとPostgreSQLにもある関数
  • MySQLで使える year, month, day は使えない
  • PostgreSQLで使えるdate_part は使えない

SELECT
  EXTRACT(DAY FROM DATE '2008-12-25') AS day,
  EXTRACT(DAYOFWEEK FROM DATE '2008-12-25') AS day_of_week,
  EXTRACT(DAYOFYEAR FROM DATE '2008-12-25') AS day_of_year,
  EXTRACT(WEEK FROM DATE '2008-12-25') AS week, -- 週初日曜日
  EXTRACT(WEEK(MONDAY) FROM DATE '2008-12-25') AS week_monday, -- 週初月曜日
  EXTRACT(ISOWEEK FROM DATE '2008-12-25') AS iso_week,
  EXTRACT(MONTH FROM DATE '2008-12-25') AS month,
  EXTRACT(QUARTER FROM DATE '2008-12-25') AS quarter,
  EXTRACT(YEAR FROM DATE '2008-12-25') AS year
day day_of_week day_of_year week week_monday iso_week month quarter year
25 5 360 51 51 52 12 4 2008

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)
  • 日付の差分を返す(date_part で単位を年、月、日などが指定できる)
  • MySQLの DATEDIFF や PERIODDIFF は使えない
  • PostgreSQLのように +/- で日数やINTERVAL型のようには使えない

SELECT
  DATE_DIFF('2008-12-26', '2008-12-25', DAY) AS days_diff,
  DATE_DIFF('2008-12-27', '2008-12-25', WEEK) AS weeks_diff,
  DATE_DIFF('2008-12-28', '2008-12-25', WEEK) AS weeks_diff2,
  DATE_DIFF('2008-12-31', '2008-12-25', MONTH) AS months_diff,
  DATE_DIFF('2009-01-01', '2008-12-25', MONTH) AS months_diff2,
  DATE_DIFF('2008-12-31', '2008-12-25', YEAR) AS years_diff,
  DATE_DIFF('2009-01-01', '2008-12-25', YEAR) AS years_diff2
days_diff weeks_diff weeks_diff2 months_diff months_diff2 years_diff years_diff2
1 0 1 0 1 0 1

DATE

DATE(year, month, day)
DATE(timestamp_expression[, time_zone])
DATE(datetime_expression)
  • DATE型の値を返す
  • MySQLにもある関数
  • PostgreSQLの make_date 相当

SELECT
  DATE(2008, 12, 25) AS date_ymd,
  DATE('2008-12-25 23:59:59') AS date_dt,
  DATE('2008-12-25 05:30:00+09', 'Asia/Tokyo') AS date_jst
date_ymd date_dt date_jst
2008-12-25 2008-12-25 2008-12-25

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)
  • 指定された形式に従って日付をフォーマットする
  • MySQL の DATE_FORMAT 相当となるが、引数の順番もフォーマットの指定方法も異なる
  • PostgreSQLの TO_CHAR 相当となるが、引数の順番もフォーマットの指定方法も異なる
  • 特に変換しなくても、SpreadやExcel等のツールでは日付型で認識され、週、月単位への切り詰めは DATE_TRUNC があり、 EXTRACT もあるので、利用するケースはあまりないかも
    • 曜日番号ではなく曜日名が欲しいときは便利

SELECT
  FORMAT_DATE('%F %A %a.', DATE '2008-12-25') AS format_date,
  CAST(DATE '2008-12-25' AS STRING FORMAT 'YYYY-MM-DD DAY DY.') AS cast_format
format_date cast_format
2008-12-25 Thursday Thu. 2008-12-25 THURSDAY THU.

LAST_DAY

LAST_DAY(date_expression[, date_part])
  • date_expression の date_part 単位での最終日取得する
  • デフォルトでは月の最終日
  • MySQLにもある関数

SELECT LAST_DAY(DATE '2008-11-25') AS last_day
last_day
2008-12-31

PARSE_DATE

PARSE_DATE(format_string, date_string)
  • 日付の文字列表現を DATE型に変換

SELECT
  PARSE_DATE('%F', '2008-12-25') AS date1,
  PARSE_DATE('%Y/%m/%d', '2008/12/15') AS date2,
  PARSE_DATE('%Y%m%d', CAST(20081215 AS STRING)) AS date3
date1 date2 date3
2008-12-25 2008-12-15 2008-12-15

日時関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions
日付関数 と同様のため説明は省略

タイムスタンプ関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timezone_definitions
日付関数 と同様のため説明は省略

文字列関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions

CONCAT

CONCAT(value1[, ...])
  • 指定した値(複数可)を 1 つに連結する
  • BYTES か、STRING にキャスト可能な型であれば、value にそのまま指定できる
  • 連結演算子(||) でも同じことが可能
  • MySQLとPostgreSQLにもある関数

SELECT
  CONCAT('A', '-', 1) AS string1,
  'A' || '-' || 1 AS string2,
  FORMAT('%s-%d', 'A', 1) AS string3
string1 string2 string3
A-1 A-1 A-1

LENGTH

LENGTH(value)
  • 文字列型の文字数、バイト型のバイト数を返す
  • 文字列のバイト数は BYTE_LENGTH でも可能
  • MySQLのLENGTHはバイト単位なので、CHAR_LENGTH相当
  • PostgreSQLにもある関数

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example,
  BYTE_LENGTH(characters) AS byte_length_example,
FROM
  UNNEST(['abcあ']) AS characters
characters string_example bytes_example byte_length_example
abcあ 4 6 6

LEFT

LEFT(value, length)
  • 文字列/バイトの先頭から指定文字数/バイト数の文字列を返す

SELECT LEFT('A001-001-01', 4) as left4
left4
A001
RIGHT(value, length)
  • 文字列/バイトの末尾から指定文字数/バイト数の文字列を返す

SELECT RIGHT('A001-001-01', 2) as right2
right2
01

SUBSTR

SUBSTR(value, position[, length])
  • value に指定した文字列/バイトの position 文字目から length 文字数/バイト数の文字列を返す

SELECT SUBSTR('A001-001-01', 6, 3) as substr_6_3
substr_6_3
001

REPLACE

REPLACE(original_value, from_value, to_value)
  • original_value 内に出現するすべての from_value を to_value に置き換える

SELECT REPLACE('A001-001-01', '-', '_') as replace_example
replace_example
A001_001_01

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)
  • value が re2正規表現 regexp に対して部分一致である場合にTRUE を返す

SELECT
  email,
  REGEXP_CONTAINS(email, r'^.+@(example\.com|example\.net)$') AS regexp_contains_example
FROM
  UNNEST(['foo@example.com', 'bar@example.org', 'baz@example.net']) AS email
email regexp_contains_example
foo@example.com true
bar@example.org false
baz@example.net true

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp[, position[, occurrence]])
  • re2正規表現 regexp と一致する value 内の部分文字列を返す
  • 一致がない場合、NULL を返す
  • position を指定すると、value のこの位置から検索を開始する(それ以外は先頭から)
  • 複数一致する場合は最初の一致を返すが、occurrence でそれ以外も指定可能
  • 複数一致する場合に全部取得する場合は REGEXP_EXTRACT_ALL を使うと Array で取得できる

SELECT
  email,
  REGEXP_EXTRACT(email, r'^.*@(.*)$') AS mail_domain
FROM
  UNNEST(['foo@example.com', 'bar@example.org', 'baz@example.net']) AS email
email mail_domain
foo@example.com example.com
bar@example.org example.org
baz@example.net example.net

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)
  • re2正規表現 regexp と一致する value のすべての部分文字列を replacement に置き換えた 文字列を返す
  • replacement 引数内でバックスラッシュでエスケープされた数字(\1~\9)を使用してグループと一致するテキストを regexp パターン内に挿入できる(\0 は一致するテキスト全体)

SELECT
  REGEXP_REPLACE(heading, r'^# ([a-zA-Z0-9\s]+$)', '<h1>\\1</h1>') AS html
FROM
  UNNEST(['# Heading', '# Another heading']) AS heading
html
<h1>Heading</h1>
<h1>Another heading</h1>

FORMAT

FORMAT(format_string_expression, data_type_expression[, ...])
  • format_string_expressionをdata_type_expressionとしてフォーマットする
  • 数値のカンマ区切りや、数値の頭ゼロ埋め等が可能
  • C言語 の printf 関数と同様の仕様
  • PostgreSQLにもある関数
  • MySQLの FORMAT 相当だが引数の指定方法が異なる

SELECT
  FORMAT("%'d", 123456789) AS format1, -- カンマ区切り
  FORMAT("%'+d", 123456789) AS format2, -- カンマ区切り&プラス記号表記
  FORMAT("%03d", 1) AS format3, -- 3桁頭ゼロ埋め
  FORMAT("%'.3f", 123456789.0) AS format4, -- カンマ区切り&小数部3桁
format1 format2 format3 format4
123,456,789 +123,456,789 001 123,456,789.000

形式指定子

%[flags][width][.precision]specifier

主なspecifier

specifier 説明
d または i 10 進の整数
f または F 整数.小数。大文字小文字は非有限値の表記違い。
s 文字列

主なflag

flag 説明
0 widthが指定されている場合スペースではなく、ゼロ(0)で数字の左側にパディング
' 10進数の場合はカンマ区切り形式へ
+ 正の数値であっても、結果の前にプラス記号またはマイナス記号(+ または -)を強制。デフォルトでは、負の数にのみ - 記号が前に付けられる。

width

数値を指定すると、この数値よりも短い場合、結果は空白スペースを使用してパディングされる。(flagで0指定した場合はゼロ(0)でパディングされる。

precision

小数点の後に出力される桁数を 小数に .数値 で指定できる(デフォルトは6)

SPLIT

SPLIT(value[, delimiter])
  • delimiter 引数を使用して value を分割した Array を返す

SELECT
  value,
  SPLIT(value) as splitted_value,
  'aa' IN UNNEST(SPLIT(value)) AS is_containing_aa
FROM
  UNNEST(['a,aa,aaa', 'aaa,aaaa']) AS value

TRIM

TRIM(value_to_trim[, set_of_characters_to_remove])
  • 前後の空白を除去する
  • set_of_characters_to_remove で空白以外の文字列も指定可能

SELECT '#' || TRIM('  aaa  ') || '#' as trim_example -- 前は半角スペース2つ、後ろは全角スペース2つ
trim_example
#aaa#

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])
  • Unicode正規化された文字列を返す
  • 「1」「1」「①」など等価な文字の表記を統一できるのでNLPの前処理などで使う
  • 大文字小文字を区別する場合は NORMALIZE を使う
  • normalization_mode の違いは難しいが、NFKC を使うケースが多いかと

SELECT
  value,
  NORMALIZE_AND_CASEFOLD(value, NFC) AS NFC,
  NORMALIZE_AND_CASEFOLD(value, NFD) AS NFD,
  NORMALIZE_AND_CASEFOLD(value, NFKC) AS NFKC,
  NORMALIZE_AND_CASEFOLD(value, NFKD) AS NFKD,
  LENGTH(value) AS value_length,
  LENGTH(NORMALIZE_AND_CASEFOLD(value, NFC)) AS NFC_length,
  LENGTH(NORMALIZE_AND_CASEFOLD(value, NFD)) AS NFD_length,
  LENGTH(NORMALIZE_AND_CASEFOLD(value, NFKC)) AS NFKC_length,
  LENGTH(NORMALIZE_AND_CASEFOLD(value, NFKD)) AS NFKD_length
FROM
  UNNEST(['1','1','①','a','A','a','A','ガ','ガ','㈱','(株)','(株)','神','神','㍍']) AS value
value NFC NFD NFKC NFKD value_length NFC_length NFD_length NFKC_length NFKD_length
1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1
1 1 1 1 1 1 1
a a a a a 1 1 1 1 1
A a a a a 1 1 1 1 1
a a 1 1 1 1 1
a a 1 1 1 1 1
ガ ガ ガ ガ 2 2 2 1 2
ガ ガ 1 1 2 1 2
(株) (株) 1 1 1 3 3
(株) (株) (株) (株) (株) 3 3 3 3 3
(株) (株) (株) (株) (株) 3 3 3 3 3
1 1 1 1 1
1 1 1 1 1
メートル メートル 1 1 1 4 4

JSON関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

下記はレガシーなので利用非推奨

  • JSON_EXTRACT
  • JSON_EXTRACT_SCALAR
  • JSON_EXTRACT_ARRAY
  • JSON_EXTRACT_STRING_ARRAY

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])
  • JSON文字列又はJSON型の値からスカラー値を抽出する
  • 最も外側の引用符を削除し、戻り値のエスケープを解除する

SELECT
  json_text,
  JSON_VALUE(json_text, "$.class.students[0].name") AS student_name,
  JSON_VALUE(json_text, "$.class.students") AS students,
FROM
  UNNEST(
    [
      '{"class" : {"students" : [{"name" : "Jane"}]}}',
      '{"class" : {"students" : []}}',
      '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
    ]
  ) json_text
json_text student_name students
{"class" : {"students" : [{"name" : "Jane"}]}} Jane (null)
{"class" : {"students" : []}} (null) (null)
{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}} John (null)

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)
  • JSON文字列又はJSON型の値からスカラー値以外(配列やオブジェクトなど)の値を抽出する

SELECT
  json_text,
  JSON_QUERY(json_text, "$.class.students[0].name") AS student_name,
  JSON_QUERY(json_text, "$.class.students") AS students,
FROM
  UNNEST(
    [
      '{"class" : {"students" : [{"name" : "Jane"}]}}',
      '{"class" : {"students" : []}}',
      '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
    ]
  ) json_text
json_text student_name students
{"class" : {"students" : [{"name" : "Jane"}]}} "Jane" [{"name":"Jane"}]
{"class" : {"students" : []}} (null) []
{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}} "John" [{"name":"John"},{"name":"Jamie"}]

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])
  • JSON文字列又はJSON型の値からスカラー値(文字列、数値、またはブール値)の配列を抽出する

SELECT
  JSON_VALUE_ARRAY(json_text,"$.class.students") AS students,
  JSON_VALUE_ARRAY(json_text,"$.class.subjects") AS subjects
FROM
  UNNEST(['{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}], "subjects" : ["science","physics","chemistry"]}}']) AS json_text

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])
  • JSON文字列又はJSON型の値から、配列やオブジェクトなどの JSON 値の配列と、文字列、数値、ブール値などの JSON スカラー値を抽出

SELECT
  JSON_QUERY_ARRAY(json_text,"$.class.students") AS students,
  JSON_QUERY_ARRAY(json_text,"$.class.subjects") AS subjects
FROM
  UNNEST(['{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}], "subjects" : ["science","physics","chemistry"]}}']) AS json_text

Net関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions
URLやIPアドレスを加工するときに便利な関数がある

NET.HOST

NET.HOST(url)
  • URLからホストを抽出する

SELECT
  url,
  NET.HOST(url) AS host,
  NET.REG_DOMAIN(url) AS domain,
  NET.PUBLIC_SUFFIX(url) AS suffix
FROM
  UNNEST(['https://raksul.com/guide/','//novelty.raksul.com/guide/']) AS url
url host domain suffix
https://raksul.com/guide/ raksul.com raksul.com com
//novelty.raksul.com/guide/ novelty.raksul.com raksul.com com

集計関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions

  • ここであげている集計関数は全てOVER句を指定すると分析関数として利用可能

抽出元テーブル

customer_id amount order_date payment_method
1 100 2008-12-25 credit
2 200 2008-12-25 bank
3 300 2008-12-25 credit
1 1000 2009-12-26 credit
2 2000 2009-12-26 bank
3 3000 2009-12-26 credit
1 10000 2010-12-24 invoice
SELECT
  customer_id,
  COUNT(*) AS count_order,
  COUNT(DISTINCT payment_method) AS count_distinct_payment_method,
  SUM(amount) AS sum_amount,
  AVG(amount) AS avg_amount,
  MIN(order_date) AS min_order_date,
  MAX(order_date) AS max_order_date,
  STRING_AGG(payment_method) as payment_methods,
  COUNT(CASE WHEN payment_method = 'credit' THEN 1 END) as count_credit,
  COUNT(IF(payment_method = 'bank', 1, NULL)) as count_bank,
  COUNTIF(payment_method = 'invoice') as count_invoice,
  SUM(CASE WHEN payment_method = 'credit' THEN amount ELSE 0 END) AS sum_credit_amount,
  SUM(IF(payment_method = 'bank', amount, 0)) AS sum_bank_amount,
  SUM(IF(payment_method = 'invoice', amount, 0)) AS sum_bank_amount
FROM
  UNNEST([
    struct(1 AS customer_id, 100 AS amount, DATE '2008-12-25' as order_date, 'credit' as payment_method),
    struct(2 AS customer_id, 200 AS amount, DATE '2008-12-25' as order_date, 'bank' as payment_method),
    struct(3 AS customer_id, 300 AS amount, DATE '2008-12-25' as order_date, 'credit' as payment_method),
    struct(1 AS customer_id, 1000 AS amount, DATE '2009-12-26' as order_date, 'credit' as payment_method),
    struct(2 AS customer_id, 2000 AS amount, DATE '2009-12-26' as order_date, 'bank' as payment_method),
    struct(3 AS customer_id, 3000 AS amount, DATE '2009-12-26' as order_date, 'credit' as payment_method),
    struct(1 AS customer_id, 10000 AS amount, DATE '2010-12-24' as order_date, 'invoice' as payment_method)
  ]) AS orders
GROUP BY
  customer_id
customer_id count_order count_distinct_payment_method sum_amount avg_amount min_order_date max_order_date payment_methods count_credit count_bank count_invoice sum_credit_amount sum_bank_amount sum_bank_amount_1
1 3 2 11100 3700.0 2008-12-25 2010-12-24 credit,credit,invoice 2 0 1 1100 0 10000
2 2 1 2200 1100.0 2008-12-25 2009-12-26 bank,bank 0 2 0 0 2200 0
3 2 1 3300 1650.0 2008-12-25 2009-12-26 credit,credit 2 0 0 3300 0 0

COUNT

COUNT(*)  [OVER (...)]
COUNT(
  [DISTINCT]
  expression
)
[OVER (...)]
  • COUNT(*) の場合は行数、 expression の場合は NULL 以外の行数を返す
    • 条件式(CASEやIF)を使ってNULLにすることで条件付きカウントが可能
    • COUNTIF を使うと条件付きカウントをすっきり書ける場合もある
    • COUNT(*) は性能悪いので COUNT(0) や COUNT(1) を使うと良いというデマがあるがそんなことはないので COUNT(*) を使うで良い
  • DISTINCT を指定すると重複しない値の数を返す

SUM

SUM(
  [DISTINCT]
  expression
)
[OVER (...)]
  • NULL 以外の値の合計を返す
  • expression に条件式を書くことで条件付き合計が可能

MAX

MAX(
  expression
)
[OVER (...)]
  • 非 NULL 式の最大値を返す

MIN

MIN(
  expression
)
[OVER (...)]
  • 非 NULL 式の最小値を返す

AVG

AVG(
  [DISTINCT]
  expression
)
[OVER (...)]

STRING_AGG

STRING_AGG(
  [DISTINCT]
  expression [, delimiter]
  [ORDER BY key [{ASC|DESC}] [, ... ]]
  [LIMIT n]
)
[OVER (...)]
  • 非 NULL 値を結合して得られた値(STRING または BYTES のいずれか)を返す
  • DISTINCT を指定すると重複を除いた値を集約して結果を返す
  • ORDER BY で値の順序を指定できる
  • LIMIT で出力数の最大値を指定できる
  • mysql の GROUP_CONCAT 相当

ナビゲーション関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions

  • 分析関数のサブセット
  • 現在の行を基にしてウィンドウ フレーム内の別の行に対していくつかの value_expression が計算される

FIRST_VALUE

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
  • 現在のウィンドウ フレーム内の最初の行の value_expression の値を返す
  • IGNORE NULLS を指定すると NULL 値が除外される

LAST_VALUE

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
  • 現在のウィンドウ フレーム内の最後の行の value_expression の値を返す
  • IGNORE NULLS を指定すると NULL 値が除外される
  • FIRST_VALUE の ORDER BY の ASC/DESC で対応した方がパフォーマンスが良いケースが多い

NTH_VALUE

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])
  • 現在のウィンドウ フレーム内の N 番目の行の value_expression の値を返す
  • IGNORE NULLS を指定すると NULL 値が除外される

LAG

  • 先行する 1 つの行の value_expression の値をデフォルトで1つ返す
  • offset の値を変更すると、どれだけ後の行が返されるか指定できる
  • 指定したオフセットの行がウィンドウ枠内に存在しない場合は、オプションの default_expression が使用さる

LEAD

LEAD (value_expression[, offset [, default_expression]])
  • 後続の 1 つの行の value_expression の値をデフォルトで1つ返す
  • offset の値を変更すると、どれだけ後の行が返されるか指定できる
  • 指定したオフセットの行がウィンドウ枠内に存在しない場合は、オプションの default_expression が使用さる

PERCENTILE_CONT

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])
  • value_expression に対して指定されたパーセンタイル値を計算
  • percentile に 0.5 を指定すると 中央値(Median)となる
  • percentile に 0.25 を指定すると 第一四分位となる
  • percentile に 0.75 を指定すると 第三四分位となる

番号付け関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions

  • 分析関数のサブセット
  • 指定されたウィンドウ内の行の位置に基づいて、各行に整数値を割り当てる

ROW_NUMBER

ROW_NUMBER()
  • 各パーティションの各行の行番号(1開始)を返す

RANK

RANK()
  • 各パーティションの各行の順位(1開始)を返す
  • 同一順位が複数あると、次のランク値がその分飛ぶ

DENSE_RANK

DENSE_RANK()
  • 各パーティションの各行の順位(1開始)を返す
  • 同一順位が複数あても次のランク値は+1だけされる

NTILE

NTILE(constant_integer_expression)
  • ソートしたデータを constant_integer_expression で指定された数のグループに均等にグルーピングする
  • デシル分析をする際に便利

統計集計関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/statistical_aggregate_functions

BigQueryの統計処理の関数はそこまで充実していないため、BigQuery上で t検定等したい場合は、UDFで外部JavaScript(Jstatなど)を利用する方法がある
https://lab.mo-t.com/blog/bigquery-udf

CORR ピアソン相関係数

CORR(
  X1, X2
)
[OVER (...)]
  • 標準偏差求める場合、全データがある場合はこちら

STDDEV_POP 母標準偏差

STDDEV_POP(
  [DISTINCT]
  expression
)
[OVER (...)]

STDDEV_SAMP 標本標準偏差

STDDEV_SAMP(
  [DISTINCT]
  expression
)
[OVER (...)]
  • 標準偏差求める場合、全データがない場合はこちら

近似集計関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/approximate_aggregate_functions
正確な結果ではなく、近似的な結果だが、性能が良い関数

APPROX_TOP_COUNT

APPROX_TOP_COUNT(
  expression, number
)
  • expression の最頻値(MODE)の STRUCT型(value, count)の配列を返す
  • number パラメータで、返される要素の数を指定できる

APPROX_TOP_SUM

APPROX_TOP_SUM(
  expression, weight, number
)
  • weight の合計の 最大値 の expression の STRUCT型(value, sum)の配列を返す
  • number パラメータで、返される要素の数を指定できる

APPROX_QUANTILES

APPROX_QUANTILES(
  [DISTINCT]
  expression, number
  [{IGNORE|RESPECT} NULLS]
)
  • expression の値のグループに対する近似境界の値の配列を返す
  • number で作成する変位値の数を指定できる(配列の要素数は number + 1)
  • 最初の要素は近似最小値であり、最後の要素は近似最大値
  • 中央値は PERCENTILE_CONT(0.5, x) よりも APPROX_QUANTILES(x, 2)[OFFSET(1)] の方が高速

配列関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])
  • array_expression 内の要素を delimiter で連結した文字列を返す
  • NULL値はデフォルト無視されが、 null_text を指定することもできる

WITH items AS (
  SELECT ['coffee', 'tea', 'milk' ] as list
  UNION ALL
  SELECT ['cake', 'pie', NULL] as list
)
SELECT
  ARRAY_TO_STRING(list, ',') AS text,
  ARRAY_TO_STRING(list, ',', 'NULL') AS text_null
FROM
  items
text text_null
coffee,tea,milk coffee,tea,milk
cake,pie cake,pie,NULL

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
  • start_date から end_date のデフォルト1日間隔の日付の配列を返す
  • INTERVAL で間隔は指定できる
  • UNNEST と合わせてカレンダーテープルを作る際に便利

SELECT
  *
FROM
  UNNEST(GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08')) AS date
date
2016-10-05
2016-10-06
2016-10-07
2016-10-08

SAFE.接頭辞

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

  • 関数の先頭に SAFE. 接頭辞を付けると、エラーではなく NULL が返される

便利な演算子

関数ではないが使い方を忘れがちな演算子をいくつか

UNNEST

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

as_alias:
    [AS] alias
  • ARRAY を受け取り、ARRAY 内の各要素を 1 行にしてテーブルを返す
  • STRUCT型のARRAYの場合、STRUCT 内の各フィールドについてそれぞれ別個の列が生成される
  • WITH OFFSET 句は、オフセット値を含む別の列を返す
    • デフォルトの列名は offset

FROM句での利用例

SELECT
  offset + 1 as id,
  * except(offset),
  orders
FROM
  UNNEST(ARRAY<STRUCT<customer_id INT64, amount INT64, order_date DATE, payment_method STRING>>[
    (1, 100, '2008-12-25', 'credit'),
    (2, 200,'2008-12-25', 'bank'),
    (3, 300,'2008-12-25', 'credit'),
    (1, 1000,'2009-12-26', 'credit'),
    (2, 2000,'2009-12-26', 'bank'),
    (3, 3000,'2009-12-26', 'credit'),
    (1, 10000,'2010-12-24', 'invoice')
  ]) AS orders WITH OFFSET

IN 演算子での利用例

SELECT
  value,
  SPLIT(value) as splitted_value,
  'aa' IN UNNEST(SPLIT(value)) AS is_containing_aa
FROM
  UNNEST(['a,aa,aaa', 'aaa,aaaa']) AS value

ネストされた配列のフラット化例

入力テーブル(nested)

WITH base AS (
  SELECT
    offset + 1 as id,
    * except(offset),
    orders
  FROM
    UNNEST(ARRAY<STRUCT<customer_id INT64, amount INT64, order_date DATE, payment_method STRING>>[
      (1, 100, '2008-12-25', 'credit'),
      (2, 200,'2008-12-25', 'bank'),
      (3, 300,'2008-12-25', 'credit'),
      (1, 1000,'2009-12-26', 'credit'),
      (2, 2000,'2009-12-26', 'bank'),
      (3, 3000,'2009-12-26', 'credit'),
      (1, 10000,'2010-12-24', 'invoice')
    ]) AS orders WITH OFFSET
),
nested AS (
  SELECT
    customer_id
    , ARRAY_AGG(struct(id, amount, order_date, payment_method)) AS orders
  FROM
    base
  GROUP BY
    customer_id
)
SELECT
  o.id,
  n.customer_id,
  o.amount,
  o.order_date,
  o.payment_method
FROM
  nested n
  CROSS JOIN UNNEST(n.orders) AS o
ORDER BY
  o.id
id customer_id amount order_date payment_method
1 1 100 2008-12-25 credit
2 2 200 2008-12-25 bank
3 3 300 2008-12-25 credit
4 1 1000 2009-12-26 credit
5 2 2000 2009-12-26 bank
6 3 3000 2009-12-26 credit
7 1 10000 2010-12-24 invoice

PIVOT

FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias
  • 行を列に変換する
  • Rのtidyrのspread, pivot_wider 相当

WITH produce AS (
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<product STRING, sales INT64, quarter STRING, year INT64>>[
      ('Kale',51,'Q1',2020),
      ('Kale',23,'Q2',2020),
      ('Kale',45,'Q3',2020),
      ('Kale',3,'Q4',2020),
      ('Kale',70,'Q1',2021),
      ('Kale',85,'Q2',2021),
      ('Apple',77,'Q1',2020),
      ('Apple',0,'Q2',2020),
      ('Apple',1,'Q1',2021)
    ]) AS produce
)
SELECT
  *
FROM
  produce
  PIVOT(
      SUM(sales)
      FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
  )
product year Q1 Q2 Q3 Q4
Kale 2020 51 23 45 3
Kale 2021 70 85
Apple 2020 77 0
Apple 2021 1

UNPIVOT

FROM from_item[, ...] unpivot_operator

unpivot_operator:
    UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
        { single_column_unpivot | multi_column_unpivot }
    ) [unpivot_alias]

single_column_unpivot:
    values_column
    FOR name_column
    IN (columns_to_unpivot)

multi_column_unpivot:
    values_column_set
    FOR name_column
    IN (column_sets_to_unpivot)

values_column_set:
    (values_column[, ...])

columns_to_unpivot:
    unpivot_column [row_value_alias][, ...]

column_sets_to_unpivot:
    (unpivot_column [row_value_alias][, ...])

unpivot_alias and row_value_alias:
    [AS] alias
  • 列を行に変換する
  • Rのtidyrの gather, pivot_longer 相当

WITH produce AS (
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<product STRING, sales INT64, quarter STRING, year INT64>>[
      ('Kale',51,'Q1',2020),
      ('Kale',23,'Q2',2020),
      ('Kale',45,'Q3',2020),
      ('Kale',3,'Q4',2020),
      ('Kale',70,'Q1',2021),
      ('Kale',85,'Q2',2021),
      ('Apple',77,'Q1',2020),
      ('Apple',0,'Q2',2020),
      ('Apple',1,'Q1',2021)
    ]) AS produce
)
, pivoted AS (
  SELECT
    *
  FROM
    produce
    PIVOT(
        SUM(sales)
        FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
    )
)
SELECT
  *
FROM
  pivoted
  UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
product year sales quarter
Kale 2020 51 Q1
Kale 2020 23 Q2
Kale 2020 45 Q3
Kale 2020 3 Q4
Kale 2021 70 Q1
Kale 2021 85 Q2
Apple 2020 77 Q1
Apple 2020 0 Q2
Apple 2021 1 Q1
RAKSUL Data Analytics

Discussion