標準 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(date_expression AS STRING)
CAST(string_expression AS DATE)

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

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

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 日が開始する時刻)を基点として解釈されます。タイムスタンプから日付へのキャスティングでは、タイムスタンプはデフォルトのタイムゾーンを基点にして効率的に切り捨てられます。

強制型変換

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

リテラル強制型変換

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

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

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

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

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

パラメータ強制型変換

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

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

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

その他の変換関数

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

集計関数

集計関数は、一連の値に対して計算を実行する関数です。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 |
+-------------+----------------+-------+------+

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

ANY_VALUE

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

説明

グループ内のいずれかの行の expression を返します。どの行が選択されるかは非決定的です。入力によって行が生成されない場合、またはすべての行の 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 には任意のデータ型を指定できます。

省略可能な句

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

  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 と評価される場合に 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 を返します。

サポートされる引数の型

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

省略可能な句

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 を返します。

サポートされる引数の型

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

省略可能な句

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 を返します。

入力に 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   |
+---+-----+

統計集計関数

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 のエイリアスです。

近似集計関数

近似集計関数は、メモリ使用量と時間においてはスケーラブルですが、正確な結果ではなく、近似的な結果を生成します。詳しい背景情報については、近似集計をご覧ください。

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++ 関数

BigQuery では、HyperLogLog++ アルゴリズムを使用する次の近似集計関数がサポートされています。近似集計関数の機能については、近似集計をご覧ください。

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

説明

1 つ以上の input 値を取り、それらの値を HyperLogLog++ スケッチに集約する集計関数です。各スケッチは 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)

説明

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

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 つ以上の HyperLogLog++ sketch 入力を受け入れ、それらを 1 つの新しいスケッチに結合する集計関数です。

入力がないか、すべての入力が 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 つの HyperLogLog++ スケッチからカーディナリティの推測値を抽出するスカラー関数です。

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               |
+------------+---------+-----------------+

番号付け関数

以降のセクションで、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 による除算はオーバーフローする可能性があります。可能な結果の型については、下の表をご覧ください。

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)

説明

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

MOD

MOD(X, Y)

説明

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

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] の範囲内の DOUBLE リテラルです。

戻りデータの型

DOUBLE

次の例では、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] の範囲内の DOUBLE リテラルです。

戻りデータの型

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;

+-------------------------------------------------+
| md5                                             |
+-------------------------------------------------+
| \xb1\n\x8d\xb1d\xe0uA\x05\xb7\xa9\x9b\xe7.?\xe5 |
+-------------------------------------------------+

SHA1

SHA1(input)

説明

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

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

戻り値の型

BYTES

SELECT SHA1("Hello World") as sha1;

+-----------------------------------------------------------+
| sha1                                                      |
+-----------------------------------------------------------+
| \nMU\xa8\xd7x\xe5\x02/\xabp\x19w\xc5\xd8@\xbb\xc4\x86\xd0 |
+-----------------------------------------------------------+

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 種類のデータ型 STRING と BYTE の値を処理できます。STRING 値は、正しい形式の UTF-8 でなければなりません。

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

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

BYTE_LENGTH

BYTE_LENGTH(value)

説明

値の型が STRING または BYTES のどちらであるかに関係なく、value の長さをバイト数で返します。

戻り値の型

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 コードポイントの配列(INT64 の ARRAY)をとり、BYTES を返します。

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

戻り値の型

BYTES

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

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

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

次の例では、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;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

説明

Unicode コードポイントの配列(INT64 の ARRAY)をとり、STRING を返します。

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

戻り値の型

STRING

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

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

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

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

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 にキャスト可能なデータ型である必要があります。

戻り値の型

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 つのを受け入れます。2 番目の value が最初の value の接尾辞である場合、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.65
inf
NaN
NUMERIC
FLOAT64
F 10 進の浮動小数点数(大文字) 392.65
inf
NAN
NUMERIC
FLOAT64
e 科学的記数法(仮数 / 指数)(小文字) 3.9265e+2
inf
NaN
NUMERIC
FLOAT64
E 科学的記数法(仮数 / 指数)(大文字) 3.9265E+2
inf
NAN
NUMERIC
FLOAT64
g %e と %f のうち、短いほうの表記を使用 392.65
FLOAT64
G %E と %F のうち、短いほうの表記を使用 392.65
FLOAT64
s 文字列 sample STRING
t 値を表す出力可能な文字列を返します。多くの場合、STRING への引数のキャストに似ています。下記の %t のセクションをご覧ください。 sample
2014‑01‑01
<任意>
T 値の型と類似した型(幅が広い、または文字列であるなど)の有効な BigQuery 定数である文字列を生成します。下記の %T のセクションをご覧ください。 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<任意>
% 「%%」は単一の「%」を生成します。 % なし

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

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

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

フラグ
フラグ 説明
- 所定のフィールド幅内で左側に寄せます。右寄せがデフォルトです(幅のサブ指定子を参照)。
+ 正の数値であっても、結果の前にプラス記号またはマイナス記号(+ または -)を強制的に設定します。デフォルトでは、負の数にのみ - 記号が前に付けられます。
<space> 記号が書き込まれない場合、値の前に空白のスペースが挿入されます。
# o、x、X 指定子付きで使用されます。ゼロではない値に対しては、それぞれ 0、0x、0X を値の前に付けます。
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)
.* 精度は形式設定文字列には指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。

%t および %T の作用

%t および %T 形式指定子は、すべての型に関して定義されます。幅、精度、およびフラグは、%s に対する場合と同じように機能します。width は最小幅であり、STRING はそのサイズに達するまでパディングされます。precision は表示するコンテンツの最大幅です。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 文字列を生成します。

