このページでは、関数と演算子を含む 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 を使用します。ただし、IN、ARRAY、UNNEST、およびこれに類似する関数などの演算子は、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 NUMERIC FLOAT64 STRING |
NUMERIC FLOAT64 |
| NUMERIC | INT64 NUMERIC FLOAT64 STRING |
FLOAT64 |
| FLOAT64 | INT64 NUMERIC FLOAT64 STRING |
|
| BOOL | BOOL INT64 STRING |
|
| STRING | BOOL INT64 NUMERIC FLOAT64 STRING BYTES DATE TIMESTAMP |
|
| BYTES | STRING BYTES |
|
| 日付 | STRING DATE TIMESTAMP |
|
| TIMESTAMP | STRING DATE TIMESTAMP |
|
| ARRAY | ARRAY | |
| STRUCT | STRUCT |
キャスティング
構文:
CAST(expr AS typename)
キャスト構文は、式の結果の型を他の型に変換する必要があることを示す目的でクエリで使用されます。
例:
CAST(x=1 AS STRING)
これは、x が 1 の場合は "true"、その他の非 NULL 値の場合は "false"、x が NULL の場合は NULL になります。
サポートされる型の間でキャストするとき、元の値からターゲット ドメインに正常にマッピングされない場合は、ランタイム エラーが発生します。たとえば、バイト列が有効な UTF-8 ではない場合に BYTES を STRING キャスティングするとランタイム エラーが発生します。
次の型の x 式をキャスティングする場合、以下の規則が適用されます。
| 型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
|---|---|---|
| INT64 | FLOAT64 | 近似値だが厳密には正確でない可能性のある FLOAT64 値を返します。 |
| INT64 | BOOL | x が 0 の場合は FALSE、それ以外の場合は TRUE を返します。 |
| NUMERIC | 浮動小数点 | NUMERIC は最も近い浮動小数点数に変換されますが、精度が低下する可能性があります。 |
| FLOAT64 | INT64 | 最も近い INT64 値を返します。 1.5 や -0.5 などの中間値の場合は、ゼロから離れるように丸められます。 |
| FLOAT64 | STRING | 近似する文字列表現を返します。 |
| FLOAT64 | NUMERIC | 浮動小数点数はゼロから遠ざかるように丸められます。NaN、+inf、-inf をキャストするとエラーが返されます。NUMERIC の範囲外の値をキャストすると、オーバーフロー エラーが返されます。 |
| BOOL | INT64 | x が TRUE の場合は 1、それ以外の場合は 0 を返します。
|
| BOOL | STRING | x が TRUE の場合は "true"、それ以外の場合は "false" を返します。
|
| STRING | FLOAT64 | 有効な FLOAT64 リテラルと同じ形式を持つと解釈して、x を FLOAT64 値として返します。また、 "inf"、"+inf"、"-inf"、"nan" からのキャストをサポートします。
変換では大文字と小文字を区別しません。 |
| STRING | NUMERIC | STRING に含まれる数値リテラルは、NUMERIC 型の最大精度や範囲を超えてはなりません。超えた場合はエラーが発生します。小数点以下が 10 桁以上の場合、キャスト後の NUMERIC 値は小数点以下が 9 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。 |
| STRING | BOOL | x が "true" の場合は TRUE を、x が "false" の場合は FALSE を返します。x のそれ以外の値はすべて無効であり、BOOL にキャスティングせずにエラーをスローします。BOOL に変換するときに、STRING では大文字と小文字が区別されません。 |
| STRING | BYTES | STRING は UTF-8 エンコーディングを使用して BYTES にキャストされます。たとえば、STRING "©" は BYTES にキャストされると、16 進数 C2 と A9 を持つ 2 バイト数列になります。 |
| BYTES | STRING | UTF-8 STRING と解釈される x を返します。たとえば BYTES リテラル b'\xc2\xa9' の場合、STRING にキャストすると UTF-8 として解釈され、Unicode 文字「©」になります。x が有効な UTF-8 でない場合、エラーが発生します。 |
| ARRAY | ARRAY | 正確に同じ ARRAY 型になっている必要があります。 |
| STRUCT | STRUCT | 次の条件が満たされる場合に許可されます。
|
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 つの値に集約する関数です。COUNT、MIN、MAX などが集計関数の例として挙げられます。
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 つの行が存在します。関連付けられた SELECT に GROUP BY 句がない場合、またはある集計関数の修飾子によって集約するグループの行がフィルタされる場合、集計関数は空のグループを集約することを必要とする可能性があります。この場合、COUNT 関数と COUNTIF 関数は 0 を返し、他のすべての集計関数は NULL を返します。
次のセクションでは、Cloud Spanner SQL でサポートされている集計関数について説明します。
ANY_VALUE
ANY_VALUE(expression [HAVING {MAX | MIN} expression2])
説明
グループから選択された行の expression を返します。どの行が選択されるかは非決定的で、ランダムではありません。入力によって行が生成されない場合、NULL を返します。グループ内のすべての行で expression が NULL の場合、NULL を返します。
ANY_VALUE は、IGNORE NULLS が指定されているかのように動作します。expression が NULL である行は考慮されず、そのような行は選択されません。
サポートされる引数の型
すべて
省略可能な句
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 を除くすべてのデータ型。
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。IGNORE NULLSまたはRESPECT NULLS:IGNORE NULLSを指定した場合、NULL値は結果から除外されます。RESPECT NULLSを指定した場合、あるいは両方とも指定しない場合、結果にNULL値が含まれます。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 のような、任意の数値入力型。浮動小数点入力型の場合、戻り値の結果は変動する可能性があります、つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。HAVING MAXまたはHAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。
返されるデータ型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | FLOAT64 | NUMERIC | FLOAT64 |
例
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
+-----+
| avg |
+-----+
| 3 |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
+------+
| avg |
+------+
| 2.75 |
+------+
BIT_AND
BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])
説明
expression でビット積演算を実行し、結果を返します。
サポートされる引数の型
- INT64
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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])
説明
- 入力内の行数を返します。
expressionがNULL以外の値と評価された行の数を返します。
サポートされる引数の型
expression には任意のデータ型を指定できます。DISTINCT が存在する場合、expression にはグループ化可能なデータ型のみ指定できます。
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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 を返します。
サポートされる引数の型
ARRAY、STRUCT を除くすべてのデータ型
省略可能な句
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 を返します。
サポートされる引数の型
ARRAY、STRUCT を除くすべてのデータ型
省略可能な句
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
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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 以外の値の合計を返します。
式が浮動小数点値である場合、合計は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。
サポートされる引数の型
サポートされているすべての数値データ型。
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。HAVING MAXまたはHAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | INT64 | NUMERIC | FLOAT64 |
特殊ケース:
入力が NULL のみの場合、NULL を返します。
入力に行が含まれていない場合、NULL を返します。
入力に Inf が含まれている場合、Inf を返します。
入力に -Inf が含まれている場合、-Inf を返します。
入力に NaN が含まれている場合、NaN を返します。
入力に Inf と -Inf の組み合わせが含まれている場合、NaN を返します。
例
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 25 |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 15 |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
+------+
| sum |
+------+
| NULL |
+------+
共通の句
HAVING MAX 句と HAVING MIN 句
ほとんどの集計関数は、HAVING MAX、HAVING MIN という任意の 2 つの句をサポートしています。これにより、関数が集計する行のセットを、特定の列に最大値または最小値を持つ行に制限します。構文は通常、次のようになります。
aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
HAVING MAX: 関数が集計する行のセットを、グループ内のexpression2の最大値と等しいexpression2の値を持つ行に制限します。最大値はMAX(expression2)の結果と同じです。HAVING MIN: 関数が集計する行のセットを、グループ内のexpression2の最小値と等しいexpression2の値を持つ行に制限します。最小値はMIN(expression2)の結果と同じです。
これらの句では、すべての行に対して expression2 が NULL と評価されない限り、最大値または最小値を計算する際には、NULL 値は無視されます。
これらの句は、データ型 ARRAY と STRUCT をサポートしていません。
例
この例では、直近の 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 の間になります。
すべての数値型がサポートされています。入力が NUMERIC の場合、内部集計は、FLOAT64 に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64 に変換され、結果が不安定になる可能性があります。
この関数は NULL 入力を無視します。NULL 以外の入力が 2 つ未満の場合、この関数は NULL を返します。
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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 の間になります。
すべての数値型がサポートされています。入力が NUMERIC の場合、内部集計は、FLOAT64 に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64 に変換され、結果が不安定になる可能性があります。
この関数は NULL 入力を無視します。NULL 以外の入力が 2 つ未満の場合、この関数は NULL を返します。
省略可能な句
次の順番で句が適用されます。
DISTINCT:expressionの重複を除いた値を集約して結果を返します。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)
説明
絶対値を計算します。引数が整数で、出力値を同じ型で表せない場合、エラーを返します（これは、対応する正の表現がない、負の最大値が入力されたときにのみ発生します）。
| X | ABS(X) |
|---|---|
| 25 | 25 |
| -25 | 25 |
+inf |
+inf |
-inf |
+inf |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | INT64 | NUMERIC | FLOAT64 |
SIGN
SIGN(X)
説明
負、ゼロ、正の引数に対してそれぞれ -1、0、+1 を返します。浮動小数点引数では正と負のゼロは区別されません。
| X | SIGN(X) |
|---|---|
| 25 | +1 |
| 0 | 0 |
| -25 | -1 |
| NaN | NaN |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | INT64 | NUMERIC | FLOAT64 |
IS_INF
IS_INF(X)
説明
値が正または負の無限大の場合に TRUE を返します。
NUMERIC を INF にすることはできないため、NUMERIC 入力に対して FALSE を返します。
| X | IS_INF(X) |
|---|---|
+inf |
TRUE |
-inf |
TRUE |
| 25 | FALSE |
IS_NAN
IS_NAN(X)
説明
値が NaN 値である場合、TRUE を返します。
NUMERIC を NaN にすることはできないため、NUMERIC 入力に対して FALSE を返します。
| X | IS_NAN(X) |
|---|---|
NaN |
TRUE |
| 25 | FALSE |
IEEE_DIVIDE
IEEE_DIVIDE(X, Y)
説明
X を Y で除算します。この関数が失敗することはありません。FLOAT64 を返します。除算演算子（/）とは異なり、この関数はゼロまたはオーバーフローによる除算エラーを生成しません。
| X | ○ | IEEE_DIVIDE(X, Y) |
|---|---|---|
| 20.0 | 4.0 | 5.0 |
| 0.0 | 25.0 | 0.0 |
| 25.0 | 0.0 | +inf |
| -25.0 | 0.0 | -inf |
| 0.0 | 0.0 | NaN |
| 0.0 | NaN |
NaN |
NaN |
0.0 | NaN |
+inf |
+inf |
NaN |
-inf |
-inf |
NaN |
SQRT
SQRT(X)
説明
X の平方根を計算します。X が 0 未満の場合にエラーを生成します。
| X | SQRT(X) |
|---|---|
25.0 |
5.0 |
+inf |
+inf |
X < 0 |
エラー |
戻りデータの型
NUMERIC は、入力として直接サポートされていません。まず、明示的に NUMERIC を FLOAT64 にキャストする必要があります。出力は FLOAT64 になります。
POW
POW(X, Y)
説明
X を Y 乗した値を返します。結果がアンダーフローし、表現できない場合、値 0 を返します。
| X | ○ | POW(X, Y) |
|---|---|---|
| 2.0 | 3.0 | 8.0 |
| 1.0 | NaN を含む任意の値 |
1.0 |
NaN を含む任意の値 |
0 | 1.0 |
| -1.0 | +inf |
1.0 |
| -1.0 | -inf |
1.0 |
| ABS(X) < 1 | -inf |
+inf |
| ABS(X) > 1 | -inf |
0.0 |
| ABS(X) < 1 | +inf |
0.0 |
| ABS(X) > 1 | +inf |
+inf |
-inf |
Y < 0 | 0.0 |
-inf |
Y > 0 | Y が奇数の場合は -inf、それ以外は +inf |
+inf |
Y < 0 | 0 |
+inf |
Y > 0 | +inf |
| 有限値 < 0 | 非整数 | エラー |
| 0 | 有限値 < 0 | エラー |
戻りデータの型
戻りデータの型は、次の表にある引数の型によって決まります。
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | FLOAT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
POWER
POWER(X, Y)
説明
POW(X, Y) と同等です。
EXP
EXP(X)
説明
e の X 乗を計算します（自然指数関数）。結果がアンダーフローする場合、ゼロを返します。結果がオーバーフローする場合は、エラーを生成します。
| X | EXP(X) |
|---|---|
| 0.0 | 1.0 |
+inf |
+inf |
-inf |
0.0 |
戻りデータの型
NUMERIC は、入力として直接サポートされていません。まず、明示的に NUMERIC 入力を FLOAT64 にキャストする必要があります。出力は FLOAT64 になります。
LN
LN(X)
説明
X の自然対数を計算します。X が 0 以下の場合、エラーを生成します。
| X | LN(X) |
|---|---|
| 1.0 | 0.0 |
+inf |
+inf |
X < 0 |
エラー |
戻りデータの型
NUMERIC は、入力として直接サポートされていません。まず、明示的に NUMERIC 入力を FLOAT64 にキャストする必要があります。出力は FLOAT64 になります。
LOG
LOG(X [, Y])
説明
X のみが存在する場合、LOG は LN と同義です。Y も存在する場合、LOG は Y を底とする X の対数を計算します。
| X | Y | LOG(X, Y) |
|---|---|---|
| 100.0 | 10.0 | 2.0 |
-inf |
任意値 | NaN |
| 任意値 | +inf |
NaN |
+inf |
0.0 < Y < 1.0 | -inf |
+inf |
Y > 1.0 | +inf |
| X <= 0 | 任意値 | エラー |
| 任意値 | Y <= 0 | エラー |
| 任意値 | 1.0 | エラー |
戻りデータの型
NUMERIC は、入力として直接サポートされていません。まず、明示的に NUMERIC 入力を FLOAT64 にキャストする必要があります。出力は FLOAT64 になります。
LOG10
LOG10(X)
説明
LOG と似ていますが、10 を底とする対数を計算します。
| X | LOG10(X) |
|---|---|
| 100.0 | 2.0 |
-inf |
NaN |
+inf |
NaN |
| X <= 0 | エラー |
戻りデータの型
NUMERIC は、入力として直接サポートされていません。まず、明示的に NUMERIC 入力を FLOAT64 にキャストする必要があります。出力は FLOAT64 になります。
GREATEST
GREATEST(X1,...,XN)
説明
< 比較によって、X1,...,XN の間の最大値を返します。
| X1,...,XN | GREATEST(X1,...,XN) |
|---|---|
| 3,5,1 | 5 |
戻りデータの型
入力値のデータ型。
LEAST
LEAST(X1,...,XN)
説明
> 比較によって X1,...,XN の間の最小値を返します。
| X1,...,XN | LEAST(X1,...,XN) |
|---|---|
| 3,5,1 | 1 |
戻りデータの型
入力値のデータ型。
DIV
DIV(X, Y)
説明
Y による X の整数除算の結果を返します。ゼロ除算はエラーを返します。-1 による除算はオーバーフローする可能性があります。両方の入力が NUMERIC で、結果がオーバーフローする場合、numeric overflow エラーが返されます。
| X | ○ | DIV(X, Y) |
|---|---|---|
| 20 | 4 | 5 |
| 0 | 20 | 0 |
| 20 | 0 | エラー |
戻りデータの型
戻りデータの型は、次の表にある引数の型によって決まります。
| 入力 | INT64 | NUMERIC |
|---|---|---|
| INT64 | INT64 | NUMERIC |
| NUMERIC | NUMERIC | NUMERIC |
SAFE_DIVIDE
SAFE_DIVIDE(X, Y)
説明
除算演算子（X / Y）と同じですが、0 で割った場合など、エラーがあると NULL を返します。
| X | ○ | SAFE_DIVIDE(X, Y) |
|---|---|---|
| 20 | 4 | 5 |
| 0 | 20 | 0 |
| 20 | 0 | NULL |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | FLOAT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_MULTIPLY
SAFE_MULTIPLY(X, Y)
説明
乗算演算子（*）と同じですが、オーバーフローが発生すると NULL を返します。
| X | ○ | SAFE_MULTIPLY(X, Y) |
|---|---|---|
| 20 | 4 | 80 |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | INT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_NEGATE
SAFE_NEGATE(X)
説明
単項マイナス演算子（-）と同じですが、オーバーフローが発生すると NULL を返します。
| X | SAFE_NEGATE(X) |
|---|---|
| +1 | -1 |
| -1 | +1 |
| 0 | 0 |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | INT64 | NUMERIC | FLOAT64 |
SAFE_ADD
SAFE_ADD(X, Y)
説明
加算演算子（+）と同じですが、オーバーフローが発生すると NULL を返します。
| X | ○ | SAFE_ADD(X, Y) |
|---|---|---|
| 5 | 4 | 9 |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | INT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_SUBTRACT
SAFE_SUBTRACT(X, Y)
説明
X から Y を減算した結果を返します。減算演算子（-）と同じですが、オーバーフローが発生すると NULL を返します。
| X | ○ | SAFE_SUBTRACT(X, Y) |
|---|---|---|
| 5 | 4 | 1 |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | INT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
MOD
MOD(X, Y)
説明
モジュロ関数: Y による X の除算の剰余を返します。返される値は X と同じ符号を持ちます。Y が 0 の場合、エラーが発生します。
| X | ○ | MOD(X, Y) |
|---|---|---|
| 25 | 12 | 1 |
| 25 | 0 | エラー |
戻りデータの型
戻りデータの型は、次の表にある引数の型によって決まります。
| 入力 | INT64 | NUMERIC |
|---|---|---|
| INT64 | INT64 | NUMERIC |
| NUMERIC | NUMERIC | NUMERIC |
ROUND
ROUND(X [, N])
説明
X のみが存在する場合、ROUND は X を最も近い整数に丸めます。N が存在する場合、ROUND は小数点以下の N 小数位に X を丸めます。N が負の場合、ROUND は小数点の左側の桁を丸めます。中間の値の場合は、ゼロから遠ざかるように丸めます。オーバーフローが発生した場合は、エラーが発生します。
| X | ROUND(X) |
|---|---|
| 2.0 | 2.0 |
| 2.3 | 2.0 |
| 2.8 | 3.0 |
| 2.5 | 3.0 |
| -2.3 | -2.0 |
| -2.8 | -3.0 |
| -2.5 | -3.0 |
| 0 | 0 |
+inf |
+inf |
-inf |
-inf |
NaN |
NaN |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | FLOAT64 | NUMERIC | FLOAT64 |
TRUNC
TRUNC(X [, N])
説明
X のみが存在する場合、TRUNC は絶対値が X 以下で最も近い整数に X を丸めます。N も存在する場合、TRUNC の動作は ROUND(X, N) と似ていますが、常にゼロに近づくように丸め、オーバーフローすることはありません。
| X | TRUNC(X) |
|---|---|
| 2.0 | 2.0 |
| 2.3 | 2.0 |
| 2.8 | 2.0 |
| 2.5 | 2.0 |
| -2.3 | -2.0 |
| -2.8 | -2.0 |
| -2.5 | -2.0 |
| 0 | 0 |
+inf |
+inf |
-inf |
-inf |
NaN |
NaN |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | FLOAT64 | NUMERIC | FLOAT64 |
CEIL
CEIL(X)
説明
X 以上で最小の整数値を返します。
| X | CEIL(X) |
|---|---|
| 2.0 | 2.0 |
| 2.3 | 3.0 |
| 2.8 | 3.0 |
| 2.5 | 3.0 |
| -2.3 | -2.0 |
| -2.8 | -2.0 |
| -2.5 | -2.0 |
| 0 | 0 |
+inf |
+inf |
-inf |
-inf |
NaN |
NaN |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | FLOAT64 | NUMERIC | FLOAT64 |
CEILING
CEILING(X)
説明
CEIL(X) と同義
FLOOR
FLOOR(X)
説明
X 以下で最大の整数を返します。
| X | FLOOR(X) |
|---|---|
| 2.0 | 2.0 |
| 2.3 | 2.0 |
| 2.8 | 2.0 |
| 2.5 | 2.0 |
| -2.3 | -3.0 |
| -2.8 | -3.0 |
| -2.5 | -3.0 |
| 0 | 0 |
+inf |
+inf |
-inf |
-inf |
NaN |
NaN |
戻りデータの型
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | FLOAT64 | NUMERIC | FLOAT64 |
COS
COS(X)
説明
X のコサインを計算します。X は RADIANS で指定されます。失敗することはありません。
| X | COS(X) |
|---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
COSH
COSH(X)
説明
X の双曲線コサインを計算します。X は RADIANS で指定されます。オーバーフローが発生した場合は、エラーが発生します。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | COSH(X) |
|---|---|
+inf |
+inf |
-inf |
+inf |
NaN |
NaN |
ACOS
ACOS(X)
説明
X の逆コサインの主値を計算します。戻り値は [0,π] の範囲内です。X が [-1, 1] の範囲外の値である場合、エラーになります。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | ACOS(X) |
|---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
| X < -1 | エラー |
| X > 1 | エラー |
ACOSH
ACOSH(X)
説明
X の逆双曲線コサインを計算します。X が 1 未満の値の場合、エラーになります。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | ACOSH(X) |
|---|---|
+inf |
+inf |
-inf |
NaN |
NaN |
NaN |
| X < 1 | エラー |
SIN
SIN(X)
説明
X のサインを計算します。X は RADIANS で指定されます。失敗することはありません。
| X | SIN(X) |
|---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
SINH
SINH(X)
説明
X の双曲線サインを計算します。X は RADIANS で指定されます。オーバーフローが発生した場合は、エラーが発生します。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | SINH(X) |
|---|---|
+inf |
+inf |
-inf |
-inf |
NaN |
NaN |
ASIN
ASIN(X)
説明
X の逆サインの主値を計算します。戻り値は [-π/2,π/2] の範囲内です。X が [-1, 1] の範囲外である場合、エラーになります。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | ASIN(X) |
|---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
| X < -1 | エラー |
| X > 1 | エラー |
ASINH
ASINH(X)
説明
X の逆双曲線正サインを計算します。失敗することはありません。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | ASINH(X) |
|---|---|
+inf |
+inf |
-inf |
-inf |
NaN |
NaN |
TAN
TAN(X)
説明
X のタンジェントを計算します。X は RADIANS で指定されます。オーバーフローが発生した場合は、エラーが発生します。
| X | Tan(X) |
|---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
TANH
TANH(X)
説明
X の双曲線タンジェントを計算します。X は RADIANS で指定されます。失敗することはありません。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | TANH(X) |
|---|---|
+inf |
1.0 |
-inf |
-1.0 |
NaN |
NaN |
ATAN
ATAN(X)
説明
X の逆タンジェントの主値を計算します。戻り値は [-π/2,π/2] の範囲内です。失敗することはありません。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | ATAN(X) |
|---|---|
+inf |
π/2 |
-inf |
-π/2 |
NaN |
NaN |
ATANH
ATANH(X)
説明
X の逆双曲線タンジェントを計算します。X が [-1, 1] 未満の範囲外である場合、エラーになります。
X が NUMERIC の場合、出力は FLOAT64 になります。
| X | ATANH(X) |
|---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
| X < -1 | エラー |
| X > 1 | エラー |
ATAN2
ATAN2(X, Y)
説明
象限を判断するために 2 つの引数の符号を用いて、X/Y の逆タンジェントの主値を計算します。戻り値は [-π,π] の範囲内です。
Y が NUMERIC の場合、出力は FLOAT64 になります。
| X | ○ | ATAN2(X, Y) |
|---|---|---|
NaN |
任意値 | NaN |
| 任意値 | NaN |
NaN |
| 0.0 | 0.0 | 0.0 |
| 正の有限値 | -inf |
π |
| 負の有限値 | -inf |
-π |
| 有限値 | +inf |
0.0 |
+inf |
有限値 | π/2 |
-inf |
有限値 | -π/2 |
+inf |
-inf |
¾π |
-inf |
-inf |
-¾π |
+inf |
+inf |
π/4 |
-inf |
+inf |
-π/4 |
ハッシュ関数
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 と BYTES の値を処理できます。STRING 値は、正しい形式の UTF-8 でなければなりません。
STRPOS などの位置値を返す関数は、INT64 としてそれらの位置をエンコードします。値 1 は最初の文字（またはバイト）を表し、2 は 2 番目を表します（以下同様）。値 0 は、無効なインデックスであることを示します。STRING 型を扱っている場合、返される位置は文字の位置です。
すべての文字列比較は、Unicode の標準的な同値に関係なく、バイト単位で行われます。
BYTE_LENGTH
BYTE_LENGTH(value)
説明
値の型が STRING または BYTES のどちらであるかにかかわらず、BYTES の STRING 値または BYTES 値の長さを返します。
戻り値の型
INT64
例
WITH example AS
(SELECT "абвгд" AS characters, b"абвгд" AS bytes)
SELECT
characters,
BYTE_LENGTH(characters) AS string_example,
bytes,
BYTE_LENGTH(bytes) AS bytes_example
FROM example;
+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд | 10 | абвгд | 10 |
+------------+----------------+-------+---------------+
CHAR_LENGTH
CHAR_LENGTH(value)
説明
STRING の長さを文字数で返します。
戻り値の型
INT64
例
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
説明
CHAR_LENGTH の類義語です。
戻り値の型
INT64
例
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_BYTES(ascii_values)
説明
拡張 ASCII コードポイントの配列（INT64 の ARRAY）を取り、BYTES を返します。
BYTES からコードポイントの配列への変換については、TO_CODE_POINTS をご覧ください。
戻り値の型
BYTES
例
以下は、CODE_POINTS_TO_BYTES の基本的な使用方法の一例です。
SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;
-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes |
+----------+
| QWJDZA== |
+----------+
次の例では、ROT13 アルゴリズム（アルファベットの文字列をすべて 13 ずつずれた別の文字に変換する手法）を使用して文字列をエンコードします。
SELECT CODE_POINTS_TO_BYTES(ARRAY(
(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;
-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+
CODE_POINTS_TO_STRING
CODE_POINTS_TO_STRING(value)
説明
Unicode コードポイントの配列（INT64 の ARRAY）を取り、STRING を返します。コードポイントが 0 の場合、STRING に文字を返しません。
文字列からコードポイントの配列への変換については、TO_CODE_POINTS をご覧ください。
戻り値の型
STRING
例
CODE_POINTS_TO_STRING を使用した基本的な例を次に示します。
SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;
+--------+
| string |
+--------+
| AÿȁЀ |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;
+--------+
| string |
+--------+
| a例 |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;
+--------+
| string |
+--------+
| NULL |
+--------+
以下の例は、一連の単語の中に出現する文字の頻度を計算します。
WITH Words AS (
SELECT word
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
CODE_POINTS_TO_STRING([code_point]) AS letter,
COUNT(*) AS letter_count
FROM Words,
UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;
+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a | 5 |
| f | 3 |
| r | 2 |
| b | 2 |
| l | 2 |
| o | 2 |
| g | 1 |
| z | 1 |
| e | 1 |
| m | 1 |
| i | 1 |
+--------+--------------+
CONCAT
CONCAT(value1[, ...])
説明
1 つ以上の 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 つの STRING 値または BYTES 値を取ります。2 番目の値が 1 番目の値の接尾辞である場合は、TRUE を返します。
戻り値の型
BOOL
例
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
ENDS_WITH(item, "e") as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
FORMAT
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 |
NUMERIC FLOAT64 |
F |
有限値の場合は [-]（整数部）.（小数部）の 10 進表記、非有限値の場合は大文字 | 392.650000 INF NAN |
NUMERIC FLOAT64 |
e |
科学的記数法（仮数 / 指数）（小文字） | 3.926500e+02 inf nan |
NUMERIC FLOAT64 |
E |
科学的記数法（仮数 / 指数）（大文字） | 3.926500E+02 INF NAN |
NUMERIC FLOAT64 |
g |
入力値の指数と指定された精度に応じて、10 進表記または科学表記のいずれか。小文字で表記します。詳細については、%g と %G の動作をご覧ください。 | 392.65 3.9265e+07 inf nan |
NUMERIC FLOAT64 |
G |
入力値の指数と指定された精度に応じて、10 進表記または科学表記のいずれか。大文字で表記します。詳細については、%g と %G の動作をご覧ください。 | 392.65 3.9265E+07 INF NAN |
NUMERIC 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> | 記号が書き込まれない場合、値の前に空白のスペースが挿入されます。 |
# |
|
0 |
パディングが指定されている場合、スペースではなく、ゼロ（0）で数字の左側にパディングされます（幅サブ指定子を参照）。 |
' |
適切なグルーピング文字を使用して整数を形式設定します。 例:
このフラグは、10 進数、16 進数、および 8 進数の値のみが対象になります。 |
フラグは任意の順序で指定できます。フラグを重複して使用しても、エラーになりません。いずれかの要素タイプにフラグが該当しない場合、それらは無視されます。
幅
| 幅 | 説明 |
| <number> | 印刷される最小文字数です。印刷される値がこの数値よりも短い場合、結果は空白スペースを使用してパディングされます。結果のほうが大きい場合でも、値は切り詰められません。 |
* |
幅は形式設定文字列には指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。 |
精度
| 精度 | 説明 |
.<number> |
|
.* |
精度は、形式設定文字列では指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。 |
%g と %G の動作
%g と %G の形式指定子は、入力値の指数と指定された精度に応じて、10 進表記（%f や %F 指定子など）または科学表記（%e や %E 指定子など）を選択します。
p は指定された精度を表します（デフォルトは 6、指定された精度が 1 未満の場合は 1）。入力値は、最初に精度 = (p - 1) の科学的記数法に変換されます。結果の指数部 x が -4 未満または p 以上の場合、精度 = (p - 1) の科学的記数法が使用されます。それ以外の場合は、精度= (p - 1 - x) の小数表記が使用されます。
# フラグが存在しない場合は、小数点以下の後続ゼロが削除され、小数点の後に数字がない場合は小数点も削除されます。
%t および %T の作用
%t および %T 形式指定子は、すべての型に関して定義されます。幅、精度、フラグは、%s の場合と同じように機能します。幅は最小幅であり、STRING はそのサイズにパディングされます。精度は、表示するコンテンツの最大幅です。STRING は指定されたサイズまで切り詰められてから、幅までパディングされます。
%t 指定子は、常に読み取り可能な形式の値になるように想定されています。
%T 指定子は、常に類似する型の有効な SQL リテラルになります（より広い数値型など）。非定形の浮動小数点値の特殊なケースを除き、リテラルには CAST や型名が含まれません。
STRING は次のように形式設定されます。
| 型 | %t | %T |
任意の型の NULL |
NULL |
NULL |
| INT64 |
123 | 123 |
| NUMERIC | 123.0（常に .0 が付く） | NUMERIC "123.0" |
| FLOAT64 | 123.0（常に .0 が付く） 123e+10 inf-infNaN |
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 STRING を生成します。
ただし、いくつかの例外が存在します。形式設定子が %t または %T である（これらはどちらも実際上 CAST とリテラル値セマンティックと一致する STRING を生成する）場合は、NULL 値によって結果の STRING 内に 'NULL'（引用符なし）が生成されます。たとえば、次の関数の場合です。
FORMAT('00-%t-00', NULL_expression);
戻り値
00-NULL-00
その他のセマンティック ルール
FLOAT64 の値は、+/-inf または NaN です。引数にこれらのいずれかの値が含まれている場合、形式指定子 %f、%F、%e、%E、%g、%G、%t の結果は、状況に応じて inf、-inf または nan（または同じ文字の大文字）になります。これは、Cloud Spanner SQL がこれらの値を STRING にキャストする動作と整合します。%T の場合、Cloud Spanner SQL は文字列リテラル表現なしの FLOAT64 の引用符付きの文字列を返します。
FROM_BASE32
FROM_BASE32(string_expr)
説明
base32 でエンコードされた入力 string_expr を BYTES 形式に変換します。BYTES を base32 でエンコードされた STRING に変換するには、TO_BASE32 を使用します。
戻り値の型
BYTES
例
SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;
-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/ |
+-----------+
FROM_BASE64
FROM_BASE64(string_expr)
説明
base64 でエンコードされた入力 string_expr を BYTES 形式に変換します。BYTES を base64 でエンコードされた STRING に変換するには、TO_BASE64 を使用します。
よく使用される base64 エンコードには、64 桁とパディングのエンコードに使用される 65 文字の ASCII 文字のアルファベットが厳密には異なるエンコードがいくつかあります。詳しくは、RFC 4648 をご覧ください。この関数はアルファベット [A-Za-z0-9+/=] を想定しています。
戻り値の型
BYTES
例
SELECT FROM_BASE64('/+A=') AS byte_data;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| /+A= |
+-----------+
別の base64 アルファベットを使用するエンコードを扱うには、REPLACE 関数で FROM_BASE64 を作成する必要がある場合があります。たとえば、ウェブ プログラミングで一般的に使用されている base64url の URL セーフおよびファイル名セーフ エンコーディングは、最後の文字として +/= ではなく -_= を使用します。base64url エンコードされた文字列をデコードするには、+ と / をそれぞれ - と _ に置き換えます。
SELECT FROM_BASE64(REPLACE(REPLACE("_-A=", "-", "+"), "_", "/")) AS binary;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A= |
+--------+
FROM_HEX
FROM_HEX(string)
説明
16 進数でエンコードされた STRING を BYTES 形式に変換します。入力 STRING に (0..9, A..F, a..f) の範囲外の文字が含まれている場合はエラーを返します。文字の大文字と小文字は区別されません。入力 STRING の文字数が奇数の場合は、入力の先頭に 0 が追加されているものとして処理されます。BYTES を 16 進数でエンコードされた STRING に変換するには、TO_HEX を使用します。
戻り値の型
BYTES
例
WITH Input AS (
SELECT '00010203aaeeefff' AS hex_str UNION ALL
SELECT '0AF' UNION ALL
SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str | bytes_str |
+------------------+--------------+
| 0AF | AAECA6ru7/8= |
| 00010203aaeeefff | AK8= |
| 666f6f626172 | Zm9vYmFy |
+------------------+--------------+
LENGTH
LENGTH(value)
説明
STRING 値または BYTES 値の長さを返します。戻り値は STRING 引数の場合は文字数、BYTES 引数の場合はバイト数で示されます。
戻り値の型
INT64
例
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
LENGTH(characters) AS string_example,
LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;
+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд | 5 | 10 |
+------------+----------------+---------------+
LPAD
LPAD(original_value, return_length[, pattern])
説明
先頭に pattern が追加された original_value で構成される STRING 値または BYTES 値を返します。return_length は、戻り値の長さを指定する INT64 です。original_value が BYTES 型の場合、return_length はバイト数です。original_value が STRING 型の場合、return_length は文字数です。
pattern のデフォルト値はスペースです。
original_value と pattern は同じデータ型にする必要があります。
return_length が original_value 以下である場合、この関数は original_value の値を return_length の値まで切り詰めて返します。たとえば、LPAD("hello world", 7); は "hello w" を返します。
original_value、return_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 の ARRAY
例
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_value を to_value に置き換えますfrom_value が空の場合、置き換えは行われません。
戻り値の型
STRING または BYTES
例
WITH desserts AS
(SELECT "apple pie" as dessert
UNION ALL
SELECT "blackberry pie" as dessert
UNION ALL
SELECT "cherry pie" as dessert)
SELECT
REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| blackberry cobbler |
| cherry cobbler |
+--------------------+
REPEAT
REPEAT(original_value, repetitions)
説明
original_value の繰り返しで構成される STRING 値または BYTES 値を返します。repetitions パラメータで、original_value の繰り返しの数を指定します。original_value または repetitions のいずれかが NULL の場合は、NULL を返します。
repetitions の値が負の数の場合、この関数はエラーを返します。
戻り値の型
STRING または BYTES
例
SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
STRUCT('abc' AS t, 3 AS n),
('例子', 2),
('abc', null),
(null, 3)
]);
+------+------+-----------+
| t | n | REPEAT |
|------|------|-----------|
| abc | 3 | abcabcabc |
| 例子 | 2 | 例子例子 |
| abc | NULL | NULL |
| NULL | 3 | NULL |
+------+------+-----------+
REVERSE
REVERSE(value)
説明
入力の STRING または BYTES を逆順で返します。
戻り値の型
STRING または BYTES
例
WITH example AS (
SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
sample_string,
REVERSE(sample_string) AS reverse_string,
sample_bytes,
REVERSE(sample_bytes) AS reverse_bytes
FROM example;
+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo | oof | bar | rab |
| абвгд | дгвба | 123 | 321 |
+---------------+----------------+--------------+---------------+
RPAD
RPAD(original_value, return_length[, pattern])
説明
pattern が追加された original_value で構成される STRING 値または BYTES 値を返します。return_length パラメータは、戻り値の長さを指定する INT64 です。original_value が BYTES の場合、return_length はバイト数です。original_value が STRING の場合、return_length は文字数です。
pattern のデフォルト値はスペースです。
original_value と pattern は同じデータ型にする必要があります。
return_length が original_value 以下である場合、この関数は original_value の値を return_length の値まで切り詰めて返します。たとえば、RPAD("hello world", 7); は "hello w" を返します。
original_value、return_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)
説明
BYTES のシーケンスを STRING に変換します。無効な UTF-8 文字列は Unicode 置換文字 U+FFFD に置き換えられます。
戻り値の型
STRING
例
次のステートメントは、Unicode 置換文字 � を返します。
SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;
SPLIT
SPLIT(value[, delimiter])
説明
delimiter 引数を使用して value を分割します。
STRING の場合、デフォルトの区切り文字はカンマ（,）です。
BYTES の場合は、区切り文字を指定する必要があります。
空の区切り文字で分割すると、STRING 値の場合は UTF-8 文字の配列が生成され、BYTES 値の場合は BYTES の配列が生成されます。
空の STRING を分割すると、1 つの空の STRING を持つ ARRAY が返されます。
戻り値の型
STRING 型の ARRAY、または BYTES 型の ARRAY
例
WITH letters AS
(SELECT "" as letter_group
UNION ALL
SELECT "a" as letter_group
UNION ALL
SELECT "b c d" as letter_group)
SELECT SPLIT(letter_group, " ") as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [] |
| [a] |
| [b, c, d] |
+----------------------+
STARTS_WITH
STARTS_WITH(value1, value2)
説明
2 つの STRING 値または BYTES 値を取ります。2 番目の値が 1 番目の値の接頭辞である場合は、TRUE を返します。
戻り値の型
BOOL
例
WITH items AS
(SELECT "foo" as item
UNION ALL
SELECT "bar" as item
UNION ALL
SELECT "baz" as item)
SELECT
STARTS_WITH(item, "b") as example
FROM items;
+---------+
| example |
+---------+
| False |
| True |
| True |
+---------+
STRPOS
STRPOS(string, substring)
説明
string 内で最初に substring が出現する位置のインデックス（開始値 1）を返します。substring が見つからない場合は 0 を返します。
戻り値の型
INT64
例
WITH email_addresses AS
(SELECT
"foo@example.com" AS email_address
UNION ALL
SELECT
"foobar@example.com" AS email_address
UNION ALL
SELECT
"foobarbaz@example.com" AS email_address
UNION ALL
SELECT
"quxexample.com" AS email_address)
SELECT
STRPOS(email_address, "@") AS example
FROM email_addresses;
+---------+
| example |
+---------+
| 4 |
| 7 |
| 10 |
| 0 |
+---------+
SUBSTR
SUBSTR(value, position[, length])
説明
指定された STRING 値または BYTES 値の部分文字列を返します。position 引数は、部分文字列の開始位置を指定する整数であり、position = 1 は最初の文字またはバイトを指します。length 引数は、STRING 引数の場合は最大文字数、BYTES 引数の場合はバイト数です。
position が負の値である場合、value の最後からカウントします（-1 は最後の文字を示す）。
position が STRING の左端の外の位置にある場合（position = 0 または position < -LENGTH(value)）、position = 1 から開始します。length が value の長さを超える場合、この関数は length 文字より少ない値を返します。
length が 0 より小さい場合、関数はエラーを返します。
戻り値の型
STRING または BYTES
例
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2) as example
FROM items;
+---------+
| example |
+---------+
| pple |
| anana |
| range |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2, 2) as example
FROM items;
+---------+
| example |
+---------+
| pp |
| an |
| ra |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, -2) as example
FROM items;
+---------+
| example |
+---------+
| le |
| na |
| ge |
+---------+
TO_BASE32
TO_BASE32(bytes_expr)
説明
BYTES のシーケンスを base32 でエンコードされた STRING に変換します。base32 でエンコードされた STRING を BYTES に変換するには、FROM_BASE32 を使用します。
戻り値の型
STRING
例
SELECT TO_BASE32(b'abcde\xFF') AS base32_string;
+------------------+
| base32_string |
+------------------+
| MFRGGZDF74====== |
+------------------+
TO_BASE64
TO_BASE64(bytes_expr)
説明
BYTES のシーケンスを base64 でエンコードされた STRING に変換します。base64 でエンコードされた STRING を BYTES に変換するには、FROM_BASE64 を使用します。
よく使用される base64 エンコードには、64 桁とパディングのエンコードに使用される 65 文字の ASCII 文字のアルファベットが厳密には異なるエンコードがいくつかあります。詳しくは、RFC 4648 をご覧ください。この関数はパディングを追加し、アルファベット [A-Za-z0-9+/=] を使用します。
戻り値の型
STRING
例
SELECT TO_BASE64(b'\377\340') AS base64_string;
+---------------+
| base64_string |
+---------------+
| /+A= |
+---------------+
別の base64 アルファベットを使用するエンコードを扱うには、REPLACE 関数で TO_BASE64 を作成する必要がある場合があります。たとえば、ウェブ プログラミングで一般的に使用されている base64url の URL セーフおよびファイル名セーフ エンコーディングは、最後の文字として +/= ではなく -_= を使用します。base64url エンコードされた文字列をエンコードするには、- と _ をそれぞれ + と / に置き換えます。
SELECT REPLACE(REPLACE(TO_BASE64(b"\377\340"), "+", "-"), "/", "_") as websafe_base64;
+----------------+
| websafe_base64 |
+----------------+
| _-A= |
+----------------+
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 をご覧ください。
戻り値の型
ARRAY/INT64
例
次の例では、文字配列内の各要素についてコードポイントを取得します。
SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;
+---------+------------------------------------+
| word | code_points |
+---------+------------------------------------+
| foo | [102, 111, 111] |
| bar | [98, 97, 114] |
| baz | [98, 97, 122] |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama | [108, 108, 97, 109, 97] |
+---------+------------------------------------+
次の例では、BYTES の整数表現を対応する ASCII 文字値に変換しています。
SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;
+------------------+------------------------+
| word | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255] |
| foo | [102, 111, 111] |
+------------------+------------------------+
次の例では、BYTES の結果と STRING の結果の違いを示しています。
SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;
+------------+----------+
| b_result | s_result |
+------------+----------+
| [196, 128] | [256] |
+------------+----------+
文字 Ā が 2 バイトの Unicode シーケンスで表されることにご注意ください。その結果、BYTES バージョンの TO_CODE_POINTS は 2 つの要素からなる配列を返し、STRING バージョンは 1 つの要素からなる配列を返します。
TO_HEX
TO_HEX(bytes)
説明
BYTES のシーケンスを 16 進数の STRING に変換します。STRING 内の各バイトを範囲 (0..9, a..f) 内の 2 つの 16 進数文字に変換します。16 進数でエンコードされた 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_format) は、JSON 値を STRING として返します。
JSON_VALUE(json_string_expr, json_path_format) は、スカラー JSON 値を STRING として返します。
説明
JSON 値または JSON スカラー値を文字列として抽出します。
json_string_expr: JSON 形式の文字列。例:{"class" : {"students" : [{"name" : "Jane"}]}}json_path_format: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。json_path_formatが JSONnullを返す場合、これは SQLNULLに変換されます。
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_format パラメータで渡されます。json_string_expr パラメータは JSON 形式の文字列で渡され、json_path_format パラメータは JSON 形式の文字列から取得する値を指定します。
json_string_expr パラメータは次のような形式の JSON 文字列にする必要があります。
{"class" : {"students" : [{"name" : "Jane"}]}}
JSONPath 形式を使用して json_path_format パラメータを作成します。このパラメータの書式は $ 記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。子の値は、ドットを使用して識別できます。JSON オブジェクトが配列の場合は、角括弧を使用して配列のインデックスを指定できます。キーに $、ドット、角括弧が含まれている場合、エスケープする方法については、各 JSON 関数をご覧ください。
| JSONPath | 説明 | 例 | 上記の json_string_expr を使用した結果 |
|---|---|---|---|
| $ | ルートのオブジェクトまたは要素 | "$" | {"class":{"students":[{"name":"Jane"}]}} |
| . | 子の演算子 | "$.class.students" | [{"name":"Jane"}] |
| [] | 添字演算子 | "$.class.students[0]" | {"name":"Jane"} |
JSON 関数は、json_path_format パラメータが json_string_expr 内の値と一致しない場合は NULL を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合は、NULL を返します。
JSONPath が無効な場合、JSON 関数はエラーとなります。
配列関数
ARRAY
ARRAY(subquery)
説明
ARRAY 関数は、サブクエリによって返される行ごとに 1 つの要素を含む ARRAY を返します。
subquery によって SQL テーブルが生成される場合、そのテーブルの列数は 1 つである必要があります。出力 ARRAY の各要素は、テーブルの行に 1 つだけある列の値となります。
subquery によって値テーブルが生成される場合、出力 ARRAY の各要素は、値テーブルの対応する行全体となります。
制約
- サブクエリは順序付けされていないため、出力
ARRAYの要素は、サブクエリのソーステーブルの順序を保証するものではありません。ただし、サブクエリにORDER BY句が含まれている場合、ARRAY関数はその句を使用するARRAYを返します。 - サブクエリが複数の列を返す場合、
ARRAY関数はエラーを返します。 - サブクエリが
ARRAY型の列またはARRAY型の行を返す場合、ARRAY関数は、Cloud Spanner SQL では型ARRAYの要素を含むARRAYをサポートしていないことを示すエラーを返します。 - サブクエリがゼロ行を返す場合、
ARRAY関数は空のARRAYを返します。NULLARRAYは返しません。
戻り値の型
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 関数は STRUCT の ARRAY を返します。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_expression が NULL である場合、NULL を返します。
戻り値の型
INT64
例
WITH items AS
(SELECT ["coffee", NULL, "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;
+---------------------------------+------+
| list | size |
+---------------------------------+------+
| [coffee, NULL, milk] | 3 |
| [cake, pie] | 2 |
+---------------------------------+------+
ARRAY_TO_STRING
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
説明
array_expression 内の要素の連結を STRING として返します。array_expression の値は STRING または BYTES データ型のいずれかの配列にできます。
null_text パラメータが使用されている場合、この関数によって、配列内のすべての NULL 値が null_text の値に置き換えられます。
null_text パラメータが使用されていない場合、この関数は NULL 値とその前にある区切り文字を無視します。
例
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie |
+--------------------------------+
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie--MISSING |
+--------------------------------+
GENERATE_ARRAY
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
説明
値の配列を返します。start_expression と end_expression のパラメータは、配列の包括的な開始点と終了点を示します。
GENERATE_ARRAY 関数は、入力として次のデータ型を受け入れます。
- INT64
- NUMERIC
- FLOAT64
step_expression パラメータは、配列の値を生成するために使用される増分を示します。このパラメータのデフォルト値は 1 です。
この関数は、step_expression が 0 に設定されているか入力が NaN の場合、エラーを返します。
引数が NULL の場合、関数は NULL 配列を返します。
戻りデータの型
ARRAY
例
以下の場合、整数の配列を返します。デフォルトのステップは 1 です。
SELECT GENERATE_ARRAY(1, 5) AS example_array;
+-----------------+
| example_array |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
以下の場合、ユーザーが指定したステップサイズを使用して配列を返します。
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9] |
+---------------+
以下の場合、負の値を使用して配列を返します。ステップサイズは -3 です。
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+
以下の場合、start_expression と end_expression に同じ値を使用して配列を返します。
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;
+---------------+
| example_array |
+---------------+
| [4] |
+---------------+
以下の場合、start_expression が end_expression よりも大きく、step_expression が正の値であるため、空の配列を返します。
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [] |
+---------------+
以下の場合、end_expression が NULL であるため、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_date と end_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_date と end_date に同じ値を使用して配列を返します。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05', INTERVAL 8 DAY) AS example;
+--------------+
| example |
+--------------+
| [2016-10-05] |
+--------------+
以下の場合、start_date が end_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 をそれぞれ入れる必要があります。デフォルトの動作はありません。
インデックスが範囲外の場合、OFFSET と ORDINAL はどちらもエラーを生成します。
戻り値の型
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] |
| [] | [] |
+-----------+-------------+
ARRAY_IS_DISTINCT
ARRAY_IS_DISTINCT(value)
説明
配列に反復要素が含まれていない場合、SELECT DISTINCT と同じ等価比較ロジックを使用して true を返します。
戻り値の型
BOOL
例
WITH example AS (
SELECT [1, 2, 3] AS arr UNION ALL
SELECT [1, 1, 1] AS arr UNION ALL
SELECT [1, 2, NULL] AS arr UNION ALL
SELECT [1, 1, NULL] AS arr UNION ALL
SELECT [1, NULL, NULL] AS arr UNION ALL
SELECT [] AS arr UNION ALL
SELECT CAST(NULL AS ARRAY<INT64>) AS arr
)
SELECT
arr,
ARRAY_IS_DISTINCT(arr) as is_distinct
FROM example;
+-----------------+-------------+
| arr | is_distinct |
+-----------------+-------------+
| [1, 2, 3] | true |
| [1, 1, 1] | false |
| [1, 2, NULL] | true |
| [1, 1, NULL] | false |
| [1, NULL, NULL] | false |
| [] | true |
| NULL | NULL |
+-----------------+-------------+
SAFE_OFFSET と SAFE_ORDINAL
array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]
説明
OFFSET や ORDINAL と同じですが、インデックスが範囲外の場合に 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] 内の値を返します。DAYDAYOFYEARWEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。ISOWEEK: ISO 8601 形式でdate_expressionの週番号を返します。ISOWEEKは月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初のISOWEEKは、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。MONTHQUARTER: 範囲 [1,4] 内の値を返します。YEARISOYEAR: ISO 8601 形式の週番号が付いた年を返します。これはdate_expressionが属している週の木曜日が含まれるグレゴリオ暦年です。
戻りデータの型
INT64
例
次の例では、EXTRACT は DAY の日付部分に対応する値を返します。
SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;
+---------+
| the_day |
+---------+
| 25 |
+---------+
次の例では、EXTRACT は年末付近の日付の列とは異なる日付部分に対応する値を返します。
SELECT
date,
EXTRACT(ISOYEAR FROM date) AS isoyear,
EXTRACT(ISOWEEK FROM date) AS isoweek,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015 | 52 | 2015 | 51 |
| 2015-12-24 | 2015 | 52 | 2015 | 51 |
| 2015-12-25 | 2015 | 52 | 2015 | 51 |
| 2015-12-26 | 2015 | 52 | 2015 | 51 |
| 2015-12-27 | 2015 | 52 | 2015 | 52 |
| 2015-12-28 | 2015 | 53 | 2015 | 52 |
| 2015-12-29 | 2015 | 53 | 2015 | 52 |
| 2015-12-30 | 2015 | 53 | 2015 | 52 |
| 2015-12-31 | 2015 | 53 | 2015 | 52 |
| 2016-01-01 | 2015 | 53 | 2016 | 0 |
| 2016-01-02 | 2015 | 53 | 2016 | 0 |
| 2016-01-03 | 2015 | 53 | 2016 | 1 |
| 2016-01-04 | 2016 | 1 | 2016 | 1 |
| 2016-01-05 | 2016 | 1 | 2016 | 1 |
| 2016-01-06 | 2016 | 1 | 2016 | 1 |
| 2016-01-07 | 2016 | 1 | 2016 | 1 |
| 2016-01-08 | 2016 | 1 | 2016 | 1 |
| 2016-01-09 | 2016 | 1 | 2016 | 1 |
+------------+---------+---------+------+------+
DATE
1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])
説明
- 年、月、日を表す DATE を INT64 値から構築します。
- 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 値をサポートします。
DAYWEEK。7DAYと等価。MONTHQUARTERYEAR
日付が月の最後の日である（または最後の日に近い）場合、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 値をサポートします。
DAYWEEK。7DAYと等価。MONTHQUARTERYEAR
日付が月の最後の日である（または最後の日に近い）場合、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_a～date_expression_b）間に存在する、全体が指定された date_part の間隔を示す数を返します。最初の DATE が 2 番目のオブジェクトよりも前に存在する場合、出力は負になります。
DATE_DIFF は、次の date_part 値をサポートします。
DAYWEEK: この日付パーツは日曜日から始まります。ISOWEEK: ISO 8601 の週の境界を使用します。ISO 週は月曜日から始まります。MONTHQUARTERYEARISOYEAR: 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 の次の値をサポートします。
DAYWEEKISOWEEK:date_expressionを、先行する ISO 8601 の週の境界に切り詰めます。ISOWEEKは月曜日に始まります。各 ISO 年の最初のISOWEEKには、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前のdate_expressionを前の月曜日に切り詰めます。MONTHQUARTERYEARISOYEAR:date_expressionを ISO 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)
説明
date_expr を指定された format_string に従ってフォーマットします。
この関数でサポートされる形式設定要素のリストについては、DATE でサポートされる形式設定要素をご覧ください。
戻りデータの型
STRING
例
SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;
+------------+
| US_format |
+------------+
| 12/25/08 |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+
PARSE_DATE
PARSE_DATE(format_string, date_string)
説明
日付の文字列表現を DATE オブジェクトに変換します。
format_string には、date_string のフォーマット方法を定義する形式設定要素が含まれます。date_string 内の各要素については、対応する要素が format_string 内に存在することが必要です。format_string 内の各要素のロケーションは、date_string 内の各要素のロケーションと一致する必要があります。
-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")
-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")
形式文字列では、%a、%A、%g、%G、%j、%u、%U、%V、%w、%Wを除くほとんどの形式要素が完全にサポートされます。
PARSE_DATE を使用する場合、次の点に注意してください。
- 指定されていないフィールド。指定されていないフィールドはすべて、
1970-01-01から初期設定されます。 - 大文字と小文字の区別がない名前。
MondayやFebruaryなどの名前は、大文字と小文字が区別されません。 - 空白文字。形式設定文字列内の 1 つ以上の連続する空白文字は、日付文字列内のゼロ以上の連続する空白文字と同じです。また、日付文字列内の先頭および末尾の空白文字は、それらが形式設定文字列内にない場合でも常に許可されます。
- 形式設定の優先度。2 つ（またはそれ以上）の形式設定要素が重複する情報を持つ場合（たとえば、
%Fと%Yの両方が年に影響を及ぼす場合など）、一般に、最後に指定した内容によって、前に指定した内容がオーバーライドされます。
戻りデータの型
DATE
例
次の例では、MM/DD/YY 形式の文字列を DATE オブジェクトに変換しています。
SELECT PARSE_DATE("%x", "12/25/08") as parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
次の例では、YYYYMMDD 形式の文字列を DATE オブジェクトに変換しています。
SELECT PARSE_DATE("%Y%m%d", "20081225") 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 timezone])
説明
入力された timestamp_expression から、指定された part に対応する値を返します。この関数では、省略可能な timezone パラメータを使用できます。タイムゾーンの指定方法については、タイムゾーンの定義をご覧ください。
指定できる part 値は次のとおりです。
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOURDAYOFWEEKDAYDAYOFYEARWEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。ISOWEEK: ISO 8601 形式でdatetime_expressionの週番号を返します。ISOWEEKは月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初のISOWEEKは、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。MONTHQUARTERYEARISOYEAR: ISO 8601 形式の週番号が付いた年を返します。これはdate_expressionが属している週の木曜日が含まれるグレゴリオ暦年です。DATE
戻り値となる値の下位の期間は切り捨てられます。たとえば、秒数を抽出するとき、EXTRACT はミリ秒とマイクロ秒の値を切り捨てます。
戻りデータの型
次の例外の場合以外は INT64 です。
partはDATEであり、DATEオブジェクトを返します。
例
次の例では、EXTRACT は DAY 時間部分に対応する値を返します。
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_date |
+----------------------+
| 2008-12-25T08:00:00Z |
+----------------------+
TIMESTAMP_ADD
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
説明
タイムゾーンに関係なく、date_part の int64_expression 単位をタイムスタンプに加算します。
TIMESTAMP_ADD は、date_part の次の値をサポートします。
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOUR。60MINUTEと等価。DAY。24HOURと等価。
戻りデータの型
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_part の int64_expression 単位を減算します。
TIMESTAMP_SUB は、date_part の次の値をサポートします。
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOUR。60MINUTEと等価。DAY。24HOURと等価。
戻りデータの型
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_a～timestamp_expression_b）間に存在する、全体が指定された date_part の間隔を示す数を返します。最初の TIMESTAMP が 2 番目のオブジェクトよりも前の場合、出力は負になります。2 つの TIMESTAMP オブジェクト間のナノ秒単位の差異が INT64 値をオーバーフローする場合など、計算が結果の型をオーバーフローする場合はエラーをスローします。
TIMESTAMP_DIFF は、date_part の次の値をサポートします。
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOUR。60MINUTEと等価。DAY。24HOURと等価。
戻りデータの型
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[, timezone])
説明
date_part の粒度までタイムスタンプを切り詰めます。
TIMESTAMP_TRUNC は、date_part の次の値をサポートします。
NANOSECONDMICROSECONDMILLISECONDSECONDMINUTEHOURDAYWEEKISOWEEK:timestamp_expressionを、先行する ISO 8601 の週の境界に切り詰めます。ISOWEEKは月曜日に始まります。各 ISO 年の最初のISOWEEKには、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前のdate_expressionを前の月曜日に切り詰めます。MONTHQUARTERYEARISOYEAR:timestamp_expressionを ISO 8601 形式の週番号が付けられた先行年の境界に切り詰めます。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。
TIMESTAMP_TRUNC 関数は、省略可能な timezone パラメータをサポートしています。このパラメータは、次の date_parts に適用されます。
MINUTEHOURDAYWEEKISOWEEKMONTHQUARTERYEARISOYEAR
このパラメータは、デフォルトのタイムゾーン America / Los_Angeles 以外のタイムゾーンを切り詰めオペレーションの一部として使用する場合に使用します。
TIMESTAMP を MINUTE あるいは 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[, timezone])
説明
タイムスタンプを指定された 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, timestamp_string[, timezone])
説明
タイムスタンプの文字列表現を TIMESTAMP オブジェクトに変換します。
format_string には、timestamp_string のフォーマット方法を定義する形式設定要素が含まれます。timestamp_string 内の各要素については、対応する要素が format_string 内に存在することが必要です。format_string 内の各要素のロケーションは、timestamp_string 内の各要素のロケーションと一致する必要があります。
-- This works because elements on both sides match.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008")
-- This works because %c can find all matching elements in timestamp_string.
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008")
形式文字列は、%a、%A、%g、%G、%j、%P、%u、%U、%V、%w、%Wを除く大半の形式要素を完全にサポートします。
PARSE_TIMESTAMP を使用する場合、次の点に注意してください。
- 指定されていないフィールド。指定されていないフィールドはすべて、
1970-01-01 00:00:00.0から初期設定されます。この初期設定値は、関数のタイムゾーン引数によって指定されたタイムゾーンを使用します（存在する場合）。存在しない場合、初期設定値はデフォルトのタイムゾーンである America/Los_Angeles を使用します。たとえば、年が指定されていない場合、デフォルトで1970に設定されます。 - 大文字と小文字の区別がない名前。
MondayやFebruaryなどの名前は、大文字と小文字が区別されません。 - 空白文字。形式設定文字列内の 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;
Net 関数
NET.IP_FROM_STRING
NET.IP_FROM_STRING(addr_str)
説明
IPv4 または IPv6 アドレスをネットワーク バイト順でテキスト形式（STRING）からバイナリ形式（BYTES）に変換します。
この関数は、addr_str で次の形式をサポートします。
- IPv4: ドット区切り形式。例:
10.1.2.3 - IPv6: コロン区切り形式。たとえば、
1234:5678:90ab:cdef:1234:5678:90ab:cdefです。他の例については、IP バージョン 6 アドレス アーキテクチャをご覧ください。
この関数は 10.1.2.3/32 のような CIDR 表記をサポートしていません。
この関数は、NULL 入力を受信すると NULL を返します。入力が無効と判断されると、OUT_OF_RANGE エラーが発生します。
戻りデータの型
BYTES
例
SELECT
addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST([
'48.49.50.51',
'::1',
'3031:3233:3435:3637:3839:4041:4243:4445',
'::ffff:192.0.2.128'
]) AS addr_str;
| addr_str | ip_from_string |
|---|---|
| 48.49.50.51 | b"0123" |
| ::1 | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" |
| 3031:3233:3435:3637:3839:4041:4243:4445 | b"0123456789@ABCDE" |
| ::ffff:192.0.2.128 | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" |
NET.SAFE_IP_FROM_STRING
NET.SAFE_IP_FROM_STRING(addr_str)
説明
NET.IP_FROM_STRING に似ていますが、入力が無効の場合にエラーを発生させず、NULL を返します。
戻りデータの型
BYTES
例
SELECT
addr_str,
FORMAT("%T", NET.SAFE_IP_FROM_STRING(addr_str)) AS safe_ip_from_string
FROM UNNEST([
'48.49.50.51',
'::1',
'3031:3233:3435:3637:3839:4041:4243:4445',
'::ffff:192.0.2.128',
'48.49.50.51/32',
'48.49.50',
'::wxyz'
]) AS addr_str;
| addr_str | safe_ip_from_string |
|---|---|
| 48.49.50.51 | b"0123" |
| ::1 | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" |
| 3031:3233:3435:3637:3839:4041:4243:4445 | b"0123456789@ABCDE" |
| ::ffff:192.0.2.128 | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" |
| 48.49.50.51/32 | NULL |
| 48.49.50 | NULL |
| ::wxyz | NULL |
NET.IP_TO_STRING
NET.IP_TO_STRING(addr_bin)
説明 IPv4 または IPv6 アドレスをネットワーク バイト順でバイナリ形式（BYTES）からテキスト形式（STRING）に変換します。
入力が 4 バイトの場合、この関数は IPv4 アドレスを STRING として返します。入力が 16 バイトの場合、IPv6 アドレスを STRING として返します。
この関数は、NULL 入力を受信すると NULL を返します。入力の長さが 4 または 16 以外の場合、OUT_OF_RANGE エラーが発生します。
戻りデータの型
STRING
例
SELECT FORMAT("%T", x) AS addr_bin, NET.IP_TO_STRING(x) AS ip_to_string
FROM UNNEST([
b"0123",
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01",
b"0123456789@ABCDE",
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
]) AS x;
| addr_bin | ip_to_string |
|---|---|
| b"0123" | 48.49.50.51 |
| b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" | ::1 |
| b"0123456789@ABCDE" | 3031:3233:3435:3637:3839:4041:4243:4445 |
| b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" | ::ffff:192.0.2.128 |
NET.IP_NET_MASK
NET.IP_NET_MASK(num_output_bytes, prefix_length)
説明
num_output_bytes の長さと等しいバイト シーケンスでネットワーク マスクを返します。最初の prefix_length ビットは 1 に設定され、他のビットは 0 に設定されます。num_output_bytes と prefix_length は INT64 です。num_output_bytes が 4（IPv4 の場合）または 16（IPv6 の場合）でない場合、エラーが発生します。prefix_length が負の数または 8 * num_output_bytes よりも大きい場合にもエラーが発生します。
戻りデータの型
BYTES
例
SELECT x, y, FORMAT("%T", NET.IP_NET_MASK(x, y)) AS ip_net_mask
FROM UNNEST([
STRUCT(4 as x, 0 as y),
(4, 20),
(4, 32),
(16, 0),
(16, 1),
(16, 128)
]);
| x | y | ip_net_mask |
|---|---|---|
| 4 | 0 | b"\x00\x00\x00\x00" |
| 4 | 20 | b"\xff\xff\xf0\x00" |
| 4 | 32 | b"\xff\xff\xff\xff" |
| 16 | 0 | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00" |
| 16 | 1 | b"\x80\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00" |
| 16 | 128 | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" |
NET.IP_TRUNC
NET.IP_TRUNC(addr_bin, prefix_length)
説明: ネットワーク バイト順のバイナリ形式（BYTES）で表した IPv4 または IPv6 アドレスである addr_bin を受け入れ、同じ形式でサブネット アドレスを返します。結果の長さは addr_bin と同じになります。最初の prefix_length ビットは addr_bin と等しくなり、残りのビットは 0 になります。
LENGTH(addr_bin) が 4 でも 16 でもない場合、あるいは prefix_len が負の数であるか LENGTH(addr_bin) * 8 より大きい場合、エラーが発生します。
戻りデータの型
BYTES
例
SELECT
FORMAT("%T", x) as addr_bin, prefix_length,
FORMAT("%T", NET.IP_TRUNC(x, prefix_length)) AS ip_trunc
FROM UNNEST([
STRUCT(b"\xAA\xBB\xCC\xDD" as x, 0 as prefix_length),
(b"\xAA\xBB\xCC\xDD", 11), (b"\xAA\xBB\xCC\xDD", 12),
(b"\xAA\xBB\xCC\xDD", 24), (b"\xAA\xBB\xCC\xDD", 32),
(b'0123456789@ABCDE', 80)
]);
| addr_bin | prefix_length | ip_trunc |
|---|---|---|
| b"\xaa\xbb\xcc\xdd" | 0 | b"\x00\x00\x00\x00" |
| b"\xaa\xbb\xcc\xdd" | 11 | b"\xaa\xa0\x00\x00" |
| b"\xaa\xbb\xcc\xdd" | 12 | b"\xaa\xb0\x00\x00" |
| b"\xaa\xbb\xcc\xdd" | 24 | b"\xaa\xbb\xcc\x00" |
| b"\xaa\xbb\xcc\xdd" | 32 | b"\xaa\xbb\xcc\xdd" |
| b"0123456789@ABCDE" | 80 | b"0123456789\x00\x00\x00\x00\x00\x00" |
NET.IPV4_FROM_INT64
NET.IPV4_FROM_INT64(integer_value)
説明
IPv4 アドレスをネットワーク バイト順に整数形式からバイナリ形式（BYTES）に変換します。整数入力の場合、ホストまたはクライアントのアーキテクチャに関係なく、IP アドレスの最下位ビットが整数の最下位ビットに保存されます。たとえば、1 は 0.0.0.1 を意味し、0x1FF は 0.0.1.255 を意味します。
この関数は、最上位 32 ビットがすべて 0、または最上位 33 ビットがすべて 1 になっているかどうか確認します（32 ビット整数からの符号拡張）。つまり、入力が [-0x80000000, 0xFFFFFFFF] の範囲に入っていない場合にエラーが発生します。
この関数は IPv6 をサポートしていません。
戻りデータの型
BYTES
例
SELECT x, x_hex, FORMAT("%T", NET.IPV4_FROM_INT64(x)) AS ipv4_from_int64
FROM (
SELECT CAST(x_hex AS INT64) x, x_hex
FROM UNNEST(["0x0", "0xABCDEF", "0xFFFFFFFF", "-0x1", "-0x2"]) AS x_hex
);
| x | x_hex | ipv4_from_int64 |
|---|---|---|
| 0 | 0x0 | b"\x00\x00\x00\x00" |
| 11259375 | 0xABCDEF | b"\x00\xab\xcd\xef" |
| 4294967295 | 0xFFFFFFFF | b"\xff\xff\xff\xff" |
| -1 | -0x1 | b"\xff\xff\xff\xff" |
| -2 | -0x2 | b"\xff\xff\xff\xfe" |
NET.IPV4_TO_INT64
NET.IPV4_TO_INT64(addr_bin)
説明
IPv4 アドレスをネットワーク バイト順にバイナリ形式（BYTES）から整数形式に変換します。整数出力の場合、ホストまたはクライアントのアーキテクチャに関係なく、IP アドレスの最下位ビットが整数の最下位ビットに保存されます。たとえば、1 は 0.0.0.1 を意味し、0x1FF は 0.0.1.255 を意味します。出力は範囲 [0, 0xFFFFFFFF] 内にあります。
入力長が 4 でない場合、エラーが発生します。
この関数は IPv6 をサポートしていません。
戻りデータの型
INT64
例
SELECT
FORMAT("%T", x) AS addr_bin,
FORMAT("0x%X", NET.IPV4_TO_INT64(x)) AS ipv4_to_int64
FROM
UNNEST([b"\x00\x00\x00\x00", b"\x00\xab\xcd\xef", b"\xff\xff\xff\xff"]) AS x;
| addr_bin | ipv4_to_int64 |
|---|---|
| b"\x00\x00\x00\x00" | 0x0 |
| b"\x00\xab\xcd\xef" | 0xABCDEF |
| b"\xff\xff\xff\xff" | 0xFFFFFFFF |
NET.HOST
NET.HOST(url)
説明
URL を STRING として受け入れ、ホストを STRING として返します。最適な結果を得るには、URL 値が RFC 3986 で定義されている形式に準拠する必要があります。URL 値が RFC 3986 の形式設定に準拠していない場合、この関数は入力をベストエフォートで解析し、関連する結果を返します。入力を解析できない場合は NULL を返します。
注: この関数は正規化を行いません。
戻りデータの型
STRING
例
SELECT
FORMAT("%T", input) AS input,
description,
FORMAT("%T", NET.HOST(input)) AS host,
FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
SELECT "" AS input, "invalid input" AS description
UNION ALL SELECT "http://abc.xyz", "standard URL"
UNION ALL SELECT "//user:password@a.b:80/path?query",
"standard URL with relative scheme, port, path and query, but no public suffix"
UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
UNION ALL SELECT " www.Example.Co.UK ",
"non-standard URL with spaces, upper case letters, and without scheme"
UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
| 入力 | 説明 | ホスト | サフィックス | ドメイン |
|---|---|---|---|---|
| "" | 無効な入力 | NULL | NULL | NULL |
| "http://abc.xyz" | 標準の URL | "abc.xyz" | "xyz" | "abc.xyz" |
| "//user:password@a.b:80/path?query" | 相対スキーム、ポート、パス、クエリを含む標準の URL。ただし、パブリック サフィックスはありません。 | "a.b" | NULL | NULL |
| "https://[::1]:80" | IPv6 ホスト付きの標準 URL | "[::1]" | NULL | NULL |
| "http://例子.卷筒纸.中国" | 国際化ドメイン名付きの標準 URL | "例子.卷筒纸.中国" | "中国" | "卷筒纸.中国" |
| " www.Example.Co.UK " | スペースと大文字を含み、スキームのない非標準 URL | "www.Example.Co.UK" | "Co.UK" | "Example.Co.UK" |
| "mailto:?to=&subject=&body=" | URL ではなく URI（サポート対象外） | "mailto" | NULL | NULL |
NET.PUBLIC_SUFFIX
NET.PUBLIC_SUFFIX(url)
説明
URL を STRING として受け入れ、そのパブリック サフィックス（com、org、net など）を STRING として返します。パブリック サフィックスは、publicsuffix.org に登録されている ICANN ドメインです。最適な結果を得るには、URL 値が RFC 3986 で定義されている形式に準拠する必要があります。URL 値が RFC 3986 の形式設定に準拠していない場合、この関数は入力をベストエフォートで解析し、関連する結果を返します。
次の条件を満たす場合、この関数は NULL を返します。
- 入力からホストを解析できない。
- 解析したホストで、先頭または末尾以外の場所でドットが連続している。
- 解析したホストにパブリック サフィックスが含まれていない。
パブリック サフィックスを検索する前に、この関数はホストを一時的に正規化します。その際、大文字の英字を小文字に変換し、ASCII 以外の文字を Punycode でエンコードします。次に、正規化されたホストではなく、元のホストの一部としてパブリック サフィックスを返します。
注: この関数は Unicode の正規化を行いません。
注: publicsuffix.org のパブリック サフィックス データにはプライベート ドメインも含まれます。この関数はプライベート ドメインを無視します。
注: パブリック サフィックス データは変更される可能性があります。今後、NULL 結果を生成する入力で NULL 以外の値が生成される可能性があります。
戻りデータの型
STRING
例
SELECT
FORMAT("%T", input) AS input,
description,
FORMAT("%T", NET.HOST(input)) AS host,
FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
SELECT "" AS input, "invalid input" AS description
UNION ALL SELECT "http://abc.xyz", "standard URL"
UNION ALL SELECT "//user:password@a.b:80/path?query",
"standard URL with relative scheme, port, path and query, but no public suffix"
UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
UNION ALL SELECT " www.Example.Co.UK ",
"non-standard URL with spaces, upper case letters, and without scheme"
UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
| 入力 | 説明 | ホスト | サフィックス | ドメイン |
|---|---|---|---|---|
| "" | 無効な入力 | NULL | NULL | NULL |
| "http://abc.xyz" | 標準の URL | "abc.xyz" | "xyz" | "abc.xyz" |
| "//user:password@a.b:80/path?query" | 相対スキーム、ポート、パス、クエリを含む標準の URL。ただし、パブリック サフィックスはありません。 | "a.b" | NULL | NULL |
| "https://[::1]:80" | IPv6 ホスト付きの標準 URL | "[::1]" | NULL | NULL |
| "http://例子.卷筒纸.中国" | 国際化ドメイン名付きの標準 URL | "例子.卷筒纸.中国" | "中国" | "卷筒纸.中国" |
| " www.Example.Co.UK " | スペースと大文字を含み、スキームのない非標準 URL | "www.Example.Co.UK" | "Co.UK" | "Example.Co.UK |
| "mailto:?to=&subject=&body=" | URL ではなく URI（サポート対象外） | "mailto" | NULL | NULL |
NET.REG_DOMAIN
NET.REG_DOMAIN(url)
説明
URL を STRING として受け入れ、登録済みまたは登録可能なドメイン（パブリック サフィックスと 1 つ前のラベル）を STRING として返します。最適な結果を得るには、URL 値が RFC 3986 で定義されている形式に準拠する必要があります。URL 値が RFC 3986 の形式設定に準拠していない場合、この関数は入力をベストエフォートで解析し、関連する結果を返します。
次の条件を満たす場合、この関数は NULL を返します。
- 入力からホストを解析できない。
- 解析したホストで、先頭または末尾以外の場所でドットが連続している。
- 解析したホストにパブリック サフィックスが含まれていない。
- 解析したホストにパブリック サフィックスが含まれているが、先頭にラベルがない。
パブリック サフィックスを検索する前に、この関数はホストを一時的に正規化します。大文字の英字を小文字に変換し、ASCII 以外のすべての文字を Punycode でエンコードします。次に、正規化されたホストではなく、元のホストの一部として登録済みまたは登録可能なドメインを返します。
注: この関数は Unicode の正規化を行いません。
注: publicsuffix.org のパブリック サフィックス データにはプライベート ドメインも含まれます。この関数は、プライベート ドメインをパブリック サフィックスとして扱いません。たとえば「us.com」がパブリック サフィックス データ内のプライベート ドメインである場合、NET.REG_DOMAIN（「foo.us.com」）は、「foo.us.com」（プライベート ドメイン「us.com」および先行するラベル「foo」）ではなく、「us.com」（パブリック サフィックス「com」および先行するラベル「us」）を返します。
注: パブリック サフィックス データは変更される可能性があります。今後、NULL 結果を生成する入力で NULL 以外の値が生成される可能性があります。
戻りデータの型
STRING
例
SELECT
FORMAT("%T", input) AS input,
description,
FORMAT("%T", NET.HOST(input)) AS host,
FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
SELECT "" AS input, "invalid input" AS description
UNION ALL SELECT "http://abc.xyz", "standard URL"
UNION ALL SELECT "//user:password@a.b:80/path?query",
"standard URL with relative scheme, port, path and query, but no public suffix"
UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
UNION ALL SELECT " www.Example.Co.UK ",
"non-standard URL with spaces, upper case letters, and without scheme"
UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
| 入力 | 説明 | ホスト | サフィックス | ドメイン |
|---|---|---|---|---|
| "" | 無効な入力 | NULL | NULL | NULL |
| "http://abc.xyz" | 標準の URL | "abc.xyz" | "xyz" | "abc.xyz" |
| "//user:password@a.b:80/path?query" | 相対スキーム、ポート、パス、クエリを含む標準の URL。ただし、パブリック サフィックスはありません。 | "a.b" | NULL | NULL |
| "https://[::1]:80" | IPv6 ホスト付きの標準 URL | "[::1]" | NULL | NULL |
| "http://例子.卷筒纸.中国" | 国際化ドメイン名付きの標準 URL | "例子.卷筒纸.中国" | "中国" | "卷筒纸.中国" |
| " www.Example.Co.UK " | スペースと大文字を含み、スキームのない非標準 URL | "www.Example.Co.UK" | "Co.UK" | "Example.Co.UK" |
| "mailto:?to=&subject=&body=" | URL ではなく URI（サポート対象外） | "mailto" | NULL | NULL |
演算子
演算子は特殊文字やキーワードによって表されます。関数の呼び出し構文は使用しません。また、演算子は、オペランドとも呼ばれる任意の数のデータ入力を操作し、結果を返します。
一般的な慣例:
- 特記のない限り、オペランドの 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 関数を使用してください。
加算、減算、乗算の結果の型:
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | INT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
除算の結果の型:
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| INT64 | FLOAT64 | NUMERIC | FLOAT64 |
| NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
| FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
単項マイナスの場合の結果のデータ型:
| 入力 | INT64 | NUMERIC | FLOAT64 |
|---|---|---|---|
| 出力 | INT64 | NUMERIC | 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 は、AND、OR、NOT の論理演算子をサポートしています。論理演算子は BOOL 入力または NULL 入力のみを許可し、3 値論理を使用して結果を生成します。結果は TRUE、FALSE、NULL のいずれかになります。
| 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 | FALSE | 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 によって指定されたパターンと一致するかどうかをチェックします。式には次の文字を含めることができます。
|
| 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 を返します。
expr と expr_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_result と else_result は、共通のスーパータイプに強制型変換できる必要があります。
戻りデータの型
true_result と else_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 は評価されません。
expr と null_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 を返します。
expr と expr_to_match は、共通のスーパータイプに暗黙的に強制型変換できる必要があります。また、比較可能型にする必要があります。
NULLIF は STRUCT 型をサポートしていません。
戻りデータの型
expr と expr_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 > 0WHERE 句の条件間の順序が保証されず、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