式、関数、演算子

式、関数、演算子

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

関数呼び出しルール

関数の表記内に特に明示的な指示がない限り、以下の規則はすべての関数に適用されます。

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

変換規則

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

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

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

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

キャスティング

構文:

CAST(expr AS typename)

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

例:

CAST(x=1 AS STRING)

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

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

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

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

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 では、次の変換関数も使用できます。

集計関数

集計関数は、一連の値に対して計算を実行する関数です。集計関数には、COUNT、MIN、MAX などがあります。

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM UNNEST([NULL, "apple", "pear", "orange"]) as fruit;

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

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

ANY_VALUE

ANY_VALUE(expression)

説明

ゼロ入力行がある場合、入力値から任意の値を返すか、NULL を返します。expression が、すべての行に対して NULL を評価する場合 NULL を、それ以外では非 NULL 値を返します。返される値は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。

サポートされる引数の型

すべて

返されるデータ型

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

例

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

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

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression)

説明

expression 値の配列を返します。

サポートされる引数の型

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

省略可能な句

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

出力要素の順序

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

返されるデータ型

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

AVG

AVG([DISTINCT] expression)

説明

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

サポートされる引数の型

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

省略可能な句

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

返されるデータ型

FLOAT64

例

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

+-----+
| avg |
+-----+
| 3   |
+-----+

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

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression)

説明

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

サポートされる引数の型

INT64

省略可能な句

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

返されるデータ型

INT64

例

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

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

BIT_OR

BIT_OR([DISTINCT] expression)

説明

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

サポートされる引数の型

INT64

省略可能な句

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

返されるデータ型

INT64

例

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

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

BIT_XOR

BIT_XOR([DISTINCT] expression)

説明

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

サポートされる引数の型

INT64

省略可能な句

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

返されるデータ型

INT64

例

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

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

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

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

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

COUNT

COUNT(*)

COUNT([DISTINCT] expression)

説明

入力内の行数を返します。
expression が NULL 以外の値と評価された行数を返します。

サポートされる引数の型

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

省略可能な句

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

戻りデータの型

INT64

例

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

+------------+---------+--------------+
| count_star | count_x | count_dist_x |
+------------+---------+--------------+
| 5          | 4       | 3            |
+------------+---------+--------------+

COUNTIF

COUNTIF([DISTINCT] expression)

説明

expression に対する TRUE の値の数を返します。ゼロ入力行がある場合に 0 を返すか、expression によってすべての行が FALSE と評価されます。

サポートされる引数の型

BOOL

省略可能な句

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

戻りデータの型

INT64

例

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

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

説明

すべての非 NULL 式の論理 AND を返します。ゼロ入力行がある場合は NULL を返すか、expression によってすべての行が NULL と評価されます。

サポートされる引数の型

BOOL

戻りデータの型

BOOL

例

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

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

LOGICAL_OR

LOGICAL_OR(expression)

説明

すべての非 NULL 式の論理 OR を返します。ゼロ入力行がある場合は NULL を返すか、expression によってすべての行が NULL と評価されます。

サポートされる引数の型

BOOL

戻りデータの型

BOOL

例

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

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

MAX

MAX(expression)

説明

非 NULL 式の最大値を返します。ゼロ入力行がある場合は NULL を返すか、expression によってすべての行が NULL と評価されます。入力に NaN が含まれている場合、NaN を返します。

サポートされる引数の型

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

戻りデータの型

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

例

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

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

MIN

MIN(expression)

説明

非 NULL 式の最小値を返します。ゼロ入力行がある場合は NULL を返すか、expression によってすべての行が NULL と評価されます。入力に NaN が含まれている場合、NaN を返します。

サポートされる引数の型

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

戻りデータの型

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

例

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

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

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter])

説明

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

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

サポートされる引数の型

STRING BYTES

省略可能な句

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

出力要素の順序

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

戻りデータの型

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", NULL, "pear", "banana", "pear"]) AS fruit;

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

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

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

SUM

SUM([DISTINCT] expression)

説明

非 NULL 値の合計を返します。

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

サポートされる引数の型

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

省略可能な句

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

戻りデータの型

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

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

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

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

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

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

例

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

+-----+
| sum |
+-----+
| 25  |
+-----+

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

+-----+
| sum |
+-----+
| 15  |
+-----+

数学関数

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

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

ABS

ABS(X)

説明

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

SIGN

SIGN(X)

説明

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

IS_INF

IS_INF(X)

説明

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

IS_NAN

IS_NAN(X)

説明

値が NaN 値である場合、TRUE を返します。NULL 入力に対しては NULL を返します。

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

説明

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

特殊ケース:

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

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

`IEEE_DIVIDE` の特殊なケース

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

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

SQRT

SQRT(X)

説明

X の平方根を計算します。X が 0 より小さい場合、エラーを出します。X が +inf の場合は +inf を返します。

POW

POW(X, Y)

説明

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

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

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

POWER

POWER(X, Y)

説明

POW() と同義。

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