ただし、形式設定子が %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_expr を BYTES 形式に変換します。BYTES を Base32 でエンコードされた文字列に変換するには、TO_BASE32 を使用します。

戻り値の型

BYTES

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

+-----------+
| byte_data |
+-----------+
| abcde\xff |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

説明

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

戻り値の型

BYTES

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

+------------------+
| byte_data        |
+------------------+
| \xde\xad\xbe\xef |
+------------------+

FROM_HEX

FROM_HEX(string)

説明

16 進数でエンコードされた STRING を BYTES 形式に変換します。入力文字列に (0..9, A..F, a..f) の範囲外の文字が含まれている場合はエラーを返します。文字の大文字と小文字は区別されません。入力文字列の文字数が奇数の場合、入力の先頭に 0 が追加されているものとして処理されます。BYTES を 16 進数でエンコードされた文字列に変換するには、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;
+------------------+----------------------------------+
| hex_str          | bytes_str                        |
+------------------+----------------------------------+
| 0AF              | \x00\xaf                         |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172     | foobar                           |
+------------------+----------------------------------+

LENGTH

LENGTH(value)

説明

value の長さを返します。戻り値は 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 で構成されるを返します。return_length は、戻り値の長さを指定する INT64 です。original_value が BYTES の場合、return_length はバイト数です。original_value が STRING の場合、return_length は文字数です。

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

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

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

original_valuereturn_length、または pattern が NULL の場合は 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])

説明

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

正規化は、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 または BYTES の配列


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 の繰り返しで構成されるを返します。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 で構成されるを返します。return_length は、戻り値の長さを指定する INT64 です。original_value が BYTES の場合、return_length はバイト数です。original_value が STRING の場合、return_length は文字数です。

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

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

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

original_valuereturn_length、または pattern が NULL の場合は 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)

説明

バイトのシーケンスを文字列に変換します。無効な 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 つのを受け入れます。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])

説明

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

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

一方、position が STRING の左端の外の位置にある場合(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 でエンコードされた STRING を BYTES に変換するには、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 でエンコードされた STRING を BYTES に変換するには、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 の配列を返します。

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

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

戻り値の型

INT64 の ARRAY

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

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 進数でエンコードされた STRING を BYTES に変換するには、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       |
+----------------------------------+------------------+
| foobar                           | 666f6f626172     |
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
+----------------------------------+------------------+

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_string_literal) は、JSON 値を STRING として返します。

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

説明

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

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literal: JSONpath 形式。 これは、JSON 形式の文字列から取得する値を指定します。

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": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| 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 |
+-------+

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_string_literal パラメータを渡します。json_string_expr パラメータでは JSON 形式の文字列を渡し、json_path_string_literal パラメータにはその JSON 形式の文字列から取得する値を指定します。

json_string_expr パラメータは次のような形式の JSON 文字列にする必要があります。

{"class" : {"students" : [{"name" : "Jane"}]}}

json_path_string_literal パラメータは JSONPath 形式を使用して作成します。このパラメータの書式は $ 記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。子の値は、ドットを使用して識別できます。JSON オブジェクトが配列の場合は、角かっこを使用して配列のインデックスを指定できます。キーに $、ドット、かっこが含まれている場合にそれらの文字をエスケープする方法については、各 JSON 関数をご覧ください。

JSONPath 説明 上記の json_string_expr を使用した結果
$ ルートのオブジェクトまたは要素 "$" {"class":{"students":[{"name":"Jane"}]}}
. 子の演算子 "$.class.students" [{"name":"Jane"}]
[] 添字演算子 "$.class.students[0]" {"name":"Jane"}

json_path_string_literal パラメータが json_string_expr 内の値と一致しない場合、JSON 関数は 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 list, 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 値は、次のものです。

    MICROSECONDMILLISECOND

    SECONDMINUTEHOURDAY

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_expr 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_expr 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, date_expression, date_part)

説明

2 つの date_expression の間の date_part の境界の数を返します。最初の日付が 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)

説明

日付の format_string と文字列表記を使用して、DATE オブジェクトを返します。

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 によって、年を完全に表現するために必要な数の文字が生成されます。

DateTime 関数

BigQuery は、以下の DATETIME 関数をサポートします。

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

説明

現在時刻を DATETIME オブジェクトとして返します。

この関数では、省略可能な timezone パラメータを使用できます。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

DATETIME

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10: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-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

説明

partINT64_expr 単位を 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-25 15:30:00         | 2008-12-25 15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

説明

DATETIME から partINT64_expr 単位を差し引きます。

DATETIME_SUB は、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_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

説明

2 つの datetime_expression の間の part の境界の数を返します。最初の DATETIME が 2 番目の DATETIME よりも前にある場合、結果は正以外の値になります。計算によって得られた型がオーバーフローする場合、エラーをスローします。たとえば、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-07 10:20:00        | 2008-12-25 15: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-25 15:30:00        | 2008-12-25 00:00:00    |
+----------------------------+------------------------+

次の例では、元の DATETIME は日曜日になります。partWEEK(MONDAY) であるため、DATE_TRUNC は前の月曜日の DATETIME を返します。

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP '2017-11-05 00:00:00') AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05 00:00:00 | 2017-10-30 00:00:00 |
+---------------------+---------------------+

次の例では、元の datetime_expression はグレゴリオ暦 2015 年です。ただし、ISOYEAR 日付パーツが指定された DATETIME_TRUNC は、datetime_expression をグレゴリオ暦年ではなく、ISO 年の最初に切り詰めます。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。したがって datetime_expression 2015-06-15 00:00:00 より前にある ISO 年境界は 2014-12-29 です。

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

+---------------------+----------------+
| isoyear_boundary    | isoyear_number |
+---------------------+----------------+
| 2014-12-29 00:00:00 | 2015           |
+---------------------+----------------+

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

説明

