関数

SQL入門の4回目です。今回は関数について説明します。関数とはいろいろな処理をまとめて名前をつけたものです。一般的なプログラミングでもよく利用されます。BigQueryではたくさんの組み込み関数があらかじめ用意されています。select命令の列を記述する場所や、where句で条件を記述する箇所などで利用できます。ここでは、利用頻度が高いと思われる主な関数をご紹介します。

本記事はFuture Coders独自教材からの引用となります。

列の演算

関数の説明に入る前に、列に対して演算ができることを紹介しておきます。列の名前を変数のように扱って、四則演算や比較演算を使って式を記述すると、その結果が新しい列として得られます。例えば以下のようなことができます。

特定の列の値を2倍する
特定の列の値をTrue/Falseに変換する

四則演算

以下はdob_age列に＋１、－1の計算を行った列を記述した例です。

SELECT dob_age, 
    dob_age+1as next_year, 
    dob_age-1as last_year 
  FROM `sample.people`

このように列に対して加減乗除などの演算を行うことが可能です。

数学関数

一般的なプログラミング言語でも、数学の計算を行うための関数が用意されいますが、BigQueryでも多くの数学関数が用意されています。

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

ABS = 絶対値
RAND = 乱数
SQRT = 平方根
CEIL = 切りあげ
FLOOR = 切り捨て

以下は年を10才単位に変換した列(age_group)を追加した例です。年齢の列dob_ageを10で割り、floor関数で小数点以下を切り捨て、その結果を10倍しています。

RANDは乱数を生成する関数ですが、ランダムに行を抽出する目的で使われることがあります。以下の例を見てください。

SELECT*FROM `sample.people`
    where rand() <0.01

rand()は実行の都度0～1までの乱数を返します。この値が0.01より小さくなる確率は約1%です。すなわち、全体の行から1％の行をランダムに抽出します。この手法は、全ての行に対してrand関数を実行するため、計算にかかるコストを削減することにはならないことに注意してください。

計算のコストを下げるには from句の後ろにTABLESAMPLE SYSTEM (10 PERCENT)のような命令を記述します。TABLESAMPLE命令はもともとのテーブルをサンプリングするため、計算のコストを抑えることが可能です。

SELECT*from `sample.people` tablesamplesystem (1percent)

テーブルのサイズが小さいときにはサンプリングは行われません。膨大なデータがあるテーブルから検索するときにだけ有効になります。

キャスト

データの型を変換することをキャストといいます。テーブルにSchemaとしてデータ型が定義されていますが、キャスト演算子を使用してデータ型を変換することが可能です。

関数：CAST(expression AS typename)
概要：式の結果の型を他の型に変換します。

以下は計算結果を整数(int)に変換する例です。

SELECT dob_age,
  dob_age /10as p1,
  cast(dob_age /10asint) as p2,
FROM `sample.people`

以下出力例です。まず、値を10で割りp1列としています。それをさらにintにキャストして整数に変換しています。

行	      dob_age         　p1      p2
1	        59              5.9     6
2	        65              6.5     7
3	        75              7.5     8

BigQueryで使用できるデータ型は以下のURLに列挙されています。

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja

全てを覚えるのは大変なので、まずは以下の型を把握し、残りは必要に応じて調べる、そんな進め方がよいでしょう。

INT64 = 整数
FLOAT64 = 少数
BOOL = ブール
STRING = 文字列
DATE = 日付
TIME = 時刻
DATETIME = 日付と時刻

ちなみに、上記例のintはint64と同じ意味で、整数を意図します。

ときにはデータに意図した型以外の情報が格納されていることがあります。例えば、数値があるはずの列に文字が格納されていることもあります。そのような場合、CASTを実行すると「変換できません」というエラーになり処理が中断してしまいます。そのようなケースでもキャストを継続して行うにはSAFE_CASTを使用します。

関数：SAFE_CAST(expression AS typename)
概要：式の結果の型を他の型に変換します。エラーがあっても継続して実行します。

例えば、以下のSQLは途中でエラーが発生します。squirrel-dataのAbove_Ground__Height_in_Feet_列には数値以外に文字などのデータが混在しているためです。

SELECT 
  Above_Ground__Height_in_Feet_, 
  cast(Above_Ground__Height_in_Feet_ as float64) float_value 
