💪

BigQuery超入門④

2025/01/29に公開

はじめに

こちらは BigQuery 超入門 ③ の続きです。

学ぶこと

  • 数値を扱う関数について知る
  • 文字列を扱う関数について知る
  • 正規表現を利用する関数について知る
  • 日付を扱う関数について知る

数値を扱う関数

切り捨てる FLOOR 関数

FLOOR 関数は切り捨てする関数。
処理の内容として「該当フィールドの値次の最大の整数値」を返す処理をしている。

例えば次のような場合だと結果は 5.0 になり切り捨てられていることが確認できる。

SELECT FLOOR(5.9) -- 5.0

値がマイナスの場合の結果は -6.0 になる。マイナスの場合は「より小さい方向」に数字が丸められる点に気を付ける。
処理内容から考えると理解しやすいかもしれない。

SELECT FLOOR(-5.9) -- -6.0

切り上げる CEIL 関数

CEIL 関数は切り上げをする関数になる。
処理の内容として「該当フィールドの値以上の最小の整数値」を返す処理をしている。

例えば次のような場合だと結果は 6.0 になり切り上げられていることが確認できる。

SELECT CEIL(5.1) -- 6.0

値がマイナスの場合の結果は -5.0 になる。マイナスの場合は「より大きい方向」に数字が丸められる点に気を付ける。
こちらも処理内容から考えるとイメージしやすいかもしれない。

SELECT CEIL(-5.9) -- -5.0

四捨五入する ROUND 関数

ROUND 関数は切り上げをする関数になる。
処理の内容として「該当フィールドの値を指定した桁数で丸める」処理をしている。

  • 桁数は省略可能(その場合は小数点以下が丸められる)
    • 小数点第一位が 0 なので、小数点第二位を指定したいときは桁数を 1 にする必要がある
  • 中間の値(丸める値が「.5」「5」などの場合は 0 から遠ざかるように丸める)
  • 桁数が正の場合は少数部、負の場合は整数部を丸める

シンプルな四捨五入を確認すると結果は 6.0 になり中間の値が切り上げられていることが確認できる。

SELECT ROUND(5.5) -- 6.0

正の桁数ですると少数部を丸める。
この場合は小数点第一位を指定していることになるので 1 が切り捨てられ 5.5 になる

SELECT ROUND(5.51, 1) -- 5.5

負の桁数ですると整数部を丸める。
小数点第一位が 0 なので、一の位を丸める場合は -1 を、十の位を丸める場合 -2 を指定する。

SELECT ROUND(4.51, -1) -- 0.0
SELECT ROUND(5.51, -1) -- 10.0
SELECT ROUND(54.51, -2) -- 100.0

マイナスの場合は「より近い数の方向」に数字が丸められる点に気を付ける。
次の場合は -5.0 になる。

SELECT ROUND(-5.4) -- -5.0

マイナスの場合の中間の値も「0 から遠ざかるように丸める」ため、次の場合は -6.0 になる

SELECT ROUND(-5.5) -- -6.0

乱数を発生させる RAND 関数

RAND 関数は乱数を発生させる。
戻り値として 「0」以上「1」未満の FLOAT64 型 の値が返ってくる。

RAND() -- 0.41950460936988837

データ型を変換する CAST 関数

CAST 関数はデータの型を変換する。
※数値てはない「田中」のような文字列を INT64 型にしたり「20201212」のような値を DATE 型に変換したりはできない。

例えば先ほど確認した数値を丸める関数や乱数を発生させる関数は FLOAT64 型の値が返ってくるため、それを整数に変えることができる。

SELECT ROUND(5.5) -- 6.0
SELECT CAST(ROUND(5.5) AS INT64) -- 6

他にも色々なケースでどのように変換されているのかを確認してみる。

SELECT CAST("2003" AS INT64) -- 2003(INT64型)
SELECT CAST(2003 AS STRING) -- 2003(STRING型)

SELECT CAST("2020-12-12" AS DATE) -- 2020-12-12(DATE型)
SELECT CAST(DATE("2020-12-12") AS STRING) -- 2020-12-12(STRING型)

データ型を変換する SAFE_CAST 関数

CAST は変換に失敗するとエラーが発生するが、SAFE_CAST 関数を使うとエラーを発生させずに NULL を出力する。