以下は、POW(X, Y) と POWER(X, Y) の特殊なケースです。

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

EXP

EXP(X)

説明

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

LN

LN(X)

説明

X の自然対数を計算します。X がゼロ以下の場合は、エラーになります。X が +inf の場合、+inf を返します。

LOG

LOG(X [, Y])

説明

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

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

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

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

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

LOG10

LOG10(X)

説明

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

GREATEST

GREATEST(X1,...,XN)

説明

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

LEAST

LEAST(X1,...,XN)

説明

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

DIV

DIV(X, Y)

説明

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

MOD

MOD(X, Y)

説明

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

ROUND

ROUND(X [, N])

説明

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

TRUNC

TRUNC(X [, N])

説明

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

CEIL

CEIL(X)

説明

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

CEILING

CEILING(X)

説明

CEIL(X) と同義

FLOOR

FLOOR(X)

説明

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

丸め関数の動作の例

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

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

COS

COS(X)

説明

X のコサインを計算します。失敗することはありません。

COSH

COSH(X)

説明

X の双曲線コサインを計算します。オーバーフローが発生した場合、エラーになります。

ACOS

ACOS(X)

説明

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

ACOSH

ACOSH(X)

説明

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

SIN

SIN(X)

説明

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

SINH

SINH(X)

説明

X の双曲線サインを計算します。オーバーフローが発生した場合、エラーになります。

ASIN

ASIN(X)

説明

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

ASINH

ASINH(X)

説明

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

TAN

TAN(X)

説明

X のタンジェントを計算します。オーバーフローが発生した場合、エラーになります。

TANH

TANH(X)

説明

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

ATAN

ATAN(X)

説明

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

ATANH

ATANH(X)

説明

X の逆双曲線タンジェントを計算します。X の絶対値が 1 以上の場合、エラーになります。

ATAN2

ATAN2(Y, X)

説明

象限を判断するために 2 つの引数の符号を用いて、Y/X のアークタンジェントの主値を計算します。戻り値は [-π,π] の範囲内です。この関数の動作は、下の表で詳しく説明します。

`ATAN2()` の特殊なケース

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

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

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

ハッシュ関数

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

説明

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

戻り値の型

INT64

例

SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM (
  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
);

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

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

SHA256

SHA256(input)

説明

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

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

戻り値の型

BYTES

例

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

説明

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

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

戻り値の型

BYTES

例

SELECT SHA512("Hello World") as sha512;

文字列関数

これらの文字列関数は、2 種類のデータ型 STRING と BYTE の値に使用できます。STRING 値は、正しい形式の UTF-8 にする必要があります。

STRPOS のような位置の値を返す関数は、それらの位置として INT64 を返します。値 1 は最初の文字（またはバイト）を参照し、2 は 2 番目のものを参照し、以下同様に続きます。値 0 は無効なインデックスであることを示します。STRING 型を扱っている場合、返される位置は文字の位置です。

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

BYTE_LENGTH

BYTE_LENGTH(value)

説明

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

戻り値の型

INT64

例

Table example:

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

例

Table example:

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

例

Table example:

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

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

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

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

説明

拡張 ASCII コードポイントの配列（INT64 の ARRAY）をとり、BYTES を返します。

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

戻り値の型

BYTES

例

以下は、CODE_POINTS_TO_BYTES を使用した基本的な例を示しています。

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

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

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

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

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

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

説明

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

文字列をコードポイント配列に変換する方法については、TO_CODE_POINTS をご覧ください。

戻り値の型

STRING

例

以下は、CODE_POINTS_TO_STRING の基本的な使用方法を示しています。

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

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

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

SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word,
     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 つ以上の value を 1 つに連結します。

戻り値の型

STRING または BYTES

例

Table Employees:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| John        | Doe       |
| Jane        | Smith     |
| Joe         | Jackson   |
+-------------+-----------+

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 つの value を取ります。2 番目の value が最初の value の接尾辞である場合、TRUE を返します。

戻り値の型

BOOL

例

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

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+
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() などの型固有の形式設定関数を使用して形式設定する必要があります。次に例を示します。

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

戻り値

date: January 02, 2015!

構文

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

FORMAT(<format_string>, ...)

<format_string> 式には、ゼロ以上の形式指定子を含めることができます。各形式指定子は % 記号によって導入され、残りの 1 つ以上の引数にマップする必要があります。大部分でこれは 1 対 1 のマッピングになりますが、* 指定子がある場合のみ異なります。たとえば、%.*i は、長さ引数と符号付き整数引数という 2 つの引数にマップします。形式指定子に関連する引数の数が、これらの引数の数と異なる場合、エラーが発生します。

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

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

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

次の表にサポートされる形式指定子が示されています。printf() からの拡張子はイタリック体で示されています。