指定された format_string に従って、DATETIME オブジェクトを形式設定します。この関数でサポートされる形式設定要素のリストについては、DATETIME でサポートされる形式設定要素をご覧ください。

戻りデータの型

STRING

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT
  FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT
  FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

説明

format_stringDATETIMESTRING 表記を使用して、DATETIME を返します。この関数でサポートされる形式設定要素のリストについては、DATETIME でサポートされる形式設定要素をご覧ください。

PARSE_DATETIME は、次のルールに従って string を解析します。

  • 指定されていないフィールド。指定されていないフィールドはすべて、1970-01-01 00:00:00.0 から初期設定されます。たとえば、年が指定されていない場合、デフォルトは 1970 です。
  • 大文字と小文字の区別がない名前MondayFebruary などの名前は、大文字と小文字が区別されません。
  • 空白文字。形式設定文字列内の 1 つ以上の連続する空白文字は DATETIME 文字列内の 0 個以上の連続する空白文字と同じです。DATETIME 文字列の先頭と末尾の空白は、形式設定文字列に含まれていなくても常に許可されます。
  • 形式設定の優先度。2 つ以上の形式設定要素に重複する情報がある場合、一部の例外を除き、最後の設定によって前の設定がオーバーライドされます。たとえば、%F%Y は両方とも年に影響するので、前者は後者をオーバーライドします。%s%C%y の説明については、DATETIME でサポートされる形式設定要素をご覧ください。

次の例では、STRING リテラルを DATETIME として解析します。

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

上記のクエリは次の出力を返します。

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18 13:45:55 |
+---------------------+

次の例では、自然言語形式の日付を含む STRING リテラルを DATETIME として解析します。

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
  AS datetime;

上記のクエリは次の出力を返します。

+---------------------+
| datetime            |
+---------------------+
| 2018-12-19 00:00:00 |
+---------------------+

戻りデータの型

DATETIME

DATETIME でサポートされる形式設定要素

特に指定されていない限り、形式設定文字列を使用する DATETIME 関数は次の要素をサポートします。

形式設定要素 説明
%A 完全な曜日名。
%a 省略された曜日名。
%B 完全な月の名前。
%b または %h 省略された月の名前。
%C 10 進数として表示される世紀(年を 100 で割って、整数値になるよう切り捨てる)(00~99)。
%c 日付および時刻の表記。
%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 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%H 10 進数で表示される時間(24 時間制)(00~23)。
%I 10 進数で表示される時間(12 時間制)(00~12)。
%j 10 進数として表示される、年内の日付(001~366)。
%k 10 進数として表示される時間(24 時間制)(0~23)。1 桁の場合は前にスペースが入れられます。
%l 10 進数として表示される時間(12 時間制)(1~12)。1 桁の場合は前にスペースが入れられます。
%M 10 進数として表示される分(00~59)。
%m 10 進数として表示される月(01~12)。
%n 改行文字。
%P am または pm のいずれか。
%p AM または PM のいずれか。
%Q 10 進数として表示される四半期(1~4)。
%R %H:%M 形式の時刻。
%r AM/PM 表記を使用する 12 時間制の時刻。
%S 10 進数として表示される秒(00~60)。
%s 1970 年 1 月 1 日 00:00:00 から数えた秒数。文字列内のどこに %s が出現するかにかかわらず、他のすべての形式要素を常にオーバーライドします。複数の %s 要素が使用されている場合、最後のものが優先されます。
%T %H:%M:%S 形式の時刻。
%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 HH:MM:SS 形式の時刻表記。
%x MM/DD/YY 形式の日付表記。
%Y 10 進数として表示される、世紀を含む年。
%y 10 進数(00-99)として表示される年。世紀は含みません。先頭に 0 を追加することもできます。%C と混在できます。%C が指定されていない場合、年 00~68 は 2000 年代、年 69~99 は 1900 年代です。
%% 単一の % 文字。
%E#S 小数第 # 位の精度で示される秒。
%E*S 完全な小数の精度で示される秒(リテラル '*')。
%E4Y 4 文字の年(0001 ... 9999)。%Y によって、年を完全に表現するために必要な数の文字が生成されます。

Time 関数

BigQuery は、以下の TIME 関数をサポートします。

CURRENT_TIME

CURRENT_TIME()

説明

現在時刻を TIME オブジェクトとして返します。

戻りデータの型

TIME

SELECT CURRENT_TIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 15:31:38.776361            |
+----------------------------+

TIME

1. TIME(hour, minute, second)
2. TIME(timestamp, [timezone])
3. TIME(datetime)

説明

  1. 時、分、秒を表す INT64 値を使用して、TIME オブジェクトを構築します。
  2. TIMESTAMP オブジェクトを使用して、TIME オブジェクトを構築します。タイムゾーンを指定するには省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。
  3. DATETIME オブジェクトを使用して、TIME オブジェクトを構築します。

戻りデータの型

TIME

SELECT
  TIME(15, 30, 00) as time_hms,
  TIME(TIMESTAMP "2008-12-25 15:30:00+08", "America/Los_Angeles") as time_tstz;
+----------+-----------+
| time_hms | time_tstz |
+----------+-----------+
| 15:30:00 | 23:30:00  |
+----------+-----------+
SELECT
  TIME(DATETIME "2008-12-25 15:30:00.000000") AS time_dt;
+----------+
| time_dt  |
+----------+
| 15:30:00 |
+----------+

TIME_ADD

TIME_ADD(time_expression, INTERVAL INT64_expr part)

説明

partINT64_expr 単位を TIME オブジェクトに追加します。

TIME_ADD は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

この関数は、値が 00:00:00 から 24:00:00 の範囲外になった場合に自動調整を行います。たとえば、23:30:00 に 1 時間を追加すると、値 00:30:00 が返されます。