SELECT CAST(("20201212") AS DATE) -- Invalid dateエラー
SELECT SAFE_CAST(("20201212") AS DATE) -- NULL

文字列を扱う関数

文字列を結合する CONCAT 関数

CONCAT 関数を使うことで次のように文字列の結合ができる。

SELECT CONCAT(customer_name, "様") AS name FROM `project_id.dataset.customers`
name
石塚 拓様
長坂 賢介様
野中 裕之様

引数は 3 つ,4 つと指定でき、フィールド同士も結合できたりする。
文字列にすれば連結できるので先ほどの CAST を使って型変換した上で結合もできる。

SELECT CONCAT(customer_name, "様", " - ", CAST(customer_id AS STRING)) FROM `project_id.dataset.customers`
name
石塚 拓様 - xxxxxxx
長坂 賢介様 - xxxxxxx
野中 裕之様 - xxxxxxx

文字列の一部を取り出す SUBSTR 関数

SUBSTR("対象文字列", 開始位置, 抽出文字数) で特定の文字を取り出すことが可能。
開始位置をマイナスにすると文字列の右側から数えた場所が開始位置になる。

SELECT SUBSTR("本日は晴天です", 1, 2) -- 本日
SELECT SUBSTR("本日は晴天です", 4, 2) -- 晴天

SELECT SUBSTR("本日は晴天です", -7, 2) -- 本日
SELECT SUBSTR("本日は晴天です", -4, 2) -- 晴天

また開始位置が左端 or 右端から決まっている場合は LEFT 関数RIGHT 関数 が使える。

SELECT LEFT("本日は晴天です", 2) -- 本日
SELECT RIGHT("本日は晴天です", 2) -- です
SELECT LEFT("本日は晴天です", 5) -- 本日は晴天
SELECT RIGHT("本日は晴天です", 4) -- 晴天です

特定文字列の出現位置を取得する INSTR 関数

SUBSTR 関数と組み合わせて使うことが多いらしい。
INSTR(対象文字列、検索文字列、検索開始位置、出現回数)

検索文字列がヒットしなかった場合は 0 が戻り値として返ってくる。(特定の文字列が含まれているかどうかの確認にも使うことができる)

SELECT INSTR("本日は晴天です", "で") -- 6
SELECT INSTR("本日は晴天です", "田中") -- 0

-- 出現回数で何回目にヒットした文字列の位置を取得するかを指定できる
SELECT INSTR("本日は晴天晴天です", "晴天", 1, 1) -- 4
SELECT INSTR("本日は晴天晴天です", "晴天", 1, 2) -- 6

組み合わせると次のようにできそう。

SELECT INSTR("本日は晴天晴天です", SUBSTR("本日は晴天です",4, 2)) -- 4
SELECT INSTR("本日は晴天晴天です", SUBSTR("本日は晴天です",4, 2), 1, 2) -- 6

文字列を置換する REPLACE 関数

文字列を別の文字に置き換える場合は REPLACE 関数を使う。
REPLACE(対象文字列、検索文字列、置換後文字列)

SELECT REPLACE("本日は晴天です", "です", "なり") -- 本日は晴天なり

文字列の長さを取得する LENGTH 関数

SELECT LENGTH("本日は晴天です") -- 7

例えば REPLACE 関数と組みわせることで特定の文字列が何回出現したか?の回数を取得できる。

SELECT
  -- シンプルにテキストの文字数
  LENGTH("満足感が半端ない。この満足感を味わえるのであれば何回でもいきます。") AS text,
  -- 「満足」を削除した時の差分
  LENGTH("満足感が半端ない。この満足感を味わえるのであれば何回でもいきます。") - LENGTH(REPLACE("満足感が半端ない。この満足感を味わえるのであれば何回でもいきます。", "満足", "")) AS length_diff,
  -- (差分/削除文字数) で特定の文字列が出現した回数
  CAST((LENGTH("満足感が半端ない。この満足感を味わえるのであれば何回でもいきます。") - LENGTH(REPLACE("満足感が半端ない。この満足感を味わえるのであれば何回でもいきます。", "満足", "")))/2 AS INT64) AS manzoku_count

正規表現

正規表現を使うことで特定のパターンに一致する文字列を抽出したり、一致するかどうかの有無を確認したりできる。

文字列の有無を判定する REGEXP_CONTAINS 関数

