標準 SQL の式、関数、演算子

このページでは、関数や演算子などを含む BigQuery の式について説明します。

関数呼び出しルール

関数の説明で特に明示しない限り、以下の規則はすべての関数に適用されます。

  • 数値型を受け入れる関数は、1 つのオペランドが浮動小数点値オペランドであり、他のオペランドが別の数値型である場合、両方のオペランドが FLOAT64 に変換されてから関数が評価されます。
  • オペランドが NULL の場合、結果は IS 演算子を除いて NULL となります。
  • (関数の説明に示されているとおりに)タイムゾーンの影響を受ける関数の場合、タイムゾーンが指定されていなければ、デフォルトのタイムゾーンである UTC が使用されます。

SAFE. 接頭辞

構文:

SAFE.function_name()

説明

関数の先頭に SAFE. 接頭辞を付けると、エラーではなく NULL が返されます。SAFE. 接頭辞では、接頭辞が付けられた関数自体のエラーが防止されるのみで、引数の式を評価するときに発生するエラーは防止されません。SAFE. 接頭辞では、範囲外の値を示すエラーなどの関数入力の値が原因で発生するエラーのみが防止され、内部エラーやシステムエラーなどのエラーが発生する可能性があることには変わりありません。エラーを返さない関数の出力には、SAFE. は作用しません。RAND のようにエラーを返さない関数には、SAFE. は作用しません。

+= などの演算子SAFE. 接頭辞をサポートしません。除算演算のエラーを防ぐには、SAFE_DIVIDE を使用します。ただし、INARRAYUNNEST、およびこれに類似する関数などの演算子は、SAFE. 接頭辞をサポートしていません。CAST 関数や EXTRACT 関数も SAFE. 接頭辞をサポートしていません。エラーのキャストを防ぐには、SAFE_CAST を使用します。

次に示す最初の例のように SUBSTR 関数を使用すると、通常はエラーが返されます。これは、この関数が負の値を持つ長さ引数をサポートしていないためです。ただし、SAFE. 接頭辞があると、代わりに NULL が返されます。SUBSTR 関数の 2 番目の使用例では、想定される出力が提供され、SAFE. 接頭辞は影響を与えません。

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

サポートされる関数

BigQuery では、エラーを発生させる可能性のあるほとんどのスカラー関数(STRING 関数数学関数DATE 関数DATETIME 関数TIMESTAMP 関数など)で SAFE. 接頭辞の使用がサポートされています。集計関数分析関数ユーザー定義関数では SAFE. 接頭辞の使用がサポートされていません。

永続的なユーザー定義関数(UDF)の呼び出し

永続的 UDF を作成した後は、他の関数と同じように、データセットの名前を接頭辞として先頭に追加することで呼び出すことができます。

構文

[`project_name`].dataset_name.function_name([parameter_value[, ...]])

クエリの実行に使用しているプロジェクト以外のプロジェクトで UDF を呼び出すには、project_name が必要です。

次の例では、multiply_by_three という名前の UDF を作成し、同じプロジェクトから呼び出します。

CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);

SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15

次の例では、別のプロジェクトから永続的 UDF を呼び出します。


CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
  AS (x * y * 2);

SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24

変換規則

「変換」にはキャスティングや強制型変換などがあります。ただし、これらに限定されるわけではありません。

  • キャスティングは明示的変換であり、CAST() 関数を使用します。
  • 強制型変換は暗黙的変換であり、以下で説明されている状態の場合に BigQuery によって自動的に実行されます。
  • UNIX_DATE() などの独自の関数名を持つ、変換関数の 3 番目のグループがあります。

以下の表に、考えられるすべての CAST と、BigQuery データ型に対する強制型変換の可能性の要約を示します。「強制型変換後」は所定のデータ型(列など)のすべての式に適用されますが、リテラルとパラメータも強制型変換されます。詳細については、リテラル強制型変換パラメータ強制型変換をご覧ください。

元の型 キャスト先 強制型変換後
INT64 BOOL
INT64
NUMERIC
FLOAT64
STRING
FLOAT64
NUMERIC
NUMERIC INT64
NUMERIC
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
NUMERIC
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
NUMERIC
FLOAT64
STRING
BYTES
DATE
DATETIME
TIME
TIMESTAMP
 
BYTES BYTES
STRING
 
DATE DATE
DATETIME
STRING
TIMESTAMP
 
DATETIME DATE
DATETIME
STRING
TIME
TIMESTAMP
 
TIME STRING
TIME
 
TIMESTAMP DATE
DATETIME
STRING
TIME
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

キャスティング

構文:

CAST(expr AS typename)

キャスト構文は、式の結果の型を他の型に変換する必要があることを示す目的でクエリで使用されます。

例:

CAST(x=1 AS STRING)

これは、x1 の場合は "true"、その他の非 NULL 値の場合は "false"xNULL の場合は NULL になります。

サポートされる型の間でキャストするとき、元の値からターゲット ドメインに正常にマッピングされない場合は、ランタイム エラーが発生します。たとえば、バイト列が有効な UTF-8 ではない場合に BYTES を STRING キャスティングするとランタイム エラーが発生します。

次の型の x 式をキャスティングする場合、以下の規則が適用されます。

型変換の前 型変換の後 x をキャスティングする場合の規則
INT64 FLOAT64 近似値だが厳密には正確でない可能性のある FLOAT64 値を返します。
INT64 BOOL x0 の場合は FALSE、それ以外の場合は TRUE を返します。
NUMERIC 浮動小数点 NUMERIC は最も近い浮動小数点数に変換されますが、精度が低下する可能性があります。
FLOAT64 INT64 最も近い INT64 値を返します。
1.5 や -0.5 などの中間値の場合は、ゼロから離れるように丸められます。
FLOAT64 STRING 近似する文字列表現を返します。
FLOAT64 NUMERIC 浮動小数点数で小数点以下が 10 桁以上の場合、中間値はゼロから遠ざかるように丸められます。NaN+inf-inf をキャストするとエラーが返されます。NUMERIC の範囲外の値をキャストすると、オーバーフロー エラーが返されます。
BOOL INT64 xTRUE の場合は 1、それ以外の場合は 0 を返します。
BOOL STRING xTRUE の場合は "true"、それ以外の場合は "false" を返します。
STRING FLOAT64 有効な FLOAT64 リテラルと同じ形式を持つと解釈して、x を FLOAT64 値として返します。
また、"inf""+inf""-inf""nan" からのキャストをサポートします。
変換では大文字と小文字を区別しません。
STRING NUMERIC STRING に含まれる数値リテラルは、NUMERIC 型の最大精度や範囲を超えてはなりません。超えた場合はエラーが発生します。小数点以下が 10 桁以上の場合、キャスト後の NUMERIC 値は小数点以下が 9 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。
STRING BOOL x"true" の場合は TRUE を、x"false" の場合は FALSE を返します。
x のそれ以外の値はすべて無効であり、BOOL にキャスティングせずにエラーをスローします。
BOOL に変換するときに、STRING では大文字と小文字が区別されません。
STRING BYTES STRING は UTF-8 エンコーディングを使用して BYTES にキャストされます。たとえば、STRING "©" は BYTES にキャストされると、16 進数 C2 と A9 を持つ 2 バイト数列になります。
BYTES STRING UTF-8 STRING と解釈される x を返します。
たとえば BYTES リテラル b'\xc2\xa9' の場合、STRING にキャストすると UTF-8 として解釈され、Unicode 文字「©」になります。
x が有効な UTF-8 でない場合、エラーが発生します。
ARRAY ARRAY 正確に同じ ARRAY 型になっている必要があります。
STRUCT STRUCT 次の条件が満たされる場合に許可されます。
  1. 2 つの STRUCT が同じ数のフィールドを持っている。
  2. 元の STRUCT フィールド型を、対応する目的の STRUCT のフィールドの型に(フィールド名ではなく、フィールド順序によって定義されるように)明示的にキャストできる。

Safe キャスティング

CAST を使用する場合、BigQuery がそのキャスティングを実行できなければクエリは失敗します。たとえば、次のクエリによってエラーが発生します。

SELECT CAST("apple" AS INT64) AS not_a_number;

このようなエラーからクエリを守るには、SAFE_CAST を使用できます。SAFE_CAST はエラーの代わりに NULL を返すという点を除き、CAST とまったく同じです。

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

また、バイトから文字列にキャストする場合には SAFE_CONVERT_BYTES_TO_STRING 関数も使用できます。無効な UTF-8 文字列は Unicode 置換文字 U+FFFD に置き換えられます。詳しくは、SAFE_CONVERT_BYTES_TO_STRING をご覧ください。

16 進文字列から整数へのキャスティング

16 進数(0x123)を処理する場合、それらの文字列を整数としてキャストできます。

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+

SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123    | -291       |
+-----------+------------+

時間型のキャスティング

BigQuery では、文字列と時間型の間のキャスティングが次のようにサポートされています。

CAST(time_expression AS STRING)
CAST(string_expression AS TIME)

時間型から文字列へのキャスティングはタイムゾーンとは独立しており、HH:MM:SS の形式になります。文字列から時間にキャスティングする場合、文字列はサポートされる時間のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の時間を表している場合は、エラーが発生します。

日付型のキャスティング

BigQuery では、文字列と日付型の間のキャスティングが次のようにサポートされています。

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

データ型から文字列へのキャスティングはタイムゾーンとは独立しており、YYYY-MM-DD の形式になります。文字列から日付にキャスティングする場合、文字列はサポートされる日付のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の日付を表している場合は、エラーが発生します。

日時型のキャスティング

BigQuery では、文字列と日時型の間のキャスティングが次のようにサポートされています。

CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)

日時型から文字列へのキャスティングはタイムゾーンとは独立しており、YYYY-MM-DD HH:MM:SS の形式になります。文字列から日時型にキャスティングする場合、文字列はサポートされる日時型のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の日時を表している場合は、エラーが発生します。

タイムスタンプ型のキャスティング

BigQuery では、次のように文字列に対するタイムスタンプ型のキャスティングがサポートされています。

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

タイムスタンプから文字列にキャスティングするとき、タイムスタンプはデフォルトのタイムゾーンである UTC を使用して解釈されます。生成されるサブ秒の桁数は、サブ秒部分の末尾のゼロの数によって決まります。CAST 関数によって 0、3、または 6 桁に切り捨てられます。

文字列からタイムスタンプにキャスティングする場合、string_expression はサポートされるタイムスタンプのリテラル形式に従う必要があります。従わない場合、ランタイム エラーが発生します。string_expression 自体に time_zone が含まれる場合があります。詳細については、タイムゾーンをご覧ください。string_expression にタイムゾーンが含まれる場合、そのタイムゾーンが変換に使用されます。それ以外の場合、デフォルトのタイムゾーンである UTC が使用されます。文字列が 6 桁未満の場合、暗黙的に拡大されます。

string_expression が無効である、サブ秒が 6 桁よりも多い(マイクロ秒よりも精度が高い)、またはサポートされるタイムスタンプ範囲外にある時刻を表している場合、エラーが発生します。

日付型、日時型、タイムスタンプ型の間のキャスティング

変換ルールテーブルに示すように、BigQuery では、日付型、日時型、タイムスタンプ型の間のキャスティングがサポートされています。

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

日付からタイムスタンプへのキャスティングでは、date_expression はデフォルトのタイムゾーンである UTC の午前 0 時(1 日が開始する時刻)を基点として解釈されます。タイムスタンプから日付へのキャスティングでは、タイムスタンプはデフォルトのタイムゾーンを基点にして効率的に切り捨てられます。

CAST(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)

日時からタイムスタンプへのキャスティングでは、datetime_expression はデフォルトのタイムゾーンである UTC の午前 0 時(1 日が開始する時刻)を基点として解釈されます。

強制型変換

BigQuery は、関数のシグネチャを一致させる必要がある場合、式の結果の型を別の型に強制的に変換します。たとえば、関数 func() が型 INT64 の単一の引数を取るように定義され、式が FLOAT64 の結果の形式を持つ引数として使用される場合、その式の結果が INT64 型に強制変換されてから、func() が計算されます。

リテラル強制型変換

BigQuery では、次のリテラル強制型変換がサポートされています。

入力データ型 結果のデータ型 メモ
STRING リテラル DATE
DATETIME
TIME
TIMESTAMP

リテラル強制型変換は、実際のリテラル型が、対象の関数によって要求される型と異なる場合に必要です。たとえば、関数 func() が DATE 引数を取る場合、STRING リテラル "2014-09-27" は DATE に強制変換されるため、式 func("2014-09-27") は有効です。

リテラル変換は解析時に評価され、入力リテラルを目的の型に正常に変換できない場合、エラーが発生します。

注: 文字列リテラルが数値型に強制変換されることはありません。

パラメータ強制型変換

BigQuery では、次のパラメータ強制型変換がサポートされています。

入力データ型 結果のデータ型
STRING パラメータ

パラメータ値を目的の型に正常に強制変換できない場合は、エラーが発生します。

その他の変換関数

BigQuery では、次の変換関数も使用できます。

集計関数

集計関数は、グループの行を 1 つの値に集約する関数です。COUNTMINMAX などが集計関数の例として挙げられます。

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

GROUP BY 句と組み合わせて使用した場合、集約されたグループには通常、少なくとも 1 つの行が存在します。関連付けられた SELECTGROUP BY 句がない場合、またはある集計関数の修飾子によって集約するグループの行がフィルタされる場合、集計関数は空のグループを集約することを必要とする可能性があります。この場合、COUNT 関数と COUNTIF 関数は 0 を返し、他のすべての集計関数は NULL を返します。

以降のセクションで、BigQuery でサポートされている集計関数について説明します。

ANY_VALUE

ANY_VALUE(expression)  [OVER (...)]

説明

グループから選択された行の expression を返します。どの行が選択されるかは非決定的で、ランダムではありません。入力によって行が生成されない場合、NULL を返します。グループ内のすべての行で expressionNULL の場合、NULL を返します。

ANY_VALUE は、あたかも RESPECT NULLS が指定されているかのように動作します。expressionNULL である行が考慮され、そのような行が選択される場合があります。

サポートされる引数の型

すべて

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

返されるデータ型

入力データ型と一致します。

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+
SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

説明

expression 値の配列を返します。

サポートされる引数の型

ARRAY を除くすべてのデータ型。

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、ARRAY_AGG() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  3. IGNORE NULLS または RESPECT NULLS: IGNORE NULLS を指定した場合、NULL 値は結果から除外されます。RESPECT NULLS を指定した場合、または両方とも指定しない場合は、結果に NULL 値が含まれます。最終的なクエリ結果の配列に NULL 要素が含まれている場合は、エラーが発生します。
  4. ORDER BY: 値の順序を指定します。
    • それぞれの並べ替えキーで、デフォルトの並べ替え方向は ASC になります。
    • NULL: ORDER BY 句のコンテキストの場合、NULL が最小の可能値です。つまり、NULL は ASC の並べ替えでは先頭になり、DESC の並べ替えでは最後になります。
    • 浮動小数点データ型の場合の順序とグループ化については、浮動小数点のセマンティクスをご覧ください。
    • DISTINCT も指定する場合には、並べ替えキーを expression と同じにする必要があります。
    • ORDER BY を指定しないと、出力配列内の要素の順序は変わる可能性があります。つまり、この関数を使用するたびに、異なる結果を受け取る可能性があります。
  5. LIMIT: 結果に含める expression 入力の最大数を指定します。上限 n は INT64 型の定数にする必要があります。

