このトピックでは、関数、演算子、条件式をまとめて説明します。
関数の呼び出し方法、関数呼び出しルール、SAFE
接頭辞、特別な型の引数の詳細については、関数呼び出しをご覧ください。
演算子と条件
演算子
演算子は特殊文字やキーワードによって表されます。関数の呼び出し構文は使用しません。また、演算子は、オペランドとも呼ばれる任意の数のデータ入力を操作し、結果を返します。
一般的な慣例:
- 特記のない限り、オペランドの 1 つが
NULL
の場合、すべての演算子はNULL
を返します。 - 演算結果がオーバーフローした場合、すべての演算子は、エラーをスローします。
- 浮動小数点演算で、オペランドの 1 つが
+/-inf
またはNaN
の場合、+/-inf
またはNaN
が返されます。それ以外の場合には、エラーが返されます。
演算子の優先順位
次の表は、すべての BigQuery 演算子を最も優先順位の高いほうから順に(つまり、ステートメント内で評価される順序で)示しています。
優先順位 | 演算子 | 入力データ型 | 名前 | 演算子の項数 |
---|---|---|---|---|
1 | フィールド アクセス演算子 | JSON 構造体 |
フィールド アクセス演算子 | 2 項 |
配列添字演算子 | ARRAY | 配列位置。OFFSET または ORDINAL とともに使用する必要があります。ARRAY 関数を参照。 | 2 項 | |
JSON 添字演算子 | JSON | JSON でのフィールド名または配列の位置。 | 2 項 | |
2 | + | すべての数値型 | 単項プラス | 単項 |
- | すべての数値型 | 単項マイナス | 単項 | |
~ | 整数または BYTES | ビット演算 NOT | 単項 | |
3 | * | すべての数値型 | 乗算 | 2 項 |
/ | すべての数値型 | 除算 | 2 項 | |
|| | STRING、BYTES、または ARRAY<T> | 連結演算子 | 2 項 | |
4 | + | すべての数値型、INT64 による DATE、INTERVAL | 加算 | 2 項 |
- | すべての数値型、INT64 による DATE、INTERVAL | 減算 | 2 項 | |
5 | << | 整数または BYTES | ビット演算左シフト | 2 項 |
>> | 整数または BYTES | ビット演算右シフト | 2 項 | |
6 | & | 整数または BYTES | ビット演算 AND | 2 項 |
7 | ^ | 整数または BYTES | ビット演算 XOR | 2 項 |
8 | | | 整数または BYTES | ビット演算 OR | 2 項 |
9(比較演算子) | = | 任意の比較可能型。全リストについてはデータ型を参照。 | 等しい | 2 項 |
< | 任意の比較可能型。全リストについてはデータ型を参照。 | より小さい | 2 項 | |
> | 任意の比較可能型。全リストについてはデータ型を参照。 | より大きい | 2 項 | |
<= | 任意の比較可能型。全リストについてはデータ型を参照。 | 以下 | 2 項 | |
>= | 任意の比較可能型。全リストについてはデータ型を参照。 | 以上 | 2 項 | |
!=, <> | 任意の比較可能型。全リストについてはデータ型を参照。 | 等しくない | 2 項 | |
[NOT] LIKE | STRING とバイト | 値が指定されたパターンと一致 [しない] | 2 項 | |
[NOT] BETWEEN | 互換性のある任意の型。全リストについてはデータ型を参照。 | 値が指定された範囲内にある [ない] | 2 項 | |
[NOT]IN | 互換性のある任意の型。全リストについてはデータ型を参照。 | 値が指定された値のセットにある [ない] | 2 項 | |
IS [NOT] NULL |
すべて | 値が NULL である [ない] |
単項 | |
IS [NOT] TRUE | BOOL | 値が TRUE である [ない]。 | 単項 | |
IS [NOT] FALSE | BOOL | 値が FALSE である [ない]。 | 単項 | |
10 | NOT | BOOL | 論理 NOT | 単項 |
11 | AND | BOOL | 論理 AND | 2 項 |
12 | OR | BOOL | 論理 OR | 2 項 |
優先順位が同じ演算子は左結合です。つまり、左から右に向かってグループ化されます。たとえば、次の式があるとします。
x AND y AND z
これは、次のように解釈されます。
( ( x AND y ) AND z )
次の式があるとします。
x * y / z
これは、次のように解釈されます。
( ( x * y ) / z )
すべての比較演算子の優先順位は同じですが、比較演算子は非結合です。したがって、あいまいさを解消するには、かっこが必要です。次に例を示します。
(x < y) IS FALSE
フィールド アクセス演算子
expression.fieldname[. ...]
説明
フィールドの値を取得します。ドット演算子とも呼ばれます。ネストされたフィールドにアクセスするために使用できます。例: expression.fieldname1.fieldname2
入力値の型
STRUCT
JSON
戻り値の型
STRUCT
の場合:fieldname
の SQL データ型。構造体にフィールドが見つからない場合は、エラーがスローされます。JSON
の場合:JSON
. JSON フィールド内にフィールドが見つからない場合は、SQLNULL
が返されます。
例
次の例では、式は t.customer
で、フィールド アクセス演算は .address
と .country
です。演算は、特定のオペランドへの演算子(.
)の適用です(この場合のオペランドは address
と country
です。より具体的には、最初の演算では t.customer
と address
、2 番目の演算では t.customer.address
と country
です)。
WITH orders AS (
SELECT STRUCT(STRUCT('Yonge Street' AS street, 'Canada' AS country) AS address) AS customer
)
SELECT t.customer.address.country FROM orders AS t;
+---------+
| country |
+---------+
| Canada |
+---------+
配列添字演算子
array_expression[array_subscript_specifier]
array_subscript_specifier:
position_keyword(index)
position_keyword:
{ OFFSET | SAFE_OFFSET | ORDINAL | SAFE_ORDINAL }
説明
配列の特定の場所にある値を取得します。
入力値の型
array_expression
: 入力配列。position_keyword
: 配列のインデックスの開始位置と範囲外のインデックスの処理方法。次の設定を選択できます。OFFSET
: インデックスはゼロから始まります。インデックスが範囲外の場合、エラーを生成します。SAFE_OFFSET
: インデックスはゼロから始まります。インデックスが範囲外の場合、NULL
を返します。ORDINAL
: インデックスは 1 から始まります。インデックスが範囲外の場合、エラーを生成します。SAFE_ORDINAL
: インデックスは 1 から始まります。インデックスが範囲外の場合、NULL
を返します。
index
: 配列内の特定の位置を表す整数。
戻り値の型
array_expression
が ARRAY<T>
の場合は T
です。
例
この例では、配列添字演算子を使用して、item_array
内の特定の場所にある値を取得しています。また、配列の範囲外のインデックス(6
)を参照した場合の動作も示されています。SAFE
接頭辞が含まれる場合は NULL
が返され、それ以外の場合はエラーが生成されます。
WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
item_array,
item_array[OFFSET(1)] AS item_offset,
item_array[ORDINAL(1)] AS item_ordinal,
item_array[SAFE_OFFSET(6)] AS item_safe_offset,
FROM Items
+----------------------------------+--------------+--------------+------------------+
| item_array | item_offset | item_ordinal | item_safe_offset |
+----------------------------------+--------------+--------------+------------------+
| [coffee, tea, milk] | tea | coffee | NULL |
+----------------------------------+--------------+--------------+------------------+
次の例では、範囲外のインデックスを参照し、SAFE
接頭辞が含まれていないため、エラーが生成されます。
WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
item_array[OFFSET(6)] AS item_offset
FROM Items
-- Error. OFFSET(6) is out of range.
JSON 添字演算子
json_expression[array_element_id]
json_expression[field_name]
説明
JSON 式の配列要素またはフィールドの値を取得します。ネストされたデータにアクセスするために使用できます。
入力値の型
JSON expression
: 返される配列要素またはフィールドを含むJSON
式。[array_element_id]
: 配列のゼロベースのインデックスを表すINT64
式。負の値が入力されている場合、または値が配列のサイズ以上である場合、または JSON 式が JSON 配列を表していない場合、SQLNULL
が返されます。[field_name]
: JSON 内のフィールド名を表すSTRING
式。フィールド名が見つからない場合、または JSON 式が JSON オブジェクトでない場合は、SQLNULL
が返されます。
戻り値の型
JSON
例
下記の例で、
json_value
は JSON 式です。.class
は JSON の フィールド アクセスです。.students
は JSON の フィールド アクセスです。[0]
は、JSON 値の配列の 0 番目の要素にアクセスする要素オフセットを含む JSON サブスクリプト式です。['name']
は、アクセスするフィールド名を入れる JSON サブスクリプト式です。
SELECT json_value.class.students[0]['name'] AS first_student
FROM
UNNEST(
[
JSON '{"class" : {"students" : [{"name" : "Jane"}]}}',
JSON '{"class" : {"students" : []}}',
JSON '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'])
AS json_value;
+-----------------+
| first_student |
+-----------------+
| "Jane" |
| NULL |
| "John" |
+-----------------+
算術演算子
以下の説明に示されていない限り、すべての算術演算子で数値型 T が受け入れられ、結果の型には型 T が含まれます。
名前 | 構文 |
---|---|
加算 | X + Y |
減算 | X - Y |
乗算 | X * Y |
除算 | X / Y |
単項プラス | + X |
単項マイナス | - X |
注: ゼロ除算はエラーを返します。異なる結果を取得するには、IEEE_DIVIDE または SAFE_DIVIDE 関数を使用してください。
加算、減算、乗算の結果の型:
入力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
除算の結果の型:
入力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
単項プラスの結果タイプ:
入力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
出力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
単項マイナスの場合の結果のデータ型:
入力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
出力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
日付算術演算子
演算子「+」と「-」は、日付の算術演算に使用できます。
date_expression + int64_expression
int64_expression + date_expression
date_expression - int64_expression
説明
date_expression
に対して int64_expression
日を加算または減算します。間隔が日数で表現されている場合、DATE_ADD
関数または DATE_SUB
関数と同等です。
戻りデータの型
DATE
例
SELECT DATE "2020-09-22" + 1 AS day_later, DATE "2020-09-22" - 7 AS week_ago
+------------+------------+
| day_later | week_ago |
+------------+------------+
| 2020-09-23 | 2020-09-15 |
+------------+------------+
日時の減算
date_expression - date_expression
timestamp_expression - timestamp_expression
datetime_expression - datetime_expression
説明
2 つの日時値の差を間隔として計算します。
戻りデータの型
INTERVAL
例
SELECT
DATE "2021-05-20" - DATE "2020-04-19" AS date_diff,
TIMESTAMP "2021-06-01 12:34:56.789" - TIMESTAMP "2021-05-31 00:00:00" AS time_diff
+-------------------+------------------------+
| date_diff | time_diff |
+-------------------+------------------------+
| 0-0 396 0:0:0 | 0-0 0 36:34:56.789 |
+-------------------+------------------------+
間隔算術演算子
加算と減算
date_expression + interval_expression = DATETIME
date_expression - interval_expression = DATETIME
timestamp_expression + interval_expression = TIMESTAMP
timestamp_expression - interval_expression = TIMESTAMP
datetime_expression + interval_expression = DATETIME
datetime_expression - interval_expression = DATETIME
説明
日時の値に間隔を追加するか、日時の値から間隔を減算します。例
SELECT
DATE "2021-04-20" + INTERVAL 25 HOUR AS date_plus,
TIMESTAMP "2021-05-02 00:01:02.345" - INTERVAL 10 SECOND AS time_minus;
+-------------------------+--------------------------------+
| date_plus | time_minus |
+-------------------------+--------------------------------+
| 2021-04-21 01:00:00 | 2021-05-02 00:00:52.345+00 |
+-------------------------+--------------------------------+
乗算と除算
interval_expression * integer_expression = INTERVAL
interval_expression / integer_expression = INTERVAL
説明
間隔の値を整数で乗算または除算します。
例
SELECT
INTERVAL '1:2:3' HOUR TO SECOND * 10 AS mul1,
INTERVAL 35 SECOND * 4 AS mul2,
INTERVAL 10 YEAR / 3 AS div1,
INTERVAL 1 MONTH / 12 AS div2
+----------------+--------------+-------------+--------------+
| mul1 | mul2 | div1 | div2 |
+----------------+--------------+-------------+--------------+
| 0-0 0 10:20:30 | 0-0 0 0:2:20 | 3-4 0 0:0:0 | 0-0 2 12:0:0 |
+----------------+--------------+-------------+--------------+
ビット演算子
すべてのビット演算子は、第 1 オペランドと同じ型と同じ長さを返します。
名前 | 構文 | 入力データ型 | 説明 |
---|---|---|---|
ビット演算 NOT | ~ X | 整数または BYTES | 与えられた変数値の補数を形成し、各ビットの論理否定を実行します。 |
ビット演算 OR | X | Y | X : 整数または BYTES Y : X と同じ型 |
同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに論理包含的和演算を実行します。X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。 |
ビット演算 XOR | X ^ Y | X : 整数または BYTES Y : X と同じ型 |
同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに排他的論理和演算を実行します。 X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。 |
ビット演算 AND | X & Y | X : 整数または BYTES Y : X と同じ型 |
同じ長さの 2 つのビットパターンをとり、対応するビットの各ペアに論理 AND 演算を実行します。 X と Y が BYTES で長さが異なる場合、この演算子はエラーを返します。 |
左シフト | X << Y | X : 整数または BYTES Y : INT64 |
第 1 オペランド X を左方にシフトします。第 2 オペランド Y が第 1 オペランド X のビット長(たとえば X が INT64 型の場合は 64)以上である場合、この演算子は 0 または b'\x00' バイト シーケンスを返します。Y が負の数の場合はエラーを返します。 |
右シフト | X >> Y | X : 整数または BYTES Y : INT64 |
第 1 オペランド X を右方にシフトします。この演算子は、符号付きの型で符号ビット拡張を行いません(左側の空いたビットを 0 で埋めます)。第 2 オペランド Y が第 1 オペランド X のビット長(たとえば X が INT64 型の場合は 64)以上である場合、この演算子は 0 または b'\x00' バイト シーケンスを返します。Y が負の数の場合はエラーを返します。 |
論理演算子
BigQuery は、AND
、OR
、NOT
の論理演算子をサポートしています。論理演算子は BOOL 入力または NULL
入力のみを許可し、3 値論理を使用して結果を生成します。結果は TRUE
、FALSE
、NULL
のいずれかになります。
x | y | x AND y | x OR y |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | TRUE | FALSE | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | TRUE | NULL | TRUE |
NULL | FALSE | FALSE | NULL |
NULL | NULL | NULL | NULL |
x | NOT x |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
例
このセクションの例では、entry_table
というテーブルを参照しています。
+-------+
| entry |
+-------+
| a |
| b |
| c |
| NULL |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'
-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL
+-------+
| entry |
+-------+
| a |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')
-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL
+-------+
| entry |
+-------+
| b |
| c |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL
-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE
+-------+
| entry |
+-------+
| NULL |
+-------+
比較演算子
比較演算子は常に BOOL を返します。通常、両方のオペランドが同じ型である必要があります。オペランドの型が異なる場合、BigQuery で精度を失うことなくこれらの型の値を共通の型に変換できるなら、一般的に BigQuery ではそれらをその共通の型に強制的に変換して、比較します。通常は、リテラルから非リテラルへの強制型変換を行います(この型変換が存在する場合)。比較可能なデータ型はデータ型に定義されています。
STRUCT は、4 つの比較演算子(等号 =、不等号 != と <>、IN)のみサポートします。
これらのデータ型を比較するときに、次の規則が適用されます。
- FLOAT64: NaN との比較演算は常に FALSE を返す。ただし、
!=
と<>
は TRUE を返します。 - BOOL: FALSE は TRUE よりも小さい。
- STRING: 文字列はコードポイントごとに比較される。つまり、正規的に同等の文字列は、それらが最初に正規化されている場合にのみ等しいと比較されることが保証されています。
NULL
: 変換がここで停止する。NULL
入力を持つ演算はすべてNULL
を返します。
名前 | 構文 | 説明 |
---|---|---|
より小さい | X < Y | X が Y よりも小さい場合に TRUE を返します。この演算子では照合を指定できます。 |
以下 | X <= Y | X が Y 以下の場合、TRUE を返します。この演算子では照合を指定できます。 |
より大きい | X > Y | X が Y よりも大きい場合、TRUE を返します。この演算子では照合を指定できます。 |
以上 | X >= Y | X が Y 以上の場合、TRUE を返します。この演算子では照合を指定できます。 |
等しい | X = Y | X が Y と等しい場合、TRUE を返します。この演算子では照合を指定できます。 |
等しくない | X != Y X <> Y |
X が Y と等しくない場合、TRUE を返します。この演算子では照合を指定できます。 |
BETWEEN | X [NOT] BETWEEN Y AND Z |
X が指定された範囲内に [ない] 場合、TRUE を返します。「X BETWEEN Y AND Z」の結果は「Y <= X AND X <= Z」と等価ですが、前者では X は一度しか評価されません。 この演算子では照合を指定できます。 |
LIKE | X [NOT] LIKE Y | 第 1 オペランド X の文字列が第 2 オペランド Y によって指定されたパターンと一致するかどうかをチェックします。式には次の文字を含めることができます。
|
IN | 複数 - 以下を参照 |
右オペランドが空の場合、FALSE を返します。左のオペランドが NULL の場合、NULL を返します。右のオペランドに NULL が含まれている場合、TRUE または NULL を返します(FALSE を返すことはありません)。IN のいずれかの側の引数は、一般式です。どちらのオペランドもリテラルにする必要はありませんが、右側にリテラルを使用するのが一般的です。X は一度だけ評価されます。この演算子では通常、照合を指定できます。 |
STRUCT データ型の値の等価性をテストするとき、1 つ以上のフィールドが NULL
である可能性があります。このような場合、比較結果は次のようになります。
- NULL 以外のフィールドがすべて等しい場合、NULL を返します。
- NULL 以外のフィールドが等しくない場合、false を返します。
次の表は、STRUCT データ型が NULL
と評価されるフィールドを持つ場合にどのように比較されるかを示しています。
Struct1 | Struct2 | Struct1 = Struct2 |
---|---|---|
STRUCT(1, NULL) |
STRUCT(1, NULL) |
NULL |
STRUCT(1, NULL) |
STRUCT(2, NULL) |
FALSE |
STRUCT(1,2) |
STRUCT(1, NULL) |
NULL |
EXISTS 演算子
EXISTS ( subquery )
Description
サブクエリによって 1 つ以上の行が生成される場合、TRUE
が返されます。サブクエリによってゼロ個の行が生成される場合、FALSE
が返されます。NULL
が返されることはありません。EXISTS
でサブクエリを使用する方法の詳細については、EXISTS
サブクエリをご覧ください。
例
この例では、方向が south
である Words
に行がないため、EXISTS
演算子は FALSE
を返します。
WITH Words AS (
SELECT 'Intend' as value, 'east' as direction UNION ALL
SELECT 'Secure', 'north' UNION ALL
SELECT 'Clarity', 'west'
)
SELECT EXISTS ( SELECT value FROM Words WHERE direction = 'south' ) as result;
+--------+
| result |
+--------+
| FALSE |
+--------+
IN 演算子
IN
演算子は、次の構文をサポートします。
search_value [NOT] IN value_set
value_set:
{
(expression[, ...])
| (subquery)
| UNNEST(array_expression)
}
説明
一連の値と等しい値を確認します。セマンティック ルールは適用されますが、通常、等しい値が見つかると IN
は TRUE
を返します。等しい値が除外されている場合は FALSE
、それ以外の場合は NULL
を返します。等しい値が見つかった場合、NOT IN
は FALSE
を返します。等しい値が除外されている場合は TRUE
を返し、それ以外の場合は NULL
を返します。
search_value
: 一連の値と比較される式。value_set
: 検索値と比較する 1 つ以上の値です。(expression[, ...])
: 式のリスト。(subquery)
: 単一の列を返すサブクエリ。その列の値は値のセットになります。行が生成されない場合、値のセットは空になります。UNNEST(array_expression)
: 配列式から値の列を返す UNNEST 演算子。これは次のものと同等です。IN (SELECT element FROM UNNEST(array_expression) AS element)
IN
演算子を使用する場合、以下のセマンティクスが次の順序で適用されます。
value_set
が空の場合、FALSE
を返します。search_value
がNULL
の場合、NULL
を返します。value_set
にsearch_value
と等しい値が含まれている場合、TRUE
を返します。value_set
にNULL
が含まれている場合、NULL
を返します。FALSE
を返します。
NOT IN
演算子を使用する場合、以下のセマンティクスが次の順序で適用されます。
value_set
が空の場合、TRUE
を返します。search_value
がNULL
の場合、NULL
を返します。value_set
にsearch_value
と等しい値が含まれている場合、FALSE
を返します。value_set
にNULL
が含まれている場合、NULL
を返します。TRUE
を返します。
この演算子は通常、照合をサポートしますが、x [NOT] IN UNNEST
はサポートしません。
次のセマンティックがあるとします。
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)
この構文の使用方法の詳細については、配列トピックをご覧ください。
IN
は STRUCT のコンストラクタ構文を使用してマルチパート キーとともに使用できます。次に例を示します。
(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )
詳細については、構造体型をご覧ください。
戻りデータの型
BOOL
例
次の例のように WITH
句を使用すると、Words
と Items
の一時テーブルをエミュレートできます。
WITH Words AS (
SELECT 'Intend' as value UNION ALL
SELECT 'Secure' UNION ALL
SELECT 'Clarity' UNION ALL
SELECT 'Peace' UNION ALL
SELECT 'Intend'
)
SELECT * FROM Words;
+----------+
| value |
+----------+
| Intend |
| Secure |
| Clarity |
| Peace |
| Intend |
+----------+
WITH
Items AS (
SELECT STRUCT('blue' AS color, 'round' AS shape) AS info UNION ALL
SELECT STRUCT('blue', 'square') UNION ALL
SELECT STRUCT('red', 'round')
)
SELECT * FROM Items;
+----------------------------+
| info |
+----------------------------+
| {blue color, round shape} |
| {blue color, square shape} |
| {red color, round shape} |
+----------------------------+
IN
と式を使用した例:
SELECT * FROM Words WHERE value IN ('Intend', 'Secure');
+----------+
| value |
+----------+
| Intend |
| Secure |
| Intend |
+----------+
NOT IN
と式を使用した例:
SELECT * FROM Words WHERE value NOT IN ('Intend');
+----------+
| value |
+----------+
| Secure |
| Clarity |
| Peace |
+----------+
IN
、スカラー サブクエリ、式を使用した例:
SELECT * FROM Words WHERE value IN ((SELECT 'Intend'), 'Clarity');
+----------+
| value |
+----------+
| Intend |
| Clarity |
| Intend |
+----------+
IN
と UNNEST
演算子を使用した例:
SELECT * FROM Words WHERE value IN UNNEST(['Secure', 'Clarity']);
+----------+
| value |
+----------+
| Secure |
| Clarity |
+----------+
IN
と STRUCT
を使用した例:
SELECT
(SELECT AS STRUCT Items.info) as item
FROM
Items
WHERE (info.shape, info.color) IN (('round', 'blue'));
+------------------------------------+
| item |
+------------------------------------+
| { {blue color, round shape} info } |
+------------------------------------+
IS 演算子
IS 演算子は、テストしている条件に対して TRUE または FALSE を返します。数学関数で定義されている IS_INF
関数や IS_NAN
関数とは異なり、入力が NULL
であっても、この演算子が NULL
を返すことはありません。NOT
がある場合は、出力 BOOL 値が反転します。
関数の構文 | 入力データ型 | 結果のデータ型 | 説明 |
---|---|---|---|
X IS TRUE |
BOOL | BOOL | X が TRUE と評価されると、TRUE に評価されます。それ以外の場合は FALSE と評価されます。 |
X IS NOT TRUE |
BOOL | BOOL | X が TRUE に評価された場合、FALSE と評価されます。それ以外の場合は TRUE と評価されます。 |
X IS FALSE |
BOOL | BOOL | X が FALSE と評価された場合は TRUE と評価されます。それ以外の場合は FALSE と評価されます。 |
X IS NOT FALSE |
BOOL | BOOL | X が FALSE と評価された場合は、FALSE と評価されます。それ以外の場合は TRUE と評価されます。 |
X IS NULL |
任意の値の型 | BOOL |
X が NULL と評価された場合は、TRUE と評価されます。それ以外の場合は FALSE と評価されます。 |
X IS NOT NULL |
任意の値の型 | BOOL |
X が NULL と評価された場合は、FALSE と評価されます。それ以外の場合は TRUE と評価されます。 |
X IS UNKNOWN |
BOOL | BOOL |
X が NULL と評価された場合は、TRUE と評価されます。それ以外の場合は FALSE と評価されます。 |
X IS NOT UNKNOWN |
BOOL | BOOL |
X が NULL と評価された場合は、FALSE と評価されます。それ以外の場合は TRUE と評価されます。 |
IS DISTINCT FROM 演算子
expression_1 IS [NOT] DISTINCT FROM expression_2
説明
DISTINCT
および
GROUP BY
句によって入力値が互いに異なると判断された場合、IS DISTINCT FROM
は TRUE
を返します。それ以外の場合は、FALSE
を返します。
a IS DISTINCT FROM b
が TRUE
であることは、以下と同等です。
SELECT COUNT(DISTINCT x) FROM UNNEST([a,b]) x
が2
を返すこと。SELECT * FROM UNNEST([a,b]) x GROUP BY x
が 2 行を返すこと。
a IS DISTINCT FROM b
は、以下の場合を除き、NOT (a = b)
と同等です。
- この演算子は
NULL
を返さないため、NULL
値は、他のNULL
値と違い、非NULL
値と異なるとみなされます。 NaN
値は非NaN
値とは区別されるものの、他のNaN
値とは区別されません。
入力値の型
expression_1
: 比較する最初の値。グループ化可能なデータ型であるNULL
またはNaN
を指定できます。expression_2
: 比較する 2 番目の値。グループ化可能なデータ型であるNULL
またはNaN
を指定できます。NOT
: 存在する場合、出力のBOOL
値は反転されます。
戻り値の型
BOOL
例
以下は TRUE
を返します。
SELECT 1 IS DISTINCT FROM 2
SELECT 1 IS DISTINCT FROM NULL
SELECT 1 IS NOT DISTINCT FROM 1
SELECT NULL IS NOT DISTINCT FROM NULL
以下は FALSE
を返します。
SELECT NULL IS DISTINCT FROM NULL
SELECT 1 IS DISTINCT FROM 1
SELECT 1 IS NOT DISTINCT FROM 2
SELECT 1 IS NOT DISTINCT FROM NULL
連結演算子
連結演算子は、複数の値を 1 つに結合します。
関数の構文 | 入力データ型 | 結果のデータ型 |
---|---|---|
STRING || STRING [ || ... ] |
STRING | STRING |
BYTES || BYTES [ || ... ] |
BYTES | STRING |
ARRAY<T> || ARRAY<T> [ || ... ] |
ARRAY<T> | ARRAY<T> |
条件式
条件式は、入力の評価順序に制約を課します。基本的に、これらは左辺から右辺にかけて短絡評価され、選択された出力値のみを評価します。対照的に、正規関数のすべての入力は、関数を呼び出す前に評価されます。条件式での短絡を、エラー処理やパフォーマンス チューニングに利用できます。
CASE expr
CASE expr
WHEN expr_to_match THEN result
[ ... ]
[ ELSE else_result ]
END
説明
expr
と、次に続く各 WHEN
句の expr_to_match
を比較し、この比較で true が返される最初の結果を返します。残りの WHEN
句と else_result
は評価されません。expr = expr_to_match
比較ですべての WHEN
句に関して FALSE または NULL が返される場合、else_result
が存在する場合はそれを返し、存在しない場合は NULL を返します。
expr
と expr_to_match
は任意の型です。これらは、共通するスーパータイプに暗黙的に強制型変換できる必要があります。等価比較は強制変換された値に対して行われます。result
型は複数存在する場合があります。result
および else_result
式は共通のスーパータイプに強制型変換できる必要があります。
この式では、照合を指定できます。
戻りデータの型
result
[, ...] と else_result
のスーパータイプ。
例
WITH Numbers AS (
SELECT 90 as A, 2 as B UNION ALL
SELECT 50, 8 UNION ALL
SELECT 60, 6 UNION ALL
SELECT 50, 10
)
SELECT
A,
B,
CASE A
WHEN 90 THEN 'red'
WHEN 50 THEN 'blue'
ELSE 'green'
END
AS result
FROM Numbers
+------------------+
| A | B | result |
+------------------+
| 90 | 2 | red |
| 50 | 8 | blue |
| 60 | 6 | green |
| 50 | 10 | blue |
+------------------+
ケース
CASE
WHEN condition THEN result
[ ... ]
[ ELSE else_result ]
END
説明
連続する各 WHEN
句の条件を評価し、条件が true である最初の結果を返します。残りの WHEN
句と else_result
は評価されません。すべての条件が false または NULL の場合は、else_result
が存在すればそれが返されます。存在しない場合は NULL が返されます。
condition
はブール式でなければなりません。result
型は複数存在する場合があります。result
および else_result
式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。
この式では、照合を指定できます。
戻りデータの型
result
[, ...] と else_result
のスーパータイプ。
例
WITH Numbers AS (
SELECT 90 as A, 2 as B UNION ALL
SELECT 50, 6 UNION ALL
SELECT 20, 10
)
SELECT
A,
B,
CASE
WHEN A > 60 THEN 'red'
WHEN A > 30 THEN 'blue'
ELSE 'green'
END
AS result
FROM Numbers
+------------------+
| A | B | result |
+------------------+
| 90 | 2 | red |
| 50 | 6 | blue |
| 20 | 10 | green |
+------------------+
COALESCE
COALESCE(expr[, ...])
説明
最初の非 NULL 式の値を返します。残りの式は評価されません。入力式には任意の型を指定できます。複数の入力式の型が存在する場合があります。すべての入力式は共通のスーパータイプに暗黙的に強制型変換できる必要があります。
戻りデータの型
expr
[, ...] のスーパータイプ。
例
SELECT COALESCE('A', 'B', 'C') as result
+--------+
| result |
+--------+
| A |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result
+--------+
| result |
+--------+
| B |
+--------+
IF
IF(expr, true_result, else_result)
説明
expr
が true の場合は true_result
を返し、それ以外の場合は else_result
を返します。expr
が true の場合、else_result
は評価されません。expr
が false または NULL の場合、true_result
は評価されません。
expr
はブール式でなければなりません。true_result
および else_result
を共通のスーパータイプに強制的に型変換できる必要があります。
戻りデータの型
true_result
と else_result
のスーパータイプ。
例
WITH Numbers AS (
SELECT 10 as A, 20 as B UNION ALL
SELECT 50, 30 UNION ALL
SELECT 60, 60
)
SELECT
A,
B,
IF(A < B, 'true', 'false') AS result
FROM Numbers
+------------------+
| A | B | result |
+------------------+
| 10 | 20 | true |
| 50 | 30 | false |
| 60 | 60 | false |
+------------------+
IFNULL
IFNULL(expr, null_result)
説明
expr
が NULL の場合は null_result
を返します。それ以外の場合は、expr
を返します。expr
が NULL でない場合、null_result
は評価されません。
expr
と null_result
には任意の型を指定できます。また、共通のスーパータイプに暗黙的に強制型変換できる必要があります。COALESCE(expr, null_result)
と同義。
戻りデータの型
expr
または null_result
のスーパータイプ。
例
SELECT IFNULL(NULL, 0) as result
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT IFNULL(10, 0) as result
+--------+
| result |
+--------+
| 10 |
+--------+
NULLIF
NULLIF(expr, expr_to_match)
説明
expr = expr_to_match
が true の場合は NULL を返し、それ以外の場合は expr
を返します。
expr
と expr_to_match
は、共通のスーパータイプに暗黙的に強制型変換できる必要があります。また、比較可能型にする必要があります。
この式では、照合を指定できます。
戻りデータの型
expr
と expr_to_match
のスーパータイプ。
例
SELECT NULLIF(0, 0) as result
+--------+
| result |
+--------+
| NULL |
+--------+
SELECT NULLIF(10, 0) as result
+--------+
| result |
+--------+
| 10 |
+--------+
FUNCTIONS
集計関数
次の一般集計関数は、Google 標準 SQL で使用できます。集計関数呼び出しの構文については、集計関数呼び出しをご覧ください。
ANY_VALUE
ANY_VALUE(
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
グループから選択された行の expression
を返します。どの行が選択されるかは非決定的で、ランダムではありません。入力によって行が生成されない場合、NULL
を返します。グループ内のすべての行で expression
が NULL
の場合、NULL
を返します。
ANY_VALUE
は、あたかも RESPECT NULLS
が指定されているかのように動作します。expression
が NULL
である行が考慮され、そのような行が選択される場合があります。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
サポートされる引数の型
すべて
返されるデータ型
入力データ型と一致します。
例
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+-----------+
| any_value |
+-----------+
| apple |
+-----------+
SELECT
fruit,
ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+--------+-----------+
| fruit | any_value |
+--------+-----------+
| pear | pear |
| apple | pear |
| banana | apple |
+--------+-----------+
ARRAY_AGG
ARRAY_AGG(
[ DISTINCT ]
expression
[ { IGNORE | RESPECT } NULLS ]
[ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
[ LIMIT n ]
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
expression
値の配列を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
最終的なクエリ結果の配列に NULL
要素が含まれている場合は、エラーが発生します。
サポートされる引数の型
ARRAY を除くすべてのデータ型。
返されるデータ型
ARRAY
入力行がない場合、この関数は NULL
を返します。
例
SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+---------------+
| array_agg |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT ARRAY_AGG(x ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT ARRAY_AGG(x LIMIT 5) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
WITH vals AS
(
SELECT 1 x UNION ALL
SELECT -2 x UNION ALL
SELECT 3 x UNION ALL
SELECT -2 x UNION ALL
SELECT 1 x
)
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) as array_agg
FROM vals;
+------------+
| array_agg |
+------------+
| [-2, 1, 3] |
+------------+
WITH vals AS
(
SELECT 1 x, 'a' y UNION ALL
SELECT 1 x, 'b' y UNION ALL
SELECT 2 x, 'a' y UNION ALL
SELECT 2 x, 'c' y
)
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;
+---------------+
| x | array_agg |
+---------------+
| 1 | [a, b] |
| 2 | [a, c] |
+---------------+
SELECT
x,
ARRAY_AGG(x) OVER (ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+----+-------------------------+
| x | array_agg |
+----+-------------------------+
| 1 | [1, 1] |
| 1 | [1, 1] |
| 2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| 2 | [1, 1, 2, -2, -2, 2] |
| 3 | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(
expression
[ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
[ LIMIT n ]
)
説明
ARRAY 型の expression
から要素を結合し、結果として単一の ARRAY を返します。この関数は NULL 入力配列を無視しますが、NULL 以外の入力配列に含まれる NULL 要素は無視しません。ただし、クエリの最終結果の配列に NULL 要素が含まれていると、エラーが発生します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
ARRAY
返されるデータ型
ARRAY
入力行がない場合、または expression
がすべての行で NULL と評価される場合に NULL
を返します。
例
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
SELECT [NULL, 1, 2, 3, 4] AS x
UNION ALL SELECT NULL
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+--------------------------+
| array_concat_agg |
+--------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9] |
+------------------+
AVG
AVG(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
NULL
以外の値の平均を返すか、入力に NaN
が含まれる場合は NaN
を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
サポートされる引数の型
INT64 のような、任意の数値入力型。浮動小数点入力型の場合、戻り値の結果は変動する可能性があります、つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。
返されるデータ型
入力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
出力 | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
例
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
+-----+
| avg |
+-----+
| 3 |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
+------+
| avg |
+------+
| 2.75 |
+------+
SELECT
x,
AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;
+------+------+
| x | avg |
+------+------+
| NULL | NULL |
| 0 | 0 |
| 2 | 1 |
| 4 | 3 |
| 4 | 4 |
| 5 | 4.5 |
+------+------+
BIT_AND
BIT_AND(
expression
)
説明
expression
でビット積演算を実行し、結果を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
- INT64
返されるデータ型
INT64
例
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
+---------+
| bit_and |
+---------+
| 1 |
+---------+
BIT_OR
BIT_OR(
expression
)
説明
expression
に対してビット論理和演算を実行し、結果を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
- INT64
返されるデータ型
INT64
例
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
+--------+
| bit_or |
+--------+
| 61601 |
+--------+
BIT_XOR
BIT_XOR(
[ DISTINCT ]
expression
)
説明
expression
でビット排他論理和演算を実行し、結果を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
- INT64
返されるデータ型
INT64
例
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678 |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
COUNT
1.
COUNT(*)
[OVER over_clause]
2.
COUNT(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
- 入力内の行数を返します。
expression
がNULL
以外の値と評価された行の数を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法の詳細については、ウィンドウ関数の呼び出しをご覧ください。
DISTINCT を含むこの関数では、照合を指定できます。
サポートされる引数の型
expression
には任意のデータ型を指定できます。DISTINCT
が存在する場合、expression
にはグループ化可能なデータ型のみ指定できます。
戻りデータの型
INT64
例
COUNT
関数を使用して、テーブル内の行数または式で重複しない値の数を返すことができます。次に例を示します。
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------+------------+--------------+
| x | count_star | count_dist_x |
+------+------------+--------------+
| 1 | 3 | 2 |
| 4 | 3 | 2 |
| 4 | 3 | 2 |
| 5 | 1 | 1 |
+------+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------+------------+---------+
| x | count_star | count_x |
+------+------------+---------+
| NULL | 1 | 0 |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| 4 | 3 | 3 |
| 5 | 1 | 1 |
+------+------------+---------+
特定の条件を満たす式で重複しない値の数をカウントする場合は、次のようなレシピを使用できます。
COUNT(DISTINCT IF(condition, expression, NULL))
ここで、IF
は、condition
が TRUE
の場合は expression
の値を返し、それ以外の場合は NULL
を返します。囲んでいる COUNT(DISTINCT ...)
は NULL
値を無視するため、condition
が TRUE
である expression
の値のみがカウントされます。
たとえば、x
の重複しない正の値をカウントするには:
SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;
+-------------------+
| distinct_positive |
+-------------------+
| 3 |
+-------------------+
また、特定の種類のイベントが発生した個別の日付の数をカウントするには:
WITH Events AS (
SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
AS distinct_dates_with_failures
FROM Events;
+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2 |
+------------------------------+
COUNTIF
COUNTIF(
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
expression
に対する TRUE
の値の数を返します。入力行がない場合、または expression
がすべての行に対して FALSE
または NULL
と評価される場合に 0
を返します。
expression
は BOOL
でなければならないため、COUNTIF(DISTINCT ...)
形式はサポートされません。TRUE
で重複しない値は 1 つだけのため、これは有用ではありません。通常、COUNTIF
と DISTINCT
を組み合わせる必要がある場合は、特定の条件を満たす式で重複しない値をカウントします。これを実現するレシピの 1 つは次のとおりです。
COUNT(DISTINCT IF(condition, expression, NULL))
これは COUNTIF
ではなく COUNT
を使用していることに注意してください。IF
の部分が内部に移動しています。詳細については、COUNT
の例をご覧ください。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
サポートされる引数の型
BOOL
戻りデータの型
INT64
例
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
+--------------+--------------+
SELECT
x,
COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;
+------+--------------+
| x | num_negative |
+------+--------------+
| NULL | 0 |
| 0 | 1 |
| -2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| -7 | 2 |
| -10 | 2 |
+------+--------------+
LOGICAL_AND
LOGICAL_AND(
expression
)
説明
すべての非 NULL
式の論理 AND を返します。入力行がない場合、または expression
がすべての行で NULL
と評価される場合に NULL
を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
BOOL
戻りデータの型
BOOL
例
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(
expression
)
説明
すべての非 NULL
式の論理 OR を返します。入力行がない場合、または expression
がすべての行で NULL
と評価される場合に NULL
を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
BOOL
戻りデータの型
BOOL
例
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
非 NULL
式の最大値を返します。入力行がない場合、または expression
がすべての行で NULL
と評価される場合に NULL
を返します。入力に NaN
が含まれている場合、NaN
を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法の詳細については、ウィンドウ関数の呼び出しをご覧ください。
この関数では、照合を指定できます。
サポートされる引数の型
任意の順序付け可能なデータ型。
戻りデータの型
入力値として使用されるデータ型と同じ型。
例
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| max |
+-----+
| 55 |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | max |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 8 |
| 4 | 8 |
| 37 | 55 |
| 55 | 55 |
+------+------+
MIN
MIN(
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
非 NULL
式の最小値を返します。入力行がない場合、または expression
がすべての行で NULL
と評価される場合に NULL
を返します。入力に NaN
が含まれている場合、NaN
を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法の詳細については、ウィンドウ関数の呼び出しをご覧ください。
この関数では、照合を指定できます。
サポートされる引数の型
任意の順序付け可能なデータ型。
戻りデータの型
入力値として使用されるデータ型と同じ型。
例
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | min |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 4 |
| 4 | 4 |
| 37 | 37 |
| 55 | 37 |
+------+------+
STRING_AGG
STRING_AGG(
[ DISTINCT ]
expression [, delimiter]
[ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
[ LIMIT n ]
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
Description
非 NULL
値を連結して得られた値(STRING
または BYTES
のいずれか)を返します。入力行がない場合、または expression
がすべての行で NULL
と評価される場合に NULL
を返します。
delimiter
が指定された場合、連結された値はその区切り文字で区切られ、それ以外の場合は区切り文字としてカンマが使用されます。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
サポートされる引数の型
STRING
または BYTES
のいずれか。
戻りデータの型
STRING
または BYTES
のいずれか。
例
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+------------------------+
| string_agg |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+-----------------------+
| string_agg |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+--------------+
| string_agg |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+---------------+
| string_agg |
+---------------+
| pear & banana |
+---------------+
SELECT
fruit,
STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+--------+------------------------------+
| fruit | string_agg |
+--------+------------------------------+
| NULL | NULL |
| pear | pear & pear |
| pear | pear & pear |
| apple | pear & pear & apple |
| banana | pear & pear & apple & banana |
+--------+------------------------------+
SUM
SUM(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
NULL 以外の値の合計を返します。
式が浮動小数点値である場合、合計は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
サポートされる引数の型
サポートされているすべての数値データ型と INTERVAL。
戻りデータの型
入力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
---|---|---|---|---|---|
出力 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
特殊ケース:
入力が NULL
のみの場合、NULL
を返します。
入力に行が含まれていない場合、NULL
を返します。
入力に Inf
が含まれている場合、Inf
を返します。
入力に -Inf
が含まれている場合、-Inf
を返します。
入力に NaN
が含まれている場合、NaN
を返します。
入力に Inf
と -Inf
の組み合わせが含まれている場合、NaN
を返します。
例
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 25 |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 15 |
+-----+
SELECT
x,
SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 6 |
| 3 | 6 |
| 1 | 10 |
| 4 | 10 |
| 4 | 10 |
| 1 | 10 |
| 2 | 9 |
| 5 | 9 |
| 2 | 9 |
+---+-----+
SELECT
x,
SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 3 |
| 3 | 3 |
| 1 | 5 |
| 4 | 5 |
| 4 | 5 |
| 1 | 5 |
| 2 | 7 |
| 5 | 7 |
| 2 | 7 |
+---+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
+------+
| sum |
+------+
| NULL |
+------+
統計集計関数
次の統計集計関数は、Google 標準 SQL で使用できます。集計関数呼び出しの構文については、集計関数呼び出しをご覧ください。
CORR
CORR(
X1, X2
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
一連の数値ペアのピアソン相関係数を返します。それぞれの数値ペアでは、最初の数値が従属変数であり、2 番目の数値が独立変数です。返される結果は -1
から 1
の間になります。結果が 0
の場合、相関がないことを意味します。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
入力されたペアに NULL の値が 1 つ以上あると、この関数はそのペアを無視します。NULL 値のない入力ペアが 2 つ未満の場合、この関数は NULL を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
COVAR_POP
COVAR_POP(
X1, X2
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
一連の数値ペアの母共分散を返します。最初の数値は従属変数です。2 番目の数値は独立変数です。返される結果は -Inf
から +Inf
の間になります。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
入力されたペアに NULL の値が 1 つ以上あると、この関数はそのペアを無視します。NULL 値のない入力ペアがない場合、この関数は NULL を返します。NULL 値のない入力ペアが 1 つだけの場合は 0 を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
COVAR_SAMP
COVAR_SAMP(
X1, X2
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
一連の数値ペアの標本共分散を返します。最初の数値は従属変数です。2 番目の数値は独立変数です。返される結果は -Inf
から +Inf
の間になります。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
入力されたペアに NULL の値が 1 つ以上あると、この関数はそのペアを無視します。NULL 値のない入力ペアが 2 つ未満の場合、この関数は NULL を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
STDDEV_POP
STDDEV_POP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
バイアスをかけた母集団の標準偏差値を返します。返される結果は 0
から +Inf
の間になります。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
この関数は NULL 入力を無視します。すべての入力が無視されると、この関数は NULL を返します。
この関数は、NULL 以外の入力を 1 つでも受け取ると 0
を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
STDDEV_SAMP
STDDEV_SAMP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
バイアスをかけない、値の標本標準偏差を返します。返される結果は 0
から +Inf
の間になります。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
この関数は NULL 入力を無視します。NULL 以外の入力が 2 つ未満の場合、この関数は NULL を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
STDDEV
STDDEV(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
STDDEV_SAMP のエイリアスです。
VAR_POP
VAR_POP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
バイアスをかけた、値の母分散を返します。返される結果は 0
から +Inf
の間になります。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
この関数は NULL 入力を無視します。すべての入力が無視されると、この関数は NULL を返します。
この関数は、NULL 以外の入力を 1 つでも受け取ると 0
を返します。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
VAR_SAMP
VAR_SAMP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
バイアスをかけない標本分散値を返します。返される結果は 0
から +Inf
の間になります。
すべての数値型がサポートされています。入力が NUMERIC
または BIGNUMERIC
の場合、内部集計は、FLOAT64
に変換された最終出力に落ち着きます。これをしないと、入力は集計の前に FLOAT64
に変換され、結果が不安定になる可能性があります。
この関数は NULL 入力を無視します。NULL 以外の入力が 2 つ未満の場合、この関数は NULL を返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻りデータの型
FLOAT64
VARIANCE
VARIANCE(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
説明
VAR_SAMP のエイリアスです。
近似集計関数
次の近似集計関数は、Google 標準 SQL で使用できます。集計関数呼び出しの構文については、集計関数呼び出しをご覧ください。
近似集計関数は、メモリ使用量と時間においてはスケーラブルですが、正確な結果ではなく、近似的な結果を生成します。これらの関数は通常、COUNT(DISTINCT ...)
などの正確な集計関数と比べ必要とするメモリは少量ですが、統計的な不確実性をもたらします。このため近似集計は、線形メモリの使用が実用的でない大規模なデータ ストリームや、すでに概算されているデータに適しています。
このセクションの近似集計関数は、データの中間的な推測ではなく、入力データに対して直接作用します。これらの関数では、スケッチでの推測の精度をユーザーが指定できません。スケッチの精度を指定する場合は、以下をご覧ください。
- カーディナリティを見積もる HyperLogLog++ 関数。
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT(
expression
)
説明
COUNT(DISTINCT expression)
の近似結果を返します。返される値は統計的な推定値であり、必ずしも実際の値ではありません。
この関数は COUNT(DISTINCT expression)
よりも精度は低いものの、入力値が非常に多い場合はより効率的に処理できます。
サポートされる引数の型
ARRAY
、STRUCT
を除くすべてのデータ型
返されるデータ型
INT64
例
SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;
+-----------------+
| approx_distinct |
+-----------------+
| 5 |
+-----------------+
APPROX_QUANTILES
APPROX_QUANTILES(
[ DISTINCT ]
expression, number
[ { IGNORE | RESPECT } NULLS ]
)
説明
expression
の値のグループに対する近似境界を返します。number
は作成する変位値の数を表します。この関数は、number
+1 個の要素からなる配列を返します。最初の要素は近似最小値であり、最後の要素は近似最大値です。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
expression
には、サポートされているデータ型のうち、ARRAY
と STRUCT
を除くすべてのデータ型を使用できます。
number
は INT64 にする必要があります。
返されるデータ型
expression
パラメータで指定された型の ARRAY。
入力行がない場合、または expression
がすべての行で NULL と評価される場合に NULL
を返します。
例
SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10] |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;
+---------------+
| percentile_90 |
+---------------+
| 9 |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10] |
+------------------+
APPROX_TOP_COUNT
APPROX_TOP_COUNT(
expression, number
)
説明
expression
の近似トップ要素を返します。number
パラメータで、返される要素の数を指定します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
expression
には、GROUP BY
句がサポートしているどのデータ型も使用できます。
number
は INT64 にする必要があります。
返されるデータ型
STRUCT 型の ARRAY。STRUCT には 2 つのフィールドが含まれています。最初のフィールド(value
)には入力値が含まれています。2 番目のフィールド(count
)には、値が返された回数を示す INT64 が含まれています。
入力行がない場合、NULL
を返します。
例
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;
+-------------------------+
| approx_top_count |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+
NULL の処理
APPROX_TOP_COUNT では、入力に含まれる NULL は無視されません。例:
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;
+------------------------+
| approx_top_count |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+
APPROX_TOP_SUM
APPROX_TOP_SUM(
expression, weight, number
)
説明
割り当てられた weight
の合計に基づいて expression
の近似トップ要素を返します。number
パラメータで、返される要素の数を指定します。
weight
入力が負の値または NaN
の場合、この関数はエラーを返します。
この関数のオプションの引数とそれらの使用方法については、集計関数の呼び出しをご覧ください。
サポートされる引数の型
expression
には、GROUP BY
句がサポートしているどのデータ型も使用できます。
weight
は、以下のいずれかにする必要があります。
INT64
NUMERIC
BIGNUMERIC
FLOAT64
number
は INT64 にする必要があります。
返されるデータ型
STRUCT 型の ARRAY。STRUCT は、value
と sum
の 2 つのフィールドで構成されます。value
フィールドには、入力式の値が含まれます。sum
フィールドは weight
と同じ型であり、ここには value
フィールドに関連付けられた weight 入力の近似値の合計が含まれます。
入力行がない場合、NULL
を返します。
例
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
STRUCT("apple" AS x, 3 AS weight),
("pear", 2),
("apple", 0),
("banana", 5),
("pear", 4)
]);
+--------------------------+
| approx_top_sum |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+
NULL の処理
APPROX_TOP_SUM は、expression
と weight
パラメータの NULL 値を無視しません。
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);
+-------------------------+
| approx_top_sum |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+
HyperLogLog++ 関数
HyperLogLog++ アルゴリズム(HLL++)では、スケッチからカーディナリティを推定します。
HLL++ 関数は近似集計関数です。近似集計は通常、正確な集計関数(COUNT(DISTINCT)
など)と比べ必要とするメモリは少量ですが、統計的な不確実性も生じます。このため、HLL++ 関数は、線形メモリの使用が実用的でない大規模なデータ ストリームや、すでに概算されているデータに適しています。
実体化されたスケッチが不要な場合は、APPROX_COUNT_DISTINCT
などのシステム定義の精度の近似集計関数を使用することもできます。ただし、APPROX_COUNT_DISTINCT
では部分的な集計、再集計、カスタム精度は使用できません。
BigQuery は、次の HLL++ 関数をサポートします。
HLL_COUNT.INIT
HLL_COUNT.INIT(input [, precision])
説明
1 つ以上の input
値を取り、それらの値を HLL++ スケッチに集約する集計関数です。各スケッチは BYTES
データ型で表現されます。スケッチは、HLL_COUNT.MERGE
または HLL_COUNT.MERGE_PARTIAL
で結合できます。結合の必要がない場合には、HLL_COUNT.EXTRACT
で個別の値の最終カウントを抽出できます。
この関数では、省略可能なパラメータ precision
を使用できます。このパラメータは推測値の精度を定義しますが、スケッチの処理やディスクへの保存をするための追加のメモリが必要になります。この値の範囲は 10
~ 24
です。デフォルト値は 15
です。精度の詳細については、スケッチの精度をご覧ください。
入力が NULL
の場合、この関数は NULL
を返します。
詳細については、HyperLogLog の実践: 最新のカーディナリティ推測アルゴリズムをご覧ください。
サポートされる入力型
INT64
NUMERIC
BIGNUMERIC
STRING
BYTES
戻り値の型
BYTES
例
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country;
HLL_COUNT.MERGE
HLL_COUNT.MERGE(sketch)
説明
複数の HLL++ セットスケッチを結合してカーディナリティを返す集計関数です。
各 sketch
は同じ型で初期化する必要があります。異なる型のスケッチを結合すると、エラーが発生します。たとえば、INT64
データから初期化したスケッチと STRING
データから初期化したスケッチは結合できません。
結合されたスケッチを異なる精度で初期化した場合、精度は、結合の中で最も低いレベルにダウングレードされます。
この関数は、スケッチを結合するときに NULL
値を無視します。行がない場合や NULL
値しか存在しない場合には、0
を返します。
サポートされる入力型
BYTES
戻り値の型
INT64
例
SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.MERGE_PARTIAL
HLL_COUNT.MERGE_PARTIAL(sketch)
説明
1 つ以上の HLL++ sketch
入力を取り、それらの値を新しいスケッチに結合する集計関数です。
各 sketch
は同じ型で初期化する必要があります。異なる型のスケッチを結合すると、エラーが発生します。たとえば、INT64
データから初期化したスケッチと STRING
データから初期化したスケッチは結合できません。
結合されたスケッチを異なる精度で初期化した場合、精度は、結合の中で最も低いレベルにダウングレードされます。たとえば、MERGE_PARTIAL
で精度 14 と 15 のスケッチが検出された場合、返される新しいスケッチの精度は 14 になります。
入力がないか、すべての入力が NULL
の場合、この関数は NULL
を返します。
サポートされる入力型
BYTES
戻り値の型
BYTES
例
SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.EXTRACT
HLL_COUNT.EXTRACT(sketch)
説明
1 つの HLL++ スケッチからカーディナリティの推測値を抽出するスカラー関数です。
sketch
が NULL
の場合、この関数はカーディナリティの推測値として 0
を返します。
サポートされる入力型
BYTES
戻り値の型
INT64
例
SELECT
flavor,
country,
HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country);
+------------+---------+-----------------+
| flavor | country | num_respondents |
+------------+---------+-----------------+
| Vanilla | CH | 1 |
| Chocolate | CH | 1 |
| Chocolate | US | 1 |
| Strawberry | US | 1 |
+------------+---------+-----------------+
番号付け関数
以降のセクションで、BigQuery でサポートされている番号付け関数について説明します。番号付け関数は、ウィンドウ関数のサブセットです。ウィンドウ関数呼び出しを作成し、ウィンドウ関数の構文を確認する方法については、ウィンドウ関数の呼び出しをご覧ください。
番号付け関数は、指定されたウィンドウ内の行の位置に基づいて、各行に整数値を割り当てます。OVER
句の構文は、番号付け関数によって異なります。
RANK
RANK()
OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
ORDER BY expression [ { ASC | DESC } ] [, ...]
説明
順序付けされたパーティション内の各行の(1 から始まる)序数ランクを返します。すべてのピア行は同じランク値を受け取ります。次の行、またはピア行のセットは、常に 1 ずつ増加する DENSE_RANK
ではなく、前のランク値を持つピアの数だけ増加するランク値を受け取ります。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻り値の型
INT64
例
WITH Numbers AS
(SELECT 1 as x
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 8
UNION ALL SELECT 10
UNION ALL SELECT 10
)
SELECT x,
RANK() OVER (ORDER BY x ASC) AS rank
FROM Numbers
+-------------------------+
| x | rank |
+-------------------------+
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 5 | 4 |
| 8 | 5 |
| 10 | 6 |
| 10 | 6 |
+-------------------------+
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 4 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 |
+-----------------+------------------------+----------+-------------+
DENSE_RANK
DENSE_RANK()
OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
ORDER BY expression [ { ASC | DESC } ] [, ...]
説明
ウィンドウ パーティション内の各行の序数(1 ベース)ランクを返します。すべてのピア行は同じランク値を受け取り、後続のランク値は 1 ずつ増加されます。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻り値の型
INT64
例
WITH Numbers AS
(SELECT 1 as x
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 8
UNION ALL SELECT 10
UNION ALL SELECT 10
)
SELECT x,
DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank
FROM Numbers
+-------------------------+
| x | dense_rank |
+-------------------------+
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 5 | 3 |
| 8 | 4 |
| 10 | 5 |
| 10 | 5 |
+-------------------------+
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
DENSE_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 3 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 |
+-----------------+------------------------+----------+-------------+
PERCENT_RANK
PERCENT_RANK()
OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
ORDER BY expression [ { ASC | DESC } ] [, ...]
説明
(RK-1)/(NR-1)として定義される行のパーセンタイル ランクを返します。RK は行の RANK
であり、NR はパーティション内の行数です。NR=1 の場合、0 を返します。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻り値の型
FLOAT64
例
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
PERCENT_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+---------------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+---------------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 0 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 0.33333333333333331 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 0.33333333333333331 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 1 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 0 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 0.33333333333333331 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 0.66666666666666663 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 1 |
+-----------------+------------------------+----------+---------------------+
CUME_DIST
CUME_DIST()
OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
ORDER BY expression [ { ASC | DESC } ] [, ...]
説明
NP/NR として定義される行の相対的ランクを返します。NP は、現在の行に先行するか、ピアになっている行数に定義されます。NR はパーティション内の行数です。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻り値の型
FLOAT64
例
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
CUME_DIST() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 0.25 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 0.75 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 0.75 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 1 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 0.25 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 0.5 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 0.75 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 1 |
+-----------------+------------------------+----------+-------------+
NTILE
NTILE(constant_integer_expression)
OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
ORDER BY expression [ { ASC | DESC } ] [, ...]
説明
この関数は行の順序付けに基づいて行を constant_integer_expression
で指定された数のバケットに分割し、各行に割り当てられた 1 ベースのバケット番号を返します。バケット内の行数は、最大で 1 ずつ変えることができます。
余りの値(行数をバケットで割った余り)は、バケット 1 で開始するバケットごとに 1 が分配されます。constant_integer_expression
が NULL、0、または負の値に評価されると、エラーが出されます。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻り値の型
INT64
例
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
NTILE(3) OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 1 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 3 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 1 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 2 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 3 |
+-----------------+------------------------+----------+-------------+
ROW_NUMBER
ROW_NUMBER()
OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
説明
ORDER BY
句は必要ありません。順序付けされた各パーティションの各行の順次行序数(1 ベース)を返します。ORDER BY
句が指定されていない場合、結果は変動する可能性があります。
OVER
句とその使用方法について詳しくは、ウィンドウ関数の呼び出しをご覧ください。
戻り値の型
INT64
例
WITH Numbers AS
(SELECT 1 as x
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 8
UNION ALL SELECT 10
UNION ALL SELECT 10
)
SELECT x,
ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers
+-------------------------+
| x | row_num |
+-------------------------+
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
| 5 | 4 |
| 8 | 5 |
| 10 | 6 |
| 10 | 7 |
+-------------------------+
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
ROW_NUMBER() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 3 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 4 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 |
+-----------------+------------------------+----------+-------------+
ビット関数
BigQuery は、次のビット関数をサポートしています。
BIT_COUNT
BIT_COUNT(expression)
説明
入力 expression
は整数または BYTES にする必要があります。
入力 expression
で設定されたビット数を返します。符号付き整数の場合、これは 2 の補数形式になります。
戻りデータの型
INT64
例
SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
(0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
(-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
(NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;
+-------+--------+---------------------------------------------+--------+
| a | a_bits | b | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0 | 0 | b"" | 0 |
| 0 | 0 | b"\x00" | 0 |
| 5 | 2 | b"\x05" | 2 |
| 8 | 1 | b"\x00\x08" | 1 |
| 65535 | 16 | b"\xff\xff" | 16 |
| -2 | 63 | b"\xff\xff\xff\xff\xff\xff\xff\xfe" | 63 |
| -1 | 64 | b"\xff\xff\xff\xff\xff\xff\xff\xff" | 64 |
| NULL | NULL | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80 |
+-------+--------+---------------------------------------------+--------+
変換関数
BigQuery は、次の変換関数をサポートしています。これらのデータ型の変換は明示的ですが、一部の変換は暗黙的に行われる可能性があります。暗黙的な変換と明示的な変換の詳細については、こちらをご覧ください。
CAST の概要
CAST(expression AS typename [format_clause])
説明
キャスト構文は、式の結果の型を他の型に変換する必要があることを示す目的でクエリで使用されます。
CAST
を使用する場合、BigQuery がそのキャスティングを実行できなければクエリは失敗します。このようなエラーの発生を防ぐには、SAFE_CAST を使用します。
元の値からターゲット ドメインに正常にマッピングされないサポートされた型の間でキャストすると、ランタイム エラーが発生します。たとえば、バイト列が有効な UTF-8 ではない場合に BYTES を STRING キャスティングするとランタイム エラーが発生します。
一部のキャストには、キャストを行う際の手順を指定する FORMAT 句を挿入できます。たとえば、UTF-8 でエンコードされた文字列ではなく、BASE64 でエンコードされた文字列にバイトのシーケンスを変換するようキャストに指示できます。
FORMAT 句の構造はキャストのタイプごとに異なります。詳細については、該当のキャストのセクションをご覧ください。
例
次のクエリを実行すると、x
が 1
の場合は "true"
になります。その他の非 NULL
値の場合は "false"
に、x
が NULL
の場合は NULL
になります。
CAST(x=1 AS STRING)
CAST AS ARRAY
CAST(expression AS ARRAY<element_type>)
説明
BigQuery は ARRAY へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
ARRAY
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
ARRAY | ARRAY | 正確に同じ ARRAY 型になっている必要があります。 |
CAST AS BIGNUMERIC
CAST(expression AS BIGNUMERIC)
説明
BigQuery は BIGNUMERIC へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
INT64
FLOAT64
NUMERIC
BIGNUMERIC
STRING
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
FLOAT64 | BIGNUMERIC |
浮動小数点数はゼロから遠ざかるように丸められます。NaN 、+inf 、-inf をキャストするとエラーが返されます。BIGNUMERIC の範囲外の値をキャストすると、オーバーフロー エラーが返されます。 |
STRING | BIGNUMERIC |
STRING に含まれる数値リテラルは、BIGNUMERIC 型の最大精度や範囲を超えてはなりません。超えた場合はエラーが発生します。小数点以下が 39 桁以上の場合、キャスト後の BIGNUMERIC 値は小数点以下が 38 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。 |
CAST AS BOOL
CAST(expression AS BOOL)
説明
BigQuery は BOOL へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
INT64
BOOL
STRING
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
INT64 | BOOL | x が 0 の場合は FALSE 、それ以外の場合は TRUE を返します。 |
STRING | BOOL |
x が "true" の場合は TRUE を、x が "false" の場合は FALSE を返します。x のそれ以外の値はすべて無効であり、BOOL にキャスティングせずにエラーをスローします。BOOL に変換するときに、STRING では大文字と小文字が区別されません。 |
CAST AS BYTES
CAST(expression AS BYTES [format_clause])
説明
BigQuery は BYTES へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
BYTES
STRING
FORMAT 句
ある型の式を別の型にキャストする場合は、FORMAT 句を使用してキャストを行う際の手順を指定できます。expression
が STRING
の場合は、このセクションの FORMAT 句を使用できます。
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRING | BYTES | STRING は UTF-8 エンコードを使用して BYTES にキャストされます。たとえば、STRING "©" は BYTES にキャストされると、16 進数 C2 と A9 を持つ 2 バイト数列になります。 |
CAST AS DATE
CAST(expression AS DATE [format_clause])
説明
BigQuery は DATE へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
STRING
TIME
DATETIME
TIMESTAMP
FORMAT 句
ある型の式を別の型にキャストする場合は、FORMAT 句を使用してキャストを行う際の手順を指定できます。expression
が STRING
の場合は、このセクションの FORMAT 句を使用できます。
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRING | DATE | 文字列から日付にキャスティングする場合、文字列はサポートされる日付のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の日付を表している場合は、エラーが発生します。 |
TIMESTAMP | DATE | タイムスタンプから日付へのキャスティングでは、タイムスタンプはデフォルトのタイムゾーンを基点にして効率的に切り捨てられます。 |
CAST AS DATETIME
CAST(expression AS DATETIME [format_clause])
説明
BigQuery は DATETIME へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
STRING
TIME
DATETIME
TIMESTAMP
FORMAT 句
ある型の式を別の型にキャストする場合は、FORMAT 句を使用してキャストを行う際の手順を指定できます。expression
が STRING
の場合は、このセクションの FORMAT 句を使用できます。
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRING | DATETIME | 文字列から日時型にキャスティングする場合、文字列はサポートされる日時型のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の日時を表している場合、エラーが発生します。 |
TIMESTAMP | DATETIME | タイムスタンプから日時へのキャスティングでは、タイムスタンプはデフォルトのタイムゾーンを基点にして効率的に切り捨てられます。 |
CAST AS FLOAT64
CAST(expression AS FLOAT64)
説明
BigQuery は、浮動小数点型へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
INT64
FLOAT64
NUMERIC
BIGNUMERIC
STRING
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
INT64 | FLOAT64 | 近似値ですが、厳密には正確でない可能性のある浮動小数点値を返します。 |
NUMERIC | FLOAT64 | NUMERIC は最も近い浮動小数点数に変換されますが、精度が低下する可能性があります。 |
BIGNUMERIC | FLOAT64 | BIGNUMERIC は最も近い浮動小数点数に変換されますが、精度が低下する可能性があります。 |
STRING | FLOAT64 |
有効な浮動小数点リテラルと同じ形式を持つと解釈して、x を浮動小数点値として返します。"[+,-]inf" から [,-]Infinity 、"[+,-]infinity" から [,-]Infinity 、"[+,-]nan" から NaN へのキャストをサポートします。変換では大文字と小文字を区別しません。 |
CAST AS INT64
CAST(expression AS INT64)
説明
BigQuery は、整数型へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
INT64
FLOAT64
NUMERIC
BIGNUMERIC
BOOL
STRING
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
FLOAT64 | INT64 |
最も近い整数値を返します。 1.5 や -0.5 などの中間値の場合は、ゼロから離れるように丸められます。 |
BOOL | INT64 | x が TRUE の場合は 1 、それ以外の場合は 0 を返します。 |
STRING | INT64 |
16 進文字列を整数にキャストできます。たとえば、0x123 は 291 に、-0x123 は -291 にそれぞれ指定します。 |
例
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 |
+-----------+------------+
CAST AS INTERVAL
CAST(expression AS INTERVAL)
説明
BigQuery は、INTERVAL へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
STRING
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRING | INTERVAL | STRING から INTERVAL にキャスティングする場合、文字列は ISO 8601 Duration 標準、または間隔リテラル形式「Y-M D H:M:S.F」に従う必要があります。部分的な間隔リテラル形式も、「H:M:S」のように明確な場合は受け入れられます。文字列式が無効であるか、サポートされる最小 / 最大範囲外の間隔を表している場合、エラーが発生します。 |
例
SELECT input, CAST(input AS INTERVAL) AS output
FROM UNNEST([
'1-2 3 10:20:30.456',
'1-2',
'10:20:30',
'P1Y2M3D',
'PT10H20M30,456S'
]) input
+--------------------+--------------------+
| input | output |
+--------------------+--------------------+
| 1-2 3 10:20:30.456 | 1-2 3 10:20:30.456 |
| 1-2 | 1-2 0 0:0:0 |
| 10:20:30 | 0-0 0 10:20:30 |
| P1Y2M3D | 1-2 3 0:0:0 |
| PT10H20M30,456S | 0-0 0 10:20:30.456 |
+--------------------+--------------------+
CAST AS NUMERIC
CAST(expression AS NUMERIC)
説明
BigQuery は、NUMERIC へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
INT64
FLOAT64
NUMERIC
BIGNUMERIC
STRING
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
FLOAT64 | NUMERIC |
浮動小数点数はゼロから遠ざかるように丸められます。NaN 、+inf 、-inf をキャストするとエラーが返されます。NUMERIC の範囲外の値をキャストすると、オーバーフロー エラーが返されます。 |
STRING | NUMERIC |
STRING に含まれる数値リテラルは、NUMERIC 型の最大精度や範囲を超えてはなりません。超えた場合はエラーが発生します。小数点以下が 10 桁以上の場合、キャスト後の NUMERIC 値は小数点以下が 9 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。 |
CAST AS STRING
CAST(expression AS STRING [format_clause [AT TIME ZONE timezone_expr]])
説明
BigQuery は、STRING へのキャストをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
INT64
FLOAT64
NUMERIC
BIGNUMERIC
BOOL
BYTES
TIME
DATE
DATETIME
TIMESTAMP
INTERVAL
STRING
FORMAT 句
ある型の式を別の型にキャストする場合は、FORMAT 句を使用してキャストを行う際の手順を指定できます。expression
が次のいずれかのデータ型の場合は、このセクションの FORMAT 句を使用できます。
INT64
FLOAT64
NUMERIC
BIGNUMERIC
BYTES
TIME
DATE
DATETIME
TIMESTAMP
STRING
の FORMAT 句には、AT TIME ZONE timezone_expr
と呼ばれる追加のオプションの句があります。これによって、TIMESTAMP
のフォーマット時に使用する特定のタイムゾーンを指定できます。このオプション句が TIMESTAMP
のフォーマット時に指定されていない場合は、現在のタイムゾーンが使用されます。
詳しくは次の記事をご覧ください。
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
FLOAT64 | STRING | 近似する文字列表現を返します。返された NaN または 0 は符号は付きません。 |
BOOL | STRING |
x が TRUE の場合は "true" 、それ以外の場合は "false" を返します。 |
BYTES | STRING |
UTF-8 STRING と解釈される x を返します。たとえば BYTES リテラル b'\xc2\xa9' の場合、STRING にキャストすると UTF-8 として解釈され、Unicode 文字「©」になります。x が有効な UTF-8 でない場合、エラーが発生します。 |
TIME | STRING | 時間型から文字列へのキャスティングはタイムゾーンとは独立しており、HH:MM:SS の形式になります。 |
DATE | STRING | 日付型から文字列へのキャスティングはタイムゾーンとは独立しており、YYYY-MM-DD の形式になります。 |
DATETIME | STRING |
日時型から文字列へのキャスティングはタイムゾーンとは独立しており、YYYY-MM-DD HH:MM:SS の形式になります。 |
TIMESTAMP | STRING | タイムスタンプから文字列にキャスティングするとき、タイムスタンプはデフォルトのタイムゾーンである UTC を使用して解釈されます。生成されるサブ秒の桁数は、サブ秒部分の末尾のゼロの数によって決まります。CAST 関数によって 0、3、または 6 桁に切り捨てられます。 |
INTERVAL | STRING |
INTERVAL から STRING へのキャスティングは Y-M D H:M:S の形式になります。 |
例
SELECT CAST(CURRENT_DATE() AS STRING) AS current_date
+---------------+
| current_date |
+---------------+
| 2021-03-09 |
+---------------+
SELECT CAST(CURRENT_DATE() AS STRING FORMAT 'DAY') AS current_day
+-------------+
| current_day |
+-------------+
| MONDAY |
+-------------+
SELECT CAST(
TIMESTAMP '2008-12-25 00:00:00+00:00'
AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS date_time_to_string
-- Results depend upon where this query was executed.
+------------------------------+
| date_time_to_string |
+------------------------------+
| 2008-12-24 16:00:00 -08:00 |
+------------------------------+
SELECT CAST(
TIMESTAMP '2008-12-25 00:00:00+00:00'
AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM'
AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string
-- Because the time zone is specified, the result is always the same.
+------------------------------+
| date_time_to_string |
+------------------------------+
| 2008-12-25 05:30:00 +05:30 |
+------------------------------+
SELECT CAST(INTERVAL 3 DAY AS STRING) AS interval_to_string
+--------------------+
| interval_to_string |
+--------------------+
| 0-0 3 0:0:0 |
+--------------------+
SELECT CAST(
INTERVAL "1-2 3 4:5:6.789" YEAR TO SECOND
AS STRING) AS interval_to_string
+--------------------+
| interval_to_string |
+--------------------+
| 1-2 3 4:5:6.789 |
+--------------------+
CAST AS STRUCT
CAST(expression AS STRUCT)
説明
BigQuery は、STRUCT へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
STRUCT
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRUCT | STRUCT |
次の条件が満たされる場合に許可されます。
|
CAST AS TIME
CAST(expression AS TIME [format_clause])
説明
BigQuery は、TIME へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
STRING
TIME
DATETIME
TIMESTAMP
FORMAT 句
ある型の式を別の型にキャストする場合は、FORMAT 句を使用してキャストを行う際の手順を指定できます。expression
が STRING
の場合は、このセクションの FORMAT 句を使用できます。
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRING | TIME | 文字列から時間にキャスティングする場合、文字列はサポートされる時間のリテラル形式に従う必要があり、タイムゾーンとは独立しています。文字列式が無効であるか、サポートされる最小 / 最大範囲外の時間を表している場合は、エラーが発生します。 |
CAST AS TIMESTAMP
CAST(expression AS TIMESTAMP [format_clause [AT TIME ZONE timezone_expr]])
説明
BigQuery は、TIMESTAMP へのキャスティングをサポートしています。expression
パラメータは、次のデータ型の式を表すことができます。
STRING
TIME
DATETIME
TIMESTAMP
FORMAT 句
ある型の式を別の型にキャストする場合は、FORMAT 句を使用してキャストを行う際の手順を指定できます。expression
が STRING
の場合は、このセクションの FORMAT 句を使用できます。
TIMESTAMP
の FORMAT 句には、AT TIME ZONE timezone_expr
と呼ばれる追加のオプションの句があります。これによって、フォーマット時に使用する特定のタイムゾーンを指定できます。この句が指定されていない場合、現在のタイムゾーンが使用されます。
変換規則
型変換の前 | 型変換の後 | x をキャスティングする場合の規則 |
---|---|---|
STRING | TIMESTAMP |
文字列からタイムスタンプにキャスティングする場合、string_expression はサポートされるタイムスタンプのリテラル形式に従う必要があります。従わない場合、ランタイム エラーが発生します。string_expression 自体にタイムゾーンが含まれる場合があります。string_expression にタイムゾーンが含まれる場合、そのタイムゾーンが変換に使用されます。それ以外の場合、デフォルトのタイムゾーンである UTC が使用されます。文字列が 6 桁未満の場合、暗黙的に拡大されます。string_expression が無効である、サブ秒が 6 桁よりも多い(マイクロ秒よりも精度が高い)、またはサポートされるタイムスタンプ範囲外にある時刻を表している場合、エラーが発生します。 |
DATE | TIMESTAMP | 日付からタイムスタンプへのキャスティングでは、date_expression はデフォルトのタイムゾーンである UTC の午前 0 時(1 日が開始する時刻)を基点として解釈されます。 |
DATETIME | TIMESTAMP |
日時からタイムスタンプへのキャスティングでは、datetime_expression はデフォルトのタイムゾーンである UTC で解釈されます。ほとんどの有効な日時値には、各タイムゾーンに対応するタイムスタンプが正確に 1 つだけ存在します。ただし、有効な日時値と、0 または 2 つの対応するタイムスタンプ値があるタイムゾーンとの特定の組み合わせがあります。これは、夏時間など、時計が前後に設定されている場合にタイムゾーンで発生します。有効なタイムスタンプが 2 つある場合は、前のタイムスタンプが使用されます。有効なタイムスタンプがない場合、時間差の長さ(通常は 1 時間)が日時に追加されます。 |
例
次の例では、文字列形式のタイムスタンプをタイムスタンプとしてキャストします。
SELECT CAST("2020-06-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp
-- Results depend upon where this query was executed.
+-----------------------------+
| as_timestamp |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+
次の例では、文字列形式の日付と時刻をタイムスタンプとしてキャストします。これらの例は前の例と同じ出力を返します。
SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE 'America/Los_Angeles') AS as_timestamp
SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE '00') AS as_timestamp
SELECT CAST('06/02/2020 17:00:53.110 +00' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS TZH') AS as_timestamp
PARSE_BIGNUMERIC
PARSE_BIGNUMERIC(string_expression)
説明
文字列を BIGNUMERIC
値に変換します。
文字列に含まれる数値リテラルは、BIGNUMERIC
型の最大精度または範囲を超えないようにする必要があります。超えた場合はエラーが発生します。小数点以下が 39 桁以上の場合、キャスト後の BIGNUMERIC
値は小数点以下が 38 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。
-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_BIGNUMERIC("123.45") AS parsed
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
-- This example shows how a string with an exponent is parsed.
SELECT PARSE_BIGNUMERIC("123.456E37") AS parsed
+-----------------------------------------+
| parsed |
+-----------------------------------------+
| 123400000000000000000000000000000000000 |
+-----------------------------------------+
-- This example shows the rounding when digits after the decimal point exceeds 38.
SELECT PARSE_BIGNUMERIC("1.123456789012345678901234567890123456789") as parsed
+------------------------------------------+
| parsed |
+------------------------------------------+
| 1.12345678901234567890123456789012345679 |
+------------------------------------------+
この関数は CAST AS BIGNUMERIC
関数を使用する場合と類似していますが、PARSE_BIGNUMERIC
関数は文字列入力のみを受け入れ、文字列で以下のことが可能な点が異なります。
- 記号(+/-)と数字の間にスペースを挿入する
- 数字の後に記号(+/-)を挿入する
有効な入力文字列のルール:
ルール | 入力例 | 出力 |
---|---|---|
文字列には、数値、カンマ、小数点、記号のみを含めることができます。 | "- 12,34567,89.0" | -123456789 |
空白文字は数値の間を除く任意の場所に入力できます。 | " - 12.345 " | -12.345 |
小数点の前には、数値とカンマのみを使用できます。 | " 12,345,678" | 12345678 |
小数点以下には数字のみを使用できます。 | "1.234 " | 1.234 |
指数には E または e を使用します。e の後に、数字と先頭記号を表すインジケーターを使用できます。 |
" 123.45e-1" | 12.345 |
整数部分が空でない場合は、数値が 1 つ以上含まれている必要があります。 | " 0,.12 -" | -0.12 |
文字列に小数点が含まれる場合は、少なくとも 1 つの数値が含まれている必要があります。 | " .1" | 0.1 |
文字列に複数の記号を含めることはできません。 | " 0.5 +" | 0.5 |
戻りデータの型
BIGNUMERIC
例
次の例は、記号と数値の前後と間にスペースを挿入した入力を示しています。
SELECT PARSE_BIGNUMERIC(" - 12.34 ") as parsed;
+--------+
| parsed |
+--------+
| -12.34 |
+--------+
次の例は、指数を含み数値の後に記号が挿入された入力を示しています。
SELECT PARSE_BIGNUMERIC("12.34e-1-") as parsed;
+--------+
| parsed |
+--------+
| -1.234 |
+--------+
次の例は、数値の整数部分に複数のカンマが含まれる入力を示しています。
SELECT PARSE_BIGNUMERIC(" 1,2,,3,.45 + ") as parsed;
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
次の例は、整数部分に小数点が含まれ、数値が含まれない入力を示しています。
SELECT PARSE_BIGNUMERIC(".1234 ") as parsed;
+--------+
| parsed |
+--------+
| 0.1234 |
+--------+
無効な入力の例
整数部分に数値が含まれていないため、次の例は無効です。
SELECT PARSE_BIGNUMERIC(",,,.1234 ") as parsed;
数値の間に空白文字があるため、次の例は無効です。
SELECT PARSE_BIGNUMERIC("1 23.4 5 ") as parsed;
指数以外の値が空白であるため、次の例は無効です。
SELECT PARSE_BIGNUMERIC(" e1 ") as parsed;
文字列に複数の記号が含まれているため、次の例は無効です。
SELECT PARSE_BIGNUMERIC(" - 12.3 - ") as parsed;
数値の値が BIGNUMERIC
の範囲外であるため、次の例は無効です。
SELECT PARSE_BIGNUMERIC("12.34E100 ") as parsed;
文字列に無効な文字が含まれているため、次の例は無効です。
SELECT PARSE_BIGNUMERIC("$12.34") as parsed;
PARSE_NUMERIC
PARSE_NUMERIC(string_expression)
説明
文字列を NUMERIC
値に変換します。
文字列に含まれる数値リテラルは、NUMERIC
型の最大精度または範囲を超えないようにする必要があります。超えた場合はエラーが発生します。小数点以下が 10 桁以上の場合、キャスト後の NUMERIC
値は小数点以下が 9 桁になるように丸められます。中間値はゼロから遠ざかるように丸められます。
-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_NUMERIC("123.45") AS parsed
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
-- This example shows how a string with an exponent is parsed.
SELECT PARSE_NUMERIC("12.34E27") as parsed
+-------------------------------+
| parsed |
+-------------------------------+
| 12340000000000000000000000000 |
+-------------------------------+
-- This example shows the rounding when digits after the decimal point exceeds 9.
SELECT PARSE_NUMERIC("1.0123456789") as parsed
+-------------+
| parsed |
+-------------+
| 1.012345679 |
+-------------+
この関数は CAST AS NUMERIC
関数を使用する場合と類似していますが、PARSE_NUMERIC
関数は文字列入力のみを受け入れ、文字列で以下のことが可能な点が異なります。
- 記号(+/-)と数字の間にスペースを挿入する
- 数字の後に記号(+/-)を挿入する
有効な入力文字列のルール:
ルール | 入力例 | 出力 |
---|---|---|
文字列には、数値、カンマ、小数点、記号のみを含めることができます。 | "- 12,34567,89.0" | -123456789 |
空白文字は数値の間を除く任意の場所に入力できます。 | " - 12.345 " | -12.345 |
小数点の前には、数値とカンマのみを使用できます。 | " 12,345,678" | 12345678 |
小数点以下には数字のみを使用できます。 | "1.234 " | 1.234 |
指数には E または e を使用します。e の後に、数字と先頭記号を表すインジケーターを使用できます。 |
" 123.45e-1" | 12.345 |
整数部分が空でない場合は、数値が 1 つ以上含まれている必要があります。 | " 0,.12 -" | -0.12 |
文字列に小数点が含まれる場合は、少なくとも 1 つの数値が含まれている必要があります。 | " .1" | 0.1 |
文字列に複数の記号を含めることはできません。 | " 0.5 +" | 0.5 |
戻りデータの型
NUMERIC
例
次の例は、記号と数値の前後と間にスペースを挿入した入力を示しています。
SELECT PARSE_NUMERIC(" - 12.34 ") as parsed;
+--------+
| parsed |
+--------+
| -12.34 |
+--------+
次の例は、指数を含み数値の後に記号が挿入された入力を示しています。
SELECT PARSE_NUMERIC("12.34e-1-") as parsed;
+--------+
| parsed |
+--------+
| -1.234 |
+--------+
次の例は、数値の整数部分に複数のカンマが含まれる入力を示しています。
SELECT PARSE_NUMERIC(" 1,2,,3,.45 + ") as parsed;
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
次の例は、整数部分に小数点が含まれ、数値が含まれない入力を示しています。
SELECT PARSE_NUMERIC(".1234 ") as parsed;
+--------+
| parsed |
+--------+
| 0.1234 |
+--------+
無効な入力の例
整数部分に数値が含まれていないため、次の例は無効です。
SELECT PARSE_NUMERIC(",,,.1234 ") as parsed;
数値の間に空白文字があるため、次の例は無効です。
SELECT PARSE_NUMERIC("1 23.4 5 ") as parsed;
指数以外の値が空白であるため、次の例は無効です。
SELECT PARSE_NUMERIC(" e1 ") as parsed;
文字列に複数の記号が含まれているため、次の例は無効です。
SELECT PARSE_NUMERIC(" - 12.3 - ") as parsed;
数値の値が BIGNUMERIC
の範囲外であるため、次の例は無効です。
SELECT PARSE_NUMERIC("12.34E100 ") as parsed;
文字列に無効な文字が含まれているため、次の例は無効です。
SELECT PARSE_NUMERIC("$12.34") as parsed;
SAFE_CAST
SAFE_CAST(expression AS typename [format_clause])
説明
CAST
を使用する場合、BigQuery がそのキャスティングを実行できなければクエリは失敗します。たとえば、次のクエリによってエラーが発生します。
SELECT CAST("apple" AS INT64) AS not_a_number;
このようなエラーからクエリを守るには、SAFE_CAST
を使用できます。SAFE_CAST
はエラーの代わりに NULL
を返すという点を除き、CAST
とまったく同じです。
SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;
+--------------+
| not_a_number |
+--------------+
| NULL |
+--------------+
バイトから文字列にキャストする場合は、SAFE_CONVERT_BYTES_TO_STRING
関数も使用できます。無効な UTF-8 文字列は Unicode 置換文字 U+FFFD
に置き換えられます。詳しくは、SAFE_CONVERT_BYTES_TO_STRING をご覧ください。
その他の変換関数
これらの変換関数の詳細については、次のドキュメントをご覧ください。
変換関数 | 型変換の前 | 型変換の後 |
---|---|---|
ARRAY_TO_STRING | ARRAY | STRING |
BOOL | JSON | BOOL |
DATE | さまざまなデータ型 | DATE |
DATETIME | さまざまなデータ型 | DATETIME |
FLOAT64 | JSON | FLOAT64 |
FROM_BASE32 | STRING | BYTES |
FROM_BASE64 | STRING | BYTES |
FROM_HEX | STRING | BYTES |
INT64 | JSON | INT64 |
PARSE_DATE | STRING | DATE |
PARSE_DATETIME | STRING | DATETIME |
PARSE_JSON | STRING | JSON |
PARSE_TIME | STRING | TIME |
PARSE_TIMESTAMP | STRING | TIMESTAMP |
SAFE_CONVERT_BYTES_TO_STRING | BYTES | STRING |
STRING | TIMESTAMP | STRING |
STRING | JSON | STRING |
TIME | さまざまなデータ型 | TIME |
TIMESTAMP | さまざまなデータ型 | TIMESTAMP |
TO_BASE32 | BYTES | STRING |
TO_BASE64 | BYTES | STRING |
TO_HEX | BYTES | STRING |
TO_JSON | すべてのデータ型 | JSON |
TO_JSON_STRING | すべてのデータ型。 | STRING |
CAST 用の FORMAT 句
format_clause:
FORMAT format_model
format_model:
format_string_expression
FORMAT 句は、一部の CAST
関数で使用できます。FORMAT 句を使用すると、キャストを行う際の手順を指定できます。たとえば、UTF-8 でエンコードされた文字列ではなく、BASE64 でエンコードされた文字列にバイトのシーケンスを変換するようキャストに指示できます。
FORMAT 句には形式設定モデルが含まれます。形式設定モデルには、形式設定文字列として組み合わされた形式設定要素を含めることができます。
バイトを文字列としてフォーマットする
CAST(bytes_expression AS STRING FORMAT format_string_expression)
形式設定文字列内に形式設定要素を使用して、バイトのシーケンスを文字列にキャストできます。形式設定要素でバイトをフォーマットできない場合、エラーが返されます。バイトのシーケンスが NULL
の場合、結果は NULL
になります。形式設定要素では大文字と小文字が区別されません。
形式設定要素 | 戻り値 | 例 |
---|---|---|
HEX | バイトのシーケンスを 16 進数の文字列に変換します。 |
入力: b'\x00\x01\xEF\xFF' 出力: 0001efff |
BASEX | バイトのシーケンスを BASEX でエンコードされた文字列に変換します。X は 2、8、16、32、64 のいずれかの数字を表します。 |
BASE8 として入力: b'\x02\x11\x3B' 出力: 00410473 |
BASE64M | MIME の rfc 2045 に基づいて、バイトのシーケンスを BASE64 エンコードの文字列に変換します。76 文字ごとに改行文字(\n)を生成します。 |
入力: b'\xde\xad\xbe\xef' 出力: 3q2+7w== |
ASCII | ASCII 値であるバイトのシーケンスを文字列に変換します。有効な ASCII エンコード以外のバイトが入力に含まれている場合、エラーが返されます。 |
入力: b'\x48\x65\x6c\x6c\x6f' 出力: Hello |
UTF-8 | UTF-8 値であるバイトのシーケンスを文字列に変換します。UTF-8 でエンコードされた有効な値ではないバイトが入力に含まれている場合、エラーが返されます。 |
入力: b'\x24' 出力: $ |
UTF8 | UTF-8 と同じ動作です。 |
戻り値の型
STRING
例
SELECT CAST(b'\x48\x65\x6c\x6c\x6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;
+-----------------+
| bytes_to_string |
+-----------------+
| Hello |
+-----------------+
文字列をバイトとしてフォーマットする
CAST(string_expression AS BYTES FORMAT format_string_expression)
形式設定文字列内に形式設定要素を使用して、文字列をバイトにキャストできます。形式設定要素で文字列をフォーマットできない場合、エラーが返されます。形式設定要素では大文字と小文字が区別されません。
文字列式では、BASE64
または BASE64M
の形式設定要素が使用されていると、\n
などの空白文字は無視されます。
形式設定要素 | 戻り値 | 例 |
---|---|---|
HEX | 16 進数でエンコードされた文字列をバイトに変換します。HEX エンコード文字(0~9、大文字と小文字を区別しない a~f)以外の文字が入力に含まれている場合、エラーが返されます。 |
入力: '0001efff' 出力: b'\x00\x01\xEF\xFF' |
BASEX | BASEX でエンコードされた文字列をバイトに変換します。X は 2、8、16、32、64 のいずれかの数字を表します。BASEX エンコード アルファベット以外の文字(形式設定要素が BASE64 である場合の空白文字を除く)が入力に含まれている場合、エラーが返されます。 |
BASE8 として入力: '00410473' 出力: b'\x02\x11\x3B' |
BASE64M | BASE64 でエンコードされた文字列をバイトに変換します。空白文字以外の文字や、rfc 2045 で定義されている BASE64 エンコード アルファベットでない文字が入力に含まれていると、エラーが返されます。BASE64M と BASE64 のデコードは同じ動作です。 |
入力: '3q2+7w==' 出力: b'\xde\xad\xbe\xef' |
ASCII | ASCII 文字のみで構成される文字列をバイトに変換します。ASCII 文字以外の文字が入力に含まれていると、エラーが返されます。 |
入力: 'Hello' 出力: b'\x48\x65\x6c\x6c\x6f' |
UTF-8 | 文字列を UTF-8 バイトのシーケンスに変換します。 |
入力: '$' 出力: b'\x24' |
UTF8 | UTF-8 と同じ動作です。 |
戻り値の型
BYTES
例
SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes
+-------------------------+
| string_to_bytes |
+-------------------------+
| b'\x48\x65\x6c\x6c\x6f' |
+-------------------------+
日付と時刻を文字列としてフォーマットする
これらの日付と時刻の部分は、文字列としてフォーマットできます。
- 年の部分を文字列としてフォーマットする
- 月の部分を文字列としてフォーマットする
- 日付の部分を文字列としてフォーマットする
- 時間の部分を文字列としてフォーマットする
- 分の部分を文字列としてフォーマットする
- 秒の部分を文字列としてフォーマットする
- 子午線インジケーターを文字列としてフォーマットする
- タイムゾーンを文字列としてフォーマットする
- リテラルを文字列としてフォーマットする
大文字と小文字の区別は、日付と時刻の部分の一部を文字列としてフォーマットし、出力に文字が含まれている場合にサポートされます。詳細については、大文字と小文字の区別をご覧ください。
大文字と小文字の区別
一部の形式設定要素の出力に文字が含まれている場合、出力の大文字と小文字の区分は形式設定要素の大文字と小文字の区分に一致します。つまり、出力内の単語は、形式設定要素が大文字に設定される方法に応じて大文字に変換されます。これを大文字と小文字の区別と呼びます。ルールは次のとおりです。
- 要素の最初の 2 文字が両方とも大文字の場合は、出力に含まれる単語が大文字になります。例:
DAY
=THURSDAY
. - 要素の最初の文字が大文字であり、2 番目の文字が小文字である場合は、出力の各単語の最初の文字が大文字になり、その他の文字は小文字になります。例:
Day
=Thursday
. - 要素の最初の文字が小文字の場合は、出力内のすべての文字が小文字になります。例:
day
=thursday
年の部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
年の部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある年のデータ型が含まれます。format_string_expression
: 年の形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には年の部分が含まれます。
DATE
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
YYYY | 年、4 桁以上の数値。 |
入力: DATE '2018-01-30' 出力: 2018 入力: DATE '76-01-30' 出力: 0076 入力: DATE '10000-01-30' 出力: 10000 |
YYY | 年、最後の 3 桁のみ。 |
入力: DATE '2018-01-30' 出力: 018 入力: DATE '98-01-30' 出力: 098 |
YY | 年、最後の 2 桁のみ。 |
入力: DATE '2018-01-30' 出力: 18 入力: DATE '8-01-30' 出力: 08 |
Y | 年、最後の数値のみ。 | 入力: DATE '2018-01-30' 出力: 8 |
RRRR | YYYY と同じ動作。 | |
RR | YY と同じ動作。 |
戻り値の型
STRING
例
SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'YYYY') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 2018 |
+---------------------+
月の部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
月の部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある月のデータ型が含まれます。format_string_expression
: 月の形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には月の部分が含まれます。
DATE
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
MM | 月、2 桁。 | 入力: DATE '2018-01-30' 出力: 01 |
MON | 3 文字で構成された月の略称。ロケールが en-US の場合、略称は JAN、FEB、MAR、APR、MAY、JUN、JUL、AUG、SEP、OCT、NOV、DEC です。大文字と小文字の区別がサポートされています。 |
入力: DATE '2018-01-30' 出力: JAN |
MONTH | 月の名前。大文字と小文字の区別はサポートされています。 |
入力: DATE '2018-01-30' 出力: JANUARY |
戻り値の型
STRING
例
SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'MONTH') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| JANUARY |
+---------------------+
日付の部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
日付の部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある日付のデータ型が含まれます。format_string_expression
: 日付の形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には日付の部分が含まれます。
DATE
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
DAY | ローカライズされた曜日の名前。出力サイズが厳密に 9 になるように、空白文字列が右側にパディングされます。大文字と小文字の区別はサポートされています。 |
入力: DATE '2020-12-31' 出力: THURSDAY |
DY | 3 文字で構成されるローカライズされた曜日の略称。ロケールが en-US の場合、略称は MON、TUE、WED、THU、FRI、SAT、SUN です。大文字と小文字の区別はサポートされています。 |
入力: DATE '2020-12-31' 出力: THU |
D | 曜日(1~7)。日曜日を 1 として開始します。 | 入力: DATE '2020-12-31' 出力: 4 |
DD | 月の 2 桁の日付。 | 入力: DATE '2018-12-02' 出力: 02 |
DDD | 年の 3 桁の日付。 | 入力: DATE '2018-02-03' 出力: 034 |
戻り値の型
STRING
例
SELECT CAST(DATE '2018-02-15' AS STRING FORMAT 'DD') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 15 |
+---------------------+
時間の部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
時間の部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある時間のデータ型が含まれます。format_string_expression
: 時間の形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には時間の部分が含まれます。
TIME
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
HH | 時刻、12 時間制、2 桁。 |
入力: TIME '21:30:00'
出力: 09 |
HH12 | 12 時間形式の時刻(時) |
入力: TIME '21:30:00'
出力: 09 |
HH24 | 時刻、24 時間制、2 桁。 |
入力: TIME '21:30:00'
出力: 21 |
戻り値の型
STRING
例
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH24') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 21 |
+---------------------+
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH12') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 09 |
+---------------------+
分の部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
分の部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある分のデータ型が含まれます。format_string_expression
: 分の形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には分の部分が含まれます。
TIME
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
MI | 分、2 桁。 |
入力: TIME '01:02:03'
出力: 02 |
戻り値の型
STRING
例
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'MI') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 30 |
+---------------------+
秒の部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
秒の部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある秒のデータ型が含まれます。format_string_expression
: 秒の形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には秒の部分が含まれます。
TIME
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
SS | 1 分の秒数、2 桁。 |
入力: TIME '01:02:03'
出力: 03 |
SSSSS | 1 日の秒数、5 桁。 |
入力: TIME '01:02:03'
出力: 03723 |
FFn |
秒の小数部、n 桁。n は、1~9 の値に置き換えます。例: FF5。秒の小数部は、出力のサイズに合わせて丸められます。 |
FF1 の入力: TIME '01:05:07.16' 出力: 1 FF2 の入力: TIME '01:05:07.16' 出力: 16 FF3 の入力: TIME '01:05:07.16' 出力: 016 |
戻り値の型
STRING
例
SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'SS') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 25 |
+---------------------+
SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'FF2') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 16 |
+---------------------+
子午線インジケーターの部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
子午線インジケーターを含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要がある子午線インジケーターのデータ型が含まれています。format_string_expression
: 子午線インジケーター形式設定要素などの形式設定要素を含む文字列。
以下のデータ型には子午線インジケーターの部分が含まれます。
TIME
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
A.M. | 時間の値が 12 未満の場合は A.M.、それ以外の場合は P.M.です。出力の大文字と小文字の区分は、形式設定要素の最初の文字の大文字と小文字の区分によって決定されます。 |
A.M. の入力:TIME '01:02:03' 出力: A.M. A.M. の入力:TIME '16:02:03' 出力: P.M. a.m. の入力:TIME '01:02:03' 出力: a.m. a.M. の入力:TIME '01:02:03' 出力: a.m. |
AM | 時間の値が 12 未満の場合は AM、それ以外の場合は PM です。出力の大文字と小文字の区分は、形式設定要素の最初の文字の大文字と小文字の区分によって決定されます。 |
AM の入力: TIME '01:02:03' 出力: AM AM の入力: TIME '16:02:03' 出力: PM am の入力: TIME '01:02:03' 出力: am aM の入力: TIME '01:02:03' 出力: am |
P.M. | 出力は A.M. 形式設定要素と同じです。 | |
PM | 出力は AM 形式設定要素と同じです。 |
戻り値の型
STRING
例
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| PM |
+---------------------+
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| AM |
+---------------------+
タイムゾーンの部分を文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
タイムゾーンの部分を含むデータ型を文字列にキャストします。キャストの手順を指定する形式設定要素が含まれます。
expression
: この式には、フォーマットする必要があるタイムゾーンのデータ型が含まれます。format_string_expression
: タイムゾーン形式設定要素などの形式設定要素を含む文字列。
以下のデータ型にはタイムゾーンの部分が含まれます。
DATE
TIME
DATETIME
TIMESTAMP
expression
または format_string_expression
が NULL
の場合、戻り値は NULL
です。format_string_expression
が空の文字列の場合、出力は空の文字列になります。サポート対象の形式設定要素ではない値が format_string_expression
または expression
に表示される場合、形式設定要素で指定された値が含まれていないと、エラーが発生します。
形式設定要素 | 戻り値 | 例 |
---|---|---|
TZH |
タイムゾーンの時間オフセット。これには、+/- 記号と 2 桁の時間が含まれます。 |
入力スタンプ: TIMESTAMP '2008-12-25 05:30:00+00' 出力: −08 |
TZM | タイムゾーンの分単位のオフセット。これには 2 桁の分のみが含まれます。 | 入力スタンプ: TIMESTAMP '2008-12-25 05:30:00+00' 出力: 00 |
戻り値の型
STRING
例
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH') AS date_time_to_string;
-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| -08 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;
-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| +05 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM') AS date_time_to_string;
-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| 00 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;
-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| 30 |
+---------------------+
リテラルを文字列としてフォーマットする
CAST(expression AS STRING FORMAT format_string_expression)
形式設定要素 | 戻り値 | 例 |
---|---|---|
- | 出力は入力と同じです。 | - |
. | 出力は入力と同じです。 | . |
/ | 出力は入力と同じです。 | / |
, | 出力は入力と同じです。 | , |
' | 出力は入力と同じです。 | ' |
; | 出力は入力と同じです。 | ; |
: | 出力は入力と同じです。 | : |
空白文字 | 出力は入力と同じです。空白文字はスペース文字(ASCII 32)を表します。タブや改行など、他の種類のスペースを表すものではありません。形式設定モデルで ASCII 32 文字以外の空白文字はエラーになります。 | |
"text" |
出力は二重引用符で囲まれた値です。二重引用符またはバックスラッシュ文字を保持するには、\" または \\ のエスケープ シーケンスを使用します。他のエスケープ シーケンスはサポートされていません。 |
入力: "abc" 出力: abc 入力: "a\"b\\c" 出力: a"b\c |
文字列を日付と時刻としてフォーマットする
文字列は以下の日付と時刻の部分でフォーマットできます。
- 文字列を年の部分としてフォーマットする
- 文字列を月の部分としてフォーマットする
- 文字列を日付の部分としてフォーマットする
- 文字列を時間の部分としてフォーマットする
- 文字列を分の部分としてフォーマットする
- 文字列を秒の部分としてフォーマットする
- 文字列を子午線インジケーターの部分としてフォーマットする
- 文字列をタイムゾーンの部分としてフォーマットする
- 文字列をリテラルの部分としてフォーマットする
日付と時刻の部分で文字列をフォーマットする場合は、形式設定モデルのルールに従う必要があります。
形式設定モデルのルール
文字列を日付と時刻の部分にキャストする場合は、形式設定モデルが有効であることを確認する必要があります。形式設定モデルは、format_string_expression
として CAST(string_expression AS type FORMAT format_string_expression)
に渡される要素を表し、次のルールに従って検証されます。
- 子午線インジケーター、年、月、日、時間の各部分のうち最大 1 つの要素が含まれます。
- 非リテラルの空白文字以外の形式設定要素を複数回使用することはできません。
- 日形式の要素
DDD
が含まれている場合は、月を含めることはできません。 - 24 時間制の形式設定要素
HH24
が含まれている場合は、12 時間制の形式設定要素または子午線インジケーターを含めることはできません。 - 12 時間制の形式設定要素
HH12
またはHH
が含まれている場合は、子午線インジケーターも含める必要があります。 - 子午線インジケーターを含む場合は、12 時間制の形式設定要素も含まれている必要があります。
- 日付形式設定要素の秒
SSSSS
が含まれている場合は、時間、分、秒、子午線インジケーターのいずれも含めることはできません。 - 設定された値がターゲット タイプに含まれないような形式要素を含めることはできません。たとえば、
HH24
などの時間形式の要素は、DATE
としてキャストする文字列には使用できません。
文字列を年の部分としてフォーマットする
CAST(string_expression AS type FORMAT format_string_expression)
文字列形式の年を年の部分を含むデータ型にキャストします。キャストの手順を指定する形式設定要素が含まれます。
string_expression
: この式には、フォーマットする必要がある年を含む文字列が挿入されています。type
: キャスト先のデータ型。年の部分を含める必要があります。format_string_expression
: 年の形式