特定のパターンに一致する文字列の有無を確認できる。
REGEXP_CONTAINS(対象文字列, 正規表現)

-- 名前の先頭が石のcustomerのみを抽出する
SELECT
  customer_name,
  REGEXP_CONTAINS(customer_name, r"^石") AS is_match
FROM
  `project_id.dataset.customers`
WHERE
  REGEXP_CONTAINS(customer_name, r"^石") IS TRUE
customer_name is_match
石塚 拓 true
石坂 政則 true
石渡 光徳 true

一致した文字列を取得できる REGEXP_EXTRACT 関数

特定のパターンに一致した文字列を取得できる。
REGEXP_EXTRACT(対象文字列, 正規表現, 検索開始位置, 出現回数)

-- 名前の先頭が石の場合は文字列を取得できる
SELECT
  customer_name,
  REGEXP_EXTRACT(customer_name, r"^石") AS match_name
FROM
  `project_id.dataset.customers`
WHERE
  REGEXP_EXTRACT(customer_name, r"^石") IS NOT NULL
customer_name match_name
石塚 拓
石坂 政則
石渡 光徳

オプションをつけることで対象文字列のどこから検索するのか、何回目に一致した文字を取得するのかを設定できる。

-- customer_id を検索して2回目の5が存在すると一致する
SELECT
  customer_id,
  REGEXP_EXTRACT(CAST(customer_id AS STRING), r"5", 1, 2) AS match_name
FROM
  `project_id.dataset.customers`
WHERE
  REGEXP_EXTRACT(CAST(customer_id AS STRING), r"5", 1, 2) IS NOT NULL
customer_id match_number
15546 5
15165 5
15415 5

一致した文字列を置換できる REGEXP_REPLACE 関数

特定のパターンに一致した文字列を置換できる。
REGEXP_REPLACE(対象文字列, 正規表現, 置換後の文字列)

SELECT
  customer_name,
  REGEXP_REPLACE(customer_name, r"^石", "A") AS replace_name
FROM
  `project_id.dataset.customers`
WHERE
  REGEXP_CONTAINS(customer_name, r"^石") IS TRUE
customer_name match_name
石塚 拓 A 塚 拓
石坂 政則 A 坂 政則
石渡 光徳 A 渡 光徳

第 2 引数につけている「r」について

REGEXP_CONTAINS(customer_name, r"^石") のようにプレフィックスについている「r」は raw string の略で公式ドキュメントでは次のように書かれている。

Quoted or triple-quoted literals that have the raw string literal prefix (r or R) are interpreted as raw strings (sometimes described as regex strings).
Backslash characters () don’t act as escape characters. If a backslash followed by another character occurs inside the string literal, both characters are preserved.
A raw string can’t end with an odd number of backslashes.
Raw strings are useful for constructing regular expressions. The prefix is case-insensitive.

日本語に訳すと次のようなことが書かれている。

  • プレフィックス r or R をつけると対象文字列は生文字列 or 正規表現文字列として扱われる
  • バックスラッシュがエスケープ文字として解釈されない
  • バックスラッシュの数が奇数で終わることはできない
  • プレフィックスは大文字と小文字で区別されない

これを読んだ上で次の動作がどうなるかを確認してみる。

SELECT '\\' -- \
SELECT '\\\\' -- \\
SELECT r'\\' -- \\
SELECT r'\\\\' -- \\\\

BigQuery で型判定する

CAST 関数を使っていてふと思ったのが、BigQuery は戻りの型がわからないということ。
見た目上は同じに見えてしまうのでわざわざスキーマを確認する必要がある。
ただ調べてみると bigquery-utils が提供している typeof 関数を使うと簡単に型判定ができた。
これを使って型による条件分岐が簡単にできるようになりそう。

SELECT bqutil.fn.typeof(CAST((15) AS STRING)) -- STRING

日付を扱う関数

まず日付のデータ型が把握できなかったのでそこから把握をする。

  • DATE 型(YYYY-MM-DD)
  • DATETIME 型(YYYY-MM-DD HH:MM:SS)
  • TIME 型(HH:MM:SS)
  • TIMESTAMP 型(YYYY-MM-DD HH:MM:SS[.FFF]UTC)

現在の日付や時刻を取得する関数

CURRENT_XXX 関数で現在の時を取得できる。

