Zenn
RAKSUL Data AnalyticsPublicationへの投稿
📌

良く使うBigQueryの関数まとめ

y.nakada@raksul
2022/05/08に公開1件
2
SQL
BigQuery
tech

はじめに

  • 本記事は、社内の非エンジニア向けの 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」「１」「①」など等価な文字の表記を統一できるので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','１','①','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
2
RAKSUL Data Analytics
RAKSUL Data AnalyticsPublication

RAKSUL の データサイエンティスト、データエンジニアを中心に情報発信している Publication です。データ分析やAI関連情報を中心に公開しています。記事内容はあくまでも個人の見解であり、会社としてレビュー等はしておりません。予めご了承ください。

Discussion

ログインするとコメントできます