指定子	説明	例	型
`d` または `i`	10 進の整数	392	INT64
`o`	8 進数	610	INT64*
`x`	16 進の整数	7fa	INT64*
`X`	16 進の整数（大文字）	7FA	INT64*
`f`	10 進の浮動小数点数（小文字）	392.65 `inf` `NaN`	FLOAT64
`F`	10 進の浮動小数点数（大文字）	392.65 `inf` NAN	FLOAT64
`e`	科学的記数法（仮数 / 指数）（小文字）	3.9265e+2 `inf` `NaN`	FLOAT64
`E`	科学的記数法（仮数 / 指数）（大文字）	3.9265E+2 `inf` NAN	FLOAT64
`g`	最短の表記 %e または %f	392.65	FLOAT64
`G`	最短の表記 %E または %F	392.65	FLOAT64
`s`	文字列	sample	STRING
`t`	値を表す出力可能な文字列を返します。多くの場合、引数を STRING にキャストするのに似ています。次の %t セクションをご覧ください。	sample 2014‑01‑01	<任意>
`T`	値の型と類似した型（幅が広い、または文字列であるなど）の有効な Cloud Spanner SQL 定数である文字列を生成します。以下の %T セクションを参照してください。	'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01'	<任意>
`%`	「%%」は単一の「%」を生成します。	%	なし

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

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

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

フラグ

フラグ	説明
`-`	所定のフィールド幅内で左側に寄せます。右寄せがデフォルトです（幅のサブ指定子を参照）。
`+`	正の数であっても、結果の前にプラスまたはマイナス記号（`+` または `-`）を強制的に設定します。デフォルトでは、負の数にのみ `-` 記号が前に付けられます。
<space>	記号が書き込まれない場合、値の前に空白のスペースが挿入されます。
`#`	o、x、X 指定子付きで使用されます。ゼロではない値に対しては、それぞれ 0、0x、0X を値の前に付けます。
`0`	パディングが指定されている場合、スペースではなく、ゼロ（0）で数字の左側にパディングされます（幅のサブ指定子を参照）
`'`	適切なグルーピング文字を使用して整数を形式設定します。次に例を示します。 `FORMAT("%'d", 12345678)` は `12,345,678` を返します `FORMAT("%'x", 12345678)` は `bc:614e` を返します `FORMAT("%'o", 55555)` は `15,4403` を返しますこのフラグは、10 進数、16 進数、および 8 進数の値のみが対象になります。

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

幅

幅	説明
<number>	印刷される最小文字数です。印刷される値がこの数値よりも短い場合、結果は空白スペースでパディングされます。結果のほうが大きい場合でも、値は切り詰められません。
`*`	幅は形式設定文字列には指定されませんが、形式設定する必要のある引数の前に追加される整数値引数として指定されます。

精度

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

%t および %T の作用

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

%t は常に読み取り可能な形式の値を意味します。

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

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

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

エラー条件

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

FORMAT('%s', 1)

FORMAT('%')

NULL 引数の処理

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

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

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

FORMAT('00-%t-00', <NULL expression>);

戻り値

00-NULL-00

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

FLOAT64 は +/-inf または NaN にすることができます。引数にこれらのいずれかの値が含まれている場合、形式指定子 %f、%F、%e、%E、%g、%G および %t の結果は、必要に応じて inf、-inf または nan（または同じ文字の大文字）になります。これは、Cloud Spanner SQL がこれらの値を STRING にキャストする動作と整合します。%T の場合、Cloud Spanner SQL は文字列ではないリテラル表記を持たない FLOAT64 値に対して符号付きの文字列を返します。

FROM_BASE64

FROM_BASE64(string_expr)

説明

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

戻り値の型

BYTES

例

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

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

FROM_HEX

FROM_HEX(string)

説明

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

戻り値の型

BYTES

例

SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM UNNEST(['00010203aaeeefff',
             '0AF',
             '666f6f626172']) as hex_str;

+------------------+----------------------------------+
| hex_str          | bytes_str                        |
+------------------+----------------------------------+
| 0AF              | \x00\xaf                         |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172     | foobar                           |
+------------------+----------------------------------+

LENGTH

LENGTH(value)

説明

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

戻り値の型

INT64

例

Table example:

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

説明

original_value の前に pattern を付加した値を返します。return_length は、戻り値の長さを指定する INT64 です。original_value が BYTES の場合、return_length はバイト数になります。original_value が STRING の場合、return_length 文字数です。

pattern のデフォルト値は空白です。

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

return_length が original_value 以下の場合、この関数は return_length の値まで切り捨てて original_value 値を返します。たとえば、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

例

Table items:

+----------------+
| item           |
+----------------+
| FOO            |
| BAR            |
| BAZ            |
+----------------+

SELECT
  LOWER(item) AS example
FROM items;

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

LTRIM

LTRIM(value1[, value2])

説明

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

戻り値の型

STRING または BYTES

例

Table items:

+----------------+
| item           |
+----------------+
|    apple       |
|    banana      |
|    orange      |
+----------------+

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

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

Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

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

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

Table items:

+----------------+
| item           |
+----------------+
| xxxapplexxx    |
| yyybananayyy   |
| zzzorangezzz   |
| xyzpearzyz     |
+----------------+

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

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

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regex)

説明

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

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

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