返されるデータ型

ARRAY

入力行がない場合、この関数は NULL を返します。

SELECT FORMAT("%T", ARRAY_AGG(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT FORMAT("%T", ARRAY_AGG(x IGNORE NULLS)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-----------+
| array_agg |
+-----------+
| [-2, 1]   |
+-----------+
SELECT
  x,
  FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+----+-------------------------+
| x  | array_agg               |
+----+-------------------------+
| 1  | [1, 1]                  |
| 1  | [1, 1]                  |
| 2  | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| 2  | [1, 1, 2, -2, -2, 2]    |
| 3  | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

説明

ARRAY 型の expression から要素を結合し、結果として単一の ARRAY を返します。この関数は NULL 入力配列を無視しますが、NULL 以外の入力配列に含まれる NULL 要素は無視しません。ただし、クエリの最終結果の配列に NULL 要素が含まれていると、エラーが発生します。

サポートされる引数の型

ARRAY

省略可能な句

次の順番で句が適用されます。

  1. ORDER BY: 値の順序を指定します。
    • それぞれの並べ替えキーで、デフォルトの並べ替え方向は ASC になります。
    • 配列の順序指定には対応していません。並べ替えキーを expression と同じにすることはできません。
    • NULL: ORDER BY 句のコンテキストの場合、NULL が最小の可能値です。つまり、NULL は ASC の並べ替えでは先頭になり、DESC の並べ替えでは最後になります。
    • 浮動小数点データ型の場合の順序とグループ化については、浮動小数点のセマンティクスをご覧ください。
    • ORDER BY を指定しないと、出力配列内の要素の順序は変わる可能性があります。つまり、この関数を使用するたびに、異なる結果を受け取る可能性があります。
  2. LIMIT: 結果に含める expression 入力の最大数を指定します。この制限は、入力配列の数に適用されます。配列内の要素数には適用されません。空の配列は 1 とカウントされます。NULL 配列はカウントされません。 上限 n は INT64 型の定数にする必要があります。

返されるデータ型

ARRAY

入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4]       |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

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

説明

NULL 以外の値の平均を返すか、入力に NaN が含まれる場合は NaN を返します。

サポートされる引数の型

INT64 のような、任意の数値入力型。浮動小数点入力型の場合、戻り値の結果は変動する可能性があります、つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、AVG() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

返されるデータ型

  • 入力型が NUMERIC の場合は NUMERIC。
  • FLOAT64

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+
SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(expression)

説明

expression でビット積演算を実行し、結果を返します。

サポートされる引数の型

  • INT64

返されるデータ型

INT64

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR(expression)

説明

expression に対してビット論理和演算を実行し、結果を返します。

サポートされる引数の型

  • INT64

返されるデータ型

INT64

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression)

説明

expression でビット排他論理和演算を実行し、結果を返します。

サポートされる引数の型

  • INT64

省略可能な句

DISTINCT: expression の重複を除いた値を集約して結果を返します。

返されるデータ型

INT64

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1. COUNT(*) [OVER (...)]

2. COUNT([DISTINCT] expression) [OVER (...)]

説明

  1. 入力内の行数を返します。
  2. expressionNULL 以外の値と評価された行の数を返します。

サポートされる引数の型

expression には任意のデータ型を指定できます。 DISTINCT が存在する場合、expression にはグループ化可能なデータ型のみ指定できます。

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

戻りデータの型

INT64

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------+------------+--------------+
| x    | count_star | count_dist_x |
+------+------------+--------------+
| 1    | 3          | 2            |
| 4    | 3          | 2            |
| 4    | 3          | 2            |
| 5    | 1          | 1            |
+------+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

COUNTIF

COUNTIF(expression)  [OVER (...)]

説明

expression に対する TRUE の値の数を返します。入力行がない場合、または expression がすべての行に対して FALSE または NULL と評価される場合に 0 を返します。

サポートされる引数の型

BOOL

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

戻りデータの型

INT64

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+
SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

説明

すべての非 NULL 式の論理 AND を返します。入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

サポートされる引数の型

BOOL

戻りデータの型

BOOL

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression)

説明

すべての非 NULL 式の論理 OR を返します。入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

サポートされる引数の型

BOOL

戻りデータの型

BOOL

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression)  [OVER (...)]

説明

NULL 式の最大値を返します。入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。入力に NaN が含まれている場合、NaN を返します。

サポートされる引数の型

ARRAYSTRUCTGEOGRAPHY を除くすべてのデータ型

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

戻りデータの型

入力値として使用されるデータ型と同じ型。

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(expression)  [OVER (...)]

説明

NULL 式の最小値を返します。入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。入力に NaN が含まれている場合、NaN を返します。

サポートされる引数の型

ARRAYSTRUCTGEOGRAPHY を除くすべてのデータ型

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

戻りデータの型

入力値として使用されるデータ型と同じ型。

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

説明

非 NULL 値を結合して得られた値(STRING または BYTES のいずれか)を返します。

delimiter が指定された場合、連結された値はその区切り文字で区切られ、それ以外の場合は区切り文字としてカンマが使用されます。

サポートされる引数の型

STRING BYTES

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、STRING_AGG() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  3. ORDER BY: 値の順序を指定します。
    • それぞれの並べ替えキーで、デフォルトの並べ替え方向は ASC になります。
    • NULL: ORDER BY 句のコンテキストの場合、NULL が最小の可能値です。つまり、NULL は ASC の並べ替えでは先頭になり、DESC の並べ替えでは最後になります。
    • 浮動小数点データ型の場合の順序とグループ化については、浮動小数点のセマンティクスをご覧ください。
    • DISTINCT も指定する場合には、並べ替えキーを expression と同じにする必要があります。
    • ORDER BY を指定しないと、出力配列内の要素の順序は変わる可能性があります。つまり、この関数を使用するたびに、異なる結果を受け取る可能性があります。
  4. LIMIT: 結果に含める expression 入力の最大数を指定します。この上限は、入力文字列の数に適用されます。入力内の文字数またはバイト数には適用されません。空の文字列は 1 とカウントされます。NULL 文字列はカウントされません。上限 n は INT64 型の定数にする必要があります。

戻りデータの型

STRING BYTES

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+--------------+
| string_agg   |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+---------------+
| string_agg    |
+---------------+
| pear & banana |
+---------------+
SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+--------+------------------------------+
| fruit  | string_agg                   |
+--------+------------------------------+
| NULL   | NULL                         |
| pear   | pear & pear                  |
| pear   | pear & pear                  |
| apple  | pear & pear & apple          |
| banana | pear & pear & apple & banana |
+--------+------------------------------+

SUM

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

説明

NULL 以外の値の合計を返します。

式が浮動小数点値である場合、合計は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。

サポートされる引数の型

サポートされているすべての数値データ型。

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

戻りデータの型

  • 入力が整数の場合、INT64 を返します。
  • 入力型が NUMERIC の場合、NUMERIC を返します。
  • 入力が浮動小数点値の場合、FLOAT64 を返します。

入力が NULL のみの場合、NULL を返します。

入力に行が含まれていない場合、NULL を返します。

入力に Inf が含まれている場合、Inf を返します。

入力に -Inf が含まれている場合、-Inf を返します。

入力に NaN が含まれている場合、NaN を返します。

入力に Inf-Inf の組み合わせが含まれている場合、NaN を返します。

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+
SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 3   |
| 3 | 3   |
| 1 | 5   |
| 4 | 5   |
| 4 | 5   |
| 1 | 5   |
| 2 | 7   |
| 5 | 7   |
| 2 | 7   |
+---+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

統計集計関数

BigQuery は、次の統計集計関数をサポートします。

CORR

CORR(X1, X2)  [OVER (...)]

説明

一連の数値ペアのピアソン相関係数を返します。それぞれの数値ペアでは、最初の数値が従属変数であり、2 番目の数値が独立変数です。返される結果は -1 から 1 の間になります。結果が 0 の場合、相関がないことを意味します。

入力されたペアに NULL の値が 1 つ以上あると、この関数はそのペアを無視します。NULL 値のない入力ペアが 2 つ未満の場合、この関数は NULL を返します。

サポートされる入力型

FLOAT64

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

戻りデータの型

FLOAT64

COVAR_POP

COVAR_POP(X1, X2)  [OVER (...)]

説明

一連の数値ペアの母共分散を返します。最初の数値は従属変数です。2 番目の数値は独立変数です。返される結果は -Inf から +Inf の間になります。

入力されたペアに NULL の値が 1 つ以上あると、この関数はそのペアを無視します。NULL 値のない入力ペアがない場合、この関数は NULL を返します。NULL 値のない入力ペアが 1 つだけの場合は 0 を返します。

サポートされる入力型

FLOAT64

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

戻りデータの型

FLOAT64

COVAR_SAMP

COVAR_SAMP(X1, X2)  [OVER (...)]

説明

一連の数値ペアの標本共分散を返します。最初の数値は従属変数です。2 番目の数値は独立変数です。返される結果は -Inf から +Inf の間になります。

入力されたペアに NULL の値が 1 つ以上あると、この関数はそのペアを無視します。NULL 値のない入力ペアが 2 つ未満の場合、この関数は NULL を返します。

サポートされる入力型

FLOAT64

省略可能な句

OVER: ウィンドウを指定します。分析関数をご覧ください。

戻りデータの型

FLOAT64

STDDEV_POP

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

説明

バイアスをかけた母集団の標準偏差値を返します。返される結果は 0 から +Inf の間になります。

この関数は NULL 入力を無視します。すべての入力が無視されると、この関数は NULL を返します。

この関数は、NULL 以外の入力を 1 つでも受け取ると 0 を返します。

サポートされる入力型

FLOAT64

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、STDDEV_POP() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

戻りデータの型

FLOAT64

STDDEV_SAMP

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

説明

バイアスをかけない、値の標本標準偏差を返します。返される結果は 0 から +Inf の間になります。

この関数は NULL 入力を無視します。NULL 以外の入力が 2 つ未満の場合、この関数は NULL を返します。

サポートされる入力型

FLOAT64

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、STDDEV_SAMP() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

戻りデータの型

FLOAT64

STDDEV

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

説明

STDDEV_SAMP のエイリアスです。

VAR_POP

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

説明

バイアスをかけた、値の母分散を返します。返される結果は 0 から +Inf の間になります。

この関数は NULL 入力を無視します。すべての入力が無視されると、この関数は NULL を返します。

この関数は、NULL 以外の入力を 1 つでも受け取ると 0 を返します。

サポートされる入力型

FLOAT64

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、VAR_POP() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

戻りデータの型

FLOAT64

VAR_SAMP

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

説明

バイアスをかけない標本分散値を返します。返される結果は 0 から +Inf の間になります。

この関数は NULL 入力を無視します。NULL 以外の入力が 2 つ未満の場合、この関数は NULL を返します。

サポートされる入力型

FLOAT64

省略可能な句

次の順番で句が適用されます。

  1. OVER: ウィンドウを指定します。分析関数をご覧ください。この句は現在、VAR_SAMP() 内の他の句と互換性がありません。
  2. DISTINCT: expression の重複を除いた値を集約して結果を返します。

戻りデータの型

FLOAT64

VARIANCE

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

説明

VAR_SAMP のエイリアスです。

近似集計関数

近似集計関数は、メモリ使用量と時間においてはスケーラブルですが、正確な結果ではなく、近似的な結果を生成します。これらの関数は通常、COUNT(DISTINCT ...) などの正確な集計関数と比べ必要とするメモリは少量ですが、統計的な不確実性をもたらします。このため近似集計は、線形メモリの使用が実用的でない大規模なデータ ストリームや、すでに概算されているデータに適しています。

このセクションの近似集計関数は、データの中間的な推測ではなく、入力データに対して直接作用します。これらの関数では、スケッチでの推測の精度をユーザーが指定できません。スケッチの精度を指定する場合は、以下を参照してください。

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

説明

COUNT(DISTINCT expression) の近似結果を返します。返される値は統計的な推定値であり、必ずしも実際の値ではありません。

この関数は COUNT(DISTINCT expression) よりも精度は低いものの、入力値が非常に多い場合はより効率的に処理できます。

サポートされる引数の型

ARRAYSTRUCT除くすべてのデータ型

返されるデータ型

INT64

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTILES

APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])

説明

expression の値のグループに対する近似境界を返します。number は作成する変位値の数を表します。この関数は、number +1 個の要素からなる配列を返します。最初の要素は近似最小値であり、最後の要素は近似最大値です。

サポートされる引数の型

expression には、サポートされているデータ型のうち、ARRAYSTRUCT除くすべてのデータ型を使用できます。

number は INT64 にする必要があります。

省略可能な句

次の順番で句が適用されます。

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. IGNORE NULLS または RESPECT NULLS: IGNORE NULLS を指定した場合、NULL 値は結果から除外されます。RESPECT NULLS を指定した場合、または両方とも指定しない場合は、結果に NULL 値が含まれます。最終的なクエリ結果の配列に NULL 要素が含まれている場合は、エラーが発生します。

返されるデータ型

expression パラメータで指定された型の ARRAY。

入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;

+---------------+
| percentile_90 |
+---------------+
| 9             |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(expression, number)

説明

expression の近似トップ要素を返します。number パラメータで、返される要素の数を指定します。

サポートされる引数の型

expression には、GROUP BY 句がサポートしているどのデータ型も使用できます。

number は INT64 にする必要があります。

返されるデータ型

STRUCT 型の ARRAY。STRUCT には 2 つのフィールドが含まれています。最初のフィールド(value)には入力値が含まれています。2 番目のフィールド(count)には、値が返された回数を示す INT64 が含まれています。

入力行がない場合、NULL を返します。

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

NULL の処理

APPROX_TOP_COUNT では、入力に含まれる NULL は無視されません。例:

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(expression, weight, number)

説明

割り当てられた weight の合計に基づいて expression の近似トップ要素を返します。number パラメータで、返される要素の数を指定します。

weight 入力が負の値または NaN の場合、この関数はエラーを返します。

サポートされる引数の型

expression には、GROUP BY 句がサポートしているどのデータ型も使用できます。

weight は、以下のいずれかにする必要があります。

  • INT64
  • FLOAT64

number は INT64 にする必要があります。

返されるデータ型

STRUCT 型の ARRAY。STRUCT は、valuesum の 2 つのフィールドで構成されます。value フィールドには、入力式の値が含まれます。sum フィールドは weight と同じ型であり、ここには value フィールドに関連付けられた weight 入力の近似値の合計が含まれます。

入力行がない場合、NULL を返します。

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

NULL の処理

APPROX_TOP_SUM は、expressionweight パラメータの NULL 値を無視しません。

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+

HyperLogLog++ 関数

HyperLogLog++ アルゴリズム(HLL++)では、スケッチからカーディナリティを推定します。スケッチを使用せず、カスタマイズされた精度を必要としない場合は、システムで定義された精度の近似集計関数を使用することを検討してください。