FROM `sample.squirrel-data` orderby float_value desc

エラーメッセージは以下の通りです。

Bad double value: 6\357\277\275\357\277\275\303\22218

castをsafe_castに変換すると以下のような出力となります。

変換できない値のときに、safe_castを実行した結果がnullになっていることが確認できます。

文字列関数

文字を操作するための関数も多数用意されています。以下のURLにBigQueryで利用できる文字列関数が列挙されています。全て覚える必要はありません。「文字列に対してこんなことができるかな？」と疑問に思ったときに、以下のURLから関数を探してみるとよいでしょう。

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

イメージを持ってもらうためにいくつか主な関数について説明します。

LENGTH

文字列の長さを求めるにはLENGTH関数を使用します。

関数：LENGTH(value)
概要：STRING 値または BYTES 値の長さを返します。

LEFT/RIGHT

次は文字列の左右から切り出す関数です。

関数：LEFT(value, length)
概要：valueに対して、左からlength個の文字を返します

関数：RIGHT(value, length)
概要：valueに対して、右からlength個の文字を返します

SELECT name_first, left(name_first,3) as L, right(name_first,3) as R
  FROM `sample.people` 
  orderby dob_age
limit 3

L列は左から３文字を、R列は右から３文字を切り出しています。

STARTS_WITH, ENDS_WITH

特定の文字列から開始、特定の文字列で終了するか調べるにはSTARTS_WITH, ENDS_WITHを使用します。

関数：STARTS_WITH(value1, value2)
概要：value1がvalue2で始まる場合TRUEを返します

関数：ENDS_WITH(value1, value2))
概要：value1がvalue2で終わる場合TRUEを返します

以下は”Ad”から始まる名前を抽出する例です。

SELECT name_first FROM `sample.people`
 where starts_with(name_first, "Ad")

出力は以下の通りです。

行	    name_first
1	    Adalberto
2	    Adamantino
3	    Adam
4	    Adam
5	    Adeli

STRPOS, SUBSTR

以下のような手順で、特定の文字までの部分文字列を切り出すことが可能です。

特定の文字が何文字目にあるかをSTRPOS関数で調べ
その数値を使ってSUBSTRで文字列を切り出す。

関数：STRPOS(value1, value2)
概要：value1 内で最初に value2 が出現する位置を（開始値 1）を返します。

関数：SUBSTR(value, position[, length])
概要：元の文字列valueから、positionからlengthまでの文字列を切り出します。lengthが省略された場合は文字列の最後までになります。

以下はメールアドレスで＠より前の部分を切り出すサンプルです。

SELECT
  email, SUBSTR(email, 0, STRPOS(email, "@") -1) AS name_part,
FROM
  `sample.people`

CONCAT

文字列をつなげるときはCONCAT関数を使用します。

関数：CONCAT(value1[, …])
概要：1つ以上の値を1つの文字列に連結します

SELECT concat("hello", "world") as str1,
  concat("hello", " ", "world") as str2,
  concat("2+3", "=", 2+3) as str3

出力は以下の通りです。

行	str1            str2            str3
1	helloworld      hello world     2+3=5

正規表現

文字列を検索する場合、STARTS_WITH, ENDS_WITHや、like演算子が利用できますが、正規表現を使用するとより柔軟な検索が可能です。

REGEXP_CONTAINS
REGEXP_EXTRACT
REGEXP_EXTRACT_ALL
REGEXP_REPLACE

などいろいろな正規表現用の関数が用意されています。

関数：REGEXP_CONTAINS(value, regexp)
概要：value が正規表現 regexp に対して部分一致である場合は、TRUE を返します。

関数：REGEXP_REPLACE(value, regexp, replacement)
概要：正規表現 regexp と一致する value のすべての部分文字列を replacement に置き換えた STRING を返します。

以下はREGEXP_CONTAINSを使用して、Stories列に4桁の数値が含まれているレコードを抽出するサンプルです。さらに、検出結果をREGEXP_REPLACE関数を使って、改行文字を削除していいます。

SELECT REGEXP_REPLACE(Stories, "\n", "") as stories FROM `sample.stories` 
  where REGEXP_CONTAINS(Stories, "[0-9]{4}")