注: Cloud Spanner SQL は、re2 ライブラリを使用した正規表現をサポートしています。正規表現の構文については、該当するドキュメントをご覧ください。

戻り値の型

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, regex)

説明

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

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

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

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

戻り値の型

STRING または BYTES

例

Table email_addresses:

+-------------------------+
| email                   |
+-------------------------+
| foo@example.com         |
| bar@example.com         |
| baz@example.com         |
+-------------------------+

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

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+

Table email_addresses:

+-------------------------+
| email                   |
+-------------------------+
| foo@example.com         |
| bar@example.com         |
| baz@example.com         |
+-------------------------+

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, regex)

説明

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

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

戻り値の型

STRING または BYTES の配列

例

Table code_markdown:

+------------------------------------+
| code                               |
+------------------------------------+
| Try `function(x)` or `function(y)` |
+------------------------------------+

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

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

REGEXP_REPLACE

REGEXP_REPLACE(value, regex, replacement)

説明

正規表現 regex と一致する value のすべての部分文字列が replacement で置き換えられて、STRING が返されます。

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

注: 正規表現にバックスラッシュを追加するには、あらかじめエスケープしておく必要があります。たとえば、SELECT REGEXP_REPLACE("abc", "b(.)", "X\\1"); は aXc を返します。

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

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

戻り値の型

STRING または BYTES

例

Table markdown:

+-------------------------+
| heading                 |
+-------------------------+
| # Heading               |
| # Another 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

例

+--------------------+
| dessert            |
+--------------------+
| apple pie          |
| blackberry pie     |
| cherry pie         |
+--------------------+

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

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

REPEAT

REPEAT(original_value, repetitions)

説明

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

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

戻り値の型

STRING または BYTES

例

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

t	n	REPEAT
abc	3	abcabcabc
例子	2	例子例子
abc	NULL	NULL
NULL	3	NULL

REVERSE

REVERSE(value)

説明

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

戻り値の型

STRING または BYTES

例

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

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

RPAD

RPAD(original_value, return_length[, pattern])

説明

original_value の後に pattern を付加した値を返します。return_length は、戻り値の長さを指定する INT64 です。original_value が BYTES の場合、return_length はバイト数になります。original_value が STRING の場合、return_length 文字数です。

pattern のデフォルト値は空白です。

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

return_length が original_value 以下の場合、この関数は return_length の値まで切り捨てて original_value 値を返します。たとえば、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

例

Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

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

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

Table items:

+----------------+
| item           |
+----------------+
| applexxx       |
| bananayyy      |
| orangezzz      |
| pearxyz        |
+----------------+

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

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

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

説明

バイトのシーケンスを文字列に変換します。すべての無効な UTF-8 文字が Unicode 置き換え文字 U+FFFD に置き換えられます。

戻り値の型

STRING

例

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

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

SPLIT

SPLIT(value[, delimiter])

説明

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

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

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

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

空の STRING を分割すると、1 つの空の STRING で ARRAY を返します。

戻り値の型

STRING 型の ARRAY または BYTES 型の ARRAY

例

SELECT SPLIT(letter_group, " ") as example
FROM (
  SELECT "a b c d" as letter_group
  UNION ALL SELECT "e f g h" as letter_group
  UNION ALL SELECT "i j k l" as letter_group) AS letters;

+----------------------+
| example              |
+----------------------+
| [a, b, c, d]         |
| [e, f, g, h]         |
| [i, j, k, l]         |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

説明

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

戻り値の型

BOOL

例

SELECT
  STARTS_WITH(item, "b") as example
FROM (
  SELECT "foo" as item
  UNION ALL SELECT "bar" as item
  UNION ALL SELECT "baz" as item) AS items;

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

STRPOS

STRPOS(string, substring)

説明

string 内の substring の初出の 1 から始まるインデックスを返します。substring が見つからない場合は 0 を返します。

戻り値の型

INT64

例

Table email_addresses:

+-------------------------+
| email_address           |
+-------------------------+
| foo@example.com         |
| foobar@example.com      |
| foobarbaz@example.com   |
| quxexample.com          |
+-------------------------+

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

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

SUBSTR

SUBSTR(value, position[, length])

説明

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

position が負の場合、この関数は value の後ろからカウントします。-1 は最後の文字を指します。

position が STRING の左側にある場合（position = 0 または position < -LENGTH(value)）、この関数は position = 1 から開始します。length が value の長さを超える場合は、length よりも少ない文字数が返されます。

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

戻り値の型

STRING または BYTES

例

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

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

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

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

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

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

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

TO_BASE64

TO_BASE64(bytes_expr)

説明

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

戻り値の型

STRING

例

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

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

TO_CODE_POINTS

TO_CODE_POINTS(value)

説明

value をとって、INT64 の配列を返します。

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

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

戻り値の型

INT64 の ARRAY

例

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

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

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

以下は、BYTES の整数値を、該当する ASCII 文字値に変換する例を示しています。

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

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

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

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

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

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

TO_HEX

TO_HEX(bytes)