HLL++ 関数は近似集計関数です。近似集計は通常、正確な集計関数COUNT(DISTINCT) など)と比べ必要とするメモリは少量ですが、統計的な不確実性も生じます。このため、HLL++ 関数は、線形メモリの使用が実用的でない大規模なデータ ストリームや、すでに概算されているデータに適しています。

BigQuery は、次の HLL++ 関数をサポートします。

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

説明

1 つ以上の input 値を取り、それらの値を HLL++ スケッチに集約する集計関数です。各スケッチは BYTES データ型で表現されます。スケッチは、HLL_COUNT.MERGE または HLL_COUNT.MERGE_PARTIAL で結合できます。結合の必要がない場合には、HLL_COUNT.EXTRACT で個別の値の最終カウントを抽出できます。

この関数では、省略可能なパラメータ precision を使用できます。このパラメータは推測値の精度を定義しますが、スケッチの処理やディスクへの保存をするための追加のメモリが必要になります。以下の表に、精度の許容値、グループごとの最大スケッチサイズ、標準精度の信頼区間(CI)を示します。

精度 最大スケッチサイズ(KiB) 65% CI 95% CI 99% CI
10 1 ±1.63% ±3.25% ±6.50%
11 2 ±1.15% ±2.30% ±4.60%
12 4 ±0.81% ±1.63% ±3.25%
13 8 ±0.57% ±1.15% ±1.72%
14 16 ±0.41% ±0.81% ±1.22%
15(デフォルト) 32 ±0.29% ±0.57% ±0.86%
16 64 ±0.20% ±0.41% ±0.61%
17 128 ±0.14% ±0.29% ±0.43%
18 256 ±0.10% ±0.20% ±0.41%
19 512 ±0.07% ±0.14% ±0.29%
20 1,024 ±0.05% ±0.10% ±0.20%
21 2,048 ±0.04% ±0.07% ±0.14%
22 4,096 ±0.03% ±0.05% ±0.10%
23 8,192 ±0.02% ±0.04% ±0.07%
24 16384 ±0.01% ±0.03% ±0.05%

入力が NULL の場合、この関数は NULL を返します。

詳細については、HyperLogLog の実践: 最新のカーディナリティ推測アルゴリズムをご覧ください。

サポートされる入力型

INT64、NUMERIC、STRING、BYTES

戻り値の型

BYTES

SELECT
  HLL_COUNT.INIT(respondent) AS respondents_hll,
  flavor,
  country
FROM UNNEST([
  STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
  (1, "Chocolate", "CH"),
  (2, "Chocolate", "US"),
  (2, "Strawberry", "US")])
GROUP BY flavor, country;

HLL_COUNT.MERGE

HLL_COUNT.MERGE(sketch)

説明

複数の HLL++ セットスケッチを結合してカーディナリティを返す集計関数です。

sketch は同じ精度にし、同じ型で初期化する必要があります。精度や型が異なるスケッチを結合すると、エラーが発生します。たとえば、INT64 データから初期化したスケッチと STRING データから初期化したスケッチは結合できません。

この関数は、スケッチを結合するときに NULL 値を無視します。行がない場合や NULL 値しか存在しない場合には、0 を返します。

サポートされる入力型

BYTES

戻り値の型

INT64

SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.MERGE_PARTIAL

HLL_COUNT.MERGE_PARTIAL(sketch)

説明

1 つ以上の HLL++ sketch 入力を取り、それらの値を新しいスケッチに結合する集計関数です。

入力がないか、すべての入力が NULL の場合、この関数は NULL を返します。

サポートされる入力型

BYTES

戻り値の型

BYTES

SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.EXTRACT

HLL_COUNT.EXTRACT(sketch)

説明

1 つの HLL++ スケッチからカーディナリティの推測値を抽出するスカラー関数です。

sketch が NULL の場合、この関数はカーディナリティの推測値として 0 を返します。

サポートされる入力型

BYTES

戻り値の型

INT64

SELECT
  flavor,
  country,
  HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country);

+------------+---------+-----------------+
| flavor     | country | num_respondents |
+------------+---------+-----------------+
| Vanilla    | CH      | 1               |
| Chocolate  | CH      | 1               |
| Chocolate  | US      | 1               |
| Strawberry | US      | 1               |
+------------+---------+-----------------+

HLL++ アルゴリズムの概要

HLL++ アルゴリズムは、非常に低い、および非常に高いカーディナリティをより正確に推定することで、HLL アルゴリズムを改善します。HLL++ アルゴリズムには、64 ビットハッシュ関数、低いカーディナリティの推測のメモリ要件を減らすためのスパース表現、および低いカーディナリティの推測の経験的バイアス補正が含まれています。

スケッチの概要

スケッチは、大規模なデータ ストリームの要約です。スケッチから統計情報を抽出して元データの特定の統計情報を推定したり、スケッチを統合して複数のデータ ストリームを要約したりできます。スケッチには次のような特長があります。

  • 元データを固定のメモリ表現に圧縮する。
  • 入力よりも、漸近的に小さくなる。
  • シリアル化されたメモリ内の形式で、劣線形データ構造である。
  • 通常、作成に使用された入力よりも必要とするメモリ量が少なくなる。

スケッチは他のシステムと統合できます。たとえば、Cloud DataflowApache Spark などの外部アプリケーションでスケッチを作成し、BigQuery で使用できます(またはその逆も可能です)。スケッチでは、COUNT(DISTINCT) のような非加法関数用の中間集計を作成することもできます。

番号付け関数

以降のセクションで、BigQuery でサポートされている番号付け関数について説明します。番号付け関数は、分析関数のサブセットです。分析関数の機能については、分析関数のコンセプトをご覧ください。番号付け関数の機能については、番号付け関数のコンセプトをご覧ください。

OVER 句の要件:

  • PARTITION BY: 省略可。
  • ORDER BY: 必須(ROW_NUMBER() を除く)。
  • window_frame_clause: 禁止

RANK

説明

順序付けされたパーティション内の各行の(1 から始まる)序数ランクを返します。すべてのピア行は同じランク値を受け取ります。次の行、またはピア行のセットは、常に 1 ずつ増加する DENSE_RANK ではなく、前のランク値を持つピアの数だけ増加するランク値を受け取ります。

サポートされる引数の型

INT64

DENSE_RANK

説明

ウィンドウ パーティション内の各行の序数(1 ベース)ランクを返します。すべてのピア行は同じランク値を受け取り、後続のランク値は 1 ずつ増加されます。

サポートされる引数の型

INT64

PERCENT_RANK

説明

(RK-1)/(NR-1)として定義される行のパーセンタイル ランクを返します。RK は行の RANK であり、NR はパーティション内の行数です。NR=1 の場合、0 を返します。

サポートされる引数の型

FLOAT64

CUME_DIST

説明

NP/NR として定義される行の相対的ランクを返します。NP は、現在の行に先行するか、ピアになっている行数に定義されます。NR はパーティション内の行数です。

サポートされる引数の型

FLOAT64

NTILE

NTILE(constant_integer_expression)

説明

この関数は行の順序付けに基づいて行を constant_integer_expression で指定された数のバケットに分割し、各行に割り当てられた 1 ベースのバケット番号を返します。バケット内の行数は、最大で 1 ずつ変えることができます。 余りの値(行数をバケットで割った余り)は、バケット 1 で開始するバケットごとに 1 が分配されます。constant_integer_expression が NULL、0、または負の値に評価されると、エラーが出されます。

サポートされる引数の型

INT64

ROW_NUMBER

説明

ORDER BY 句は必要ありません。順序付けされた各パーティションの各行の順次行序数(1 ベース)を返します。ORDER BY 句が指定されていない場合、結果は変動する可能性があります。

サポートされる引数の型

INT64

ビット関数

BigQuery は、次のビット関数をサポートしています。

BIT_COUNT

BIT_COUNT(expression)

説明

入力 expression は整数または BYTES にする必要があります。

入力 expression で設定されたビット数を返します。符号付き整数の場合、これは 2 の補数形式になります。

戻りデータの型

INT64

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

+-------+--------+---------------------------------------------+--------+
| a     | a_bits | b                                           | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0     | 0      | b""                                         | 0      |
| 0     | 0      | b"\x00"                                     | 0      |
| 5     | 2      | b"\x05"                                     | 2      |
| 8     | 1      | b"\x00\x08"                                 | 1      |
| 65535 | 16     | b"\xff\xff"                                 | 16     |
| -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
| -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
| NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
+-------+--------+---------------------------------------------+--------+

数学関数

数学関数の動作は次のとおりです。

  • いずれかの入力パラメータが NULL の場合、NULL を返します。
  • いずれかの引数が NaN の場合、NaN を返します。

ABS

ABS(X)

説明

絶対値を計算します。引数が整数で、出力値を同じ型で表せない場合、エラーを返します(これは、正の表記が含まれていない最大の負の入力値に対してのみ発生します)。+/-inf 引数に対しては +inf を返します。

SIGN

SIGN(X)

説明

負、ゼロ、正の引数に対してそれぞれ -1、0、+1 を返します。浮動小数点引数では、正と負のゼロは区別されません。NaN 引数に対しては NaN を返します。

IS_INF

IS_INF(X)

説明

値が正または負の無限大の場合に TRUE を返します。

NULL 入力に対しては NULL を返します。

IS_NAN

IS_NAN(X)

説明

値が NaN 値である場合、TRUE を返します。

NULL 入力に対しては NULL を返します。

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

説明

X を Y で除算します。この関数が失敗することはありません。FLOAT64 を返します。除算演算子(/)とは異なり、この関数はゼロまたはオーバーフローによる除算エラーを生成しません。

特殊ケース:

  • 結果がオーバーフローした場合は、+/-inf を返します。
  • Y=0 かつ X=0 の場合、NaN を返します。
  • Y=0 かつ X!=0 の場合、+/-inf を返します。
  • X = +/-inf かつ Y = +/-inf の場合、NaN を返します。

IEEE_DIVIDE の動作については、下の表で詳しく説明します。

IEEE_DIVIDE の特殊なケース

次の表は、IEEE_DIVIDE の特殊なケースを示しています。

分子データ型(X) 分母データ型(Y) 結果値
0 以外 0 +/-inf
0 0 NaN
0 NaN NaN
NaN 0 NaN
+/-inf +/-inf NaN

RAND

RAND()

説明

[0, 1) の範囲の FLOAT64 型の疑似ランダム値を生成します。0 は含まれますが、1 は含まれません。

SQRT

SQRT(X)

説明

X の平方根を計算します。X が 0 未満の場合にエラーを生成します。

X が +inf の場合、+inf を返します。

POW

POW(X, Y)

説明

X を Y 乗した値を返します。結果がアンダーフローし、表現できない場合、関数は値 0 を返します。次のいずれかの条件を満たす場合、エラーを返します。

  • X が 0 未満の有限の値で、かつ Y が整数値でない場合
  • X が 0 で、かつ Y が 0 未満の有限の値の場合

POW() の動作については、下の表で詳しく説明します。

POWER

POWER(X, Y)

説明

POW() と同等です。

POW(X, Y)POWER(X, Y) の特殊ケース

POW(X, Y)POWER(X, Y) の特殊ケースは次のとおりです。

X Y POW(X, Y) または POWER(X, Y)
1.0 NaN を含む任意の値 1.0
NaN を含む任意の値 0 1.0
-1.0 +/-inf 1.0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0
ABS(X) < 1 +inf 0
ABS(X) > 1 +inf +inf
-inf Y < 0 0
-inf Y > 0 Y が奇数の場合は -inf、それ以外は +inf
+inf Y < 0 0
+inf Y > 0 +inf

EXP

EXP(X)

説明

e の X 乗を計算します(自然指数関数)。結果がアンダーフローする場合、ゼロを返します。結果がオーバーフローする場合は、エラーを生成します。X が +/-inf である場合、この関数は +inf または 0 を返します。

LN

LN(X)

説明

X の自然対数を計算します。X が 0 以下の場合、エラーを生成します。X が +inf の場合、+inf を返します。

LOG

LOG(X [, Y])

説明

X のみが存在する場合、LOGLN と同義です。Y も存在する場合、LOG は Y を底とする X の対数を計算します。次の場合はエラーを生成します。

  • X がゼロ以下
  • Y が 1.0
  • Y がゼロ以下

LOG(X, Y) の動作については、下の表で詳しく説明します。

LOG(X, Y) の特殊なケース

X Y LOG(X, Y)
-inf 任意値 NaN
任意値 +inf NaN
+inf 0.0 Y < 1.0 -inf
+inf Y > 1.0 +inf

LOG10

LOG10(X)

説明

LOG と似ていますが、10 を底とする対数を計算します。

GREATEST

GREATEST(X1,...,XN)

説明

入力のいずれかが NULL の場合 NULL を返します。入力のいずれかが NaN の場合 NaN を返します。それ以外では、< 比較によって X1,...,XN の間の最大値を返します。

LEAST

LEAST(X1,...,XN)

説明

入力のいずれかが NULL の場合、NULL を返します。入力のいずれかが NaN の場合、NaN を返します。それ以外では、> 比較によって X1,...,XN の間の最小値を返します。

DIV

DIV(X, Y)

説明

Y による X の整数除算の結果を返します。ゼロ除算はエラーを返します。-1 による除算はオーバーフローする可能性があります。 考えられる結果の型については、DIV(X, Y)MOD(X, Y) の結果の型をご覧ください。

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

説明

除算演算子(/)と同じですが、0 で割った場合など、エラーがあると NULL を返します。

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

説明

乗算演算子(*)と同じですが、オーバーフローが発生すると NULL を返します。

SAFE_NEGATE

SAFE_NEGATE(X)

説明

単項マイナス演算子(-)と同じですが、オーバーフローが発生すると NULL を返します。

SAFE_ADD

SAFE_ADD(X, Y)

説明

加算演算子(+)と同じですが、オーバーフローが発生すると NULL を返します。

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

説明

X から Y を減算した結果を返します。減算演算子(-)と同じですが、オーバーフローが発生すると NULL を返します。

MOD

MOD(X, Y)

説明

モジュロ関数: Y による X の除算の剰余を返します。返される値は X と同じ符号を持ちます。Y が 0 の場合、エラーが発生します。考えられる結果の型については、DIV(X, Y)MOD(X, Y) の結果の型をご覧ください。

DIV(X, Y)MOD(X, Y) の結果の型

 FLOAT64INT64NUMERIC
FLOAT64FLOAT64ERRORFLOAT64
INT64FLOAT64INT64NUMERIC
NUMERICFLOAT64NUMERICNUMERIC

ROUND

ROUND(X [, N])

説明

X のみが存在する場合、ROUND は X を最も近い整数に丸めます。N が存在する場合、ROUND は小数点以下の N 小数位に X を丸めます。N が負の場合、ROUND は小数点の左側の桁を丸めます。中間の値の場合は、ゼロから遠ざかるように丸めます。オーバーフローが発生した場合は、エラーが発生します。

TRUNC

TRUNC(X [, N])

説明

X のみが存在する場合、TRUNC は絶対値が X 以下で最も近い整数に X を丸めます。N も存在する場合、TRUNC の動作は ROUND(X, N) と似ていますが、常にゼロに近づくように丸め、オーバーフローすることはありません。

CEIL

CEIL(X)