以下は出力例です。1972と4桁の数値を含むレコードのみが抽出されています。

行	stories
1	On 125th Street, butterfly gardenMajor acorn farm near Grant's TombAt Grant's Tomb - City Art 1972 Benches Etc. CeramicsSnowdropsStreamMore acornsForever wild

正規表現を使うほどではなく、単に文字列を置換する場合はREPLACE関数が使用できます。

関数：REPLACE(original_value, from_value, to_value)
概要：original_value 内に出現するすべての from_value を to_value に置き換えます

SPLIT

SPLIT関数は指定した文字で文字列を分割します。

関数：SPLIT(value[, delimiter])
概要：delimiter 引数を使用して value を分割します。STRING の場合、デフォルトの区切り文字はカンマ（,）です。

SPLIT関数で分割した結果は配列になります。個々の要素にアクセスするには[OFFSET(番号)]でアクセスします。

SELECT email, split(email, "@") as split, 
  split(email, "@")[OFFSET(0)] as name,
  split(email, "@")[OFFSET(1)] as domain,
  FROM `sample.people`

BigQueryでは列の値として配列（Array）を格納することができます。このように1列に複数の値を格納することを許容しないデータベースシステムも存在します。配列型の扱いについては別途説明します。

演習

ex-func1.py

peopleテーブルでmaleならtrue, femaleならfalseと、gender値がブール値になるようにしてください。

行	        gender      bool_gender
135         male        true
136	        male        true
137	        female      false
138	        female      false

ex-func2.py

peopleテーブルで、年齢を20代、30代となるように10の倍数にしてください。floor関数で切り捨てを使用すると良いでしょう。

行	    dob_age     tens
1	    59          50
2	    65          60
3	    75          70
4	    67          60

ex-func3.py

peopleテーブルで、名前の文字数をカウントする列を追加してください。

行	    name_first      len
1	    John            4
2	    Fletcher        8
3	    David           5

ex-func4.py

peopleテーブルで、メールアドレスが”da”で始まる行を抽出してください。

行	    email
1	    david.cooper@example.com
2	    david.chan@example.com
3	    dale.jackson@example.com
4	    daniel.gregory@example.com

ex-func5.py

peopleテーブルで、電話番号から市内番号だけを取り出してください。正規表現は"[0-9]{3,4}-[0-9]{4}" （＝数字を3か4桁 – 数字を4桁）を使用してください。

ex-func6.py

park-dataのOther_Animal_Sightingsにはリス以外の動物がカンマ区切りで列挙されています。splitで分割し、その配列の個数を出力してください。配列の長さを取得するにはARRAY_LENGTH関数を使用します。

ex-func7.py

peopleテーブルのメールアドレスで、先頭からピリオドまでの部分文字列を取得してください。

substrとstrposを使用した例
regexp_extractを使用した例

ex-func8.py

peopleテーブルのname_first, name_lastをつなげて以下のように出力してください。

解答例

ex-func1.py

SELECT gender, gender = "male" as bool_gender FROM `sample.people`

ex-func2.py

SELECT dob_age, cast(floor(dob_age /10) asint) *10as tens
FROM `sample.people`

ex-func3.py

SELECT name_first, length(name_first) as len FROM `sample.people`

ex-func4.py

SELECT email FROM `sample.people`
 where starts_with(email, "da")

ex-func5.py

SELECT REGEXP_EXTRACT(phone, "[0-9]{3,4}-[0-9]{4}") as city, phone 
    FROM `sample.people`

ex-func6.py

SELECT Other_Animal_Sightings, 
  array_length(split(Other_Animal_Sightings)) as couunt 
  FROM `sample.park-data`

ex-func7.py

SELECT email,
  SUBSTR(email, 0, STRPOS(email, ".") -1) AS name1,
  REGEXP_EXTRACT(email, "[a-z]+") AS name2
FROM `sample.people`

ex-func8.py

SELECT concat(name_first , " ", name_last) as full_name
FROM `sample.people`

日付・日時関数

BigQueryでは日時を扱う関数が多数用意されています。混乱を避けるために。最初にデータ型を意識することが大切です。

DATE = 日
DATETIME = 日時
TIME = 時刻
TIMESTAMP = 時刻