説明

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

戻り値の型

STRING

例

SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM UNNEST([b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF',
             b'foobar']) AS byte_str;

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

TRIM

TRIM(value1[, value2])

説明

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

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

戻り値の型

STRING または BYTES

例

Table items:

+----------------+
| item           |
+----------------+
|    apple       |
|    banana      |
|    orange      |
+----------------+

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

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

Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

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

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

Table items:

+----------------+
| item           |
+----------------+
| xxxapplexxx    |
| yyybananayyy   |
| zzzorangezzz   |
| xyzpearxyz     |
+----------------+

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

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

UPPER

UPPER(value)

説明

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

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

戻り値の型

STRING または BYTES

例

Table items:

+----------------+
| item           |
+----------------+
| foo            |
| bar            |
| baz            |
+----------------+

SELECT
  UPPER(item) AS example
FROM items;

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

JSON 関数

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

JSON_QUERY または JSON_VALUE

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

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

説明

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

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

json_path_string_literal パラメータは、JSON 形式の文字列から取得する値を識別します。このパラメータは JSONPath 形式を使用して作成します。このパラメータの書式は $ 記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。ドットを使用するか、二重引用符で囲むことで子の値を識別できます。JSON オブジェクトが配列の場合は、ブラケットを使用して配列のインデックスを指定できます。

JSONPath	説明
$	ルートのオブジェクトまたは要素
.	子の演算子
[]	添字演算子

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

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

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

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

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

例

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

上のクエリは、次の結果を生成します。

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"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      |
+-----------+-------------+----------+--------+

配列関数

ARRAY

ARRAY(subquery)

説明

ARRAY 関数は、サブクエリの各行に対して 1 つの要素を含む ARRAY を返します。

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

制約

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

戻り値の型

ARRAY

例

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

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

複数の列を含むサブクエリから ARRAY を構成するには、SELECT AS STRUCT を使用するようにサブクエリを変更します。これで ARRAY 関数は 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

例

SELECT list, ARRAY_LENGTH(list) AS size
FROM (
  SELECT ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie"] as list) AS items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, 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 値とその前にある区切り文字を無視します。

例

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM (
  SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie", NULL] as list) AS items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM (
  SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie", NULL] as list) AS items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

説明

値の配列を返します。start_expression と end_expression のパラメータは、配列の包括的な開始点と終了点を示します。

GENERATE_ARRAY 関数は、入力として次のデータ型を受け入れます。

INT64
FLOAT64

step_expression パラメータは、配列の値を生成するために使用される増分を示します。このパラメータのデフォルト値は 1 です。

この関数は、step_expression が 0 に設定されている場合、またはいずれかの入力値が NaN の場合にエラーを返します。

引数が NULL 場合、関数は NULL 配列を返します。

戻りデータの型

ARRAY

例

以下の場合、整数の配列を返します。デフォルトのステップは 1 です。

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

以下の場合、ユーザーが指定したステップサイズを使用して配列を返します。

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

以下の場合、負の値を使用して配列を返します。ステップサイズは -3 です。

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

以下の場合、start_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] |
+--------------------------------------------------------------------------+

以下の場合、非定数の日付を使用して配列を生成します。

WITH StartsAndEnds AS (
  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"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

+--------------------------------------------------------------+
| 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 内の要素によって異なります。

例

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM (
  SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie"] as list) AS 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

例

SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr) AS example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

日付関数

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

CURRENT_DATE

CURRENT_DATE([time_zone])

説明

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

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

time_zone パラメータが NULL と評価されると、この関数は NULL を返します。

戻りデータの型

DATE

例

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

説明

指定した日付の一部に対応する値が返されます。part は、次のいずれかにする必要があります。

DAYOFWEEK: 週の最初の曜日が日曜日である範囲 [1,7] 内の値を返します。
DAY
DAYOFYEAR
WEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。
ISOWEEK: date_expression の ISO 8601 週番号を返します。ISOWEEK は月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初の ISOWEEK は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。
MONTH
QUARTER: 範囲 [1,4] 内の値を返します。
YEAR
ISOYEAR: 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_expression を 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_expr date_part)

説明

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

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

DAY
WEEK。7 DAY（7 日）と等価。
MONTH
QUARTER
YEAR

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

戻りデータの型

DATE

例

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

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

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

説明

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

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

DAY
WEEK。7 DAY（7 日）と等価。
MONTH
QUARTER
YEAR

戻りデータの型

DATE

例

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

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

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

説明

2 つの date_expression の間にある date_part 境界の数を返します。最初の日付が 2 番目の日付よりも前にある場合、結果は負の値になります。

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

DAY
WEEK: この日付パーツは日曜日から始まります。
MONTH
QUARTER
YEAR

戻りデータの型

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 つあります。

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

説明

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

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

DAY
WEEK
MONTH
QUARTER
YEAR

戻りデータの型

DATE

例

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

説明

INT64_expression を 1970-01-01 からの日数として解釈します。

戻りデータの型

DATE

例

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

説明

指定した format_string に従って、date_expr を形式設定します。

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

戻りデータの型

STRING

例

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

説明

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

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

指定されていないフィールド。 指定されていないフィールドはすべて、1970-01-01 から初期設定されます。
大文字と小文字の区別がない名前。 Monday や February などの名前は、大文字と小文字が区別されません。
空白文字。 形式設定文字列内の 1 つ以上の連続する空白文字は、日付文字列内のゼロ以上の連続する空白文字と同じです。また、日付文字列内の先頭および末尾の空白文字は、それらが形式設定文字列内にない場合でも常に許可されます。
形式設定の優先度。 2 つ（またはそれ以上）の形式設定要素が重複する情報を持つ場合（たとえば、%F と %Y の両方が年に影響を及ぼす場合など）、一般に、最後に指定した内容によって、前に指定した内容がオーバーライドされます。

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

戻りデータの型

DATE

例

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

説明

1970-01-01 からの日数を返します。

戻りデータの型

INT64

例

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

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

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

形式設定要素	説明
%A	完全な曜日名。
%a	省略された曜日名。
%B	完全な月の名前。
%b または %h	省略された月の名前。
%C	10 進数として表示される世紀（年を 100 で割って、整数値になるよう切り捨てる）（00～99）。
%D	%m/%d/%y 形式の日付。
%d	10 進数として表示される、月内の日付（01～31）。
%e	10 進数として表示される、月内の日付（1～31）。1 桁の場合は前にスペースが入れられます。
%F	%Y-%m-%d 形式の日付。
%G	10 進数として表示される、世紀を含む ISO 8601 年。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。%G と %Y は、グレゴリオ暦年と ISO 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%g	10 進数として表示される、世紀を含まない ISO 8601 年（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	10 進数として表示される、年内の週番号（週の 1 日目が月曜日）（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                              |
+----------------------------------+
| 2016-05-16 18:12:47.145482639+00 |
+----------------------------------+

EXTRACT

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

説明

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

使用可能な part 値は次のとおりです。

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

返される値によって、それよりも下位の期間が切り捨てられます。たとえば、秒数を抽出するとき、EXTRACT はミリ秒とマイクロ秒の値を切り捨てます。

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

戻りデータの型

通常、INT64。part が DATE の場合、DATE が返されます。

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

SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM (
    SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
    SELECT TIMESTAMP '2007-12-31' UNION ALL
    SELECT TIMESTAMP '2009-01-01' UNION ALL
    SELECT TIMESTAMP '2009-12-31' UNION ALL
    SELECT TIMESTAMP '2017-01-02' UNION ALL
    SELECT TIMESTAMP '2017-05-26'
  ) AS Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

STRING

STRING(timestamp_expression[, timezone])

説明

timestamp_expression を STRING データ型に変換します。タイムゾーンを指定する省略可能なパラメータを使用できます。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

STRING

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])