説明

X 以上で最小の整数値を(FLOAT64 型で)返します。

CEILING

CEILING(X)

説明

CEIL(X) と同義

FLOOR

FLOOR(X)

説明

X よりも大きくない最大の整数値を(FLOAT64 型で)返します。

丸め関数の動作の例

BigQuery 丸め関数の動作例:

入力「X」 ROUND(X) TRUNC(X) CEIL(X) FLOOR(X)
2.0 2.0 2.0 2.0 2.0
2.3 2.0 2.0 3.0 2.0
2.8 3.0 2.0 3.0 2.0
2.5 3.0 2.0 3.0 2.0
-2.3 -2.0 -2.0 -2.0 -3.0
-2.8 -3.0 -2.0 -2.0 -3.0
-2.5 -3.0 -2.0 -2.0 -3.0
0 0 0 0 0
+/-inf +/-inf +/-inf +/-inf +/-inf
NaN NaN NaN NaN NaN

COS

COS(X)

説明

X のコサインを計算します。X は RADIANS で指定されます。失敗することはありません。

COSH

COSH(X)

説明

X の双曲線コサインを計算します。X は RADIANS で指定されます。オーバーフローが発生した場合は、エラーが発生します。

ACOS

ACOS(X)

説明

X の逆コサインの主値を計算します。戻り値は [0,π] の範囲内です。X が [-1, 1] の範囲外の値である場合、エラーになります。

ACOSH

ACOSH(X)

説明

X の逆双曲線コサインを計算します。X が 1 未満の値の場合、エラーになります。

SIN

SIN(X)

説明

X のサインを計算します。X は RADIANS で指定されます。失敗することはありません。

SINH

SINH(X)

説明

X の双曲線サインを計算します。X は RADIANS で指定されます。オーバーフローが発生した場合は、エラーが発生します。

ASIN

ASIN(X)

説明

X の逆サインの主値を計算します。戻り値は [-π/2,π/2] の範囲内です。X が [-1, 1] の範囲外である場合、エラーになります。

ASINH

ASINH(X)

説明

X の逆双曲線正サインを計算します。失敗することはありません。

TAN

TAN(X)

説明

X のタンジェントを計算します。X は RADIANS で指定されます。オーバーフローが発生した場合は、エラーが発生します。

TANH

TANH(X)

説明

X の双曲線タンジェントを計算します。X は RADIANS で指定されます。失敗することはありません。

ATAN

ATAN(X)

説明

X の逆タンジェントの主値を計算します。戻り値は [-π/2,π/2] の範囲内です。失敗することはありません。

ATANH

ATANH(X)

説明

X の逆双曲線タンジェントを計算します。X が [-1, 1] 未満の範囲外である場合、エラーになります。

ATAN2

ATAN2(Y, X)

説明

象限を判断するために 2 つの引数の符号を用いて、Y/X の逆タンジェントの主値を計算します。戻り値は [-π,π] の範囲内です。

この関数の動作は、下の表で詳しく説明します。

ATAN2() の特殊なケース

Y X ATAN2(Y, X)
NaN 任意値 NaN
任意値 NaN NaN
0 0 X と Y の符号に応じて 0、π または -π
有限値 -inf Y の符号に応じて π または -π
有限値 +inf 0
+/-inf 有限値 Y の符号に応じて π/2 または π/2
+/-inf -inf Y の符号に応じて ¾π または -¾π
+/-inf +inf Y の符号に応じて π/4 または -π/4

三角関数と双曲線丸め関数の特殊なケース

X COS(X) COSH(X) ACOS(X) ACOSH(X) SIN(X) SINH(X) ASIN(X) ASINH(X) Tan(X) TANH(X) ATAN(X) ATANH(X)
+/-inf NaN =+inf NaN =+inf NaN =+inf NaN =+inf NaN =+1.0 π/2 NaN
-inf NaN =+inf NaN NaN NaN -inf NaN -inf NaN -1.0 -π/2 NaN
NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

RANGE_BUCKET

RANGE_BUCKET(point, boundaries_array)

説明

RANGE_BUCKET は、並べ替えられた配列をスキャンして、ポイントの上限の 0 ベースの位置を返します。ビルド パーティション、ヒストグラム、ビジネス定義ルールなどでデータをグループ化する必要がある場合に有用です。

RANGE_BUCKET は、次のルールに従います。

  • 配列内にポイントがある場合は、次に大きい値のインデックスを返します。

    RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value
    RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
    
  • ポイントが配列内に存在せず、2 つの値の間にある場合は、大きい方の値のインデックスを返します。

    RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
    
  • ポイントが配列の最初の値より小さい場合は 0 が返されます。

    RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
    
  • ポイントが配列の最後の値以上である場合、配列の長さを返します。

    RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
    
  • 配列が空の場合は 0 を返します。

    RANGE_BUCKET(80, []) -- 0 is return value
    
  • ポイントが NULL または NaN である場合、NULL を返します。

    RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
    
  • ポイントと配列のデータ型は互換性がある必要があります。

    RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value
    RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value
    RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
    

次の場合に実行エラーが発生します。

  • 配列に NaN 値または NULL 値が含まれている。

    RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
    
  • 配列が昇順で並べ替えられていない。

    RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
    

パラメータ

  • point: 汎用値です。
  • boundaries_array: 汎用値の汎用配列です。

戻り値

INT64

students という名前のテーブル内で、各 age_group バケットにいくつのレコードが存在するかを、生徒の年齢に基づいて確認します。

  • age_group 0 (age < 10)
  • age_group 1 (age >= 10, age < 20)
  • age_group 2 (age >= 20, age < 30)
  • age_group 3(age >= 30)
WITH students AS
(
  SELECT 9 AS age UNION ALL
  SELECT 20 AS age UNION ALL
  SELECT 25 AS age UNION ALL
  SELECT 31 AS age UNION ALL
  SELECT 32 AS age UNION ALL
  SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

+--------------+-------+
| age_group    | count |
+--------------+-------+
| 0            | 1     |
| 2            | 2     |
| 3            | 3     |
+--------------+-------+

次のセクションでは、BigQuery でサポートされているナビゲーション関数について説明します。ナビゲーション関数は、分析関数のサブセットです。分析関数の機能については、分析関数のコンセプトをご覧ください。ナビゲーション関数の機能については、ナビゲーション関数のコンセプトをご覧ください。

FIRST_VALUE

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

説明

現在のウィンドウ フレーム内の最初の行の value_expression の値を返します。

この関数は、IGNORE NULLS が使用されていない場合は、計算に NULL 値が含まれます。IGNORE NULLS が使用されている場合、関数の計算から NULL 値が除外されます。

サポートされる引数の型

value_expression には、式から返すことができる任意のデータ型を指定できます。

戻りデータの型

ANY

以下は、各部門の最速時間を計算した例です。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  FIRST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 0                |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 436              |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 891              |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 956              |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 1109             |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 0                |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 426              |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 691              |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 733              |
+-----------------+-------------+----------+--------------+------------------+

LAST_VALUE

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

説明

現在のウィンドウ フレーム内の最後の行に対する value_expression の値を返します。

IGNORE NULLS が使用されていない場合、この関数の計算には NULL 値が含まれます。IGNORE NULLS が使用されている場合、関数の計算から NULL 値が除外されます。

サポートされる引数の型

value_expression には、式から返すことができる任意のデータ型を指定できます。

戻りデータの型

ANY

以下は、各部門について最遅時間を計算した例です。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
  TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  LAST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 03:10:14     | 1109             |
| Nikki Leith     | 02:59:01    | F30-34   | 03:10:14     | 673              |
| Jen Edwards     | 03:06:36    | F30-34   | 03:10:14     | 218              |
| Meghan Lederer  | 03:07:41    | F30-34   | 03:10:14     | 153              |
| Lauren Reasoner | 03:10:14    | F30-34   | 03:10:14     | 0                |
| Lisa Stelzner   | 02:54:11    | F35-39   | 03:06:24     | 733              |
| Lauren Matthews | 03:01:17    | F35-39   | 03:06:24     | 307              |
| Desiree Berry   | 03:05:42    | F35-39   | 03:06:24     | 42               |
| Suzy Slane      | 03:06:24    | F35-39   | 03:06:24     | 0                |
+-----------------+-------------+----------+--------------+------------------+

NTH_VALUE

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])

説明

現在のウィンドウ フレーム内の N 番目の行の value_expression の値を返します。N は constant_integer_expression によって定義されます。該当する行がない場合、NULL を返します。

IGNORE NULLS が使用されていない場合、この関数の計算には NULL 値が含まれます。IGNORE NULLS が使用されている場合、関数の計算から NULL 値が除外されます。

サポートされる引数の型

  • value_expression には、式から返すことができる任意のデータ型を指定できます。
  • constant_integer_expression には、整数を返す任意の定数式を指定できます。

戻りデータの型

ANY

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
  SELECT name,
  finish_time,
  division,finishers,
  FIRST_VALUE(finish_time)
    OVER w1 AS fastest_time,
  NTH_VALUE(finish_time, 2)
    OVER w1 as second_fastest
  FROM finishers
  WINDOW w1 AS (
    PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));

+-----------------+-------------+----------+--------------+----------------+
| name            | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | NULL           |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 02:59:01       |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 02:59:01       |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 02:59:01       |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 02:59:01       |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 02:59:01       |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 03:01:17       |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 03:01:17       |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 03:01:17       |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 03:01:17       |
+-----------------+-------------+----------+--------------+----------------+

LEAD

LEAD (value_expression[, offset [, default_expression]])

説明

後続の 1 つの行の value_expression の値を返します。offset の値を変更すると、どれだけ後の行が返されるかが変更されます。デフォルト値は 1 で、ウィンドウ枠内の直後の行を示します。offset が NULL または負の値の場合はエラーになります。

指定したオフセットの行がウィンドウ枠内に存在しない場合は、オプションの default_expression が使用されます。この式は定数式である必要があり、その型を value_expression の型に暗黙的に強制型変換できる必要があります。指定されていない場合、default_expression はデフォルトで NULL になります。

サポートされる引数の型

  • value_expression には、式から返すことができる任意のデータ型を指定できます。
  • offset は、負の値ではない整数値のリテラルまたはパラメータである必要があります。
  • default_expression は、value_expression の型との間で互換性が必要です。

戻りデータの型

ANY

次の例は、LEAD 関数の基本的な使用方法を示しています。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;

+-----------------+-------------+----------+-----------------+
| name            | finish_time | division | followed_by     |
+-----------------+-------------+----------+-----------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL            |
| Sophia Liu      | 02:51:45    | F30-34   | Nikki Leith     |
| Nikki Leith     | 02:59:01    | F30-34   | Jen Edwards     |
| Jen Edwards     | 03:06:36    | F30-34   | Meghan Lederer  |
| Meghan Lederer  | 03:07:41    | F30-34   | Lauren Reasoner |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL            |
| Lisa Stelzner   | 02:54:11    | F35-39   | Lauren Matthews |
| Lauren Matthews | 03:01:17    | F35-39   | Desiree Berry   |
| Desiree Berry   | 03:05:42    | F35-39   | Suzy Slane      |
| Suzy Slane      | 03:06:24    | F35-39   | NULL            |
+-----------------+-------------+----------+-----------------+

次の例では、省略可能な offset パラメータを使用しています。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | NULL             |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL             |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | NULL             |
| Suzy Slane      | 03:06:24    | F35-39   | NULL             |
+-----------------+-------------+----------+------------------+

次の例では、NULL 値をデフォルト値に置換しています。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody           |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | Nobody           |
| Lauren Reasoner | 03:10:14    | F30-34   | Nobody           |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | Nobody           |
| Suzy Slane      | 03:06:24    | F35-39   | Nobody           |
+-----------------+-------------+----------+------------------+

LAG

LAG (value_expression[, offset [, default_expression]])

説明

先行する 1 つの行の value_expression の値を返します。offset の値を変更すると、どれだけ前の行が返されるかが変更されます。デフォルト値は 1 で、ウィンドウ枠内の直前の行を示します。offset が NULL または負の値の場合はエラーになります。

指定したオフセットの行がウィンドウ枠内に存在しない場合は、オプションの default_expression が使用されます。この式は定数式である必要があり、その型を value_expression の型に暗黙的に強制型変換できる必要があります。指定されていない場合、default_expression はデフォルトで NULL になります。

サポートされる引数の型

  • value_expression には、式から返すことができる任意のデータ型を指定できます。
  • offset は、負の値ではない整数値のリテラルまたはパラメータである必要があります。
  • default_expression は、value_expression の型との間で互換性が必要です。

戻りデータの型

ANY

次の例は、LAG 関数の基本的な使用方法を示しています。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | NULL             |
| Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       |
| Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      |
| Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      |
| Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL             |
| Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    |
| Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  |
| Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    |
+-----------------+-------------+----------+------------------+

次の例では、省略可能な offset パラメータを使用しています。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL              |
| Sophia Liu      | 02:51:45    | F30-34   | NULL              |
| Nikki Leith     | 02:59:01    | F30-34   | NULL              |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL              |
| Lauren Matthews | 03:01:17    | F35-39   | NULL              |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

次の例では、NULL 値をデフォルト値に置換しています。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody            |
| Sophia Liu      | 02:51:45    | F30-34   | Nobody            |
| Nikki Leith     | 02:59:01    | F30-34   | Nobody            |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | Nobody            |
| Lauren Matthews | 03:01:17    | F35-39   | Nobody            |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

PERCENTILE_CONT

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])

説明

線形補間を使用して、value_expression に対して指定されたパーセンタイル値を計算します。

RESPECT NULLS が存在しない場合、この関数は NULL 値を無視します。RESPECT NULLS が存在する場合:

  • 2 つの NULL 値の間の補間は NULL を返します。
  • NULL 値と非 NULL 値の間の補間は非 NULL 値を返します。

サポートされる引数の型

  • value_expression は数値式です。
  • percentile は、[0, 1] の範囲内の FLOAT64 リテラルです。

戻りデータの型

FLOAT64

次の例では、NULL を無視して、値の列からいくつかのパーセンタイルの値を計算します。

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0   | 0.03        | 1.5    | 2.7          | 3   |
+-----+-------------+--------+--------------+-----+

次の例では、NULL を尊重しながら、値の列からいくつかのパーセンタイルの値を計算します。

SELECT
  PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+------+-------------+--------+--------------+-----+
| min  | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0           | 1      | 2.6          | 3   |
+------+-------------+--------+--------------+-----+

PERCENTILE_DISC

PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])

説明

個別の value_expression に対する指定されたパーセンタイル値を計算します。返される値は、特定の percentile と等しいかそれ以上の累積分布を持つ value_expression の最初に並べ替えられた値です。

RESPECT NULLS が存在しない場合、この関数は NULL 値を無視します。

サポートされる引数の型

  • value_expression は、任意の順序付け可能な型にできます。
  • percentile は、[0, 1] の範囲内の FLOAT64 リテラルです。

戻りデータの型

ANY

次の例では、NULL を無視して、値の列からいくつかのパーセンタイルの値を計算します。

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+-----+--------+-----+
| x    | min | median | max |
+------+-----+--------+-----+
| c    | a   | b      | c   |
| NULL | a   | b      | c   |
| b    | a   | b      | c   |
| a    | a   | b      | c   |
+------+-----+--------+-----+