SELECT
  CURRENT_DATE(), -- 2025-01-18
  CURRENT_DATETIME(), -- 2025-01-18T09:08:49.458559
  CURRENT_TIME(), -- 09:08:49.458559
  CURRENT_TIMESTAMP() -- 2025-01-18 09:08:49.458559 UTC

何も指定しない場合は UTC なので日本のタイムゾーンに指定して JST にすると日本時間で現在の時が取得できる。

SELECT
  CURRENT_DATE("Asia/Tokyo"), -- 2025-01-18
  CURRENT_DATETIME("Asia/Tokyo"), -- 2025-01-18T18:19:50.191533
  CURRENT_TIME("Asia/Tokyo"), -- 18:19:50.191533
  CURRENT_TIMESTAMP() -- 2025-01-18 09:19:50.191533 UTC

TIMESTAMP 型は公式ドキュメントでは次のように記載されている。

A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST).

TIMESTAMP 型は UTC の情報を最初から持っており「世界で一つだけの時刻」を記録しているため時差に影響されない唯一の時間をはかる物差しとして使われる。

日付や時刻に値を加える関数

DATE_ADD(DATE型の値、INTERVAL 整数 デイトパート)
DATETIME_ADD(DATETIME型の値、INTERVAL 整数 パート)

デイトパートパートにはどの単位で日付を扱いたいかのキーワードを指定できる。

  • デイトパートに指定できるキーワード

    • YEAR,QUARTER,MONTH,WEEK,DAY
  • パートに指定できるキーワード

    • YEAR,QUARTER,MONTH,WEEK,DAY,HOUR,MINUTE,SECOND,MILLISECOND,MICROSECOUND

SELECT
  DATE_ADD(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 YEAR), -- 2026-01-18
  DATE_ADD(CURRENT_DATE("Asia/Tokyo"), INTERVAL 4 WEEK), -- 2025-02-15
  DATETIME_ADD(CURRENT_DATETIME("Asia/Tokyo"), INTERVAL 4 DAY), -- 2025-01-22T19:26:52.343412
  DATETIME_ADD(CURRENT_DATETIME("Asia/Tokyo"), INTERVAL 7 HOUR) -- 2025-01-19T02:26:52.343412

前の日付や時刻を取得する関数

先ほどの DATE_ADD 関数に負の整数を指定すると過去の日付や時刻を取得できる。
DATE_ADD(DATE型の値、INTERVAL 負の整数 デイトパート)
DATETIME_ADD(DATE_TIME型の値、INTERVAL 負の整数 パート)

もしくは次のようなDATE_SUB 関数を使って前の日付や日時を取得できる。
DATE_SUB(DATE型の値、INTERVAL 整数 デイトパート)
DATETIME_SUB(DATETIME型の値、INTERVAL 整数 パート)

SELECT
  DATE_ADD(CURRENT_DATE("Asia/Tokyo"), INTERVAL -1 YEAR), -- 2024-01-18 1年前
  DATE_ADD(CURRENT_DATE("Asia/Tokyo"), INTERVAL -4 DAY), -- 2025-01-14 4日前
  DATETIME_ADD(CURRENT_DATETIME("Asia/Tokyo"), INTERVAL -7 HOUR) -- 2025-01-18T12:29:21.361041 7時間前
SELECT
  DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 YEAR), -- 2023-01-23 2年前
  DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 MONTH), -- 2024-11-23 2ヶ月前
  DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 DAY) -- 2025-01-21 2日前

また「日」の加算・減算であれば次のような算術演算子で計算可能なので便利。

CURRENT_DATE("Asia/Tokyo")+30 -- 2025-02-22 30日後
CURRENT_DATE("Asia/Tokyo")-30 -- 2025-12-24 30日前

日付や時刻の差分を取得する関数

DATE_DIFF(DATE型の新しい日付, DATE型の古い日付, デイトパート)
DATETIME_DIFF(DATETIME型の新しい日時, DATETIME型の古い日時, パート)

SELECT
  DATE_DIFF(DATE("2023-05-01"), DATE("2022-05-01"), YEAR), -- 1
  DATE_DIFF(DATE("2023-05-01"), DATE("2022-05-01"), MONTH), -- 12
  DATE_DIFF(DATE("2023-05-01"), DATE("2022-05-01"), DAY) -- 365
  DATETIME_DIFF(DATETIME("2023-05-01 17:00:00"), DATETIME("2022-05-01 17:00:00"), HOUR) -- 8760