説明

STRING 表現を TIMESTAMP データ型に変換します。
DATE オブジェクトを TIMESTAMP データ型に変換します。

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

戻りデータの型

TIMESTAMP

例

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

+------------------------+------------------------+
| timestamp_str          | timestamp_date         |
+------------------------+------------------------+
| 2008-12-25 15:30:00-08 | 2008-12-25 00:00:00-08 |
+------------------------+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

説明

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

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

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

戻りデータの型

TIMESTAMP

例

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

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

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

説明

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

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

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

戻りデータの型

TIMESTAMP

例

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

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

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

説明

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

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

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

戻りデータの型

INT64

例

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

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

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

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

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

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

説明

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

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

NANOSECOND
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

TIMESTAMP_TRUNC 関数はオプションの time_zone パラメータをサポートします。このパラメータは次の date_parts に適用されます。

MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

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

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

戻りデータの型

TIMESTAMP

例

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

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

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

説明

指定された format_string に従って、タイムスタンプを形式設定します。

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

戻りデータの型

STRING

例

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

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

説明

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

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

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

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

戻りデータの型

TIMESTAMP

例

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

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

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

説明

1970-01-01 00:00:00 UTC 以降の秒数として int64_expression を解釈します。

戻りデータの型

TIMESTAMP

例

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

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

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

説明

1970-01-01 00:00:00 UTC 以降のミリ秒数として int64_expression を解釈します。

戻りデータの型

TIMESTAMP

例

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

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

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

説明

1970-01-01 00:00:00 UTC 以降のマイクロ秒数として int64_expression を解釈します。

戻りデータの型

TIMESTAMP

例

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

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

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

説明

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

戻りデータの型

INT64

例

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30: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 UTC") as millis;

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

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

説明

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

戻りデータの型

INT64

例

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

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

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

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