次の例では、NULL を尊重しながら、値の列からいくつかのパーセンタイルの値を計算します。

SELECT
  x,
  PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+------+--------+-----+
| x    | min  | median | max |
+------+------+--------+-----+
| c    | NULL | a      | c   |
| NULL | NULL | a      | c   |
| b    | NULL | a      | c   |
| a    | NULL | a      | c   |
+------+------+--------+-----+

集計分析関数

次のセクションでは、BigQuery でサポートされている集計分析関数について説明します。分析関数の機能については、分析関数のコンセプトをご覧ください。集計分析関数の機能については、集計分析関数のコンセプトをご覧ください。

BigQuery では、分析関数として次の集計関数がサポートされています。

OVER 句の要件:

  • PARTITION BY: 省略可。
  • ORDER BY: 省略可。DISTINCT が存在する場合は使用できません。
  • window_frame_clause: 省略可。DISTINCT が存在する場合は使用できません。

例:

COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)
SUM(DISTINCT x) OVER ()

ハッシュ関数

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

説明

オープンソースの FarmHash ライブラリFingerprint64 関数を使用して、STRING または BYTES 入力のフィンガープリントを計算します。特定の入力に対して、この関数の出力が変わることはありません。

戻り値の型

INT64

WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

MD5

MD5(input)

説明

MD5 アルゴリズムを使用して、入力のハッシュを計算します。入力は STRING または BYTES のいずれかです。文字列の場合、入力はバイトの配列として扱われます。

この関数は 16 バイトを返します。

戻り値の型

BYTES

SELECT MD5("Hello World") as md5;

-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
+--------------------------+
| md5                      |
+--------------------------+
| sQqNsWTgdUEFt6mb5y4/5Q== |
+--------------------------+

SHA1

SHA1(input)

説明

SHA-1 アルゴリズムを使用して入力のハッシュを計算します。入力は STRING または BYTES のいずれかです。文字列の場合、入力はバイトの配列として扱われます。

この関数は 20 バイトを返します。

戻り値の型

BYTES

SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1                         |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+

SHA256

SHA256(input)

説明

SHA-256 アルゴリズムを使用して、入力のハッシュを計算します。入力は STRING または BYTES のいずれかです。文字列の場合、入力はバイトの配列として扱われます。

この関数は 32 バイトを返します。

戻り値の型

BYTES

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

説明

SHA-512 アルゴリズムを使用して、入力のハッシュを計算します。入力は STRING または BYTES のいずれかです。文字列の場合、入力はバイトの配列として扱われます。

この関数は 64 バイトを返します。

戻り値の型

BYTES

SELECT SHA512("Hello World") as sha512;

文字列関数

以下の文字列関数は、2 種類のデータ型 STRINGBYTES の値を処理できます。STRING 値は、正しい形式の UTF-8 でなければなりません。

STRPOS などの位置値を返す関数は、INT64 としてそれらの位置をエンコードします。値 1 は最初の文字(またはバイト)を表し、2 は 2 番目を表します(以下同様)。値 0 は、無効なインデックスであることを示します。STRING 型を扱っている場合、返される位置は文字の位置です。

すべての文字列比較は、Unicode の標準的な同値に関係なく、バイト単位で行われます。

BYTE_LENGTH

BYTE_LENGTH(value)

説明

値の型が STRING または BYTES のどちらであるかにかかわらず、BYTESSTRING 値または BYTES 値の長さを返します。

戻り値の型

INT64

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

説明

STRING の長さを文字数で返します。

戻り値の型

INT64

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

説明

CHAR_LENGTH の類義語です。

戻り値の型

INT64

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

説明

拡張 ASCII コードポイントの配列(INT64ARRAY)を取り、BYTES を返します。

BYTES からコードポイントの配列への変換については、TO_CODE_POINTS をご覧ください。

戻り値の型

BYTES

以下は、CODE_POINTS_TO_BYTES の基本的な使用方法の一例です。

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes    |
+----------+
| QWJDZA== |
+----------+

次の例では、ROT13 アルゴリズム(アルファベットの文字列をすべて 13 ずつずれた別の文字に変換する手法)を使用して文字列をエンコードします。

SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' and b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' and b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
  ) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string   |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

説明

Unicode コードポイントの配列(INT64ARRAY)を取り、STRING を返します。コードポイントが 0 の場合、STRING に文字を返しません。

文字列からコードポイントの配列への変換については、TO_CODE_POINTS をご覧ください。

戻り値の型

STRING

CODE_POINTS_TO_STRING を使用した基本的な例を次に示します。

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;

+--------+
| string |
+--------+
| a例    |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;

+--------+
| string |
+--------+
| NULL   |
+--------+

以下の例は、一連の単語の中に出現する文字の頻度を計算します。

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

説明

1 つ以上の値を 1 つに連結します。すべての値が BYTES か、STRING にキャスト可能なデータ型である必要があります。

入力引数が NULL の場合、この関数は NULL を返します。

戻り値の型

STRING または BYTES

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;

+---------------------+
| release_date        |
+---------------------+
| Summer 1923         |
+---------------------+

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

説明

2 つの STRING 値または BYTES 値を取ります。2 番目の値が 1 番目の値の接尾辞である場合は、TRUE を返します。

戻り値の型

BOOL

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

BigQuery では、文字列の形式設定として FORMAT() 関数がサポートされます。この関数は C の printf 関数に似ています。これは、ゼロ個以上の形式指定子を含む形式設定文字列から STRING を生成し、さらに、形式指定子と一致する追加引数の可変長リストを生成します。次に例を示します。

説明 ステートメント 結果
単純な整数 format("%d", 10) 10
左がブランクでパディングされた整数 format("|%10d|", 11) |           11|
左がゼロでパディングされた整数 format("+%010d+", 12) +0000000012+
カンマで区切られた整数 format("%'d", 123456789) 123,456,789
STRING format("-%s-", 'abcd efg') -abcd efg-
FLOAT64 format("%f %E", 1.1, 2.2) 1.100000 2.200000E+00
DATE format("%t", date "2015-09-01") 2015-09-01
TIMESTAMP format("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") 2015‑09‑01 19:34:56+00

FORMAT() 関数では、すべての型と値に対して完全にカスタマイズ可能な形式設定や、ロケールの影響を受ける形式設定は行えません。

特定の型に対してカスタム形式設定を行う必要がある場合、まず、FORMAT_DATE()FORMAT_TIMESTAMP() などの型固有の形式設定関数を使用して形式設定する必要があります。例:

SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

戻り値

date: January 02, 2015!

構文

FORMAT() 構文は、形式設定文字列と引数の可変長リストを取得し、STRING の結果を生成します。

FORMAT(format_string, ...)

format_string 表現には、0 個以上の形式指定子を含めることができます。各形式指定子は % 記号で始まり、1 つ以上の他の引数にマップする必要があります。ほとんどの場合、これは 1 対 1 のマッピングになりますが、* 指定子がある場合は除きます。たとえば、%.*i は length 引数と符号付き整数引数の 2 つの引数にマッピングされます。形式指定子に関連する引数の数が、これらの引数の数と異なる場合、エラーが発生します。

サポートされる形式指定子

FORMAT() 関数の形式指定子は、次のプロトタイプに従います。

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

次の表にサポートされる形式指定子が示されています。printf()との差は斜体で示されています。

指定子 説明
d または i 10 進の整数 392 INT64
o 8 進数 610
INT64*
x 16 進の整数 7fa
INT64*
X 16 進の整数(大文字) 7FA
INT64*
f 有限値の場合は [-](整数部).(小数部)の 10 進表記、非有限値の場合は小文字 392.650000
inf
nan
NUMERIC
FLOAT64
F 有限値の場合は [-](整数部).(小数部)の 10 進表記、非有限値の場合は大文字 392.650000
INF
NAN
NUMERIC
FLOAT64
e 科学的記数法(仮数 / 指数)(小文字) 3.926500e+02
inf
nan
NUMERIC
FLOAT64
E 科学的記数法(仮数 / 指数)(大文字) 3.926500E+02
INF
NAN
NUMERIC
FLOAT64
g 入力値の指数と指定された精度に応じて、10 進表記または科学表記のいずれか。小文字で表記します。詳細については、%g と %G の動作をご覧ください。 392.65
3.9265e+07
inf
nan

FLOAT64
G 入力値の指数と指定された精度に応じて、10 進表記または科学表記のいずれか。大文字で表記します。詳細については、%g と %G の動作をご覧ください。 392.65
3.9265E+07
INF
NAN

FLOAT64
s 文字列 sample STRING
t 値を表す出力可能な文字列を返します。多くの場合、STRING への引数のキャストと類似しています。%t と %T の動作をご覧ください。 sample
2014‑01‑01
<任意>
T 値の型と類似した型(幅が広い、または文字列であるなど)の有効な BigQuery 定数である文字列を生成します。%t と %T の動作をご覧ください。 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<任意>
% 「%%」は単一の「%」を生成します。 % なし

*負の値が使用されている場合は、指定子 %o%x%X を指定するとエラーが発生します。

オプションで、形式指定子には指定子プロトタイプの上記のサブ指定子を含めることもできます。

これらのサブ指定子は、次の仕様に従う必要があります。

フラグ
フラグ 説明
- 所定のフィールド幅内で左側に寄せます。右寄せがデフォルトです(幅のサブ指定子を参照)。
+ 正の数値であっても、結果の前にプラス記号またはマイナス記号(+ または -)を強制的に設定します。デフォルトでは、負の数にのみ - 記号が前に付けられます。
<space> 記号が書き込まれない場合、値の前に空白のスペースが挿入されます。
#
  • `%o`、`%x`、`%X` の場合、このフラグは、値がゼロ以外の場合にそれぞれ値の前に 0、0x、0X を付けることを意味します。
  • `%f`、`%F`、`%e`、`%E` の場合、このフラグは、値が非有限でない限り、小数部がない場合でも小数点を追加することを意味します。
  • `%g` と `%G` の場合、このフラグは、値が非有限でない限り、小数部がない場合でも小数点を追加し、小数点の後のゼロを削除しないことを意味します。
0 パディングが指定されている場合、スペースではなく、ゼロ(0)で数字の左側にパディングされます(幅サブ指定子を参照)。
'

適切なグルーピング文字を使用して整数を形式設定します。 例:

  • FORMAT("%'d", 12345678)12,345,678 を返します。
  • FORMAT("%'x", 12345678)bc:614e を返します。
  • FORMAT("%'o", 55555)15,4403 を返します。
  • このフラグは、10 進数、16 進数、および 8 進数の値のみが対象になります。

フラグは任意の順序で指定できます。フラグを重複して使用しても、エラーになりません。いずれかの要素タイプにフラグが該当しない場合、それらは無視されます。

説明
<number> 印刷される最小文字数です。印刷される値がこの数値よりも短い場合、結果は空白スペースを使用してパディングされます。結果のほうが大きい場合でも、値は切り詰められません。
* 幅は形式設定文字列には指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。
精度
精度 説明
.<number>
  • 整数指定子 `%d`、`%i`、`%o`、`%u`、`%x`、`%X` の場合: 精度は、書き込まれる最小の桁数を指定します。書き込まれる値がこの数値よりも短い場合、結果の値の末尾にゼロがパディングされます。結果のほうが長い場合でも、値は切り詰められません。精度を 0 にすると、値 0 に対して文字は書き込まれません。
  • 指定子 `%a`、`%A`、`%e`、`%E`、`%f`、`%F` の場合: 小数点の後に出力される桁数です。デフォルト値は 6 です。
  • 指定子 `%g` と `%G` の場合: 小数点の後の後続ゼロを削除する前の、出力される有効桁数です。デフォルト値は 6 です。
.* 精度は、形式設定文字列では指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。

%g と %G の動作

%g%G の形式指定子は、入力値の指数と指定された精度に応じて、10 進表記(%f%F 指定子など)または科学表記(%e%E 指定子など)を選択します。

p は指定された精度を表します(デフォルトは 6、指定された精度が 1 未満の場合は 1)。入力値は、最初に精度 = (p - 1) の科学的記数法に変換されます。結果の指数部 x が -4 未満または p 以上の場合、精度 = (p - 1) の科学的記数法が使用されます。それ以外の場合は、精度= (p - 1 - x) の小数表記が使用されます。

# フラグが存在しない場合は、小数点以下の後続ゼロが削除され、小数点の後に数字がない場合は小数点も削除されます。

%t と %T の動作

%t および %T 形式指定子は、すべての型に関して定義されます。精度フラグは、%s の場合と同じように機能します。は最小幅であり、STRING はそのサイズにパディングされます。精度は、表示するコンテンツの最大幅です。STRING は指定されたサイズまで切り詰められてから、幅までパディングされます。

%t 指定子は、常に読み取り可能な形式の値になるように想定されています。

%T 指定子は、常に類似する型の有効な SQL リテラルになります(より広い数値型など)。非定形の浮動小数点値の特殊なケースを除き、リテラルには CAST や型名が含まれません。

STRING は次のように形式設定されます。

%t %T
任意の型の NULL NULL NULL
INT64
123 123
NUMERIC 123.0(常に .0 が付く) NUMERIC "123.0"
FLOAT64 123.0(常に .0 が付く)
123e+10
inf
-inf
NaN
123.0(常に .0 が付く)
123e+10
CAST("inf" AS <type>)
CAST("-inf" AS <type>)
CAST("nan" AS <type>)
STRING 引用符で囲まれていない文字列値 引用符で囲まれた文字列リテラル
BYTES 引用符で囲まれないエスケープ バイト
例: abc\x01\x02
引用符付きバイトリテラル
例: b"abc\x01\x02"
DATE 2011-02-03 DATE "2011-02-03"
TIMESTAMP 2011-02-03 04:05:06+00 TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY [value, value, ...]
ここで、値は %t で形式設定される
[value, value, ...]
ここで、値は %T で形式設定される
STRUCT (value, value, ...)
ここで、フィールドは %t で形式設定される
(value, value, ...)
ここで、フィールドは %T で形式設定される

特別なケース:
ゼロ個のフィールド: STRUCT()
1 つのフィールド: STRUCT(value)

エラー条件

形式指定子が無効な場合や該当する引数型と互換性がない場合、あるいは間違った数や引数が指定されている場合、エラーが生成されます。たとえば、次の <format_string> 式は無効です。

FORMAT('%s', 1)
FORMAT('%')

NULL 引数の処理

NULL 形式文字列は、NULL の出力 STRING になります。この場合、その他の引数は無視されます。

NULL 引数がある場合、通常、関数によって NULL 値が生成されます。たとえば、FORMAT('%i', NULL_expression) は出力として NULL STRING を生成します。

ただし、いくつかの例外が存在します。形式設定子が %t または %T である(これらはどちらも実際上 CAST とリテラル値セマンティックと一致する STRING を生成する)場合は、NULL 値によって結果の STRING 内に 'NULL'(引用符なし)が生成されます。たとえば、次の関数の場合です。

FORMAT('00-%t-00', NULL_expression);

戻り値

00-NULL-00

その他のセマンティック ルール