戻りデータの型

TIME

SELECT
  TIME "15:30:00" as original_time,
  TIME_ADD(TIME "15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_time               | later                  |
+-----------------------------+------------------------+
| 15:30:00                    | 15:40:00               |
+-----------------------------+------------------------+

TIME_SUB

TIME_SUB(time_expression, INTERVAL INT_expr part)

説明

TIME オブジェクトから partINT64_expr 単位を減算します。

TIME_SUB は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

この関数は、値が 00:00:00 から 24:00:00 の範囲外になった場合に自動調整を行います。たとえば、00:30:00 から 1 時間を減算すると、23:30:00 の値が返されます。

戻りデータの型

TIME

SELECT
  TIME "15:30:00" as original_date,
  TIME_SUB(TIME "15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date                | earlier                |
+-----------------------------+------------------------+
| 15:30:00                    | 15:20:00               |
+-----------------------------+------------------------+

TIME_DIFF

TIME_DIFF(time_expression, time_expression, part)

説明

2 つの TIME オブジェクト間の、全体が指定された part の間隔を示す数を返します。計算によって得られた型がオーバーフローする場合、エラーをスローします。たとえば、2 つの TIME オブジェクト間のマイクロ秒単位の差異が INT64 値をオーバーフローする場合などです。

TIME_DIFF は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

戻りデータの型

INT64

SELECT
  TIME "15:30:00" as first_time,
  TIME "14:35:00" as second_time,
  TIME_DIFF(TIME "15:30:00", TIME "14:35:00", MINUTE) as difference;

+----------------------------+------------------------+------------------------+
| first_time                 | second_time            | difference             |
+----------------------------+------------------------+------------------------+
| 15:30:00                   | 14:35:00               | 55                     |
+----------------------------+------------------------+------------------------+

TIME_TRUNC

TIME_TRUNC(time_expression, part)

説明

part の粒度で TIME オブジェクトを切り詰めます。

TIME_TRUNC は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

戻りデータの型

TIME

SELECT
  TIME "15:30:00" as original,
  TIME_TRUNC(TIME "15:30:00", HOUR) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 15:30:00                   | 15:00:00               |
+----------------------------+------------------------+

FORMAT_TIME

FORMAT_TIME(format_string, time_object)

説明: 指定された format_string に従って、TIME オブジェクトを形式設定します。この関数でサポートされる形式設定要素のリストについては、TIME でサポートされる形式設定要素をご覧ください。

戻りデータの型

STRING

SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;

+----------------+
| formatted_time |
+----------------+
| 15:30          |
+----------------+

PARSE_TIME

PARSE_TIME(format_string, string)

説明

format_string と文字列を使用して TIME オブジェクトを返します。この関数でサポートされる形式設定要素のリストについては、TIME でサポートされる形式設定要素をご覧ください。

PARSE_TIME を使用する場合、次の点に注意してください。

  • 指定されていないフィールド。指定されていないフィールドはすべて、00:00:00.0 から初期設定されます。たとえば、seconds を指定しないと、デフォルトで 00 が使用されます。
  • 空白文字。形式設定文字列内の 1 つ以上の連続する空白文字は、TIME 文字列内のゼロ以上の連続する空白文字と同じです。また、TIME 文字列内の先頭および末尾の空白文字は、それらが形式設定文字列内にない場合でも常に許可されます。
  • 形式設定の優先度。2 つ以上の形式設定要素に重複する情報がある場合、最後の設定によって前の設定がオーバーライドされます。

戻りデータの型

TIME

SELECT PARSE_TIME("%H", "15") as parsed_time;

+-------------+
| parsed_time |
+-------------+
| 15:00:00    |
+-------------+

TIME に使用できる形式設定要素

特に指定されていない限り、形式設定文字列を使用する TIME 関数は次の要素をサポートします。

形式設定要素 説明
%H 10 進数で表示される時間(24 時間制)(00~23)。
%I 10 進数で表示される時間(12 時間制)(00~12)。
%j 10 進数として表示される、年内の日付(001~366)。
%k 10 進数として表示される時間(24 時間制)(0~23)。1 桁の場合は前にスペースが入れられます。
%l 10 進数として表示される時間(12 時間制)(1~12)。1 桁の場合は前にスペースが入れられます。
%M 10 進数として表示される分(00~59)。
%n 改行文字。
%P am または pm のいずれか。
%p AM または PM のいずれか。
%R %H:%M 形式の時刻。
%r AM/PM 表記を使用する 12 時間制の時刻。
%S 10 進数として表示される秒(00~60)。
%T %H:%M:%S 形式の時刻。
%t タブ文字。
%X HH:MM:SS 形式の時刻表記。
%% 単一の % 文字。
%E#S 小数第 # 位の精度で示される秒。
%E*S 完全な小数の精度で示される秒(リテラル '*')。

TIMESTAMP 関数

BigQuery は、以下の TIMESTAMP 関数をサポートします。

注: オーバーフローが生じると、これらの関数によってランタイム エラーが返されます。結果の値は定義済みの日付とタイムスタンプの最小値 / 最大値によって制限されます。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

説明

括弧は省略可能です。この関数は、挿入されたうるう秒の前後 20 時間にわたってうるう秒を分散することで、うるう秒を処理します。CURRENT_TIMESTAMP() は、連続し、明確であり、1 分あたり正確に 60 秒が含まれ、うるう秒に対して値を繰り返さない TIMESTAMP 値を生成します。

サポートされる入力型

該当なし

結果のデータ型

TIMESTAMP

SELECT CURRENT_TIMESTAMP() as now;

+-------------------------------+
| now                           |
+-------------------------------+
| 2016-05-16 18:12:47.145482+00 |
+-------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE tz_spec])