形式設定要素	説明
%A	完全な曜日名。
%a	省略された曜日名。
%B	完全な月の名前。
%b または %h	省略された月の名前。
%C	10 進数として表示される世紀（年を 100 で割って、整数値になるよう切り捨てる）（00～99）。
%c	日付および時刻の表記。
%D	%m/%d/%y 形式の日付。
%d	10 進数として表示される、月内の日付（01～31）。
%e	10 進数として表示される、月内の日付（1～31）。1 桁の場合は前にスペースが入れられます。
%F	%Y-%m-%d 形式の日付。
%G	10 進数として表示される、世紀を含む ISO 8601 年。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。%G と %Y は、グレゴリオ暦年と ISO 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%g	10 進数として表示される、世紀を含まない ISO 8601 年（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	10 進数として表示される、年内の週番号（週の 1 日目が月曜日）（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 によって、年を完全に表現するために必要な数の文字が生成されます。

タイムゾーンの定義

特定の日付およびタイムスタンプ関数を使用して、デフォルトのタイムゾーンをオーバーライドし、別の値を指定できます。次の形式を使用し、UTC オフセットを指定してタイムゾーンを指定できます。

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

例:

-08:00

演算子

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

一般的な慣例:

特記のない限り、オペランドの 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 項
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

下の例よりも、上の例のようにすることをおすすめします。

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	FLOAT64
INT64	INT64	FLOAT64
FLOAT64	FLOAT64	FLOAT64

減算の結果の型:

	INT64	FLOAT64
INT64	INT64	FLOAT64
FLOAT64	FLOAT64	FLOAT64

除算の結果の型:

	INT64	FLOAT64
INT64	FLOAT64	FLOAT64
FLOAT64	FLOAT64	FLOAT64

単項マイナスの結果の型:

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

ビット演算子

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

名前	構文	入力データ型	説明
ビット演算 NOT	~ X	整数または BYTES	与えられた変数値の補数を形成し、各ビットの論理否定を実行します。
ビット演算 OR	X \| Y	X: 整数または BYTES Y: X と同じ	同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに論理包含的和演算を実行します。X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。
ビット演算 XOR	X ^ Y	X: 整数または BYTES Y: X と同じ	同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに排他的論理和演算を実行します。X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。
ビット演算 AND	X & Y	X: 整数または BYTES Y: X と同じ	同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに論理 AND 演算を実行します。X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。
左シフト	X << Y	X: 整数または BYTES Y: INT64	第 1 オペランド X を左にシフトします。第 2 オペランド Y が第 1 オペランド X のビット長（たとえば、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 が負の数の場合、この演算子はエラーを返します。

論理演算子

すべての論理演算子は BOOL 入力のみ許可します。

名前	構文	説明
論理 NOT	NOT X	入力が TRUE の場合は FALSE を返します。入力が FALSE の場合は TRUE を返します。それ以外では `NULL` を返します。
論理 AND	X AND Y	少なくとも 1 つの入力が FALSE の場合、FALSE を返します。X と Y の両方が TRUE の場合、TRUE を返します。それ以外では `NULL` を返します。
論理 OR	X OR Y	X と Y の両方が FALSE であれば FALSE を返します。少なくとも 1 つの入力が TRUE の場合、TRUE を返します。それ以外では `NULL` を返します。

比較演算子

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

STRUCT は、4 つの比較演算子（等号（=）、不等号（!= および <>）、IN）のみをサポートします。

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

FLOAT64: NaN との比較演算は常に FALSE を返す。ただし、!= と <> は TRUE を返します。
BOOL: FALSE は TRUE よりも小さい。
STRING: 文字列はコードポイントごとに比較される。つまり、正規的に同等の文字列は、それらが最初に正規化されている場合にのみ等しいと比較されることが保証されています。
NULL: 変換がここで停止する。NULL 入力を持つ演算はすべて NULL を返します。

名前	構文	説明
より小さい	X < Y	X が Y よりも小さい場合に TRUE を返します。
以下	X <= Y	X が Y 以下の場合、TRUE を返します。
より大きい	X > Y	X が Y よりも大きい場合、TRUE を返します。
以上	X >= Y	X が Y 以上の場合、TRUE を返します。
等しい	X = Y	X が Y と等しい場合、TRUE を返します。
等しくない	X != Y X <> Y	X が Y と等しくない場合、TRUE を返します。
BETWEEN	X [NOT] BETWEEN Y AND Z	X が指定された範囲内に [ない] 場合、TRUE を返します。「X BETWEEN Y AND Z」の結果は「Y <= X AND X <= Z」と等価ですが、前者では X は一度しか評価されません。
LIKE	X [NOT] LIKE Y	第 1 オペランド X の文字列が第 2 オペランド Y によって指定されたパターンと一致するかどうかをチェックします。式にはこれらの文字を含めることができます。パーセント記号「%」は、任意の数の文字またはバイトと一致します。アンダースコア「_」は、単一の文字またはバイトと一致します。 "\"、"_" または "%" は、2 つのバックスラッシュを使用してエスケープします。たとえば、`"\\%"` のようにします。文字列をそのまま使用する場合には、バックスラッシュを 1 つだけ使用します。たとえば、`r"\%".` のようにします。
IN	複数 - 以下を参照	右オペランドが空の場合、FALSE を返します。左のオペランドが `NULL` の場合は `NULL` を返します。右のオペランドに `NULL` が含まれる場合、TRUE または `NULL` を返します。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 の左辺式を持ち、空ではない右辺式を持つ NULL は常に IN です
IN リスト内に NULL を持つ IN は TRUE または NULL のみを返すことができます。FALSE は返されません
NULL IN (NULL) は NULL を返します
IN UNNEST(<NULL array>) は FALSE（NULL ではない）を返します

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

