式、関数、演算子

このページでは、関数と演算子を含む Cloud Spanner SQL 式について説明します。

関数呼び出しルール

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

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

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

変換規則

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

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

以下の表に、考えられるすべての CAST と、Cloud Spanner SQL データ型に対する強制型変換の可能性の要約を示します。「強制型変換後」は所定のデータ型(列など)のすべてのに適用されます。

元の型 キャスト先 強制型変換後
INT64 BOOL
INT64
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
FLOAT64
STRING
BYTES
DATE
TIMESTAMP
 
BYTES BYTES
STRING
 
DATE DATE
STRING
TIMESTAMP
 
TIMESTAMP DATE
STRING
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 を返します。
FLOAT64 INT64 最も近い INT64 値を返します。
1.5 や -0.5 などの中間値の場合は、ゼロから離れるように丸められます。
FLOAT64 STRING 近似する文字列表現を返します。
BOOL INT64 xTRUE の場合は 1、それ以外の場合は 0 を返します。
BOOL STRING xTRUE の場合は "true"、それ以外の場合は "false" を返します。
STRING FLOAT64 有効な FLOAT64 リテラルと同じ形式を持つと解釈して、x を FLOAT64 値として返します。
また、"inf""+inf""-inf""nan" からのキャストをサポートします。
変換では大文字と小文字を区別しません。
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 を使用する場合、Cloud Spanner SQL がそのキャスティングを実行できなければクエリは失敗します。たとえば、次のクエリによってエラーが発生します。

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

日付型のキャスティング

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

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

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

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

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

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

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

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

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

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

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

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

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

強制型変換

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

その他の変換関数

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

集計関数

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

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

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

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

次のセクションでは、Cloud Spanner SQL でサポートされている集計関数について説明します。

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

説明

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

ANY_VALUE は、IGNORE NULLS が指定されているかのように動作します。expressionNULL である行は考慮されず、そのような行は選択されません。

サポートされる引数の型

すべて

省略可能な句

HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

返されるデータ型

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

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

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

説明

expression 値の配列を返します。

サポートされる引数の型

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

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. IGNORE NULLS または RESPECT NULLS: IGNORE NULLS が指定されている場合、NULL 値は結果から除外されます。RESPECT NULLS を指定した場合、あるいは両方とも指定しない場合、結果に NULL 値が含まれます。
  3. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

出力要素の順序

出力内の要素の順序は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。

返されるデータ型

ARRAY

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

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

説明

ARRAY 型の expression から要素を結合し、結果として単一の ARRAY を返します。この関数は、NULL 入力配列を無視しますが、NULL 以外の入力配列に含まれる NULL 要素は無視しません。

サポートされる引数の型

ARRAY

省略可能な句

HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

出力要素の順序

出力内の要素の順序は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。

返されるデータ型

ARRAY

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

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

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

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

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

返されるデータ型

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

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

  • INT64

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

返されるデータ型

INT64

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

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

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

  • INT64

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

返されるデータ型

INT64

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

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

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

  • INT64

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