説明

提供された timestamp_expression から指定された part に対応する INT64 値を返します。

指定できる part 値は次のとおりです。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。
  • WEEK(<WEEKDAY>) : 範囲 [0, 53] 内の timestamp_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
  • DATETIME
  • TIME

戻り値となる値の下位の期間は切り捨てられます。たとえば、秒数を抽出するとき、EXTRACT はミリ秒とマイクロ秒の値を切り捨てます。

タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

通常、INT64partDATE の場合、DATE を返します。

次の例では、EXTRACTDAY 時間部分に対応する値を返します。

SELECT EXTRACT(DAY
  FROM TIMESTAMP "2008-12-25 15:30:00" AT TIME ZONE "America/Los_Angeles")
  AS the_day;

+------------+
| the_day    |
+------------+
| 25         |
+------------+

次の例では、EXTRACT はタイムスタンプの列とは異なる時間部分に対応する値を返します。

WITH Timestamps AS (
  SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
  SELECT TIMESTAMP '2007-12-31' UNION ALL
  SELECT TIMESTAMP '2009-01-01' UNION ALL
  SELECT TIMESTAMP '2009-12-31' UNION ALL
  SELECT TIMESTAMP '2017-01-02' UNION ALL
  SELECT TIMESTAMP '2017-05-26'
)
SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM Timestamps
ORDER BY timestamp;

+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

以下の例では、timestamp_expression は日曜日になります。EXTRACT は、日曜日から始まる週を使用して最初の列を計算し、月曜日から始まる週を使用して 2 番目の列を計算します。

WITH table AS (SELECT TIMESTAMP('2017-11-05 00:00:00') AS timestamp)
SELECT
  timestamp,
  EXTRACT(WEEK(SUNDAY) FROM timestamp) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp) AS week_monday
FROM table;

+------------------------+-------------+---------------+
| timestamp              | week_sunday | week_monday   |
+------------------------+-------------+---------------+
| 2017-11-05 00:00:00+00 | 45          | 44            |
+------------------------+-------------+---------------+

STRING

STRING(timestamp_expression[, timezone])

説明

timestamp_expression を STRING データ型に変換します。タイムゾーンを指定するためのパラメータがサポートされています(省略可能)。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

STRING

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles") as string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 07:30:00-08        |
+-------------------------------+

TIMESTAMP

TIMESTAMP(
  string_expression[, timezone] |
  date_expression[, timezone] |
  datetime_expression[, timezone]
)

説明

  • string_expression[, timezone]: STRING 表現を TIMESTAMP データ型に変換します。string_expression にはタイムスタンプのリテラルを含める必要があります。string_expression のタイムスタンプ リテラルにタイムゾーンが含まれている場合は、明示的な timezone 引数を含めないでください。
  • date_expression[, timezone]: DATE オブジェクトを TIMESTAMP データ型に変換します。
  • datetime_expression[, timezone]: DATETIME オブジェクトを TIMESTAMP データ型に変換します。

この関数では、タイムゾーンを指定するためのパラメータがサポートされています(省略可能)。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。

戻りデータの型

TIMESTAMP

以下の例ではタイムゾーンが指定されています。

SELECT CAST(
  TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING
) AS timestamp_str;

+------------------------+
| timestamp_str          |
+------------------------+
| 2008-12-25 23:30:00+00 |
+------------------------+
SELECT CAST(
  TIMESTAMP("2008-12-25 15:30:00 America/Los_Angeles") AS STRING
) AS timestamp_str_timezone;

+------------------------+
| timestamp_str_timezone |
+------------------------+
| 2008-12-25 23:30:00+00 |
+------------------------+
SELECT CAST(
  TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING
) AS timestamp_datetime;

+------------------------+
| timestamp_datetime     |
+------------------------+
| 2008-12-25 23:30:00+00 |
+------------------------+
SELECT CAST(
  TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING
) AS timestamp_date;

+------------------------+
| timestamp_date         |
+------------------------+
| 2008-12-25 08:00:00+00 |
+------------------------+

以下の例ではデフォルトのタイムゾーンとして UTC が使用されます。

SELECT CAST(
  TIMESTAMP("2008-12-25 15:30:00") AS STRING
) AS timestamp_str;

+------------------------+
| timestamp_str          |
+------------------------+
| 2008-12-25 15:30:00+00 |
+------------------------+
SELECT CAST(
  TIMESTAMP(DATE "2008-12-25") AS STRING
) AS timestamp_date;

+------------------------+
| timestamp_date         |
+------------------------+
| 2008-12-25 00:00:00+00 |
+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

説明

タイムゾーンに関係なく、date_partint64_expression 単位をタイムスタンプに加算します。

TIMESTAMP_ADD は、date_part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。60 MINUTE と等価。
  • DAY。24 HOUR と等価。

戻りデータの型

TIMESTAMP

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS later;

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

説明

タイムゾーンに関係なく、タイムスタンプから date_partint64_expression 単位を減算します。

TIMESTAMP_SUB は、date_part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。60 MINUTE と等価。
  • DAY。24 HOUR と等価。

戻りデータの型

TIMESTAMP

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

説明

2 つのタイムスタンプ間の、全体が指定された date_part の間隔を示す数を返します。最初の timestamp_expression は後の日付を表します。最初の timestamp_expression が 2 番目の timestamp_expression よりも前の場合、出力は負になります。計算によって得られた型がオーバーフローする場合、エラーになります。たとえば、2 つのタイムスタンプの間のナノ秒単位の差異が INT64 値をオーバーフローする場合などです。

TIMESTAMP_DIFF は、date_part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。60 MINUTE と等価。
  • DAY。24 HOUR と等価。

戻りデータの型

INT64