条件式

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

構文	入力データ型	結果のデータ型	説明
CASE expr WHEN value THEN result [WHEN ...] [ELSE else_result] END	`expr`、`value`: 任意の型	`result`、`else_result`: 入力型のスーパータイプ	`expr` と、連続する各 `WHEN` 句を比較し、この比較で TRUE が返される最初の結果を返します。残りの `WHEN` 句と `else_result` は評価されません。`expr = value` 比較ですべての `WHEN` 句に対して FALSE または `NULL` が返される場合、`else_result` が存在する場合はこれを返し、存在しない場合は `NULL` を返します。`expr` および `value` 式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。等価比較は強制型変換された値に対して行われます。`result` および `else_result` 式は共通のスーパータイプに強制的に型変換できる必要があります。
CASE WHEN cond1 THEN result [WHEN cond2...] [ELSE else_result] END	`cond`: ブール値	`result`、`else_result`: 入力型のスーパータイプ	連続する各 `WHEN` 句の条件 `cond` を評価し、条件が TRUE になる最初の結果を返します。残りのすべての `WHEN` 句と `else_result` は評価されません。すべての条件が FALSE または `NULL` である場合、`else_result` が存在する場合はこれを返し、存在しない場合は `NULL` を返します。`result` および `else_result` 式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。
COALESCE(expr1, ..., exprN)	任意の形式	入力型のスーパータイプ	最初の非 NULL 式の値を返します。残りの式は評価されません。すべての入力式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。
IF(cond, true_result, else_result)	`cond`: ブール値	`true_result`、`else_result`: 任意の型	`cond` が TRUE の場合、`true_result` を返します。それ以外の場合は `else_result` を返します。`cond` が TRUE の場合、`else_result` は評価されません。`cond` が FALSE または `NULL` の場合、`true_result` は評価されません。`true_result` と `else_result` は共通のスーパータイプに強制型変換できる必要があります。
IFNULL(expr, null_result)	任意の形式	任意の型または入力型のスーパータイプ	`expr` が `NULL` の場合、`null_result` を返します。それ以外の場合は、`expr` を返します。`expr` が `NULL` ではない場合、`null_result` は評価されません。`expr` と `null_result` は共通のスーパータイプに暗黙的に強制型変換できる必要があります。`COALESCE(expr, null_result)` と同義です。
NULLIF(expression, expression_to_match)	任意の型 T または T のサブタイプ	任意の型 T または T のサブタイプ	`NULL` が TRUE の場合、`expression = expression_to_match` を返します。それ以外の場合、`expression` を返します。`expression` と `expression_to_match` は共通のスーパータイプに暗黙的に強制型変換できる必要があります。等価比較は、強制型変換された値に対して行われます。 `NULLIF` は `STRUCT` 型をサポートしていません。

式のサブクエリ

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
EXISTS	`NOT EXISTS(SELECT x,y,z FROM table WHERE y=z)`	BOOL
IN	`x IN (SELECT y FROM table WHERE z)`	BOOL
IN	`x NOT IN (SELECT y FROM table WHERE z)`	BOOL

デバッグ関数

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

ERROR

ERROR(error_message)

説明

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

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

戻りデータの型

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

例

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

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

Found unexpected value: baz

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

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

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

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

Error: x must be positive but is -1

関数呼び出しルール

変換規則

キャスティング

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

データ型のキャスティング

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

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

強制型変換

その他の変換関数

集計関数

ANY_VALUE

ARRAY_AGG

AVG

BIT_AND

BIT_OR

BIT_XOR

COUNT

COUNTIF

LOGICAL_AND

LOGICAL_OR

MAX

MIN

STRING_AGG

SUM

数学関数

ABS

SIGN

IS_INF

IS_NAN

IEEE_DIVIDE

IEEE_DIVIDE の特殊なケース

SQRT

POW

POWER

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

EXP

LN

LOG

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

LOG10

GREATEST

LEAST

DIV

MOD

ROUND

TRUNC

CEIL

CEILING

FLOOR

丸め関数の動作の例

COS

COSH

ACOS

ACOSH

SIN

SINH

ASIN

ASINH

TAN

TANH

ATAN

ATANH

ATAN2

ATAN2() の特殊なケース

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

ハッシュ関数

FARM_FINGERPRINT

SHA1

SHA256

SHA512

文字列関数

BYTE_LENGTH

CHAR_LENGTH

CHARACTER_LENGTH

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_STRING

CONCAT

ENDS_WITH

FORMAT

`IEEE_DIVIDE` の特殊なケース

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

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

`ATAN2()` の特殊なケース

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