返されるデータ型

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(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

expression には任意のデータ型を指定できます。

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

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 COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

BOOL

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

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

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

BOOL

省略可能な句

HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

BOOL

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

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

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

BOOL

省略可能な句

HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

BOOL

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

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

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

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

省略可能な句

HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

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

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

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

説明

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

サポートされる引数の型

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

省略可能な句

HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

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

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

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

説明

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

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

サポートされる引数の型

STRING BYTES

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

出力要素の順序

出力内の要素の順序は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。

戻りデータの型

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

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

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

サポートされる引数の型

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

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

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

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

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

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

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

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

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

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

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

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

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

共通の句

HAVING MAX 句と HAVING MIN 句

ほとんどの集計関数は、HAVING MAXHAVING MIN という任意の 2 つの句をサポートしています。これにより、関数が集計する行のセットを、特定の列に最大値または最小値を持つ行に制限します。構文は通常、次のようになります。

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: 関数が集計する行のセットを、グループ内の expression2 の最大値と等しい expression2 の値を持つ行に制限します。最大値は MAX(expression2) の結果と同じです。
  • HAVING MIN: 関数が集計する行のセットを、グループ内の expression2 の最小値と等しい expression2 の値を持つ行に制限します。最小値は MIN(expression2) の結果と同じです。

これらの句では、すべての行に対して expression2NULL と評価されない限り、最大値または最小値を計算する際には、NULL 値は無視されます。

これらの句は、データ型 ARRAYSTRUCT をサポートしていません。

この例では、直近の 2001 年の平均降水量が返されます。

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

まず、クエリが year 列に最大値を持つ行を取得します。 次の 2 つの行が該当します。

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

最後に、クエリは inches 列の値(9 と 1)の平均を計算して、次の結果を返します。

+---------+
| average |
+---------+
| 5       |
+---------+

統計集計関数

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

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

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

サポートされる入力型

FLOAT64

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

FLOAT64

STDDEV

STDDEV([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

STDDEV_SAMP のエイリアスです。

VAR_SAMP

VAR_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

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

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

サポートされる入力型

FLOAT64

省略可能な句

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

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression [HAVING {MAX | MIN} expression2])

説明

VAR_SAMP のエイリアスです。

数学関数

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

  • いずれかの入力パラメータが 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

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 型で)返します。

丸め関数の動作の例

Cloud Spanner SQL 丸め関数の動作例:

入力「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

ハッシュ関数

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

SHA1

SHA1(input)

説明

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

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

戻り値の型

BYTES

SELECT SHA1("Hello World") as sha1;

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

SHA256

SHA256(input)

説明

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

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

戻り値の型

BYTES

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

説明

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

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

戻り値の型

BYTES

SELECT SHA512("Hello World") as sha512;

文字列関数

これらの文字列関数は、2 種類のデータ型 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(
  (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(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

+----------------+
| 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 つ以上の STRING または BYTE の値を 1 つに連結します。

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

戻り値の型

STRING または BYTES

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

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

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

Cloud Spanner SQL では、文字列の形式設定に FORMAT() 関数を使用できます。この関数は C の printf 関数に似ています。これは、ゼロ個以上の形式指定子を含む形式設定文字列から STRING を生成します。さらに、それらの形式指定子に一致する追加的な引数からなる可変長リストを生成します。たとえば次のようなものがあります。

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

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

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

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

戻り値

date: January 02, 2015!

構文

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

FORMAT(<format_string>, ...)

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

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

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

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

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

指定子 説明
d または i 10 進の整数 392 INT64
o 8 進数 610
INT64*
x 16 進の整数 7fa
INT64*
X 16 進の整数(大文字) 7FA
INT64*
f 有限値の場合は [-](整数部).(小数部)の 10 進表記、非有限値の場合は小文字 392.650000
inf
nan

FLOAT64
F 有限値の場合は [-](整数部)(小数部)の 10 進表記、非有限値の場合は大文字 392.650000
INF
NAN

FLOAT64
e 科学的記数法(仮数 / 指数)(小文字) 3.926500e + 02
inf
nan

FLOAT64
E 科学的記数法(仮数 / 指数)(大文字) 3.926500E+02
INF
NAN

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

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

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

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

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

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

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

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

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

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

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

%g と %G の動作

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

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

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

%t と %T の動作

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

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

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

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

%t %T
任意の型の NULL NULL NULL
INT64
123 123
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 で形式設定される

エラー条件

形式指定子が無効な場合や該当する引数型と互換性がない場合、あるいは間違った数や引数が指定されている場合、エラーが生成されます。たとえば、次の <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(または同じ文字の大文字)になります。これは、Cloud Spanner SQL がこれらの値を STRING にキャストする動作と整合します。%T の場合、Cloud Spanner SQL は文字列リテラル表現なしの FLOAT64 の引用符付きの文字列を返します。

FROM_BASE64

FROM_BASE64(string_expr)

説明

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

戻り値の型

BYTES

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

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

FROM_HEX

FROM_HEX(string)

説明

16 進数でエンコードされた 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;

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

LENGTH

LENGTH(value)

説明

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

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_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       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

説明

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

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

戻り値の型

STRING または BYTES


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

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

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

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

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

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

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

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

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

UPPER

UPPER(value)

説明

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

BYTES 引数の場合、引数は ASCII テキストとして処理され、127 よりも大きいすべてのバイトはそのままとなります。

戻り値の型

STRING または BYTES


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

SELECT
  UPPER(item) AS example
FROM items;

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

JSON 関数

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

JSON_QUERY または JSON_VALUE

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

JSON_VALUE(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_path_string_literal が JSON null を返す場合、SQL NULL に変換されます。

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

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

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

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

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

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

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

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

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

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

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"}]}}

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

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

JSON 関数は、json_path_string_literal パラメータが json_string_expr 内の値と一致しない場合は NULL を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合は、NULL を返します。

JSONPath が無効な場合、JSON 関数はエラーとなります。

配列関数

ARRAY

ARRAY(subquery)

説明

ARRAY 関数は、サブクエリによって返される行ごとに 1 つの要素を含む ARRAY を返します。

subquery によって SQL テーブルが生成される場合、そのテーブルの列数は 1 つである必要があります。出力 ARRAY の各要素は、テーブルの行に 1 つだけある列の値となります。

制約

  • サブクエリは順序付けされていないため、出力 ARRAY の要素は、サブクエリのソーステーブルの順序を保証するものではありません。ただし、サブクエリに ORDER BY 句が含まれている場合、ARRAY 関数はその句を使用する ARRAY を返します。
  • サブクエリが複数の列を返す場合、ARRAY 関数はエラーを返します。
  • サブクエリが ARRAY 型の列または ARRAY 型の行を返す場合、ARRAY 関数は、Cloud Spanner SQL では型 ARRAY の要素を含む ARRAY をサポートしていないことを示すエラーを返します。
  • サブクエリがゼロ行を返す場合、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
  • 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] |
+--------------------------------------------------------------+

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

日付関数

Cloud Spanner SQL は、次の DATE 関数をサポートしています。

CURRENT_DATE

CURRENT_DATE([time_zone])

説明

指定した、またはデフォルトのタイムゾーンの今現在の日付を返します。

この関数では、省略可能な time_zone パラメータを使用できます。このパラメータは、使用するタイムゾーンを示す文字列です。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの America/Los_Angeles が使用されます。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

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 週目です。
  • 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    |
+------------+---------+---------+------+------+

日付

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

説明

  1. 年、月、日を表す DATE を INT64 値から構築します。
  2. TIMESTAMP 式から DATE を抽出します。タイムゾーンを指定する省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの America/Los_Angeles が使用されます。

戻りデータの型

DATE

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

説明

指定した時間間隔を DATE に追加します。

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

  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATE

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

説明

DATE から指定した期間を減算します。

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

  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATE

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

説明

2 つの DATE オブジェクト(date_expression_adate_expression_b)間に存在する、全体が指定された date_part の間隔を示す数を返します。最初の DATE が 2 番目のオブジェクトよりも前の場合、出力は負になります。

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

  • DAY
  • WEEK: この日付パーツは日曜日から始まります。
  • 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 を返します。これは、この日付部分が日曜日から始まる週を使用するためです。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', ISOWEEK) AS isoweek_diff;

+-----------+--------------+
| week_diff | isoweek_diff |
+-----------+--------------+
| 0         | 1            |
+-----------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

説明

指定した粒度まで日付を切り詰めます。

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

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

タイムスタンプ関数

Cloud Spanner SQL では次の TIMESTAMP 関数がサポートされています。

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

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

説明

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

サポートされる入力型

該当なし

結果のデータ型

TIMESTAMP

SELECT CURRENT_TIMESTAMP() as now;

+--------------------------------+
| now                            |
+--------------------------------+
| 2020-06-02T23:58:40.347847393Z |
+--------------------------------+

EXTRACT

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

説明

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

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

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。
  • ISOWEEK: ISO 8601 形式datetime_expression の週番号を返します。ISOWEEK は月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初の ISOWEEK は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 形式の週番号が付いた年を返します。これは date_expression が属している週の木曜日が含まれるグレゴリオ暦年です。
  • DATE

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

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

戻りデータの型

次の例外の場合以外は INT64。

  • partDATE であり、DATE オブジェクトを返します。

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

WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input

+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25          | 24                 |
+-------------+--------------------+

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

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+---------+---------+------+------+
| timestamp_value        | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03T12:34:56Z   | 2005    | 1       | 2005 | 1    |
| 2007-12-31T12:00:00Z   | 2008    | 1       | 2007 | 52   |
| 2009-01-01T12:00:00Z   | 2009    | 1       | 2009 | 0    |
| 2009-12-31T12:00:00Z   | 2009    | 53      | 2009 | 52   |
| 2017-01-02T12:00:00Z   | 2017    | 1       | 2017 | 1    |
| 2017-05-26T12:00:00Z   | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

STRING

STRING(timestamp_expression[, timezone])

説明

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

戻りデータの型

STRING

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;

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

TIMESTAMP

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

説明

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

この関数では、タイムゾーンを指定する省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの America/Los_Angeles が使用されます。

戻りデータの型

TIMESTAMP

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T23:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T08:00:00Z |
+----------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

説明

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

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

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

戻りデータの型

TIMESTAMP

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:40:00Z   |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

説明

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

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

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

戻りデータの型

TIMESTAMP

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:20:00Z   |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)

説明

2 つの TIMESTAMP オブジェクト(timestamp_expression_atimestamp_expression_b)間に存在する、全体が指定された date_part の間隔を示す数を返します。最初の TIMESTAMP が 2 番目のオブジェクトよりも前の場合、出力は負になります。2 つの TIMESTAMP オブジェクト間のナノ秒単位の差異が INT64 値をオーバーフローする場合など、計算が結果の型をオーバーフローする場合はエラーをスローします。

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

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

戻りデータの型

INT64

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07T10:20:00Z   | 2008-12-25T15:30:00Z   | 13410 |
+------------------------+------------------------+-------+

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

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

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

この例では、全体が指定された HOUR 間隔の数のみが含まれるため、結果は 0 になります。

SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR)

+---------------+
| negative_diff |
+---------------+
| 0             |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

説明

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

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

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • 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
  • MONTH
  • QUARTER
  • YEAR

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

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

戻りデータの型

TIMESTAMP

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25T00:00:00Z   | 2008-12-25T08:00:00Z   |
+------------------------+------------------------+

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

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+----------------+
| parsed                 | isoyear_number |
+------------------------+----------------+
| 2014-12-29T08:00:00Z   | 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+00", "UTC") AS formatted;

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

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

戻りデータの型

TIMESTAMP

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| parsed                 |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

説明

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

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

説明

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

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

説明

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

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

説明

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

戻りデータの型

INT64

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") 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+00") 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+00") AS micros;

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

PENDING_COMMIT_TIMESTAMP

PENDING_COMMIT_TIMESTAMP()

説明

DML の INSERT または UPDATE ステートメントで PENDING_COMMIT_TIMESTAMP() 関数を使用して、保留中の commit タイムスタンプ、つまり commit される時点での書き込みの commit タイムスタンプを TIMESTAMP 型の列に書き込みます。

トランザクションが commit されると、Cloud Spanner SQL が commit タイムスタンプを選択します。PENDING_COMMIT_TIMESTAMP 関数は、適切に型指定された列の INSERT または UPDATE の値としてのみ使用できます。SELECT では使用できません。また、他のスカラー式への入力としても使用できません。

戻りデータの型

TIMESTAMP

次の DML ステートメントは、commit タイムスタンプで Singers テーブルの LastUpdated 列を更新します。

UPDATE Performances SET LastUpdated = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

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 のいずれか。
%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;

演算子

演算子は特殊文字やキーワードによって表されます。関数の呼び出し構文は使用しません。また、演算子は、オペランドとも呼ばれる任意の数のデータ入力を操作し、結果を返します。

一般的な慣例:

  • 特記のない限り、オペランドの 1 つが NULL の場合、すべての演算子は NULL を返します。
  • 演算結果がオーバーフローした場合、すべての演算子は、エラーをスローします。
  • 浮動小数点演算で、オペランドの 1 つが +/-inf または NaN の場合、+/-inf または NaN が返されます。それ以外の場合には、エラーが返されます。

次の表には、すべての Cloud Spanner SQL 演算子を最も優先順位の高い方から最も低い方に、つまり、それらがステートメント内で評価される順序で表示しています。

優先順位 演算子 入力データ型 名前 演算子の項数
1 . STRUCT
メンバーのフィールド アクセス演算子 2 項
  [ ] ARRAY 配列位置。OFFSET または ORDINAL とともに使用する必要があります。ARRAY 関数を参照。 2 項
2 - すべての数値型 単項マイナス 単項
  ~ 整数または BYTES ビット演算 NOT 単項
3 * すべての数値型 乗算 2 項
  / すべての数値型 除算 2 項
  || STRING、BYTES、または ARRAY<T> 連結演算子 2 項
4 + すべての数値型 加算 2 項
  - すべての数値型 減算 2 項
5 << 整数または BYTES ビット演算左シフト 2 項
  >> 整数または BYTES ビット演算右シフト 2 項
6 整数または BYTES ビット演算 AND 2 項
7 ^ 整数または BYTES ビット演算 XOR 2 項
8 | 整数または BYTES ビット演算 OR 2 項
9(比較演算子) = 任意の比較可能型。全リストについてはデータ型を参照。 等しい 2 項
  < 任意の比較可能型。全リストについてはデータ型を参照。 より小さい 2 項
  > 任意の比較可能型。全リストについてはデータ型を参照。 より大きい 2 項
  <= 任意の比較可能型。全リストについてはデータ型を参照。 以下 2 項
  >= 任意の比較可能型。全リストについてはデータ型を参照。 以上 2 項
  !=, <> 任意の比較可能型。全リストについてはデータ型を参照。 等しくない 2 項
  [NOT] LIKE STRING とバイト 値が指定されたパターンと一致 [しない] 2 項
  [NOT] BETWEEN 互換性のある任意の型。全リストについてはデータ型を参照。 値が指定された範囲内にある [ない]。 2 項
  [NOT]IN 互換性のある任意の型。全リストについてはデータ型を参照。 値が指定された値のセットにある [ない]。 2 項
  IS [NOT] NULL すべて 値が NULL である [ない] 単項
  IS [NOT] TRUE BOOL 値が TRUE である [ない]。 単項
  IS [NOT] FALSE BOOL 値が FALSE である [ない]。 単項
10 NOT BOOL 論理 NOT 単項
11 AND BOOL 論理 AND 2 項
12 OR BOOL 論理 OR 2 項

優先順位が同じ演算子は左結合です。つまり、左から右に向かってグループ化されます。たとえば、次の式があるとします。

x AND y AND z

これは、次のように解釈されます。

( ( x AND y ) AND z )

次の式があるとします。

x * y / z

これは、次のように解釈されます。

( ( x * y ) / z )

すべての比較演算子の優先順位は同じですが、比較演算子は非結合です。したがって、あいまいさを解消するには、かっこが必要です。例:

(x < y) IS FALSE

要素アクセス演算子

演算子 構文 入力データ型 結果のデータ型 説明
. expression.fieldname1... STRUCT
fieldname1 に格納された型 T ドット演算子。ネストされたフィールドにアクセスするために使用できます。例: expression.fieldname1.fieldname2...
[ ] array_expression [position_keyword (int_expression ) ] ARRAY 関数を参照してください。 ARRAY に保存された型 T position_keyword は、OFFSET または ORDINAL のいずれかです。この演算子を使用する 2 つの関数については、ARRAY 関数をご覧ください。

算術演算子

以下の説明に示されていない限り、すべての算術演算子で数値型 T が受け入れられ、結果の型には型 T が含まれます。

名前 構文
加算 X + Y
減算 X - Y
乗算 X * Y
除算 X / Y
単項マイナス - X

注: ゼロ除算はエラーを返します。異なる結果を取得するには、IEEE_DIVIDE または SAFE_DIVIDE 関数を使用してください。

加算と乗算の結果の型:

 INT64FLOAT64
INT64INT64FLOAT64
FLOAT64FLOAT64FLOAT64

減算の結果の型:

 INT64FLOAT64
INT64INT64FLOAT64
FLOAT64FLOAT64FLOAT64

除算の結果の型:

 INT64FLOAT64
INT64FLOAT64FLOAT64
FLOAT64FLOAT64FLOAT64

単項マイナスの場合の結果のデータ型:

入力データ型 結果のデータ型
INT64 INT64
FLOAT64 FLOAT64

ビット演算子

すべてのビット演算子は、第 1 オペランドと同じ型と同じ長さを返します。

名前 構文 入力データ型 説明
ビット演算 NOT ~ X 整数または BYTES 与えられた変数値の補数を形成し、各ビットの論理否定を実行します。
ビット演算 OR X | Y X : 整数または BYTES
Y : X と同じ型
同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに論理包含的和演算を実行します。X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。
ビット演算 XOR X ^ Y X : 整数または BYTES
Y : X と同じ型
同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに排他的論理和演算を実行します。 X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。
ビット演算 AND X & Y X : 整数または BYTES
Y : X と同じ型
同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに論理 AND 演算を実行します。 X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。
左シフト X << Y X : 整数または BYTES
Y : INT64
第 1 オペランド X を左方にシフトします。第 2 オペランド Y が第 1 オペランド X のビット長(たとえば X が INT64 型の場合は 64)以上である場合、この演算子は 0 または b'\x00' バイト シーケンスを返します。Y が負の数の場合はエラーを返します。
右シフト X >> Y X : 整数または BYTES
Y : INT64
第 1 オペランド X を右方にシフトします。この演算子は、符号付きの型で符号ビット拡張を行いません(左側の空いたビットを 0 で埋めます)。第 2 オペランド Y が第 1 オペランド X のビット長(たとえば X が INT64 型の場合は 64)以上である場合、この演算子は 0 または b'\x00' バイト シーケンスを返します。Y が負の数の場合はエラーを返します。

論理演算子

Cloud Spanner SQL は、ANDORNOT の論理演算子をサポートしています。論理演算子は BOOL 入力または NULL 入力のみを許可し、3 値論理を使用して結果を生成します。結果は TRUEFALSENULL のいずれかになります。

x y x AND y x OR y
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL TRUE NULL TRUE
NULL FALSE NULL NULL
NULL NULL NULL NULL
x NOT x
TRUE FALSE
FALSE TRUE
NULL NULL

このセクションの例では、entry_table というテーブルを参照しています。

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

比較演算子

比較演算子は常に BOOL を返します。通常、両方のオペランドが同じ型である必要があります。オペランドの型が異なり、かつ Cloud Spanner SQL で精度が失われることなくこれらの型の値を共通の型に変換できる場合、一般的に Cloud Spanner SQL ではそれらをその共通の型に強制的に変換して、比較します。通常は、リテラルから非リテラルへの強制型変換を行います(この型変換が存在する場合)。比較可能なデータ型はデータ型に定義されています。

STRUCT は、4 つの比較演算子(等号 =、不等号 != と <>、IN)のみサポートします。

これらのデータ型を比較するときに、次の規則が適用されます。

  • FLOAT64: NaN との比較演算は常に FALSE を返す。ただし、!=<> は TRUE を返します。
  • BOOL: FALSE は TRUE よりも小さい。
  • STRING: 文字列はコードポイントごとに比較される。つまり、正規的に同等の文字列は、それらが最初に正規化されている場合にのみ等しいと比較されることが保証されています。
  • NULL: 変換がここで停止する。NULL 入力を持つ演算はすべて NULL を返します。
名前 構文 説明
より小さい X < Y X が Y よりも小さい場合に TRUE を返します。
以下 X <= Y X が Y 以下の場合、TRUE を返します。
より大きい X > Y X が Y よりも大きい場合、TRUE を返します。
以上 X >= Y X が Y 以上の場合、TRUE を返します。
等しい X = Y X が Y と等しい場合、TRUE を返します。
等しくない X != Y
X <> Y
X が Y と等しくない場合、TRUE を返します。
BETWEEN X [NOT] BETWEEN Y AND Z X が指定された範囲内に [ない] 場合、TRUE を返します。「X BETWEEN Y AND Z」の結果は「Y <= X AND X <= Z」と等価ですが、前者では X は一度しか評価されません。
LIKE X [NOT] LIKE Y 第 1 オペランド X の文字列が第 2 オペランド Y によって指定されたパターンと一致するかどうかをチェックします。式には次の文字を含めることができます。
  • パーセント記号「%」は、任意の数の文字またはバイトと一致します。
  • アンダースコア「_」は、単一の文字またはバイトと一致します。
  • 「\」、「_」 または「%」は、2 つのバックスラッシュを使用してエスケープできます。たとえば "\\%" です。文字列をそのまま使用する場合には、バックスラッシュを 1 つだけ使用します。例: r"\%"
IN 複数 - 以下を参照 右オペランドが空の場合、FALSE を返します。左のオペランドが NULL である場合、NULL を返します。右のオペランドに NULL が含まれている場合、TRUE または NULL を返します(FALSE を返すことはない)。IN のいずれかの側の引数は、一般式です。どちらのオペランドもリテラルにする必要はありませんが、右側にリテラルを使用するのが一般的です。X は一度だけ評価されます。

STRUCT データ型の値が等価かどうかテストする場合、1 つ以上のフィールドが NULL になる場合があります。この場合、比較結果は次のようになります。

  • NULL 以外のフィールドがすべて等しい場合、NULL を返します。
  • NULL 以外のフィールドが等しくない場合、false を返します。

次の表は、STRUCT データ型が NULL と評価されるフィールドを持つ場合にどのように比較されるかを示しています。

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

IN 演算子

IN 演算子は、次の構文をサポートします。

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

IN 演算子いずれかの側の引数は、一般式です。右側の式にリテラルを使用するのが一般的ですが、必須ではありません。

次のセマンティックがあるとします。

x IN (y, z, ...)

これは、次と等価の定義になります。

(x = y) OR (x = z) OR ...

また、サブクエリと配列の形式は同様に定義されます。

x NOT IN ...

これは次と同等です。

NOT(x IN ...)

UNNEST 形式は、FROM 句に含まれる UNNEST のような方法で配列スキャンを扱います。

x [NOT] IN UNNEST(<array expression>)

この形式は、ARRAY パラメータとともに使用されることがよくあります。例:

x IN UNNEST(@array_parameter)

注: NULL ARRAY は空の ARRAY と同等に扱われます。

この構文の使用方法の詳細については、配列トピックをご覧ください。

IN 演算子を使用している場合、以下のセマンティクスが適用されます。

  • 空の右辺式を持つ IN は常に FALSE です。
  • NULL の左辺式を持ち、空ではない右辺式を持つ IN は常に NULL です
  • IN リスト内に NULL を持つ IN は TRUE または NULL のみを返すことができます。FALSE は返されません
  • NULL IN (NULL)NULL を返します。
  • IN UNNEST(<NULL array>) は FALSE を返します(NULL ではない)。
  • IN リスト内に NULL を持つ NOT IN は、FALSE または NULL のみを返すことができます。TRUE は返されません。

IN は STRUCT のコンストラクタ構文を使用してマルチパート キーとともに使用できます。例:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

この構文の詳細については、データ型トピックの Struct 型セクションをご覧ください。

IS 演算子

IS 演算子は、テストしている条件に関して TRUE または FALSE を返します。数学関数で定義している IS_INF 関数や IS_NAN 関数とは異なり、入力が NULL であっても、この演算子が NULL を返すことはありません。NOT がある場合、出力 BOOL 値は反転されます。

関数の構文 入力データ型 結果のデータ型 説明

X IS [NOT] NULL
任意の値の型 BOOL オペランド X が NULL に評価された場合は TRUE を返し、それ以外の場合は FALSE を返します。

X IS [NOT] TRUE
BOOL BOOL BOOL オペランドが TRUE に評価された場合は TRUE を返し、それ以外の場合は FALSE を返します。

X IS [NOT] FALSE
BOOL BOOL BOOL オペランドが FALSE と評価された場合は TRUE を返します。それ以外の場合は FALSE を返します。

連結演算子

連結演算子は、複数の値を 1 つに結合します。

関数の構文 入力データ型 結果のデータ型

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>

条件式

条件式は、入力の評価順序に制約を課します。基本的に、これらは左辺から右辺にかけて短絡評価され、選択された出力値のみを評価します。対照的に、正規関数のすべての入力は、関数を呼び出す前に評価されます。条件式での短絡は、エラー処理やパフォーマンス チューニングに利用できます。

CASE expr

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
END

説明

expr と、次に続く各 WHEN 句の expr_to_match を比較し、この比較で true が返される最初の結果を返します。残りの WHEN 句と else_result は評価されません。expr = expr_to_match 比較ですべての WHEN 句に関して FALSE または NULL が返される場合、else_result が存在する場合はそれを返し、存在しない場合は NULL を返します。

exprexpr_to_match は任意の型です。これらは、共通するスーパータイプに暗黙的に強制型変換できる必要があります。等価比較は強制変換された値に対して行われます。result 型は複数ある場合があります。result および else_result 式を共通のスーパータイプに強制的に型変換できる必要があります。

戻りデータの型

result[, ...] と else_result のスーパータイプ。

WITH Numbers AS
 (SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 8 UNION ALL
  SELECT 60, 6 UNION ALL
  SELECT 50, 10)
SELECT A, B,
  CASE A
    WHEN 90 THEN 'red'
    WHEN 50 THEN 'blue'
    ELSE 'green'
  END
  AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 8  | blue   |
| 60 | 6  | green  |
| 50 | 10 | blue   |
+------------------+

ケース

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
  END

説明

後に続く各 WHEN 句の条件を評価し、条件が true である最初の結果を返します。残りの WHEN 句と else_result は評価されません。すべての条件が false または NULL の場合、else_result が存在すればそれが返されます。存在しない場合は NULL が返されます。

condition はブール式でなければなりません。result 型は複数ある場合があります。result および else_result 式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。

戻りデータの型

result[, ...] と else_result のスーパータイプ。

WITH Numbers AS
 (SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 6 UNION ALL
  SELECT 20, 10)
SELECT A, B,
  CASE
    WHEN A > 60 THEN 'red'
    WHEN A > 30 THEN 'blue'
    ELSE 'green'
  END
  AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 6  | blue   |
| 20 | 10 | green  |
+------------------+

COALESCE

COALESCE(expr[, ...])

説明

最初の非 NULL 式の値を返します。残りの式は評価されません。入力式には任意の型を指定できます。複数の入力式の型がある場合があります。すべての入力式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。

戻りデータの型

expr [, ...] のスーパータイプ。

SELECT COALESCE('A', 'B', 'C') as result

+--------+
| result |
+--------+
| A      |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result

+--------+
| result |
+--------+
| B      |
+--------+

IF

IF(expr, true_result, else_result)

説明

expr が true の場合は true_result を返し、それ以外の場合は else_result を返します。expr が true の場合、else_result は評価されません。expr が false または NULL の場合、true_result は評価されません。

expr はブール式でなければなりません。true_resultelse_result は、共通のスーパータイプへの強制型変換が可能でなければなりません。

戻りデータの型

true_resultelse_result のスーパータイプ。

WITH Numbers AS
 (SELECT 10 as A, 20 as B UNION ALL
  SELECT 50, 30 UNION ALL
  SELECT 60, 60)
SELECT
  A, B,
  IF( A<B, 'true', 'false') as result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 10 | 20 | true   |
| 50 | 30 | false  |
| 60 | 60 | false  |
+------------------+

IFNULL

IFNULL(expr, null_result)

説明

expr が NULL の場合、null_result を返します。それ以外の場合は、expr を返します。 expr が NULL でない場合、null_result は評価されません。

exprnull_result には任意の型を指定でき、共通のスーパータイプに暗黙的に強制型変換する必要があります。COALESCE(expr, null_result) と同義です。

戻りデータの型

expr または null_result のスーパータイプ。

SELECT IFNULL(NULL, 0) as result

+--------+
| result |
+--------+
| 0      |
+--------+
SELECT IFNULL(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

NULLIF

NULLIF(expr, expr_to_match)

説明

expr = expr_to_match が true の場合は NULL を返し、それ以外の場合は expr を返します。

exprexpr_to_match は、共通のスーパータイプに暗黙的に強制型変換できる必要があります。また、比較可能型にする必要があります。

NULLIFSTRUCT 型をサポートしていません。

戻りデータの型

exprexpr_to_match のスーパータイプ。

SELECT NULLIF(0, 0) as result

+--------+
| result |
+--------+
| NULL   |
+--------+
SELECT NULLIF(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

式のサブクエリ

4 種類の式サブクエリ、つまり、式として使用されるサブクエリがあります。式サブクエリは、列または表とは対照的に、NULL または単一の値を返します。また、式サブクエリをかっこで囲む必要があります。サブクエリの詳細については、サブクエリをご覧ください。

サブクエリの型 結果のデータ型 説明
スカラー 任意の型 T 式でかっこに囲まれているサブクエリ(SELECT リストや WHERE 句内など)は、スカラー サブクエリとして解釈されます。スカラー サブクエリ内の SELECT リストのフィールド数は、正確に 1 つである必要があります。サブクエリによって正確に 1 行が返される場合、その単一の値がスカラー サブクエリの結果になります。サブクエリがゼロ個の行を返す場合、スカラー サブクエリ値は NULL です。サブクエリが複数の行を返す場合、クエリはランタイム エラーで失敗します。サブクエリが SELECT AS STRUCT で作成される場合、複数の列を含めることができ、戻り値は構造化された STRUCT になります。SELECT AS を使用せずに複数の列を選択すると、エラーが発生します。
ARRAY ARRAY SELECT AS STRUCT を使用して STRUCT の配列を作成できます。SELECT AS を使用せずに複数の列を選択すると、エラーが発生します。サブクエリがゼロ個の行を返す場合、空の ARRAY が返されます。NULL ARRAY が返されることはありません。
IN BOOL IN 演算子に続く式で使用されます。サブクエリによって、IN 演算子の左側の式と等式互換性がある型の単一の列を生成する必要があります。サブクエリによってゼロ個の行が返される場合、FALSE が返されます。x IN ()x IN (value, value, ...) と同等です。完全なセマンティクスについては、比較演算子IN 演算子をご覧ください。
EXISTS BOOL サブクエリによって 1 つ以上の行が生成される場合、TRUE が返されます。サブクエリによってゼロ個の行が生成される場合、FALSE が返されます。NULL が返されることはありません。その他のすべての式サブクエリとは異なり、列リストに関する規則はありません。任意の数の列を選択でき、クエリの結果に影響はありません。

次の式サブクエリの例では、t.int_array の型が ARRAY<INT64> であることを前提としています。

サブクエリ 結果のデータ型 備考
スカラー (SELECT COUNT(*) FROM t.int_array) INT64  
(SELECT DISTINCT i FROM t.int_array i) INT64、場合によりランタイム エラー  
(SELECT i FROM t.int_array i WHERE i=5) INT64、場合によりランタイム エラー  
(SELECT ARRAY_AGG(i) FROM t.int_array i) ARRAY ARRAY_AGG 集計関数を使用して、ARRAY を返します。
(SELECT 'xxx' a) STRING  
(SELECT 'xxx' a, 123 b) Error 複数の列があるためエラーを返します。
(SELECT AS STRUCT 'xxx' a, 123 b) STRUCT  
(SELECT AS STRUCT 'xxx' a) STRUCT  
ARRAY ARRAY(SELECT COUNT(*) FROM t.int_array) サイズ 1 の ARRAY  
ARRAY(SELECT x FROM t) ARRAY  
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) Error 複数の列があるためエラーを返します。
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) ARRAY  
ARRAY(SELECT AS STRUCT i FROM t.int_array i) ARRAY 1 フィールドの STRUCT の ARRAY を作成します
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) ARRAY 無記名または重複フィールドを持った STRUCT の ARRAY を返します。
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) array<TypeName> 名前付きの型を選択します。TypeName はフィールド a、b、c を持つ STRUCT 型であると見なします。
STRUCT (SELECT AS STRUCT 1 x, 2, 3 x) STRUCT 無記名または重複フィールドを持つ STRUCT を構築します。
EXISTS EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
IN x IN (SELECT y FROM table WHERE z) BOOL  
x NOT IN (SELECT y FROM table WHERE z) BOOL  

デバッグ関数

Cloud Spanner SQL では、次のデバッグ関数がサポートされています。

ERROR

ERROR(error_message)

説明

エラーを返します。error_message 引数は STRING です。

Cloud Spanner SQL は、エラーが発生する可能性のある式と同じ方法で ERROR を処理します。評価順序の特別な保証はありません。

戻りデータの型

Cloud Spanner SQL によってコンテキスト内で戻り値の型が推論されます。

次の例では、行の値が 2 つの定義された値のいずれかと一致しない場合、クエリはエラー メッセージを返します。

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

次の例では、Cloud Spanner SQL は ERROR 関数を x > 0 条件の前に評価する場合もあれば、後に評価する場合もあります。これは、Cloud Spanner SQL では通常、WHERE 句の条件間の順序が保証されず、ERROR 関数に対する特別な保証もないためです。

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

次の例では、WHERE 句で IF 条件が評価されるため、Cloud Spanner SQL は、条件が失敗した場合にのみ ERROR 関数を評価します。

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1