日付や時刻を丸める関数

DATE_TRUNC(DATE型の値, デイトパート)
DATETIME_TRUNC(DATETIME型の値, パート)

日付や時刻は「秒->分->時->日->月->四半期->年」という階層構造になっており、より大きい単位に丸めることができる。
他の用途として月初を知りたい時などにも使える。

SELECT
  DATE_TRUNC(DATE("2022-05-01"), YEAR), -- 2022-01-01
  DATE_TRUNC(DATE("2022-05-01"), QUARTER), -- 2022-04-01
  DATE_TRUNC(DATE("2022-05-30"), MONTH), -- 2022-05-01 月初の取得
  DATETIME_TRUNC(DATETIME("2022-05-01 17:45:34"), MINUTE), -- 2022-05-01T17:45:00
  DATETIME_TRUNC(DATETIME("2022-05-01 17:45:34"), HOUR), -- 2022-05-01T17:00:00
  DATETIME_TRUNC(DATETIME("2022-05-01 17:45:34"), DAY), -- 2022-05-01T00:00:00
  DATETIME_TRUNC(DATETIME("2022-05-01 17:45:34"), MONTH), -- 2022-05-01T00:00:00
  DATETIME_TRUNC(DATETIME("2022-05-01 17:45:34"), QUARTER), -- 2022-04-01T00:00:00
  DATETIME_TRUNC(DATETIME("2022-05-01 17:45:34"), YEAR) -- 2022-01-01T00:00:00

月末を知りたい場合は LAST_DAY 関数 を使うことで簡単に対象月の末日を取得できる。

SELECT LAST_DAY(CURRENT_DATE()) -- 2025-01-31

日付や時刻の一部の情報を取得する関数

日付や時刻から「年」や「月」などの一部の情報だけを取得できる。
また特徴として「DAYOFYEAR(年の内何日目か)」や「DAYOFWEEK(曜日)」のような情報も取得できる。

EXTRACT(デイトパート FROM DATE 型の値)
EXTRACT(パート FROM DATETIME 型の値)

SELECT
  EXTRACT(DAYOFYEAR FROM DATE("2022-05-01")), -- 121(121日/365日)
  EXTRACT(DAYOFWEEK FROM DATE("2022-05-01")), -- 1(日曜日が1で土曜日が7)
  EXTRACT(YEAR FROM DATE("2022-05-01")), -- 2022
  EXTRACT(QUARTER FROM DATE("2022-05-01")), -- 2
  EXTRACT(MONTH FROM DATE("2022-05-01")), -- 5
  EXTRACT(DAY FROM DATE("2022-05-01")), -- 1

Unix 時間を UTC に変換する関数

Unix 時間とは

コンピュータの世界では日付を表現する上でUnix 時間と呼ばれる表現方法がある。
Unix 時間は UTC(協定世界時)の 1970年1月1日0時0分0秒 からの経過時間で表されるとのこと。
例えば 1971年1月1日0時0分0秒 の場合はちょうど一年経過しているため
365日*24時間*60分*60秒=31536000
のように表現される。

この Unix 時間のフォーマットのままだと直感的に理解しづらい。
そのためこのフォーマットを UTC に変換できる関数がある。

SELECT
  TIMESTAMP_SECONDS(1363463153), -- 2013-03-16 19:45:53 UTC
  TIMESTAMP_MILLIS(1203634631536), -- 2008-02-21 22:57:11.536000 UTC
  TIMESTAMP_MICROS(1453634631536000), -- 2016-01-24 11:23:51.536000 UTC
  TIMESTAMP_SECONDS(1363463153), -- 2013-03-16 19:45:53 UTC
  -- UTCから日本時間に変換
  DATE(TIMESTAMP_SECONDS(1363463153), "+9"), -- 2013-03-17
  DATETIME(TIMESTAMP_MICROS(1453634631536000),"+9"), --2016-01-24T20:23:51.536000

参考記事・書籍

BigQuery ではじめる SQL データ分析 GA4 & Search Console & Google フォーム対応
(BigQuery)DATE 型と STRING 型を型判定する方法
BigQuery のプレフィックス文字を理解する
備忘録:BigQuery で月末や月初を指定するときの SQL
Lexical structure and syntax
Timestamp type

GitHubで編集を提案

Discussion