FLOAT64 の値は、+/-inf または NaN です。引数にこれらのいずれかの値が含まれている場合、形式指定子 %f%F%e%E%g%G%t の結果は、状況に応じて inf-inf または nan(または同じ文字の大文字)になります。これは、BigQuery がこれらの値を STRING にキャストする方法と一致します。%T の場合、BigQuery は文字列リテラル表現なしの FLOAT64 値に対して、引用符付きの文字列を返します。

FROM_BASE32

FROM_BASE32(string_expr)

説明

base32 でエンコードされた入力 string_exprBYTES 形式に変換します。BYTES を base32 でエンコードされた STRING に変換するには、TO_BASE32 を使用します。

戻り値の型

BYTES

SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;

-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/  |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

説明

base64 でエンコードされた入力 string_exprBYTES 形式に変換します。BYTES を base64 でエンコードされた STRING に変換するには、TO_BASE64 を使用します。

戻り値の型

BYTES

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w==  |
+-----------+

FROM_HEX

FROM_HEX(string)

説明

16 進数でエンコードされた STRINGBYTES 形式に変換します。入力 STRING(0..9, A..F, a..f) の範囲外の文字が含まれている場合はエラーを返します。文字の大文字と小文字は区別されません。入力 STRING の文字数が奇数の場合は、入力の先頭に 0 が追加されているものとして処理されます。BYTES を 16 進数でエンコードされた STRING に変換するには、TO_HEX を使用します。

戻り値の型

BYTES

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

LENGTH

LENGTH(value)

説明

STRING 値または BYTES 値の長さを返します。戻り値は STRING 引数の場合は文字数、BYTES 引数の場合はバイト数で示されます。

戻り値の型

INT64


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

説明

先頭に pattern が追加された original_value で構成される STRING 値または BYTES 値を返します。return_length は、戻り値の長さを指定する INT64 です。original_valueBYTES 型の場合、return_length はバイト数です。original_valueSTRING 型の場合、return_length は文字数です。

pattern のデフォルト値はスペースです。

original_valuepattern は同じデータ型にする必要があります。

return_lengthoriginal_value 以下である場合、この関数は original_value の値を return_length の値まで切り詰めて返します。たとえば、LPAD("hello world", 7);"hello w" を返します。

original_valuereturn_length、または patternNULL の場合、この関数は NULL を返します。

次の場合、この関数はエラーを返します。

  • return_length が負の値である
  • pattern が空白である

戻り値の型

STRING または BYTES

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | LPAD     |
|------|-----|----------|
| abc  | 5   | "  abc"  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "  例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | LPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "defdeabc"   |
| abc  | 5   | -       | "--abc"      |
| 例子  | 5   | 中文    | "中文中例子"   |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | LPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"  abc"         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | LPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"defdeabc"             |
| b"abc"          | 5   | b"-"    | b"--abc"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+

LOWER

LOWER(value)

説明

STRING 引数の場合は、元の文字列をすべて小文字の英字で返します。小文字と大文字の間のマッピングは、言語固有のマッピングを考慮せずに、Unicode 文字データベースに従って行われます。

BYTES 引数の場合、引数は ASCII テキストとして処理され、127 を超えるすべてのバイトはそのまま残ります。

戻り値の型

STRING または BYTES


WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

説明

TRIM と同じですが、先頭からのみ文字を削除します。

戻り値の型

STRING または BYTES

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+
WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+
WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)
SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

NORMALIZE

NORMALIZE(value[, normalization_mode])

説明

文字列値を取り、正規化された文字列として返します。

正規化は、2 つの文字列を同等にするために使用されます。画面上で同じように表示される 2 つの文字列が、実際には異なる Unicode コードポイントを持つような状況で、正規化がよく使用されます。

NORMALIZE は、4 つのオプション正規化モードをサポートしています。

名前 説明
NFC 正規化形式 C 標準的な同値によって文字を分解し、再構成する。
NFKC 正規化形式 KC 互換性によって文字を分解し、標準的な同値によって再構成する。
NFD 正規化形式 D 標準的な同値によって文字を分解し、複数の結合文字を特定の順序で並べる。
NFKD 正規化形式 KD 互換性によって文字を分解し、複数の結合文字を特定の順序で並べる。

デフォルトの正規化モードは NFC です。

戻り値の型

STRING

SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;

+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true       |
+---+---+------------+

次の例では、複数のスペース文字を正規化しています。

WITH EquivalentNames AS (
  SELECT name
  FROM UNNEST([
      'Jane\u2004Doe',
      'John\u2004Smith',
      'Jane\u2005Doe',
      'Jane\u2006Doe',
      'John Smith']) AS name
)
SELECT
  NORMALIZE(name, NFKC) AS normalized_name,
  COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;

+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith      | 2          |
| Jane Doe        | 3          |
+-----------------+------------+

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])

説明

STRING value を取り、NORMALIZE と同じ処理を行います。さらに、大文字と小文字を区別しないオペレーション向けにケースフォールディングを行います。

NORMALIZE_AND_CASEFOLD は、4 つのオプション正規化モードをサポートしています。

名前 説明
NFC 正規化形式 C 標準的な同値によって文字を分解し、再構成する。
NFKC 正規化形式 KC 互換性によって文字を分解し、標準的な同値によって再構成する。
NFD 正規化形式 D 標準的な同値によって文字を分解し、複数の結合文字を特定の順序で並べる。
NFKD 正規化形式 KD 互換性によって文字を分解し、複数の結合文字を特定の順序で並べる。

デフォルトの正規化モードは NFC です。

戻り値の型

STRING

WITH Strings AS (
  SELECT '\u2168' AS a, 'IX' AS b UNION ALL
  SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
  NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;

+---+----+-------+-------+------+------+
| a | b  | nfd   | nfc   | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å  | true  | true  | true | true |
+---+----+-------+-------+------+------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

説明

value が正規表現 regexp に対して部分一致である場合は、TRUE を返します。

regexp の引数が無効な場合は、関数はエラーを返します。

完全一致を検索するには、^(テキストの先頭)と $(テキストの末尾)を使用します。正規表現演算子が優先されるため、^$ 間のすべてのものを括弧で囲むことをおすすめします。

戻り値の型

BOOL

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp)

説明

正規表現 regexp と一致する value 内の最初の部分文字列を返します。一致がない場合、NULL を返します。

正規表現がキャプチャ グループを含む場合、この関数はそのキャプチャ グループによって照合された部分文字列を返します。式にキャプチャ グループが含まれていない場合、この関数によって一致しているすべての部分文字列が返されます。

次の場合にエラーを返します。

  • 正規表現が無効の場合。
  • 正規表現に複数のキャプチャ グループがある場合。

戻り値の型

STRING または BYTES

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+
WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

説明

正規表現 regexp と一致する value のすべての部分文字列の配列を返します。

REGEXP_EXTRACT_ALL 関数は、重複しない一致のみを返します。たとえば、この関数を使用して banana から ana を抽出した場合、返される部分文字列は 2 つではなく、1 つのみです。

戻り値の型

STRING または BYTESARRAY

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

説明

正規表現 regexp と一致する value のすべての部分文字列を replacement に置き換えた STRING を返します。

replacement 引数内でバックスラッシュでエスケープされた数字(\1~\9)を使用して、対応する括弧で囲まれたグループと一致するテキストを regexp パターン内に挿入できます。一致するテキスト全体を参照するには、\0 を使用します。

REGEXP_REPLACE 関数は、重複しない一致のみを置き換えます。たとえば、banana に含まれる ana を置き換える場合、置き換えられるのは 2 つではなく、1 つのみです。

regexp の引数が有効な正規表現でない場合、この関数はエラーを返します。

戻り値の型

STRING または BYTES

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

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

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

説明

original_value 内に出現するすべての from_valueto_value に置き換えますfrom_value が空の場合、置き換えは行われません。

戻り値の型

STRING または BYTES

WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

説明

original_value の繰り返しで構成される STRING 値または BYTES 値を返します。repetitions パラメータで、original_value の繰り返しの数を指定します。original_value または repetitions のいずれかが NULL の場合は、NULL を返します。

repetitions の値が負の数の場合、この関数はエラーを返します。

戻り値の型

STRING または BYTES

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);

+------+------+-----------+
| t    | n    | REPEAT    |
|------|------|-----------|
| abc  | 3    | abcabcabc |
| 例子 | 2    | 例子例子  |
| abc  | NULL | NULL      |
| NULL | 3    | NULL      |
+------+------+-----------+

REVERSE

REVERSE(value)

説明

入力の STRING または BYTES を逆順で返します。

戻り値の型

STRING または BYTES

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

説明

pattern が追加された original_value で構成される STRING 値または BYTES 値を返します。return_length パラメータは、戻り値の長さを指定する INT64 です。original_valueBYTES の場合、return_length はバイト数です。original_valueSTRING の場合、return_length は文字数です。

pattern のデフォルト値はスペースです。

original_valuepattern は同じデータ型にする必要があります。

return_lengthoriginal_value 以下である場合、この関数は original_value の値を return_length の値まで切り詰めて返します。たとえば、RPAD("hello world", 7);"hello w" を返します。

original_valuereturn_length、または patternNULL の場合、この関数は NULL を返します。

次の場合、この関数はエラーを返します。

  • return_length が負の値である
  • pattern が空白である

戻り値の型

STRING または BYTES

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | RPAD     |
|------|-----|----------|
| abc  | 5   | "abc  "  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "例子  " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | RPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "abcdefde"   |
| abc  | 5   | -       | "abc--"      |
| 例子  | 5   | 中文     | "例子中文中"  |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | RPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"abc  "         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | RPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"abcdefde"             |
| b"abc"          | 5   | b"-"    | b"abc--"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+

RTRIM

RTRIM(value1[, value2])

説明

TRIM と同一ですが、文字を末尾からのみ削除します。

戻り値の型

STRING または BYTES

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+
WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

説明

BYTES のシーケンスを STRING に変換します。無効な UTF-8 文字列は Unicode 置換文字 U+FFFD に置き換えられます。

戻り値の型

STRING

次のステートメントは、Unicode 置換文字 � を返します。

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

説明

delimiter 引数を使用して value を分割します。

STRING の場合、デフォルトの区切り文字はカンマ(,)です。

BYTES の場合は、区切り文字を指定する必要があります。

空の区切り文字で分割すると、STRING 値の場合は UTF-8 文字の配列が生成され、BYTES 値の場合は BYTES の配列が生成されます。

空の STRING を分割すると、1 つの空の STRING を持つ ARRAY が返されます。

戻り値の型

STRING 型の ARRAY、または BYTES 型の ARRAY

WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

説明

2 つの STRING 値または BYTES 値を取ります。2 番目の値が 1 番目の値の接頭辞である場合は、TRUE を返します。

戻り値の型

BOOL

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(string, substring)

説明

string 内で最初に substring が出現する位置のインデックス(開始値 1)を返します。substring が見つからない場合は 0 を返します。

戻り値の型

INT64

WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

説明

指定された STRING 値または BYTES 値の部分文字列を返します。position 引数は、部分文字列の開始位置を指定する整数であり、position = 1 は最初の文字またはバイトを指します。length 引数は、STRING 引数の場合は最大文字数、BYTES 引数の場合はバイト数です。

position が負の値である場合、value の最後からカウントします(-1 は最後の文字を示す)。

positionSTRING の左端の外の位置にある場合(position = 0 または position < -LENGTH(value))、position = 1 から開始します。lengthvalue の長さを超える場合、この関数は length 文字より少ない値を返します。

length が 0 より小さい場合、関数はエラーを返します。

戻り値の型

STRING または BYTES

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+
WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+
WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE32

TO_BASE32(bytes_expr)

説明

BYTES のシーケンスを base32 でエンコードされた STRING に変換します。base32 でエンコードされた STRINGBYTES に変換するには、FROM_BASE32 を使用します。

戻り値の型

STRING

SELECT TO_BASE32(b'abcde\xFF') AS base32_string;

+------------------+
| base32_string    |
+------------------+
| MFRGGZDF74====== |
+------------------+

TO_BASE64

TO_BASE64(bytes_expr)

説明

BYTES のシーケンスを base64 でエンコードされた STRING に変換します。base64 でエンコードされた STRINGBYTES に変換するには、FROM_BASE64 を使用します。

戻り値の型

STRING

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

説明

value を取り、INT64 の配列を返します。

  • valueSTRING の場合、返される配列の各要素はコードポイントを表します。各コードポイントは、[0, 0xD7FF] から [0xE000, 0x10FFFF] の範囲になります。
  • valueBYTES の場合、配列内の各要素は拡張 ASCII 文字の値で、範囲は [0, 255] です。

コードポイントの配列を STRING または BYTES に変換するには、CODE_POINTS_TO_STRING または CODE_POINTS_TO_BYTES をご覧ください。

戻り値の型

ARRAY/INT64

次の例では、文字配列内の各要素についてコードポイントを取得します。

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

次の例では、BYTES の整数表現を対応する ASCII 文字値に変換しています。

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

次の例では、BYTES の結果と STRING の結果の違いを示しています。

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

文字 Ā が 2 バイトの Unicode シーケンスで表されることにご注意ください。その結果、BYTES バージョンの TO_CODE_POINTS は 2 つの要素からなる配列を返し、STRING バージョンは 1 つの要素からなる配列を返します。

TO_HEX

TO_HEX(bytes)

説明

BYTES のシーケンスを 16 進数の STRING に変換します。STRING 内の各バイトを範囲 (0..9, a..f) 内の 2 つの 16 進数文字に変換します。16 進数でエンコードされた STRINGBYTES に変換するには、FROM_HEX を使用します。

戻り値の型

STRING

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

説明

value2 に一致するすべての先頭と末尾の文字を削除します。value2 が指定されていない場合は、すべての先頭と末尾の(Unicode 標準で定義されている)空白文字が削除されます。最初の引数が BYTES 型の場合、2 番目の引数は必須です。

value2 が複数の文字またはバイトを含む場合、この関数は value2 に含まれている文字が先頭または末尾の文字またはバイトに存在した場合にこれらをすべて削除します。

戻り値の型

STRING または BYTES

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+
WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+
WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

説明

STRING 引数の場合は、元の文字列をすべて大文字の英字で返します。大文字と小文字の間のマッピングは、言語固有のマッピングを考慮せずに Unicode 文字データベースに従って行われます。

BYTES 引数の場合、引数は ASCII テキストとして処理され、127 を超えるすべてのバイトはそのまま残ります。

戻り値の型

STRING または BYTES

WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

JSON 関数

BigQuery は JSON 形式の文字列に格納されたデータを取得する関数と、データを JSON 形式の文字列に変換する関数をサポートしています。

JSON_EXTRACT または JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_format) は、JSON 値を STRING として返します。

JSON_EXTRACT_SCALAR(json_string_expr, json_path_format) は、スカラー JSON 値を STRING として返します。

説明

JSON 値または JSON スカラー値を文字列として抽出します。

  • json_string_expr: JSON 形式の文字列。例:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。json_path_format が JSON null を返す場合、これは SQL NULL に変換されます。

JSON キーが無効な JSONPath 文字を使用した場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

戻り値の型

STRING

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

JSON キーが無効な JSONPath 文字を使用した場合は、単一引用符や括弧 [' '] を使用して、それらの文字をエスケープできます。次に例を示します。

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_format])