現在時刻・日時の取得

関数：CURRENT_DATE([time_zone])
指定したタイムゾーンまたはデフォルトのタイムゾーンの現在の日付（DATE型）を返します。

関数：CURRENT_TIME([time_zone])
指定したタイムゾーンまたはデフォルトのタイムゾーンの現在の時刻（TIME型）を返します。

関数：CURRENT_DATETIME([time_zone])
指定したタイムゾーンまたはデフォルトのタイムゾーンの現在の時刻（DATETIME型）を返します。

関数：CURRENT_TIMESTAMP()
UTCにおける時刻をナノ秒単位の精度で管理します。

ある列がDATE, TIME, DATETIME型かスキーマをみればわかります。自分で調べる場合には、BigQueryの組み込み関数を使用します。

関数：bqutil.fn.typeof(v)
引数vの型を返します。

以下はこれらの関数を実行した結果です。実行したのは日本時間の午前11時ころです（＝UTCは-9時間なので2時ころ）。

タイムゾーンを明示しない場合、time, datetimeはUTC時刻になっていることに注意してください。日本のタイムゾーンを明示する場合は”Asia/Tokyo”と引数で指定してください。日本で実行するとデフォルトで日本時間のように感じるかもしれませんが、クラウドにとってはあまり関係ありません。一方、TIMESTAMPはUTC時刻で管理します。タイムゾーンが重要な場合は、これらの違いに注意してください。

現在時刻・日時の指定

特定の日時を指定して、DATE/TIME/DATETIME/TIMESTAMPのデータを作成することも可能です。

select
  datetime('2023-02-20') as ex1,
  cast('2023-02-20'as datetime) as ex2,
  parse_datetime('%Y-%m-%d', '2023-02-20') as ex3,

上記SQLを実行すると以下のように出力されます。

行	ex1                     ex2                     ex3
1	2023-02-20T00:00:00     2023-02-20T00:00:00     2023-02-20T00:00:00

最初の列はdatetime関数を使って
2行目は文字列をdatetimeへキャスト
3行目はparse_datetime関数を使って

いずれもdatetimeのデータを取得しています。このように明示的に変換することもできます。おそらくは、日付や時刻の範囲指定をwhere句で指定することが多いでしょう。この場合は、自動的に適切な型への変換が行われるため、このような変換方法を意識せずに利用できます。

以下の例をみてください。2000年1月1日以降に生まれた人を検索しています。

SELECT email, dob_date FROM `sample.people`
  where dob_date > "2000-01-01"
  orderby dob_date

出力は以下の通りです。

dob_dateはTIMESTAMP型です。単に”2000-01-01″と文字列を記述しているだけですが、内部で型変換が行われ、正しく比較が行われています。まずはこのような記述方法を押さえておきましょう。

特定のフィールド（年月日時分秒）の抽出

日時で特定のフィールドをとりだすにはExtract関数を」使用します、。

関数：EXTRACT(part FROM date_expression)
指定した日付パーツに対応する値が返されます。part には、取り出したい値に応じて以下のような値を指定します。 DAYOFWEEK(曜日)、DAY(日)、DAYOFYEAR()、WEEK(週)、MONTH(月)、YEAR(年)、

以下はDATE型から年月日を抽出する例です。

以下はTIME型から時分秒を抽出する例です。

日時の加減算

昨年、来年、昨日、今日、1週間後などいろいろ差分を求めたいこともあるでしょう。日時の加減算用の関数が用意されています。

関数：DATE_ADD(date_expression, INTERVAL int64_expression date_part)
指定した時間間隔を DATE に追加します。 date_partには以下の値が指定できます。DAY, WEEK, MONTH, QUARTER, YEAR

関数：DATE_SUB(date_expression, INTERVAL int64_expression date_part)
DATE から指定した期間を減算します。 date_partには以下の値が指定できます。DAY, WEEK, MONTH, QUARTER, YEAR

関数：DATE_DIFF(date_expression_a, date_expression_b, date_part)
2 つの DATE オブジェクト（date_expression_a～date_expression_b）間に存在する、指定された date_part の間隔を示す整数を返します。 date_partには以下の値等が指定できます。DAY, WEEK, MONTH, QUARTER, YEAR

日時の切り捨て