SELECT
  TIMESTAMP "2010-07-07 10:20:00 UTC" as later_timestamp,
  TIMESTAMP "2008-12-25 15:30:00 UTC" as earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00 UTC",
    TIMESTAMP "2008-12-25 15:30:00 UTC", HOUR) AS hours;

+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 13410 |
+------------------------+------------------------+-------+

次の例では、最初のタイムスタンプが 2 番目のタイムスタンプより前であるため、負の出力になります。

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

説明

date_part の粒度までタイムスタンプを切り詰めます。

TIMESTAMP_TRUNC は、date_part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>):: timestamp_expression を週が WEEKDAY で始まる前の週の境界に切り詰めます。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: timestamp_expression を、先行する ISO 8601 の週の境界に切り詰めます。ISOWEEK は月曜日に始まります。各 ISO 年の最初の ISOWEEK には、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前の date_expression を前の月曜日に切り詰めます。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: timestamp_expressionISO 8601 形式の週番号が付けられた先行年の境界に切り詰めます。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

TIMESTAMP_TRUNC 関数は、省略可能な time_zone パラメータをサポートしています。このパラメータは、次の date_parts に適用されます。

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>)
  • MONTH
  • QUARTER
  • YEAR

このパラメータは、デフォルトのタイムゾーンである UTC 以外のタイムゾーンを切り詰めオペレーションの一部として使用する場合に使用します。

TIMESTAMPMINUTE あるいは HOUR に切り詰める場合、TIMESTAMP_TRUNC は指定された(あるいはデフォルトの)タイムゾーンでの TIMESTAMP の常用時を計算し、そこから分と秒(HOUR に切り詰める場合)あるいは秒(MINUTE に切り詰める場合)をその TIMESTAMP から減じます。これは、ほとんどの場合に直観に合う結果となりますが、夏時間の移行が時間境界に合っていない場合、その近辺で直観に合わない結果となります。

戻りデータの型

TIMESTAMP

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'UTC') as utc,
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'America/Los_Angeles') as la;

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

次の例では、timestamp_expression には +12 のタイムゾーン オフセットが適用されます。最初の列には、UTC 時間の timestamp_expression が表示されています。2 番目の列には、月曜日から始まる週を使用した TIMESTAMP_TRUNC の出力が表示されています。timestamp_expression は UTC では日曜日になるため、TIMESTAMP_TRUNC はそれを前の月曜日に切り詰めます。3 番目の列には、オプションのタイムゾーン定義引数の 'Pacific/Auckland' が指定された同じ関数が表示されています。この関数は、ニュージーランド夏時間を使用して timestamp_expression を切り詰めます。ここでは月曜日になります。

SELECT
  timestamp,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY)) AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY), 'Pacific/Auckland') AS nzdt_truncated
FROM (SELECT TIMESTAMP('2017-11-06 00:00:00+12') AS timestamp);

+------------------------+------------------------+------------------------+
| timestamp              | utc_truncated          | nzdt_truncated         |
+------------------------+------------------------+------------------------+
| 2017-11-05 12:00:00+00 | 2017-10-30 00:00:00+00 | 2017-11-05 11:00:00+00 |
+------------------------+------------------------+------------------------+

次の例では、元の timestamp_expression はグレゴリオ暦 2015 年です。ただし、ISOYEAR 日付パーツが指定された TIMESTAMP_TRUNC は、timestamp_expression をグレゴリオ暦年ではなく、ISO 年の最初に切り詰めます。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。そのため、timestamp_expression 2015-06-15 00:00:00+00 より前の ISO 年の境界は 2014-12-29 になります。