説明

JSON 形式の文字列から配列を抽出します。

  • json_string_expr: JSON 形式の文字列。例:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。この省略可能なパラメータが指定されていない場合は、JSONPath $ 記号が適用され、JSON 形式の文字列全体が分析されます。

JSON キーが無効な JSONPath 文字を使用した場合は、単一引用符や括弧を使用して、それらの文字をエスケープできます。

戻り値の型

ARRAY<STRING>

これにより、JSON 形式の文字列内の項目が文字列配列に抽出されます。

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array

+----------------+
| string_array   |
+----------------+
| ['1','2','3']  |
+----------------+

文字列配列が抽出され、整数配列に変換されます。

SELECT ARRAY(
  SELECT CAST(integer_element as INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array

+---------------+
| integer_array |
+---------------+
| [1,2,3]       |
+---------------+

JSON 形式文字列の文字列の値が配列に抽出されます。

-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array

+--------------------------------------+
| string_array                         |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"']  |
+--------------------------------------+

-- Strip the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array

+---------------------------------+
|  string_array                   |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+

これにより、fruit 内の項目のみが配列に抽出されます。

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
  '$.fruit'
) as string_array

+----------------------------------------------------------------------+
| string_array                                                         |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+

次のものは同等です。

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array

JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array

-- The queries above produce this result:
+---------------------------------------+
|  string_array                         |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+

JSON キーが無効な JSONPath 文字を使用した場合は、単一引用符や括弧 [' '] を使用して、それらの文字をエスケープできます。次に例を示します。

SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

次の例は、無効なリクエストと空の配列の処理方法を示しています。

  • JSONPath が無効な場合は、エラーがスローされます。
  • JSON 形式の文字列が無効の場合、出力は NULL になります。
  • JSON 形式の文字列に空の配列を含めることは可能です。
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result

-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY または JSON_VALUE

JSON_QUERY(json_string_expr, json_path_format) は、JSON 値を STRING として返します。

JSON_VALUE(json_string_expr, json_path_format) は、スカラー JSON 値を STRING として返します。

説明

JSON 値または JSON スカラー値を文字列として抽出します。

  • json_string_expr: JSON 形式の文字列。例:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。json_path_format が JSON null を返す場合、これは SQL NULL に変換されます。

JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。例:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;

+-------+
| hello |
+-------+
| world |
+-------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

説明

value の JSON 形式の文字列表現を返します。この関数では、省略可能な pretty_print パラメータを使用できます。pretty_print がある場合、戻り値は読みやすいように書式設定されています。

入力データ型 戻り値
任意の型の NULL null
BOOL true または false
INT64

value が [-253, 253] の範囲内にある場合は、CAST(value AS STRING) と同じです。この範囲は IEEE 754 倍精度浮動小数点数としてロスレスに表現できる整数の範囲です。この範囲外となる値は、引用符付きの文字列で表されます。例:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 は 253 より大きいため、引用符付きの文字列として表されます。

NUMERIC

value が [-253, 253] の範囲内にあり、小数部が存在しない場合は、CAST(value AS STRING) と同じです。この範囲外となる値は、引用符付きの文字列で表されます。例:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-infNaN は、それぞれ Infinity-InfinityNaN として表されます。

それ以外は、CAST(value AS STRING) と同じです。

STRING 引用符付きの文字列値。JSON 標準に従ってエスケープされます。具体的には "\、および U+0000 から U+001F までの制御文字がエスケープされます。
BYTES

引用符付きの RFC 4648 の base64 エスケープ値。例:

"R29vZ2xl" はバイト b"Google" の base64 表現です。

DATE

引用符付きの日付。例:

"2017-03-06"
TIMESTAMP

引用符付きの ISO 8601 の日時。T は日付と時刻を区切り、Zulu/UTC はタイムゾーンを表します。例:

"2017-03-06T12:34:56.789012Z"
DATETIME

引用符付きの ISO 8601 の日時。T は日付と時刻を区切ります。例:

"2017-03-06T12:34:56.789012"
TIME

引用符付きの ISO 8601 の時刻。例:

"12:34:56.789012"
ARRAY

[elem1,elem2,...]。各 elem は要素タイプに従って書式設定されます。

書式設定の例:

[
  elem1,
  elem2,
  ...
]

各 elem は、要素の種類に応じて書式設定されます。空の配列は [] と表されます。

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

field_value は型に従って書式設定されます。

書式設定の例:

{
  "field_name1": field_value1,
  "field_name2": field_value2,
  ...
}

field_value は型に従って書式設定されます。field_value が、空でない ARRAY または STRUCT である場合、要素は適切なレベルにインデントされます。空の STRUCT は {} と表されます。

名前が重複するフィールドがある場合、JSON が解析不能になる可能性があります。匿名のフィールドは "" と表されます。

無効な UTF-8 フィールド名がある場合、JSON が解析不能になる可能性があります。文字列値は、JSON 標準に従ってエスケープされます。具体的には "\、および U+0000 から U+001F までの制御文字がエスケープされます。

戻り値の型

値の JSON 文字列表現。

テーブル内の行を JSON に変換します。

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

上のクエリは、次の結果を生成します。

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

テーブル内の行を、書式設定付きの JSON に変換します。

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

上のクエリは、次の結果を生成します。

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

JSONPath 形式

ほとんどの JSON 関数は json_string_expr パラメータと json_path_format パラメータで渡されます。json_string_expr パラメータは JSON 形式の文字列で渡され、json_path_format パラメータは JSON 形式の文字列から取得する値を指定します。

json_string_expr パラメータは次のような形式の JSON 文字列にする必要があります。

{"class" : {"students" : [{"name" : "Jane"}]}}

JSONPath 形式を使用して json_path_format パラメータを作成します。このパラメータの書式は $ 記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。子の値は、ドットを使用して識別できます。JSON オブジェクトが配列の場合は、角括弧を使用して配列のインデックスを指定できます。キーに $、ドット、角括弧が含まれている場合、エスケープする方法については、各 JSON 関数をご覧ください。

JSONPath 説明 上記の json_string_expr を使用した結果
$ ルートのオブジェクトまたは要素 "$" {"class":{"students":[{"name":"Jane"}]}}
. 子の演算子 "$.class.students" [{"name":"Jane"}]
[] 添字演算子 "$.class.students[0]" {"name":"Jane"}

JSON 関数は、json_path_format パラメータが json_string_expr 内の値と一致しない場合は NULL を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合は、NULL を返します。

JSONPath が無効な場合、JSON 関数はエラーとなります。

配列関数

ARRAY

ARRAY(subquery)

説明

ARRAY 関数は、サブクエリによって返される行ごとに 1 つの要素を含む ARRAY を返します。

subquery によって SQL テーブルが生成される場合、そのテーブルの列数は 1 つである必要があります。出力 ARRAY の各要素は、テーブルの行に 1 つだけある列の値となります。

subquery によって値テーブルが生成される場合、出力 ARRAY の各要素は、値テーブルの対応する行全体となります。

制約

  • サブクエリは順序付けされていないため、出力 ARRAY の要素は、サブクエリのソーステーブルの順序を保証するものではありません。ただし、サブクエリに ORDER BY 句が含まれている場合、ARRAY 関数はその句を使用する ARRAY を返します。
  • サブクエリが複数の列を返す場合、ARRAY 関数はエラーを返します。
  • サブクエリが ARRAY 型の列または ARRAY 型の行を返す場合、ARRAY 関数は、「ARRAY 型の要素を含む ARRAY は BigQuery でサポートされていない」というエラーを返します。
  • サブクエリがゼロ行を返す場合、ARRAY 関数は空の ARRAY を返します。NULL ARRAY は返しません。

戻り値の型

ARRAY

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

複数の列を含むサブクエリから ARRAY を構成するには、SELECT AS STRUCT を使用するようにサブクエリを変更します。これで ARRAY 関数は STRUCTARRAY を返します。ARRAY では、サブクエリの各行に 1 つの STRUCT が含まれます。こうした STRUCT には、その行の各列のフィールドが含まれます。

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

同様に、1 つまたは複数の ARRAY を含むサブクエリから ARRAY を構成するには、SELECT AS STRUCT を使用するサブクエリを変更します。

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

説明

同じ要素型の 1 つまたは複数の配列を 1 つの配列に連結します。

戻り値の型

ARRAY

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

説明

配列のサイズを返します。空の配列の場合は 0 を返します。array_expressionNULL である場合、NULL を返します。

戻り値の型

INT64

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [coffee, NULL, milk]            | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

説明

array_expression 内の要素の連結を STRING として返します。array_expression の値は STRING または BYTES データ型のいずれかの配列にできます。

null_text パラメータが使用されている場合、この関数によって、配列内のすべての NULL 値が null_text の値に置き換えられます。

null_text パラメータが使用されていない場合、この関数は NULL 値とその前にある区切り文字を無視します。

WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+
WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

説明

値の配列を返します。start_expressionend_expression のパラメータは、配列の包括的な開始点と終了点を示します。

GENERATE_ARRAY 関数は、入力として次のデータ型を受け入れます。

  • INT64
  • NUMERIC
  • FLOAT64

step_expression パラメータは、配列の値を生成するために使用される増分を示します。このパラメータのデフォルト値は 1 です。

この関数は、step_expression が 0 に設定されているか入力が NaN の場合、エラーを返します。

引数が NULL の場合、関数は NULL 配列を返します。

戻りデータの型

ARRAY

以下の場合、整数の配列を返します。デフォルトのステップは 1 です。

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

以下の場合、ユーザーが指定したステップサイズを使用して配列を返します。

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

以下の場合、負の値を使用して配列を返します。ステップサイズは -3 です。

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

以下の場合、start_expressionend_expression に同じ値を使用して配列を返します。

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

以下の場合、start_expressionend_expression よりも大きく、step_expression が正の値であるため、空の配列を返します。

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

以下の場合、end_expressionNULL であるため、NULL の配列を返します。

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

以下は、複数の配列を返します。

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

説明

日付の配列を返します。start_dateend_date のパラメータは、配列の包括的な開始点と終了点を示します。

GENERATE_DATE_ARRAY 関数は、入力として次のデータ型を受け入れます。

  • start_date は DATE にする必要があります。
  • end_date は DATE にする必要があります。
  • INT64_expr には INT64 にする必要があります。
  • date_part は、DAY、WEEK、MONTH、QUARTER または YEAR にする必要があります。

INT64_expr パラメータは、日付の生成に使用される増分を示します。このパラメータのデフォルト値は 1 日です。

この関数は、INT64_expr が 0 に設定されている場合、エラーを返します。

戻りデータの型

0 個以上の DATE 値が含まれている配列。

以下の場合、日付の配列を返します。デフォルトのステップは 1 です。

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

以下の場合、ユーザーが指定したステップサイズを使用して配列を返します。

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

以下の場合、負の値を使用して配列を返します。ステップサイズは -3 です。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

以下の場合、start_dateend_date に同じ値を使用して配列を返します。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

以下の場合、start_dateend_date よりも大きく、step が正の値であるため、空の配列を返します。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

以下の場合、入力値の 1 つが NULL であるため、NULL の配列を返します。

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

以下の場合、date_part の間隔に MONTH を使用して、日付の配列を返します。

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

以下の場合、非定数の日付を使用して配列を生成します。

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

説明

指定の間隔で区切られた TIMESTAMPSARRAY を返します。start_timestampend_timestamp のパラメータは、ARRAY の包括的な上限と下限を示します。

GENERATE_TIMESTAMP_ARRAY 関数は、入力として次のデータ型を受け入れます。

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64
  • date_part に使用できる値は、MICROSECONDMILLISECONDSECONDMINUTEHOURDAY です。

step_expression パラメータは、タイムスタンプの生成に使用される増分を示します。

戻りデータの型

0 個以上の TIMESTAMP 値が含まれている ARRAY

次の例では、間隔 1 日の TIMESTAMPARRAY を返します。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                INTERVAL 1 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+

次の例では、間隔 1 秒の TIMESTAMPARRAY を返します。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

次の例では、間隔が負である TIMESTAMPSARRAY を返します。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

次の例では、start_timestampend_timestamp の値が同じであるため、要素が 1 つの ARRAY を返します。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

次の例では、start_timestampend_timestamp の後であるため、空の ARRAY を返します。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

次の例では、入力の 1 つが NULL であるため、null の ARRAY を返します。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

次の例では、start_timestampend_timestamp の値を含む列から TIMESTAMPARRAY を生成します。

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+

OFFSET と ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

説明

位置によって ARRAY 要素にアクセスし、その要素を返します。OFFSET は番号付けがゼロから始まることを意味し、ORDINAL は番号付けが 1 から始まることを意味します。

指定した配列は、0 ベースまたは 1 ベースのいずれかとして解釈できます。配列要素にアクセスする場合、配列位置の前に OFFSET または ORDINAL をそれぞれ入れる必要があります。デフォルトの動作はありません。

インデックスが範囲外の場合、OFFSETORDINAL はどちらもエラーを生成します。

戻り値の型

ARRAY 内の要素によって異なります。

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

説明

要素のある ARRAY 入力を逆順で返します。

戻り値の型

ARRAY

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

SAFE_OFFSET と SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

説明

OFFSETORDINAL と同じですが、インデックスが範囲外の場合に NULL を返す点が異なります。

戻り値の型

ARRAY 内の要素によって異なります。

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+

DATE 関数

BigQuery は、以下の DATE 関数をサポートします。

CURRENT_DATE

CURRENT_DATE([time_zone])

説明

指定した、またはデフォルトのタイムゾーンの今現在の日付を返します。

この関数では、省略可能な time_zone パラメータを使用できます。このパラメータは、使用するタイムゾーンを示す文字列です。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

time_zone パラメータが NULL と評価されると、この関数は NULL を返します。

戻りデータの型

DATE

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

説明

指定した日付の一部に対応する値が返されます。part は、以下のいずれかにする必要があります。

  • DAYOFWEEK: 週の最初の曜日が日曜日である範囲 [1,7] 内の値を返します。
  • DAY
  • DAYOFYEAR
  • WEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。
  • WEEK(<WEEKDAY>): 範囲 [0, 53] 内の日付の週番号を返します。週は WEEKDAY から始まります。最初の WEEKDAY より前の日付は、第 0 週になります。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: ISO 8601 形式date_expression の週番号を返します。ISOWEEK は月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初の ISOWEEK は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。
  • MONTH
  • QUARTER: 範囲 [1,4] 内の値を返します。
  • YEAR
  • ISOYEAR: ISO 8601 形式の週番号が付いた年を返します。これは date_expression が属している週の木曜日が含まれるグレゴリオ暦年です。

戻りデータの型

INT64

次の例では、EXTRACTDAY の日付部分に対応する値を返します。

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

次の例では、EXTRACT は年末付近の日付の列から、さまざまな日付部分に対応する値を返します。

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

以下の例では、date_expression は日曜日になります。EXTRACT は、日曜日から始まる週を使用して最初の列を計算し、月曜日から始まる週を使用して 2 番目の列を計算します。

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])
3. DATE(datetime_expression)

説明

  1. 年、月、日を表す DATE を INT64 値から構築します。
  2. TIMESTAMP 式から DATE を抽出します。タイムゾーンを指定するには省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。
  3. DATETIME 式から DATE を抽出します。