月のはじめ、週の初め、年の初めなど、特定の日時に値を揃えたいこともあるでしょう。そんなときにはTRUNC関数を使用します。データ型に応じて以下のような関数が用意されています。

DATE_TRUNC
DATETIME_TRUNC
TIME_TRUNC
TIMESTAMP_TRUNC

指定できるパラメタが異なるだけで使い方は同じです。

関数：TIMESTAMP_TRUNC(timestamp_expression, date_time_part[, time_zone])
date_time_part の粒度まで TIMESTAMP 値を切り詰めます。 date_partには以下の値等が指定できます。 SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

以下は、TRUNC関数で年、月、日で切り捨てた例です。

演習

ex-date1.py

peopleテーブルにおいて2000年以降に生まれた人を列挙してください。9件ヒットします。

ex-date2.py

peopleテーブルにおいて1995年1/1～1999/12/31に生まれた人を列挙してください。30件ヒットします。

ex-date3.py

peopleテーブルにおいてbetweenを使用して、1995年1/1～1999/12/31に生まれた人を列挙してください。30件ヒットします。

ex-date4.py

peopleテーブルにおいて誕生日を、年・月・日と別々の列に分けて以下のように表示してください。

ex-date5.py

peopleテーブルにおいて、年度別に誕生した人の人数を数え、多い順に並べてください。上位3レコードは以下のようになります。

行	    year    count
1	    1998    11
2	    1961    10
3	    1958    9

ex-date6.py

今日の日付、昨年の日付、来月の日付を表示してください。

ex-date7.py

2020-01-01から今日までの経過年、月、日を表示してください。

ex-date8.py

peopleテーブルで、誕生日の列を使用して、以下のように年代ごとに生まれた人の数をカウントしてください。

解答例

ex-date1.py

SELECT*FROM `sample.people` where dob_date >= "2000-01-01"

ex-date2.py

SELECT*FROM `sample.people` 
    where "1995-01-01" <= dob_date and dob_date < "2000-01-01"

ex-date3.py

SELECT*FROM `sample.people` 
    where dob_date between "1995-01-01" and "1999-12-31"

ex-date4.py

SELECT dob_date, 
  EXTRACT(YEARfrom dob_date) year,
  EXTRACT(MONTHfrom dob_date) month,
  EXTRACT(DAYfrom dob_date) day,  
FROM `sample.people`

ex-date5.py

SELECTEXTRACT(YEARfrom dob_date) year, count(*) as count
   FROM `sample.people` 
groupbyyearorderby count desc

ex-date6.py

SELECTcurrent_date() as today,
  date_sub(current_date(), interval1year) as lastyear,
  date_add(current_date(), interval1month) as nextmonth

ex-date7.py

SELECTcurrent_date() today,
  date_diff(current_date(), "2000-01-01", year) as year_num,
  date_diff(current_date(), "2000-01-01", month) as month_num,
  date_diff(current_date(), "2000-01-01", day) as day_num,

ex-date8.py

SELECTcast(floor(EXTRACT(YEARfrom dob_date) /10) *10asint) as years, 
  count(*) as count
FROM `sample.people`
groupby years
orderby years

関数

目次

列の演算

四則演算

数学関数

キャスト

文字列関数

LENGTH

LEFT/RIGHT

STARTS_WITH, ENDS_WITH

STRPOS, SUBSTR

CONCAT

正規表現

SPLIT

演習

ex-func1.py

ex-func2.py

ex-func3.py

ex-func4.py

ex-func5.py

ex-func6.py

ex-func7.py

ex-func8.py

解答例

ex-func1.py

ex-func2.py

ex-func3.py

ex-func4.py

ex-func5.py

ex-func6.py

ex-func7.py

ex-func8.py

日付・日時関数

現在時刻・日時の取得

現在時刻・日時の指定

特定のフィールド（年月日時分秒）の抽出

日時の加減算

日時の切り捨て

演習

ex-date1.py

ex-date2.py

ex-date3.py

ex-date4.py

ex-date5.py

ex-date6.py

ex-date7.py

ex-date8.py

解答例

ex-date1.py

ex-date2.py

ex-date3.py

ex-date4.py

ex-date5.py

ex-date6.py

ex-date7.py

ex-date8.py