SELECT
  TIMESTAMP_TRUNC('2015-06-15 00:00:00+00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP '2015-06-15 00:00:00+00') AS isoyear_number;

+------------------------+----------------+
| isoyear_boundary       | isoyear_number |
+------------------------+----------------+
| 2014-12-29 00:00:00+00 | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

説明

タイムスタンプを指定された format_string に従ってフォーマットします。

この関数でサポートされる形式設定要素のリストについては、TIMESTAMP でサポートされる形式設定要素をご覧ください。

戻りデータの型

STRING

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+
SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

説明

タイムスタンプの format_string および文字列表記を使用して、TIMESTAMP オブジェクトを返します。

PARSE_TIMESTAMP を使用する場合、次の点に注意してください。

  • 指定されていないフィールド。指定されていないフィールドはすべて、1970-01-01 00:00:00.0 から初期設定されます。この初期設定値は、関数のタイムゾーン引数によって指定されたタイムゾーンを使用します(存在する場合)。存在しない場合、初期設定値はデフォルトのタイムゾーンである UTC を使用します。たとえば、年が指定されていない場合、デフォルトで 1970 に設定されます。
  • 大文字と小文字の区別がない名前MondayFebruary などの名前は、大文字と小文字が区別されません。
  • 空白文字。形式設定文字列内の 1 つ以上の連続する空白文字は、タイムスタンプ文字列内のゼロ以上の連続する空白文字と同じです。また、タイムスタンプ文字列内の先頭および末尾の空白文字は、それらが形式設定文字列内にない場合でも常に許可されます。
  • 形式設定の優先度。2 つ以上の形式設定要素に重複する情報がある場合(たとえば、%F%Y の両方が年に影響を及ぼす場合など)、一部の例外を除き、最後の設定によって前の設定がオーバーライドされます(%s%C%y の説明を参照)。

戻りデータの型

TIMESTAMP

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+------------------------+
| parsed                 |
+------------------------+
| 2008-12-25 15:30:00+00 |
+------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

説明

int64_expression は 1970-01-01 00:00:00 UTC からの秒数として解釈します。

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+------------------------+
| timestamp              |
+------------------------+
| 2008-12-25 15:30:00+00 |
+------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

説明

int64_expression は 1970-01-01 00:00:00 UTC からのミリ秒数として解釈します。

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+------------------------+
| timestamp              |
+------------------------+
| 2008-12-25 15:30:00+00 |
+------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

説明

int64_expression は 1970-01-01 00:00:00 UTC からのマイクロ秒数として解釈されます。

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+------------------------+
| timestamp              |
+------------------------+
| 2008-12-25 15:30:00+00 |
+------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

説明

1970-01-01 00:00:00 UTC 以降の秒数を返します。これよりも高い精度のレベルは切り捨てます。

戻りデータの型

INT64

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00 UTC") as seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

説明

1970-01-01 00:00:00 UTC 以降のミリ秒数を返します。これよりも高い精度のレベルは切り捨てます。

戻りデータの型

INT64

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 UTC") as millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

説明

1970-01-01 00:00:00 UTC 以降のマイクロ秒数を返します。これよりも高い精度のレベルは切り捨てます。

戻りデータの型

INT64

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00 UTC") as micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

TIMESTAMP でサポートされる形式設定要素

特に指定されていない限り、形式設定文字列を使用する TIMESTAMP 関数は次の要素をサポートします。

形式設定要素 説明
%A 完全な曜日名。
%a 省略された曜日名。
%B 完全な月の名前。
%b または %h 省略された月の名前。
%C 10 進数として表示される世紀(年を 100 で割って、整数値になるよう切り捨てる)(00~99)。
%c %a %b %e %T %Y の形式で表現される日時。
%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 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%H 10 進数で表示される時間(24 時間制)(00~23)。
%I 10 進数で表示される時間(12 時間制)(00~12)。
%j 10 進数として表示される、年内の日付(001~366)。
%k 10 進数として表示される時間(24 時間制)(0~23)。1 桁の場合は前にスペースが入れられます。
%l 10 進数として表示される時間(12 時間制)(1~12)。1 桁の場合は前にスペースが入れられます。
%M 10 進数として表示される分(00~59)。
%m 10 進数として表示される月(01~12)。
%n 改行文字。
%P am または pm のいずれか。
%p AM または PM のいずれか。
%Q 10 進数として表示される四半期(1~4)。
%R %H:%M 形式の時刻。
%r AM/PM 表記を使用する 12 時間制の時刻。
%S 10 進数として表示される秒(00~60)。
%s 1970-01-01 00:00:00 UTC からの秒数。文字列内のどこに %s が出現するかにかかわらず、他のすべての形式要素を常にオーバーライドします。複数の %s 要素が使用されている場合、最後のものが優先されます。
%T %H:%M:%S 形式の時刻。
%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 HH:MM:SS 形式の時刻表記。
%x MM/DD/YY 形式の日付表記。
%Y 10 進数として表示される、世紀を含む年。
%y 10 進数(00-99)として表示される年。世紀は含みません。先頭に 0 を追加することもできます。%C と混在できます。%C が指定されていない場合、年 00~68 は 2000 年代、年 69~99 は 1900 年代です。
%Z タイムゾーンの名前。
%z 必要に応じて +HHMM または -HHMM の形式で示されるグリニッジ子午線からのオフセット。正の値はグリニッジよりも東側にある場所を示します。
%% 単一の % 文字。
%Ez RFC 3339 と互換性のある数値タイムゾーン(+HH:MM または -HH:MM)。
%E#S 小数第 # 位の精度で示される秒。
%E*S 完全な小数の精度で示される秒(リテラル '*')。
%E4Y 4 文字の年(0001 ... 9999)。%Y によって、年を完全に表現するために必要な数の文字が生成されます。

タイムゾーンの定義

特定の日付およびタイムスタンプ関数を使用して、デフォルトのタイムゾーンをオーバーライドし、別の値を指定できます。タイムゾーンを指定するには、タイムゾーン名(例: America/Los_Angeles)、または UTC からのタイムゾーン オフセット(例: -08)を指定します。

タイムゾーン オフセットを使用する場合は、次の形式を使用します。

(+|-)H[H][:M[M]]

指定された日時の America/Los_Angeles のタイムゾーン オフセットと -08 は同じであるため、次のタイムスタンプは同じになります。

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 America/Los_Angeles") as millis;
SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00-08:00") as millis;

地理関数

地理関数によって BigQuery の GEOGRAPHY 値が処理または生成されます。地理関数のシグネチャは、すべて ST_ で始まります。BigQuery は、以下の関数をサポートしています。これらの関数を使用して、地理データの分析、地理フィーチャー間の空間関係の特定、GEOGRAPHY の作成または操作を実施できます。

地理関数は、それぞれの動作に基づいて次のカテゴリに分類されます。

  • コンストラクタ: 座標または既存の GEOGRAPHY 値から新しい GEOGRAPHY 値を作成する関数。ST_GEOGPOINT など。
  • パーサー: WKTGeoJSON などの外部形式から GEOGRAPHY を作成する関数。たとえば、ST_GEOGFROMTEXT では WKT から GEOGRAPHY が作成されます。
  • フォーマッタ: GEOGRAPHY を WKT や GeoJSON などの外部形式にエクスポートする関数。たとえば、ST_ASTEXT では GEOGRAPHY が WKT 形式に設定されます。
  • 変換: なんらかのプロパティに従い他から新しい GEOGRAPHY を生成する関数。ST_INTERSECTIONST_BOUNDARY などがあります。
  • 述語: 2 つの GEOGRAPHY 間の空間関係、または地理上のなんらかのプロパティに関して TRUE または FALSE を返す関数。この関数は、一般にフィルタ句で使用されます。述語の例として ST_DWITHIN があります。
  • アクセサ: 副次的影響を生じないで GEOGRAPHY のプロパティにアクセスできるようにする関数。たとえば ST_NUMPOINTS です。
  • 測定: 1 つ以上の GEOGRAPHY の測定値を計算する関数。たとえば ST_DISTANCE です。
  • 集計関数: 地理固有の集計関数。たとえば ST_UNION_AGG です。

BigQuery の地理関数はすべて、入力引数のいずれかに NULL があると、NULL を返します。

ST_GEOGPOINT

ST_GEOGPOINT(longitude, latitude)

説明

単一のポイントを含む GEOGRAPHY を作成します。ST_GEOGPOINT は、指定された FLOAT64 の経度と緯度のパラメータからポイントを作成し、そのポイントを GEOGRAPHY 値に含めて返します。

制約

  • 緯度の範囲は [-90, 90] です。緯度がこの範囲に含まれない場合、エラーが発生します。
  • 経度は [-180, 180] の範囲外であっても許容されます。ST_GEOGPOINT では、入力された経度のモジュロ 360 を使用して、[-180, 180] の範囲内の経度が取得されます。

戻り値の型

GEOGRAPHY

ST_MAKELINE

ST_MAKELINE(geography_1, geography_2)
ST_MAKELINE(array_of_geography)

説明

入力された各 GEOGRAPHY に含まれるポイントまたはラインの頂点を指定順に連結することで、1 つの LineString を含む GEOGRAPHY を作成します。

ST_MAKELINE には 2 種類あります。最初のバリアントでは、入力は 2 つの GEOGRAPHY である必要があります。2 番目のバリアントでは、入力は GEOGRAPHY タイプの ARRAY でなければなりません。いずれのバリアントの場合も、各入力 GEOGRAPHY は次のいずれかの値で構成されている必要があります。

  • 1 つのポイント。
  • 1 つの LineString。

ST_MAKELINE の最初のバリアントで、入力 GEOGRAPHYNULL の場合、ST_MAKELINENULL を返します。2 番目のバリアントで、入力 ARRAY または入力 ARRAY にある要素が NULL の場合、ST_MAKELINENULL を返します。

制約

それぞれのエッジは 180 度未満にする必要があります。

注: BigQuery のスナップ処理では、所定の短さのエッジが破棄され、2 つの端点が一緒にスナップされる場合があります。たとえば、入力された 2 つの GEOGRAPHY のそれぞれに 1 つのポイントが含まれ、その 2 つのポイント間の距離がスナップ半径より短い場合、その 2 つのポイントはまとめてスナップされます。この結果、GEOGRAPHY に含まれるポイントは 1 つだけになります。

戻り値の型

GEOGRAPHY

ST_MAKEPOLYGON

ST_MAKEPOLYGON(geography_expression)
ST_MAKEPOLYGON(geography_expression, array_of_geography)

説明

入力された LineString のそれぞれがポリゴンリングの構成に使用される LineString の入力から、1 つのポリゴンを含む GEOGRAPHY を作成します。

ST_MAKEPOLYGON には 2 種類あります。最初のバリアントでは、1 つの LineString のみを含む単一の GEOGRAPHY によって入力 LineString が提供されます。2 つ目のバリアントでは、入力が 1 つの GEOGRAPHY と複数 GEOGRAPHY からなる配列で構成され、各配列には 1 つの LineString のみが含まれます。各バリアントの最初の GEOGRAPHY が、ポリゴンシェルの作成に使用されます。入力 ARRAY で提供された追加の GEOGRAPHY は、ポリゴンホールを指定します。1 つの LineString のみを含む入力 GEOGRAPHY はすべて、次の条件に合致している必要があります。

  • LineString が、少なくとも 3 つの個別の頂点で構成されていること。
  • LineString は閉じている必要があります。つまり、最初と最後の頂点が同じでなければなりません。最初と最後の頂点が異なる場合、この関数によって最初の頂点から最後の頂点に至る最後のエッジが作成されます。

ST_MAKEPOLYGON の最初のバリアントでは、入力 GEOGRAPHYNULL の場合、ST_MAKEPOLYGONNULL を返します。2 番目のバリアントでは、入力 ARRAY または入力 ARRAY 内のいずれかの要素が NULL の場合、ST_MAKEPOLYGONNULL を返します。

注: ST_MAKEPOLYGON では、空の GEOGRAPHY が入力として受け入れられます。ST_MAKEPOLYGON は空の GEOGRAPHY を、空の LineString を含むものとして解釈します。空の LineString は完全ループ(つまり、地球全体をカバーするポリゴン)を作成します。

制約

入力されたリング全体で、以下のように有効なポリゴンが形成される必要があります。

  • ポリゴンシェルが、各ポリゴンホールをカバーしている必要があります。
  • 存在できるポリゴンシェルは 1 つだけです(最初に入力されたリングであること)。 これは、ポリゴンホールはネストできないことを意味します。
  • ポリゴンリングが交差できる地点は、交差する両方のリングの境界上にある頂点だけです。

それぞれのエッジは 180 度未満にする必要があります。

各ポリゴンリングによって球体が 2 つの領域に分割されます。ST_MAKEPOLYGON に入力された最初の LineString がポリゴンシェルを形成し、その内部が 2 つの領域のうちの小さい方になるよう選択されます。それ以降に入力されるそれぞれの LineString がポリゴンホールを指定するので、ポリゴンの内部はすでに適切に定義されていることになります。ポリゴンの内部が 2 つの領域の大きい方になるようポリゴンシェルを定義する方法については、ST_MAKEPOLYGONORIENTED をご覧ください。

注: BigQuery のスナップ処理では、所定の短さのエッジが破棄され、2 つの端点が一緒にスナップされる場合があります。このため、頂点がスナップされた時点で、極めて小さいポリゴンホールが消滅する可能性、つまり、出力の GEOGRAPHY に 1 つのラ