戻りデータの型

DATE

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(DATETIME "2016-12-25 23:59:59") as date_dt,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+------------+
| date_ymd   | date_dt    | date_tstz  |
+------------+------------+------------+
| 2016-12-25 | 2016-12-25 | 2016-12-24 |
+------------+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

説明

指定した時間間隔を DATE に追加します。

DATE_ADD は、次の date_part 値をサポートします。

  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATE

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

説明

DATE から指定した期間を減算します。

DATE_SUB は、次の date_part 値をサポートします。

  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATE

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

説明

2 つの DATE オブジェクト(date_expression_adate_expression_b)間に存在する、全体が指定された date_part の間隔を示す数を返します。最初の DATE が 2 番目のオブジェクトよりも前に存在する場合、出力は負になります。

DATE_DIFF は、次の date_part 値をサポートします。

  • DAY
  • WEEK: この日付パーツは日曜日から始まります。
  • WEEK(<WEEKDAY>): この日付パートは WEEKDAY から始まります。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: ISO 8601 の週の境界を使用します。ISO 週は月曜日から始まります。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 の週番号が付いた年の境界を使用します。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

戻りデータの型

INT64

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

上記の例は、2 つの日付に対する DATE_DIFF の結果を連続して示しています。 DATE_DIFF は、これらの日付の範囲内で日付パーツの境界の数をカウントするため、日付パーツ WEEK が指定された DATE_DIFF は 1 を返します。各 WEEK は日曜日から始まるため、2017 年 10 月 14 日土曜日と 2017 年 10 月 15 日日曜日の間には日付パーツの境界が 1 つあります。

次の例は、異なる年の 2 つの日付に対する DATE_DIFF の結果を示しています。日付パーツ YEAR が指定された DATE_DIFF は、2 つの日付間のグレゴリオ暦年の境界の数をカウントするため、3 を返します。2 番目の日付は ISO 基準の 2015 年に属しているため、日付パーツ ISOYEAR が指定された DATE_DIFF は 2 を返します。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

次の例は、2 つの日付に対する DATE_DIFF の結果を連続して示しています。最初の日付は月曜日で、2 番目の日付は日曜日になります。日付部分 WEEK が指定された DATE_DIFF は 0 を返します。これは、この日付部分が日曜日から始まる週を使用するためです。日付パーツ WEEK(MONDAY) が指定された DATE_DIFF は 1 を返します。ISO 週は月曜日から始まるので、日付パーツ ISOWEEK が指定された DATE_DIFF も 1 を返します。

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

説明

指定した粒度まで日付を切り詰めます。

DATE_TRUNC は、date_part の次の値をサポートします。

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): date_expression を、WEEKDAY で週が始まる先行する週の境界に切り詰めます。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: date_expression を、先行する ISO 8601 の週の境界に切り詰めます。ISOWEEK は月曜日に始まります。各 ISO 年の最初の ISOWEEK には、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前の date_expression を前の月曜日に切り詰めます。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: date_expressionISO 8601 形式の週番号が付けられた先行年の境界に切り詰めます。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

戻りデータの型

DATE

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

次の例では、元の日付は日曜日になります。date_partWEEK(MONDAY) であるため、DATE_TRUNC は前の月曜日の DATE を返します。

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

次の例では、元の date_expression はグレゴリオ暦 2015 年です。ただし、ISOYEAR 日付パーツが指定された DATE_TRUNC は、date_expression をグレゴリオ暦年ではなく、ISO 年の最初に切り詰めます。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。そのため、date_expression 2015-06-15 より前の ISO 年の境界は 2014-12-29 になります。

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(int64_expression)

説明

int64_expression は、1970-01-01 からの日数です。

戻りデータの型

DATE

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

説明

指定された format_string に従って date_expr をフォーマットします。

この関数でサポートされる形式設定要素のリストについては、DATE でサポートされる形式設定要素をご覧ください。

戻りデータの型

STRING

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

説明

日付の文字列表現DATE オブジェクトに変換します。

format_string には、date_string のフォーマット方法を定義する形式設定要素が含まれます。date_string 内の各要素については、対応する要素が format_string 内に存在することが必要です。format_string 内の各要素のロケーションは、date_string 内の各要素のロケーションと一致する必要があります。

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

形式設定文字列は、%Q%a%A%g%G%j%u%U%V%w%W を除く大部分の形式設定要素を完全にサポートしています。

PARSE_DATE を使用する場合、次の点に注意してください。

  • 指定されていないフィールド。指定されていないフィールドはすべて、1970-01-01 から初期設定されます。
  • 大文字と小文字の区別がない名前。MondayFebruary などの名前は、大文字と小文字が区別されません。
  • 空白文字。形式設定文字列内の 1 つ以上の連続する空白文字は、日付文字列内のゼロ以上の連続する空白文字と同じです。また、日付文字列内の先頭および末尾の空白文字は、それらが形式設定文字列内にない場合でも常に許可されます。
  • 形式設定の優先度。2 つ(またはそれ以上)の形式設定要素が重複する情報を持つ場合(たとえば、%F%Y の両方が年に影響を及ぼす場合など)、一般に、最後に指定した内容によって、前に指定した内容がオーバーライドされます。

戻りデータの型

DATE

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

説明

1970-01-01 からの日数を返します。

戻りデータの型

INT64

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

DATE でサポートされる形式設定要素

特に指定されていない限り、形式設定文字列を使用する DATE 関数は次の要素をサポートします。

形式設定要素 説明
%A 完全な曜日名。
%a 省略された曜日名。
%B 完全な月の名前。
%b または %h 省略された月の名前。
%C 10 進数として表示される世紀(年を 100 で割って、整数値になるよう切り捨てる)(00~99)。
%D %m/%d/%y 形式の日付。
%d 10 進数として表示される、月内の日付(01~31)。
%e 10 進数として表示される、月内の日付(1~31)。1 桁の場合は前にスペースが入れられます。
%F %Y-%m-%d 形式の日付。
%G ISO 8601 の年。10 進数として表され、世紀の部分の数字が含まれます。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。グレゴリオ暦年と ISO 年が同一ではない可能性があるグレゴリオ暦年の境界付近では、%G と %Y で異なる結果が生成されることがあります。
%g ISO 8601 の年。10 進数として表され、世紀の部分の数字は含まれません(00~99)。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。%g と %y は、グレゴリオ暦年と ISO 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%j 10 進数として表示される、年内の日付(001~366)。
%m 10 進数として表示される月(01~12)。
%n 改行文字。
%Q 10 進数として表示される四半期(1~4)。
%t タブ文字。
%U 10 進数として表示される、年内の週番号(週の 1 日目が日曜日)(00~53)。
%u 10 進数として表示される曜日(週の 1 日目が月曜日)(1~7)。
%V 1 年の中での ISO 8601 の週番号(週の 1 日目は月曜日)。10 進数として表されます(01~53)。1 月 1 日を含む週に新しい年の 4 日以上の日数がある場合、その週が week 1 になります。そうでない場合、その週は前の年の week 53 になり、その次の週が新しい年の week 1 になります。
%W 10 進数として表示される、年内の週番号(週の 1 日目が月曜日)(00~53)。
%w 10 進数として表示される曜日(週の 1 日目が日曜日)(0~6)。
%x MM/DD/YY 形式の日付表記。
%Y 10 進数として表示される、世紀を含む年。
%y 10 進数(00-99)として表示される年。世紀は含みません。先頭に 0 を追加することもできます。%C と混在できます。%C が指定されていない場合、年 00~68 は 2000 年代、年 69~99 は 1900 年代です。
%E4Y 4 文字の年(0001 ... 9999)。%Y によって、年を完全に表現するために必要な数の文字が生成されます。

日時関数

BigQuery は、以下の DATETIME 関数をサポートします。

すべての出力は ISO 8601 に従って自動的にフォーマットされ、日付と時刻は T で区切られます。

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

説明

現在時刻を DATETIME オブジェクトとして返します。

この関数では、省略可能な timezone パラメータを使用できます。タイムゾーンの指定方法については、タイムゾーンの定義をご覧ください。

戻りデータの型

DATETIME

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19T10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression[, time_expression])
3. DATETIME(timestamp_expression [, timezone])

説明

  1. 年、月、日、時、分、秒を表す INT64 値を使用して DATETIME オブジェクトを構築します。
  2. DATE オブジェクトとオプションの TIME オブジェクトを使用して、DATETIME オブジェクトを構築します。
  3. TIMESTAMP オブジェクトを使用して DATETIME オブジェクトを構築します。タイムゾーンを指定するには省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。

戻りデータの型

DATETIME

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25T05:30:00 | 2008-12-24T21:30:00 |
+---------------------+---------------------+

EXTRACT

EXTRACT(part FROM datetime_expression)

説明

入力された datetime_expression から、指定された part に対応する値を返します。

指定できる part 値は次のとおりです。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。
  • WEEK(<WEEKDAY>) : 範囲 [0, 53] 内の datetime_expression の週番号を返します。週は WEEKDAY から始まります。年の最初の WEEKDAY より前の datetime は、第 0 週になります。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: ISO 8601 形式datetime_expression の週番号を返します。ISOWEEK は月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初の ISOWEEK は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 形式の週番号が付いた年を返します。これは date_expression が属している週の木曜日が含まれるグレゴリオ暦年です。
  • DATE
  • TIME

戻り値となる値の下位の期間は切り捨てられます。たとえば、秒数を抽出するとき、EXTRACT はミリ秒とマイクロ秒の値を切り捨てます。

戻りデータの型

INT64。ただし、以下の場合は除きます。

  • partDATE の場合は、DATE オブジェクトを返します。
  • partTIME の場合は、TIME オブジェクトを返します。

次の例では、EXTRACTHOUR 時間部分に対応する値を返します。

SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;

+------------------+
| hour             |
+------------------+
| 15               |
+------------------+

次の例では、EXTRACT は datetime の列から異なる時間部分に対応する値を返します。

WITH Datetimes AS (
  SELECT DATETIME '2005-01-03 12:34:56' AS datetime UNION ALL
  SELECT DATETIME '2007-12-31' UNION ALL
  SELECT DATETIME '2009-01-01' UNION ALL
  SELECT DATETIME '2009-12-31' UNION ALL
  SELECT DATETIME '2017-01-02' UNION ALL
  SELECT DATETIME '2017-05-26'
)
SELECT
  datetime,
  EXTRACT(ISOYEAR FROM datetime) AS isoyear,
  EXTRACT(ISOWEEK FROM datetime) AS isoweek,
  EXTRACT(YEAR FROM datetime) AS year,
  EXTRACT(WEEK FROM datetime) AS week
FROM Datetimes
ORDER BY datetime;

+---------------------+---------+---------+------+------+
| datetime            | isoyear | isoweek | year | week |
+---------------------+---------+---------+------+------+
| 2005-01-03T12:34:56 | 2005    | 1       | 2005 | 1    |
| 2007-12-31T00:00:00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01T00:00:00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31T00:00:00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02T00:00:00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26T00:00:00 | 2017    | 21      | 2017 | 21   |
+---------------------+---------+---------+------+------+

以下の例では、datetime_expression は日曜日になります。EXTRACT は、日曜日から始まる週を使用して最初の列を計算し、月曜日から始まる週を使用して 2 番目の列を計算します。

WITH table AS (SELECT DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime)
SELECT
  datetime,
  EXTRACT(WEEK(SUNDAY) FROM datetime) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM datetime) AS week_monday
FROM table;

+---------------------+-------------+---------------+
| datetime            | week_sunday | week_monday   |
+---------------------+-------------+---------------+
| 2017-11-05T00:00:00 | 45          | 44            |
+---------------------+-------------+---------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

説明

partint64_expression 単位を DATETIME オブジェクトに追加します。

DATETIME_ADD は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の DATETIME の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATETIME

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00         | 2008-12-25T15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

説明

DATETIME から partint64_expression 単位を減算します。

DATETIME_SUB は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTHQUARTERYEAR の各部分には特別な処理が必要です。結果の月の日数が元の DATETIME 日よりも少ない場合、結果の日は新しい月の最終日です。

戻りデータの型

DATETIME

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00         | 2008-12-25T15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

説明

2 つの DATETIME オブジェクト(datetime_expression_adatetime_expression_b)間に存在する、全体が指定された part の間隔を示す数を返します。最初の DATETIME が 2 番目のオブジェクトよりも前に存在する場合、出力は負になります。2 つの DATETIME オブジェクト間のマイクロ秒単位の差異が INT64 値をオーバーフローするなど、計算によって得られた型がオーバーフローする場合はエラーをスローします。

DATETIME_DIFF は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK: この日付パーツは日曜日から始まります。
  • WEEK(<WEEKDAY>): この日付パートは WEEKDAY から始まります。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: ISO 8601 の週の境界を使用します。ISO 週は月曜日から始まります。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 の週番号が付いた年の境界を使用します。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

戻りデータの型

INT64

SELECT
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07T10:20:00        | 2008-12-25T15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+
SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

上記の例は、24 時間離れている 2 つの DATETIME に対する DATETIME_DIFF の結果を示しています。DATETIME_DIFF は、これらの DATETIME の範囲内でパーツの境界の数をカウントするため、パーツ WEEK が指定された DATETIME_DIFF は 1 を返します。各 WEEK は日曜日から始まるため、2017-10-14 00:00:00 土曜日と 2017-10-15 00:00:00 日曜日の間にはパーツの境界が 1 つあります。

次の例は、異なる年の 2 つの日付に対する DATETIME_DIFF の結果を示しています。日付パーツ YEAR が指定された DATETIME_DIFF は、2 つの DATETIME 間のグレゴリオ暦年の境界の数をカウントするため、3 を返します。2 番目の DATETIME は ISO 基準の 2015 年に属しているため、日付パーツ ISOYEAR が指定された DATETIME_DIFF は 2 を返します。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。

SELECT
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', YEAR) AS year_diff,
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

次の例は、2 つの日付に対する DATETIME_DIFF の結果を連続して示しています。最初の日付は月曜日で、2 番目の日付は日曜日になります。日付パーツ WEEK が指定された DATETIME_DIFF は 0 を返します。これは、この時間パーツが日曜日で始まる週を使用するためです。日付パーツ WEEK(MONDAY) が指定された DATETIME_DIFF は 1 を返します。ISO 週は月曜日から始まるので、日付パーツ ISOWEEK が指定された DATETIME_DIFF も 1 を返します。

SELECT
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

説明

part の粒度で DATETIME オブジェクトを切り詰めます。

DATETIME_TRUNC は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): datetime_expression を、WEEKDAY で週が始まる先行する週の境界に切り詰めます。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: datetime_expression を、先行する ISO 8601 の週の境界に切り詰めます。ISOWEEK は月曜日に始まります。各 ISO 年の最初の ISOWEEK には、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前の date_expression を前の月曜日に切り詰めます。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: datetime_expressionISO 8601 形式の週番号が付けられた先行年の境界に切り詰めます。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

戻りデータの型

DATETIME

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25T15:30:00        | 2008-12-25T00:00:00    |
+----------------------------+------------------------+

次の例では、元の DATETIME は日曜日になります。partWEEK(MONDAY) であるため、