', default: (\)
```
CSV 形式でデータをエクスポートする場合、このフィールドは S3 バケットに書き込まれたデータファイルでエスケープ文字として扱う文字を指定します。エスケープは以下のようなシナリオで発生します。
1. 値自体に引用文字 (") が含まれている場合、エスケープ文字を使用してエスケープされます。例えば、値が `Time"stream` で、(\$1) が設定されたエスケープ文字である場合、`Time\"stream` としてエスケープされます。
1. 値に設定されたエスケープ文字が含まれている場合、エスケープされます。例えば、値が `Time\stream` の場合、`Time\\stream` としてエスケープされます。
エクスポートされた出力に配列、行、時系列などの複雑なデータ型が含まれている場合、JSON 文字列としてシリアル化されます。次に例を示します。
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/timestream/latest/developerguide/supported-sql-constructs.UNLOAD.html)
INCLUDE\$1HEADER
```
include_header = 'true' , default: 'false'
```
CSV 形式でデータをエクスポートする場合、このフィールドではエクスポートされた CSV データファイルの最初の行として列名を含めることができます。
使用できる値は「true」と「false」で、デフォルト値は「false」です。`escaped_by` や `field_delimiter` などのテキスト変換オプションは、ヘッダーにも適用されます。
ヘッダーを含める場合、キャリッジリターン文字 (ASCII 13、16 進 0D、テキスト '\$1r') または改行文字 (ASCII 10、16 進 0A、テキスト '\$1n') を `FIELD_DELIMITER` として選択しないことが重要です。これらの文字を選択すると、多くのパーサーが生成された CSV 出力でヘッダーを正しく解析できなくなるためです。
MAX\$1FILE\$1SIZE
```
max_file_size = 'X[MB|GB]' , default: '78GB'
```
このフィールドは、`UNLOAD` ステートメントが Amazon S3 で作成するファイルの最大サイズを指定します。`UNLOAD` ステートメントは複数のファイルを作成できますが、Amazon S3 に書き込まれる各ファイルの最大サイズは、このフィールドで指定されているサイズとほぼ同じになります。
フィールドの値は、16 MB～78 GB である必要があります。`12GB` などの整数、または `0.5GB` や `24.7MB` などの小数で指定できます。デフォルト値は 78 GB です。
実際のファイルサイズは、ファイルの書き込み時に概算されるため、実際の最大サイズは指定した数字と正確に等しくない場合があります。
# 論理演算子
Timestream for LiveAnalytics は、次の論理演算子をサポートしています。
| 演算子 | 説明 | 例 |
| --- | --- | --- |
| AND | 両方の値が true の場合、true | a AND b |
| OR | いずれかの値が true の場合は true | a OR b |
| NOT | 値が false の場合、true | NOT a |
+ `AND` 比較の結果は、式の一方または両側が `NULL` である場合、`NULL` です。
+ `AND` 演算子の少なくとも一方が `FALSE` の場合、式は `FALSE` に評価されます。
+ `OR` 比較の結果は、式の一方または両側が `NULL` である場合、`NULL` です。
+ `OR` 演算子の少なくとも一方が `TRUE` の場合、式は `TRUE` に評価されます。
+ `NULL` の論理補数は `NULL` です。
次の真理値表は、`AND` および `OR` での `NULL` の処理を示しています。
| A | B | A and b | A or b |
| --- | --- | --- | --- |
| null | null | null | null |
| false | null | false | null |
| null | false | false | null |
| true | null | null | true |
| null | true | null | true |
| false | false | false | false |
| true | false | false | true |
| false | true | false | true |
| true | true | true | true |
次の真理値表は、NOT での NULL の処理を示しています。
| A | Not a |
| --- | --- |
| null | null |
| true | false |
| false | true |
# 比較演算子
Timestream for LiveAnalytics は、次の比較演算子をサポートしています。
| 演算子 | 説明 |
| --- | --- |
| < | Less than |
| > | Greater than |
| <= | 以下 |
| >= | 以上 |
| = | Equal |
| <> | 等しくない |
| \$1= | 等しくない |
**注記**
`BETWEEN` 演算子は、値が指定された範囲内にあるかどうかをテストします。構文は次のとおりです。
```
BETWEEN min AND max
```
`BETWEEN` または `NOT BETWEEN` ステートメントに `NULL` が存在すると、ステートメントは `NULL` に評価されます。
`IS NULL ` および `IS NOT NULL` 演算子は、値が null (未定義) であるかどうかをテストします。`IS NULL` で `NULL` を使用すると true と評価されます。
SQL では、`NULL` 値は不明な値を示します。
# 比較関数
Timestream for LiveAnalytics は、次の比較関数をサポートしています。
**Topics**
+ [
# greatest()
](comparison-functions.greatest.md)
+ [
# least()
](comparison-functions.least.md)
+ [
# ALL()、ANY()、および SOME()
](comparison-functions.all-any-some.md)
# greatest()
**greatest()** 関数は、指定された値の最大値を返します。指定された値のいずれかが `NULL` の場合、`NULL` を返します。構文は次のとおりです。
```
greatest(value1, value2, ..., valueN)
```
# least()
**least()** 関数は、指定された値の最小値を返します。指定された値のいずれかが `NULL` の場合、`NULL` を返します。構文は次のとおりです。
```
least(value1, value2, ..., valueN)
```
# ALL()、ANY()、および SOME()
`ALL`、`ANY`、`SOME` の数量詞は、次のように比較演算子と一緒に使用できます。
| 式 | 意味 |
| --- | --- |
| A = ALL(...) | A がすべての値と等しい場合、true に評価されます。 |
| A <> ALL(...) | A が値と一致しない場合、true に評価されます。 |
| A < ALL(...) | A が最小値より小さい場合に true に評価されます。 |
| A = ANY(...) | A がいずれかの値と等しい場合に true に評価されます。 |
| A <> ANY(...) | A が 1 つ以上の値と一致しない場合、true に評価されます。 |
| A < ANY(...) | A が最大値より小さい場合に true に評価されます。 |
## 例と使用上の注意事項
**注記**
`ALL`、`ANY`、`SOME` のいずれかを使用する際、比較値がリテラルのリストである場合は、キーワード `VALUES` を使用する必要があります。
## 例: `ANY()`
クエリステートメントの `ANY()` の例は次のとおりです。
```
SELECT 11.7 = ANY (VALUES 12.0, 13.5, 11.7)
```
同じオペレーションの代替構文は次のとおりです。
```
SELECT 11.7 = ANY (SELECT 12.0 UNION ALL SELECT 13.5 UNION ALL SELECT 11.7)
```
この場合、`ANY()` は `True` に評価されます。
## 例: `ALL()`
クエリステートメントの `ALL()` の例は次のとおりです。
```
SELECT 17 < ALL (VALUES 19, 20, 15);
```
同じオペレーションの代替構文は次のとおりです。
```
SELECT 17 < ALL (SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 15);
```
この場合、`ALL()` は `False` に評価されます。
## 例: `SOME()`
クエリステートメントの `SOME()` の例は次のとおりです。
```
SELECT 50 >= SOME (VALUES 53, 77, 27);
```
同じオペレーションの代替構文は次のとおりです。
```
SELECT 50 >= SOME (SELECT 53 UNION ALL SELECT 77 UNION ALL SELECT 27);
```
この場合、`SOME()` は `True` に評価されます。
# 条件式
Timestream for LiveAnalytics は、次の条件式をサポートしています。
**Topics**
+ [
# CASE ステートメント
](conditional-expressions.CASE.md)
+ [
# IF ステートメント
](conditional-expressions.IF.md)
+ [
# COALESCE ステートメント
](conditional-expressions.COALESCE.md)
+ [
# NULLIF ステートメント
](conditional-expressions.NULLIF.md)
+ [
# TRY ステートメント
](conditional-expressions.TRY.md)
# CASE ステートメント
**CASE** ステートメントは、`expression` に等しい式が見つかるまで、左から右に各値式を検索します。一致が検出されると、一致する値の結果が返されます。一致が見つからない場合、存在する場合は `ELSE` 句の結果が返されます。それ以外の場合は `null` が返されます。構文は次のとおりです。
```
CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
```
Timestream は、**CASE** ステートメントの次の構文もサポートしています。この構文では、「検索済み」形式は、各ブール条件を左から右へ `true` の条件があるまで評価し、一致する結果を返します。`true` の条件がない場合、存在する場合は `ELSE` 句から結果が返され、それ以外の場合は `null` が返されます。代替構文については、以下を参照してください。
```
CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
```
# IF ステートメント
**IF** ステートメントは条件を true または false と評価し、適切な値を返します。Timestream は、**IF** に対して次の 2 つの構文表現をサポートしています。
```
if(condition, true_value)
```
この構文は、条件が `true` の場合は `true_value` を評価して返します。それ以外の場合は `null` が返され、`true_value` は評価されません。
```
if(condition, true_value, false_value)
```
この構文は、条件が `true` の場合は `true_value` を評価して返します。それ以外の場合は `false_value` を評価して返します。
## 例
```
SELECT
if(true, 'example 1'),
if(false, 'example 2'),
if(true, 'example 3 true', 'example 3 false'),
if(false, 'example 4 true', 'example 4 false')
```
| \$1col0 | \$1col1 | \$1col2 | \$1col3 |
| --- | --- | --- | --- |
| `example 1` | `-` `null` | `example 3 true` | `example 4 false` |
# COALESCE ステートメント
**COALESCE** は、引数リストに存在する NULL 以外の最初の値を返します。構文は次のとおりです。
```
coalesce(value1, value2[,...])
```
# NULLIF ステートメント
**IF** ステートメントは条件を true または false と評価し、適切な値を返します。Timestream は、**IF** に対して次の 2 つの構文表現をサポートしています。
**NULLIF** は、`value1` が `value2` と等しい場合に null を返します。それ以外の場合は、`value1` を返します。構文は次のとおりです。
```
nullif(value1, value2)
```
# TRY ステートメント
**TRY** 関数は式を評価し、`null` を返すことで特定のタイプのエラーを処理します。構文は次のとおりです。
```
try(expression)
```
# 変換関数
Timestream for LiveAnalytics は、次の変換関数をサポートしています。
**Topics**
+ [
## cast()
](#conversion-functions.cast)
+ [
## try\$1cast()
](#conversion-functions.try-cast)
## cast()
値を型として明示的にキャストする cast 関数の構文は次のとおりです。
```
cast(value AS type)
```
## try\$1cast()
Timestream for LiveAnalytics は、cast に似ている try\$1cast 関数もサポートしていますが、cast が失敗すると null を返します。構文は次のとおりです。
```
try_cast(value AS type)
```
# 数学演算子
Timestream for LiveAnalytics は、次の数学演算子をサポートしています。
| 演算子 | 説明 |
| --- | --- |
| \$1 | 加算 |
| - | 減算 |
| \$1 | 乗算 |
| / | 除算 (整数除算は切り捨てを実行します) |
| % | モジュロ (剰余) |
# 数学関数
Timestream for LiveAnalytics は、次の数学関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| abs(x) | [入力と同じ] | x の絶対値を返します。 |
| cbrt(x) | double | x の立方根を返します。 |
| ceiling(x) または ceil(x) | [入力と同じ] | 最も近い整数に切り上げられた x を返します。 |
| degrees(x) | double | ラジアン単位の角度 x を度に変換します。 |
| e() | double | 定数のオイラー数値を返します。 |
| exp(x) | double | オイラー数の x 乗値を返します。 |
| floor(x) | [入力と同じ] | 最も近い整数に切り下げられた x を返します。 |
| from\$1base(string,radix) | bigint | 基数の数値として解釈される文字列の値を返します。 |
| ln(x) | double | x の自然対数を返します。 |
| log2(x) | double | 2 を底とする x の対数を返します。 |
| log10(x) | double | 10 を底とする x の対数を返します。 |
| mod(n,m) | [入力と同じ] | n のモジュロ (剰余) を m で割った値を返します。 |
| pi() | double | 定数 Pi を返します。 |
| pow(x, p) または power(x, p) | double | x の p 乗値を返します。 |
| radians(x) | double | 度単位の角度 x をラジアンに変換します。 |
| rand() または random() | double | 0.0 1.0 の範囲の擬似ランダム値を返します。 |
| random(n) | [入力と同じ] | 0～n の擬似乱数を返します (両端を除く)。 |
| round(x) | [入力と同じ] | 最も近い整数に丸められた x を返します。 |
| round(x,d) | [入力と同じ] | 少数第 d 位に丸めた x を返します。 |
| sign(x) | [入力と同じ] | x の符号関数を返します。つまり、次のようになります。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/timestream/latest/developerguide/mathematical-functions.html) double の引数の場合、関数はさらに以下を返します。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/timestream/latest/developerguide/mathematical-functions.html) |
| sqrt(x) | double | x の平方根を返します。 |
| to\$1base(x, radix) | varchar | x の基数表現を返します。 |
| truncate(x) | double | 小数点以下の桁を切り捨てて、x を整数に四捨五入して返します。 |
| acos(x) | double | x のアークコサイン (逆余弦) を返します。 |
| asin(x) | double | x のアークサイン (逆正弦) を返します。 |
| atan(x) | double | x のアークタンジェント (逆正接) を返します。 |
| atan2(y, x) | double | y/x のアークタンジェント (逆正接) を返します。 |
| cos(x) | double | x のコサイン (余弦) を返します。 |
| cosh(x) | double | x のハイパーボリックコサイン (双曲線余弦) を返します。 |
| sin(x) | double | x のサイン (正弦) を返します。 |
| tan(x) | double | x のタンジェント (正接) を返します。 |
| tanh(x) | double | x のハイパーボリックタンジェント (双曲線正接) を返します。 |
| infinity() | double | 正の無限大を表す定数を返します。 |
| is\$1finite(x) | boolean | x が有限かどうかを判断します。 |
| is\$1infinite(x) | boolean | x が無限であるかどうかを判断します。 |
| is\$1nan(x) | boolean | x が非数かどうかを判断します。 |
| nan() | double | 非数を表す定数を返します。 |
# 文字列演算子
Timestream for LiveAnalytics は、1 つ以上の文字列を連結するための `||` 演算子をサポートしています。
# 文字列関数
**注記**
これらの関数の入力データ型は、特に指定がない限り、varchar とみなされます。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| chr(n) | varchar | Unicode コードポイント n を varchar として返します。 |
| codepoint(x) | integer | str の唯一の文字の Unicode コードポイントを返します。 |
| concat(x1, ..., xN) | varchar | x1, x2, ..., xN の連結を返します。 |
| hamming\$1distance(x1,x2) | bigint | x1 と x2 のハミング距離を返します。つまり、対応する文字が異なる位置の数を返します。2 つの varchar 入力の長さは同じである必要があります。 |
| length(x) | bigint | x の長さを文字で返します。 |
| levenshtein\$1distance(x1, x2) | bigint | x1 と x2 のレーベンシュタイン編集距離を返します。つまり、x1 を x2 に変更するために必要な 1 文字の編集 (挿入、削除、置換) の最小数を返します。 |
| lower(x) | varchar | x を小文字に変換します。 |
| lpad(x1, bigint size, x2) | varchar | x1 の左側を x2 を使用して指定文字数になるようパディングします。指定文字数が x1 の長さより短い場合、結果は指定文字数に切り捨てられます。指定文字数は負にできず、x2 は空にできません。 |
| ltrim(x) | varchar | x から先頭の空白を削除します。 |
| replace(x1, x2) | varchar | x1 から x2 のすべてのインスタンスを削除します。 |
| replace(x1, x2, x3) | varchar | x1 の x2 のインスタンスすべてを x3 に置き換えます。 |
| Reverse(x) | varchar | 文字を逆順にして x を返します。 |
| rpad(x1, bigint size, x2) | varchar | x1 の右側を x2 を使用して指定文字数になるようパディングします。指定文字数が x1 の長さより短い場合、結果は指定文字数に切り捨てられます。指定文字数は負にできず、x2 は空にできません。 |
| rtrim(x) | varchar | x から末尾の空白を削除します。 |
| split(x1, x2) | array(varchar) | 区切り記号 x2 で x1 を分割し、配列を返します。 |
| split(x1, x2, bigint limit) | array(varchar) | 区切り記号 x2 で x1 を分割し、配列を返します。配列の最後の要素には、常に x1 に残っているすべてが含まれます。制限は正の数である必要があります。 |
| split\$1part(x1, x2, bigint pos) | varchar | 区切り記号 x2 で x1 を分割し、pos に varchar フィールドを返します。フィールドインデックスは 1 で始まります。pos がフィールド数より大きい場合、null が返されます。 |
| strpos(x1, x2) | bigint | x1 内における x2 の先頭インスタンスの開始位置を返します。位置は 1 で始まります。検出されなかった場合は、0 が返されます。 |
| strpos(x1, x2,bigint instance) | bigint | x1 内における x2 の第 N インスタンスの位置を返します。インスタンスは正の数である必要があります。位置は 1 で始まります。検出されなかった場合は、0 が返されます。 |
| strrpos(x1, x2) | bigint | x1 内における x2 の最後尾インスタンスの開始位置を返します。位置は 1 で始まります。検出されなかった場合は、0 が返されます。 |
| strrpos(x1, x2, bigint instance) | bigint | x1 の末尾からの、x1 にある x2 の第 N インスタンスの位置を返します。インスタンスは正の数である必要があります。位置は 1 で始まります。検出されなかった場合は、0 が返されます。 |
| position(x2 IN x1) | bigint | x1 内における x2 の先頭インスタンスの開始位置を返します。位置は 1 で始まります。検出されなかった場合は、0 が返されます。 |
| substr(x, bigint start) | varchar | 開始位置から x の残りを返します。位置は 1 で始まります。負の開始位置は、x の末尾を基準として解釈されます。 |
| substr(x, bigint start, bigint len) | varchar | 開始位置からの長さの x から部分文字列を返します。位置は 1 で始まります。負の開始位置は、x の末尾を基準として解釈されます。 |
| trim(x) | varchar | x から先頭および末尾の空白を削除します。 |
| upper(x) | varchar | x を大文字に変換します。 |
# 配列演算子
Timestream for LiveAnalytics は、次の配列演算子をサポートしています。
| 演算子 | 説明 |
| --- | --- |
| [] | 最初のインデックスが 1 で始まる配列の要素にアクセスします。 |
| \$1\$1 | 配列を同じタイプの別の配列または要素と連結します。 |
# 配列関数
Timestream for LiveAnalytics は、次の配列関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| array\$1distinct(x) | array | 配列 x から重複した値を削除します。 SELECT array_distinct(ARRAY[1,2,2,3])
結果の例: `[ 1,2,3 ]` |
| array\$1intersect(x, y) | array | x と y の共通部分にある要素の配列を重複なしで返します。 SELECT array_intersect(ARRAY[1,2,3], ARRAY[3,4,5])
結果の例: `[ 3 ]` |
| array\$1union(x, y) | array | x と y の和集合内の要素の配列を重複なしで返します。 SELECT array_union(ARRAY[1,2,3], ARRAY[3,4,5])
結果の例: `[ 1,2,3,4,5 ]` |
| array\$1except(x, y) | array | x にあるが y にはない要素の配列を重複なしで返します。 SELECT array_except(ARRAY[1,2,3], ARRAY[3,4,5])
結果の例: `[ 1,2 ]` |
| array\$1join(x, delimiter, null\$1replacement) | varchar | 区切り文字とオプションの文字列を使用して特定の配列の要素を連結し、null を置き換えます。 SELECT array_join(ARRAY[1,2,3], ';', '')
結果の例: `1;2;3` |
| array\$1max(x) | 配列要素と同じ | 入力配列の最大値を返します。 SELECT array_max(ARRAY[1,2,3])
結果の例: `3` |
| array\$1min(x) | 配列要素と同じ | 入力配列の最小値を返します。 SELECT array_min(ARRAY[1,2,3])
結果の例: `1` |
| array\$1position(x, element) | bigint | 配列 x 内における要素の最初の出現位置を返します (見つからない場合は 0)。 SELECT array_position(ARRAY[3,4,5,9], 5)
結果の例: `3` |
| array\$1remove(x, element) | array | 配列 x の要素と等しいすべての要素を削除します。 SELECT array_remove(ARRAY[3,4,5,9], 4)
結果の例: `[ 3,5,9 ]` |
| array\$1sort(x) | array | 配列 x をソートして返します。x の要素は順序付け可能である必要があります。Null 要素は、返された配列の最後に配置されます。 SELECT array_sort(ARRAY[6,8,2,9,3])
結果の例: `[ 2,3,6,8,9 ]` |
| arrays\$1overlap(x, y) | boolean | 配列 x と y に null 以外の要素が共通しているかどうかをテストします。NULL 以外の共通の要素がなく、いずれかの配列に NULL が含まれている場合は、NULL を返します。 SELECT arrays_overlap(ARRAY[6,8,2,9,3], ARRAY[6,8])
結果の例: `true` |
| cardinality(x) | bigint | 配列 x のサイズを返します。 SELECT cardinality(ARRAY[6,8,2,9,3])
結果の例: `5` |
| concat(array1, array2, ..., arrayN) | array | 配列 array1, array2, ..., arrayN を連結します。 SELECT concat(ARRAY[6,8,2,9,3], ARRAY[11,32], ARRAY[6,8,2,0,14])
結果の例: `[ 6,8,2,9,3,11,32,6,8,2,0,14 ]` |
| element\$1at(array(E), index) | E | 指定されたインデックスで配列の要素を返します。インデックスが 0 より小さい場合、element\$1at は末尾から先頭に向かって要素にアクセスします。 SELECT element_at(ARRAY[6,8,2,9,3], 1)
結果の例: `6` |
| repeat(element, count) | array | カウント時間に対して要素を繰り返します。 SELECT repeat(1, 3)
結果の例: `[ 1,1,1 ]` |
| reverse(x) | array | 配列 x の逆順の配列を返します。 SELECT reverse(ARRAY[6,8,2,9,3])
結果の例: `[ 3,9,2,8,6 ]` |
| sequence(start, stop) | array(bigint) | 開始から停止までの整数のシーケンスを生成し、開始が停止以下の場合は 1 ずつ増分します。そうでない場合は -1 ずつとなります。 SELECT sequence(3, 8)
結果の例: `[ 3,4,5,6,7,8 ]` |
| sequence(start, stop, step) | array(bigint) | 開始から停止まで整数のシーケンスを生成し、ステップごとに増分します。 SELECT sequence(3, 15, 2)
結果の例: `[ 3,5,7,9,11,13,15 ]` |
| sequence(start, stop) | array(timestamp) | 開始日から終了日までの一連のタイムスタンプを生成し、1 日ずつ増やします。 SELECT sequence('2023-04-02 19:26:12.941000000', '2023-04-06 19:26:12.941000000', 1d) 結果の例: `[ 2023-04-02 19:26:12.941000000,2023-04-03 19:26:12.941000000,2023-04-04 19:26:12.941000000,2023-04-05 19:26:12.941000000,2023-04-06 19:26:12.941000000 ]` |
| sequence(start, stop, step) | array(timestamp) | 開始から停止までの一連のタイムスタンプを生成し、ステップごとに増分します。ステップのデータ型は interval です。 SELECT sequence('2023-04-02 19:26:12.941000000', '2023-04-10 19:26:12.941000000', 2d) 結果の例: `[ 2023-04-02 19:26:12.941000000,2023-04-04 19:26:12.941000000,2023-04-06 19:26:12.941000000,2023-04-08 19:26:12.941000000,2023-04-10 19:26:12.941000000 ]` |
| shuffle(x) | array | 指定された配列 x のランダム順列を生成します。 SELECT shuffle(ARRAY[6,8,2,9,3])
結果の例: `[ 6,3,2,9,8 ]` |
| slice(x, start, length) | array | 長さが長さのインデックス開始 (または開始が負の場合は終了から始まる) 配列 x をサブセットします。 SELECT slice(ARRAY[6,8,2,9,3], 1, 3)
結果の例: `[ 6,8,2 ]` |
| zip(array1, array2[, ...]) | array(row) | 指定された配列を要素ごとに 1 つの行の配列にマージします。引数の長さが不均等である場合、欠損値は NULL で埋められます。 SELECT zip(ARRAY[6,8,2,9,3], ARRAY[15,24])
結果の例: `[ ( 6, 15 ),( 8, 24 ),( 2, - ),( 9, - ),( 3, - ) ]` |
# ビット単位関数
Timestream for LiveAnalytics は、次のビット単位の関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| bit\$1count(bigint, bigint) | bigint (2 の補数) | 最初の bigint パラメータのビット数を返します。2 番目のパラメータは 8 や 64 などのビット符号付き整数です。 SELECT bit_count(19, 8)
結果の例: `3` SELECT bit_count(19, 2)
結果の例: `Number must be representable with the bits specified. 19 can not be represented with 2 bits` |
| bitwise\$1and(bigint, bigint) | bigint (2 の補数) | bigint パラメータのビット単位の AND を返します。 SELECT bitwise_and(12, 7)
結果の例: `4` |
| bitwise\$1not(bigint) | bigint (2 の補数) | bigint パラメータのビット単位の NOT を返します。 SELECT bitwise_not(12)
結果の例: `-13` |
| bitwise\$1or(bigint, bigint) | bigint (2 の補数) | bigint パラメータのビット単位の OR を返します。 SELECT bitwise_or(12, 7)
結果の例: `15` |
| bitwise\$1xor(bigint, bigint) | bigint (2 の補数) | bigint パラメータのビット単位の XOR を返します。 SELECT bitwise_xor(12, 7)
結果の例: `11` |
# 正規表現関数
Timestream for LiveAnalytics の正規表現関数は、[Java パターン構文](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html)をサポートしています。Timestream for LiveAnalytics は、次の正規表現関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| regexp\$1extract\$1all(string, pattern) | array(varchar) | 文字列の正規表現パターンに一致する部分文字列 (複数可) を返します。 SELECT regexp_extract_all('example expect complex', 'ex\w') 結果の例: `[ exa,exp ]` |
| regexp\$1extract\$1all(string, pattern, group) | array(varchar) | 文字列内の正規表現パターンの出現すべてを検索し、[キャプチャグループ番号](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html#gnumber)グループを返します。 SELECT regexp_extract_all('example expect complex', '(ex)(\w)', 2) 結果の例: `[ a,p ]` |
| regexp\$1extract(string, pattern) | varchar | 文字列内の正規表現パターンに一致する最初の部分文字列を返します。 SELECT regexp_extract('example expect', 'ex\w') 結果の例: `exa` |
| regexp\$1extract(string, pattern, group) | varchar | 文字列内における正規表現パターンの最初の出現を検索し、[キャプチャグループ番号](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html#gnumber)グループを返します。 SELECT regexp_extract('example expect', '(ex)(\w)', 2) 結果の例: `a` |
| regexp\$1like(string, pattern) | boolean | 正規表現パターンを評価し、そのパターンが文字列内に含まれているかどうかを判断します。この関数は LIKE 演算子に似ていますが、パターンはすべての文字列と一致するのではなく、文字列内に含める必要がある点が異なります。つまり、match オペレーションではなく、contains オペレーションを実行します。^ と \$1 を使用してパターンを固定することで、文字列全体を一致させることができます。 SELECT regexp_like('example', 'ex') 結果の例: `true` |
| regexp\$1replace(string, pattern) | varchar | 文字列から正規表現パターンに一致する部分文字列のすべてのインスタンスを削除します。 SELECT regexp_replace('example expect', 'expect') 結果の例: `example` |
| regexp\$1replace(string, pattern, replacement) | varchar | 文字列内にある正規表現パターンに一致する部分文字列のすべてのインスタンスを置き換えます。キャプチャグループは、番号付きグループの \$1g または名前付きグループの \$1\$1name\$1 を使用して置換で参照できます。ドル記号 (\$1) は、バックスラッシュでエスケープ (\$1\$1) することで置換に含めることができます。 SELECT regexp_replace('example expect', 'expect', 'surprise') 結果の例: `example surprise` |
| regexp\$1replace(string, pattern, function) | varchar | 関数を使用して、文字列内の正規表現パターンで一致した部分文字列のすべてのインスタンスを置き換えます。[ラムダ式](https://prestodb.io/docs/current/functions/lambda.html)関数は、配列として渡されたキャプチャグループとの一致ごとに呼び出されます。キャプチャグループ番号は 1 から始まります。完全一致のグループはありません (必要な場合は、式全体を括弧で囲みます)。 SELECT regexp_replace('example', '(\w)', x -> upper(x[1])) 結果の例: `EXAMPLE` |
| regexp\$1split(string, pattern) | array(varchar) | 正規表現パターンを使用して文字列を分割し、配列を返します。末尾の空の文字列は保持されます。 SELECT regexp_split('example', 'x') 結果の例: `[ e,ample ]` |
# 日付/時間演算子
**注記**
Timestream for LiveAnalytics は負の時間値をサポートしていません。負の時間になるオペレーションはエラーになります。
Timestream for LiveAnalytics は `timestamps`、`dates`、`intervals` で次のオペレーションをサポートしています。
| 演算子 | 説明 |
| --- | --- |
| \$1 | 加算 |
| - | 減算 |
**Topics**
+ [
## オペレーション
](#date-time-operators-operations)
+ [
## 加算
](#date-time-operators-addition)
+ [
## 減算
](#date-time-operators-subtraction)
## オペレーション
オペレーションの結果タイプは、オペランドに基づいています。`1day` や `3s` などの間隔リテラルを使用できます。
```
SELECT date '2022-05-21' + interval '2' day
```
```
SELECT date '2022-05-21' + 2d
```
```
SELECT date '2022-05-21' + 2day
```
それぞれの結果の例: `2022-05-23`
間隔単位には `second`、`minute`、`hour`、`day`、`week`、`month`、`year` などがあります。ただし、場合によっては、すべてが適用できるとは限りません。例えば、秒数、分数、時間数を日付に追加したり、日付から減算したりすることはできません。
```
SELECT interval '4' year + interval '2' month
```
結果の例: `4-2`
```
SELECT typeof(interval '4' year + interval '2' month)
```
結果の例: `interval year to month`
間隔オペレーションの結果タイプは、オペランドに応じて `'interval year to month'` または `'interval day to second'` になります。間隔は、`dates` と `timestamps` に追加することも、これらから減算することもできます。ただし、`date` または `timestamp` は、`date` または `timestamp` に追加したり、これらから減算したりすることはできません。日付またはタイムスタンプに関連する間隔または期間を確認するには、「`date_diff`」、および「[間隔と期間](date-time-functions.md#date-time-functions-interval-duration)」の関連関数を参照してください。
## 加算
**Example**
```
SELECT date '2022-05-21' + interval '2' day
```
結果の例: `2022-05-23`
**Example**
```
SELECT typeof(date '2022-05-21' + interval '2' day)
```
結果の例: `date`
**Example**
```
SELECT interval '2' year + interval '4' month
```
結果の例: `2-4`
**Example**
```
SELECT typeof(interval '2' year + interval '4' month)
```
結果の例: `interval year to month`
## 減算
**Example**
```
SELECT timestamp '2022-06-17 01:00' - interval '7' hour
```
結果の例: `2022-06-16 18:00:00.000000000`
**Example**
```
SELECT typeof(timestamp '2022-06-17 01:00' - interval '7' hour)
```
結果の例: `timestamp`
**Example**
```
SELECT interval '6' day - interval '4' hour
```
結果の例: `5 20:00:00.000000000`
**Example**
```
SELECT typeof(interval '6' day - interval '4' hour)
```
結果の例: `interval day to second`
# 日付/時間関数
**注記**
Timestream for LiveAnalytics は負の時間値をサポートしていません。負の時間になるオペレーションはエラーになります。
Timestream for LiveAnalytics は、日付と時刻に UTC タイムゾーンを使用します。Timestream は、次の日付と時刻の関数をサポートしています。
**Topics**
+ [
## 一般および変換
](#date-time-functions-general)
+ [
## 間隔と期間
](#date-time-functions-interval-duration)
+ [
## フォーマットと解析
](#date-time-functions-formatting-parsing)
+ [
## 抽出
](#date-time-functions-extraction)
## 一般および変換
Timestream for LiveAnalytics は、日付と時刻に関する次の一般的な関数と変換関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| current\$1date | date | UTC で現在の日付を返します。括弧は使用されません。 SELECT current_date
結果の例: `2022-07-07` これは予約キーワードでもあります。予約キーワードの一覧については、「[予約済みキーワード](ts-limits.md#limits.reserved)」を参照してください。 |
| current\$1time | 時間 | UTC で現在の時刻を返します。括弧は使用されません。 SELECT current_time
結果の例: `17:41:52.827000000` これは予約キーワードでもあります。予約キーワードの一覧については、「[予約済みキーワード](ts-limits.md#limits.reserved)」を参照してください。 |
| current\$1timestamp または now() | timestamp | UTC で現在のタイムスタンプを返します。 SELECT current_timestamp
結果の例: `2022-07-07 17:42:32.939000000` これは予約キーワードでもあります。予約キーワードの一覧については、「[予約済みキーワード](ts-limits.md#limits.reserved)」を参照してください。 |
| current\$1timezone() | varchar 値は「UTC」になります。 | Timestream は、日付と時刻に UTC タイムゾーンを使用します。 SELECT current_timezone()
結果の例: `UTC` |
| date(varchar(x))、date(timestamp) | date | SELECT date(TIMESTAMP '2022-07-07 17:44:43.771000000')
結果の例: `2022-07-07` |
| last\$1day\$1of\$1month(timestamp)、last\$1day\$1of\$1month(date) | date | SELECT last_day_of_month(TIMESTAMP '2022-07-07 17:44:43.771000000')
結果の例: `2022-07-31` |
| from\$1iso8601\$1timestamp(string) | timestamp | ISO 8601 タイムスタンプを内部タイムスタンプ形式に解析します。 SELECT from_iso8601_timestamp('2022-06-17T08:04:05.000000000+05:00') 結果の例: `2022-06-17 03:04:05.000000000` |
| from\$1iso8601\$1date(string) | date | ISO 8601 の日付文字列を、指定された日付の UTC 00:00:00 の内部タイムスタンプ形式に解析します。 SELECT from_iso8601_date('2022-07-17') 結果の例: `2022-07-17` |
| to\$1iso8601(timestamp)、to\$1iso8601(date) | varchar | 入力の ISO 8601 形式の文字列を返します。 SELECT to_iso8601(from_iso8601_date('2022-06-17')) 結果の例: `2022-06-17` |
| from\$1milliseconds(bigint) | timestamp | SELECT from_milliseconds(1)
結果の例: `1970-01-01 00:00:00.001000000` |
| from\$1nanoseconds(bigint) | timestamp | select from_nanoseconds(300000001)
結果の例: `1970-01-01 00:00:00.300000001` |
| from\$1unixtime(double) | timestamp | 指定された unixtime に対応するタイムスタンプを返します。 SELECT from_unixtime(1)
結果の例: `1970-01-01 00:00:01.000000000` |
| localtime | 時間 | UTC で現在の時刻を返します。括弧は使用されません。 SELECT localtime
結果の例: `17:58:22.654000000` これは予約キーワードでもあります。予約キーワードの一覧については、「[予約済みキーワード](ts-limits.md#limits.reserved)」を参照してください。 |
| localtimestamp | timestamp | UTC で現在のタイムスタンプを返します。括弧は使用されません。 SELECT localtimestamp
結果の例: `2022-07-07 17:59:04.368000000` これは予約キーワードでもあります。予約キーワードの一覧については、「[予約済みキーワード](ts-limits.md#limits.reserved)」を参照してください。 |
| to\$1milliseconds(interval day to second)、to\$1milliseconds(timestamp) | bigint | SELECT to_milliseconds(INTERVAL '2' DAY + INTERVAL '3' HOUR)
結果の例: `183600000` SELECT to_milliseconds(TIMESTAMP '2022-06-17 17:44:43.771000000')
結果の例: `1655487883771` |
| to\$1nanoseconds(interval day to second)、to\$1nanoseconds(timestamp) | bigint | SELECT to_nanoseconds(INTERVAL '2' DAY + INTERVAL '3' HOUR)
結果の例: `183600000000000` SELECT to_nanoseconds(TIMESTAMP '2022-06-17 17:44:43.771000678')
結果の例: `1655487883771000678` |
| to\$1unixtime(timestamp) | double | 指定されたタイムスタンプの unixtime を返します。 SELECT to_unixtime('2022-06-17 17:44:43.771000000') 結果の例: `1.6554878837710001E9` |
| date\$1trunc(unit, timestamp) | timestamp | 単位に切り捨てられたタイムスタンプを返します。単位は [秒、分、時間、日、週、月、四半期、年] のいずれかです。 SELECT date_trunc('minute', TIMESTAMP '2022-06-17 17:44:43.771000000') 結果の例: `2022-06-17 17:44:00.000000000` |
## 間隔と期間
Timestream for LiveAnalytics は、日付と時刻に関する次の間隔関数と期間関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| date\$1add(unit, bigint, date)、date\$1add(unit, bigint, time)、date\$1add(varchar(x), bigint, timestamp) | timestamp | 単位の bigint を追加します。単位は [秒、分、時間、日、週、月、四半期、年] のいずれかです。 SELECT date_add('hour', 9, TIMESTAMP '2022-06-17 00:00:00') 結果の例: `2022-06-17 09:00:00.000000000` |
| date\$1diff(unit, date, date)、date\$1diff(unit, time, time)、date\$1diff(unit, timestamp, timestamp) | bigint | 差を返します。単位は [秒、分、時間、日、週、月、四半期、年] のいずれかです。 SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02') 結果の例: `1` |
| parse\$1duration(string) | interval | 入力文字列を解析して `interval` の同値を返します。 SELECT parse_duration('42.8ms') 結果の例: `0 00:00:00.042800000` SELECT typeof(parse_duration('42.8ms')) 結果の例: `interval day to second` |
| bin(timestamp, interval) | timestamp | `timestamp` パラメータの整数値を、`interval` パラメータの整数値の最も近い倍数に切り下げます。 この戻り値の意味は明確でない場合があります。最初に整数演算を使用してタイムスタンプ整数を間隔整数で除算し、次にその結果を間隔整数で乗算して計算します。 タイムスタンプは、POSIX エポック (1970 年 1 月 1 日) から経過した 1 秒の小数点以下の数値として UTC ポイントを指定することに注意してください。戻り値が暦の単位と一致することはほとんどありません。例えば、30 日間の間隔を指定すると、エポックからのすべての日が増分で 30 日単位に分割され、暦月とは関係のない直近の増分の 30 日の開始値が返されます。 次に例を示します。 bin(TIMESTAMP '2022-06-17 10:15:20', 5m) ==> 2022-06-17 10:15:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 1d) ==> 2022-06-17 00:00:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 10day) ==> 2022-06-17 00:00:00.000000000
bin(TIMESTAMP '2022-06-17 10:15:20', 30day) ==> 2022-05-28 00:00:00.000000000
|
| ago(interval) | timestamp | current\$1timestamp `interval` に対応する値を返します。 SELECT ago(1d)
結果の例: `2022-07-06 21:08:53.245000000` |
| 1h、1d、30m などの間隔リテラル | interval | 間隔リテラルは、parse\$1duration(string) の便利な機能です。例えば、`1d` は `parse_duration('1d')` と同じです。これにより、間隔が使用されるすべての場所でリテラルを使用できます。例えば、`ago(1d)` と `bin(, 1m)` です。 |
一部の間隔リテラルは、parse\$1duration の簡易表現として機能します。例えば、`parse_duration('1day')`、`1day`、`parse_duration('1d')`、`1d` はそれぞれ、型が `interval day to second` である `1 00:00:00.000000000` を返します。スペースは、`parse_duration` の指定された形式で許可されます。例えば、`parse_duration('1day')` は `00:00:00.000000000` も返します。ただし、`1 day` は間隔リテラルではありません。
`interval day to second` に関連する単位は、ns、ナノ秒、us、マイクロ秒、ms、ミリ秒、s、秒、m、分、h、時間、d、日です。
`interval year to month` もあります。interval year to month に関連する単位は、y、年、月です。例えば、`SELECT 1year` は `1-0` を返します。`SELECT 12month` も `1-0` を返します。`SELECT 8month` は `0-8` を返します。
`quarter` の単位は、`date_trunc`、`date_add`、`quarter` などの一部の関数でも使用できますが、間隔リテラルの一部としては使用できません。
## フォーマットと解析
Timestream for LiveAnalytics は、日付と時刻に関する次のフォーマット関数と解析関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| date\$1format(timestamp, varchar(x)) | varchar | この関数で使用される書式指定子の詳細については、[https://trino.io/docs/current/functions/datetime.html\$1mysql-date-functions](https://trino.io/docs/current/functions/datetime.html#mysql-date-functions) を参照してください。 SELECT date_format(TIMESTAMP '2019-10-20 10:20:20', '%Y-%m-%d %H:%i:%s')
結果の例: `2019-10-20 10:20:20` |
| date\$1parse(varchar(x), varchar(y)) | timestamp | この関数で使用される書式指定子の詳細については、[https://trino.io/docs/current/functions/datetime.html\$1mysql-date-functions](https://trino.io/docs/current/functions/datetime.html#mysql-date-functions) を参照してください。 SELECT date_parse('2019-10-20 10:20:20', '%Y-%m-%d %H:%i:%s') 結果の例: `2019-10-20 10:20:20.000000000` |
| format\$1datetime(timestamp, varchar(x)) | varchar | この関数で使用される書式文字列の詳細については、[http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html](http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) を参照してください。 SELECT format_datetime(parse_datetime('1968-01-13 12', 'yyyy-MM-dd HH'), 'yyyy-MM-dd HH') 結果の例: `1968-01-13 12` |
| parse\$1datetime(varchar(x), varchar(y)) | timestamp | この関数で使用される書式文字列の詳細については、[http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html](http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) を参照してください。 SELECT parse_datetime('2019-12-29 10:10 PST', 'uuuu-LL-dd HH:mm z') 結果の例: `2019-12-29 18:10:00.000000000` |
## 抽出
Timestream for LiveAnalytics は、日付と時刻に関する次の抽出関数をサポートしています。抽出関数は利便性が高く、残りの関数の基礎となります。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| extract | bigint | タイムスタンプからフィールドを抽出します。ここで、フィールドは [YEAR、QUARTER、MONTH、WEEK、DAY、DAY\$1OF\$1MONTH、DAY\$1OF\$1WEEK、DOW、DAY\$1OF\$1YEAR、DOY、YEAR\$1OF\$1WEEK、YOW、HOUR、MINUTE、SECOND] のいずれかです。 SELECT extract(YEAR FROM '2019-10-12 23:10:34.000000000')
結果の例: `2019` |
| day(timestamp)、day(date)、day(interval day to second) | bigint | SELECT day('2019-10-12 23:10:34.000000000') 結果の例: `12` |
| day\$1of\$1month(timestamp)、day\$1of\$1month(date)、day\$1of\$1month(interval day to second) | bigint | SELECT day_of_month('2019-10-12 23:10:34.000000000') 結果の例: `12` |
| day\$1of\$1week(timestamp)、day\$1of\$1week(date) | bigint | SELECT day_of_week('2019-10-12 23:10:34.000000000') 結果の例: `6` |
| day\$1of\$1year(timestamp)、day\$1of\$1year(date) | bigint | SELECT day_of_year('2019-10-12 23:10:34.000000000') 結果の例: `285` |
| dow(timestamp)、dow(date) | bigint | day\$1of\$1week のエイリアス |
| doy(timestamp)、doy(date) | bigint | day\$1of\$1year のエイリアス |
| hour(timestamp)、hour(time)、hour(interval day to second) | bigint | SELECT hour('2019-10-12 23:10:34.000000000') 結果の例: `23` |
| millisecond(timestamp)、millisecond(time)、millisecond(interval day to second) | bigint | SELECT millisecond('2019-10-12 23:10:34.000000000') 結果の例: `0` |
| minute(timestamp)、minute(time)、minute(interval day to second) | bigint | SELECT minute('2019-10-12 23:10:34.000000000') 結果の例: `10` |
| month(timestamp)、month(date)、month(interval year to month) | bigint | SELECT month('2019-10-12 23:10:34.000000000') 結果の例: `10` |
| nanosecond(timestamp)、nanosecond(time)、nanosecond(interval day to second) | bigint | SELECT nanosecond(current_timestamp)
結果の例: `162000000` |
| quarter(timestamp)、quarter(date) | bigint | SELECT quarter('2019-10-12 23:10:34.000000000') 結果の例: `4` |
| second(timestamp)、second(time)、second(interval day to second) | bigint | SELECT second('2019-10-12 23:10:34.000000000') 結果の例: `34` |
| week(timestamp)、week(date) | bigint | SELECT week('2019-10-12 23:10:34.000000000') 結果の例: `41` |
| week\$1of\$1year(timestamp)、week\$1of\$1year(date) | bigint | 週のエイリアス |
| year(timestamp)、year(date)、year(interval year to month) | bigint | SELECT year('2019-10-12 23:10:34.000000000') 結果の例: `2019` |
| year\$1of\$1week(timestamp)、year\$1of\$1week(date) | bigint | SELECT year_of_week('2019-10-12 23:10:34.000000000') 結果の例: `2019` |
| yow(timestamp)、yow(date) | bigint | year\$1of\$1week のエイリアス |
# 集計関数
Timestream for LiveAnalytics は、次の集計関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| arbitrary(x) | [入力と同じ] | 存在する場合は、x の任意の非 NULL 値を返します。 SELECT arbitrary(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `1` |
| array\$1agg(x) | array<[same as input] | 入力 x の要素から作成された配列を返します。 SELECT array_agg(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `[ 1,2,3,4 ]` |
| avg(x) | double | すべての入力値の平均 (算術平均) を返します。 SELECT avg(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `2.5` |
| bool\$1and(boolean) every(boolean) | boolean | すべての入力値が TRUE の場合は TRUE を返し、それ以外の場合は FALSE を返します。 SELECT bool_and(t.c) FROM (VALUES true, true, false, true) AS t(c)
結果の例: `false` |
| bool\$1or(boolean) | boolean | 入力値が TRUE の場合は TRUE を返し、それ以外の場合は FALSE を返します。 SELECT bool_or(t.c) FROM (VALUES true, true, false, true) AS t(c)
結果の例: `true` |
| count(\$1) count(x) | bigint | count(\$1) は入力行数を返します。 count(x) は NULL 以外の入力値の数を返します。 SELECT count(t.c) FROM (VALUES true, true, false, true) AS t(c)
結果の例: `4` |
| count\$1if(x) | bigint | TRUE の入力値の数を返します。 SELECT count_if(t.c) FROM (VALUES true, true, false, true) AS t(c)
結果の例: `3` |
| geometric\$1mean(x) | double | すべての入力値の幾何平均を返します。 SELECT geometric_mean(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `2.213363839400643` |
| max\$1by(x, y) | [x と同じ] | すべての入力値に対して y が最大値の場合の関連付けられた x の値を返します。 SELECT max_by(t.c1, t.c2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) 結果の例: `d` |
| max\$1by(x, y, n) | array<[x と同じ]> | y の全入力値のうち n 個の最大値に関連付けられた x の n 個の値を y の降順で返します。 SELECT max_by(t.c1, t.c2, 2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) 結果の例: `[ d,c ]` |
| min\$1by(x, y) | [x と同じ] | すべての入力値に対して y が最小値の場合の関連付けられた x の値を返します。 SELECT min_by(t.c1, t.c2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) 結果の例: `a` |
| min\$1by(x, y, n) | array<[x と同じ]> | y の全入力値のうち n 個の最小値に関連付けられた x の n 個の値を y の昇順で返します。 SELECT min_by(t.c1, t.c2, 2) FROM (VALUES (('a', 1)), (('b', 2)), (('c', 3)), (('d', 4))) AS t(c1, c2) 結果の例: `[ a,b ]` |
| max(x) | [入力と同じ] | すべての入力値の最大値を返します。 SELECT max(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `4` |
| max(x, n) | array<[x と同じ]> | x の全入力値の n 個の最大値を返します。 SELECT max(t.c, 2) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `[ 4,3 ]` |
| min(x) | [入力と同じ] | すべての入力値の最小値を返します。 SELECT min(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `1` |
| min(x, n) | array<[x と同じ]> | x の全入力値の n 個の最小値を返します。 SELECT min(t.c, 2) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `[ 1,2 ]` |
| sum(x) | [入力と同じ] | 列の全入力値の合計を返します。 SELECT sum(t.c) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `10` |
| bitwise\$1and\$1agg(x) | bigint | 全入力値のビット単位の AND を 2 の補数表現で返します。 SELECT bitwise_and_agg(t.c) FROM (VALUES 1, -3) AS t(c)
結果の例: `1` |
| bitwise\$1or\$1agg(x) | bigint | 全入力値のビット単位の OR を 2 の補数表現で返します。 SELECT bitwise_or_agg(t.c) FROM (VALUES 1, -3) AS t(c)
結果の例: `-3` |
| approx\$1distinct(x) | bigint | 固有の入力値の概数を返します。この関数は count(DISTINCT x) の近似値を提供します。すべての入力値が null の場合、ゼロが返されます。この関数では、2.3% の標準誤差が発生するはずです。これは、すべての可能なセットに対する (ほぼ通常の) エラー分布の標準偏差です。特定の入力セットにおけるエラーの上限は保証されません。 SELECT approx_distinct(t.c) FROM (VALUES 1, 2, 3, 4, 8) AS t(c)
結果の例: `5` |
| approx\$1distinct(x, e) | bigint | 固有の入力値の概数を返します。この関数は count(DISTINCT x) の近似値を提供します。すべての入力値が null の場合、ゼロが返されます。この関数では、e 以下の標準誤差が発生するはずです。これは、すべての可能なセットに対する (ほぼ通常の) エラー分布の標準偏差です。特定の入力セットにおけるエラーの上限は保証されません。この関数の現在の実装では、e が [0.0040625, 0.26000] の範囲内にある必要があります。 SELECT approx_distinct(t.c, 0.2) FROM (VALUES 1, 2, 3, 4, 8) AS t(c)
結果の例: `5` |
| approx\$1percentile(x, percentage) | [x と同じ] | 指定された割合で x の全入力値の近似パーセンタイルを返します。割合の値は 0 と 1 の間で、すべての入力行で一定である必要があります。 SELECT approx_percentile(t.c, 0.4) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `2` |
| approx\$1percentile(x, percentages) | array<[x と同じ]> | 指定された各割合で x の全入力値の近似パーセンタイルを返します。割合の配列の各要素は 0 と 1 の間で、配列はすべての入力行で一定である必要があります。 SELECT approx_percentile(t.c, ARRAY[0.1, 0.8, 0.8]) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `[ 1,4,4 ]` |
| approx\$1percentile(x, w, percentage) | [x と同じ] | 割合 p で項目ごとの重み w を使用して、x のすべての入力値に対して重み付けした近似パーセンタイルを返します。重みは少なくとも 1 の整数値である必要があります。これは実質的に、パーセンタイルセットの値 x のレプリケーション数です。p の値は 0 と 1 の間で、すべての入力行で一定である必要があります。 SELECT approx_percentile(t.c, 1, 0.1) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `1` |
| approx\$1percentile(x, w, percentages) | array<[x と同じ]> | 配列で指定された各割合で項目ごとの重み w を使用して、x のすべての入力値に対して重み付けした近似パーセンタイルを返します。重みは少なくとも 1 の整数値である必要があります。これは実質的に、パーセンタイルセットの値 x のレプリケーション数です。配列の各要素は 0 と 1 の間で、配列はすべての入力行で一定である必要があります。 SELECT approx_percentile(t.c, 1, ARRAY[0.1, 0.8, 0.8]) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `[ 1,4,4 ]` |
| approx\$1percentile(x, w, percentage, accuracy) | [x と同じ] | 割合 p で項目ごとの重み w を使用し、最大ランク誤差を精度として、x のすべての入力値に対して重み付けした近似パーセンタイルを返します。重みは少なくとも 1 の整数値である必要があります。これは実質的に、パーセンタイルセットの値 x のレプリケーション数です。p の値は 0 と 1 の間で、すべての入力行で一定である必要があります。精度は 0 より大きく 1 より小さい値で、すべての入力行で一定である必要があります。 SELECT approx_percentile(t.c, 1, 0.1, 0.5) FROM (VALUES 1, 2, 3, 4) AS t(c)
結果の例: `1` |
| corr(y, x) | double | 入力値の相関係数を返します。 SELECT corr(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
結果の例: `1.0` |
| covar\$1pop(y, x) | double | 入力値の母共分散を返します。 SELECT covar_pop(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
結果の例: `1.25` |
| covar\$1samp(y, x) | double | 入力値の標本共分散を返します。 SELECT covar_samp(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
結果の例: `1.6666666666666667` |
| regr\$1intercept(y, x) | double | 入力値の線形回帰切片を返します。y は依存値で、x は独立した値です。 SELECT regr_intercept(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
結果の例: `0.0` |
| regr\$1slope(y, x) | double | 入力値の線形回帰スロープを返します。y は依存値で、x は独立した値です。 SELECT regr_slope(t.c1, t.c2) FROM (VALUES ((1, 1)), ((2, 2)), ((3, 3)), ((4, 4))) AS t(c1, c2)
結果の例: `1.0` |
| skewness(x) | double | すべての入力値の歪度を返します。 SELECT skewness(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
結果の例: `0.8978957037987335` |
| stddev\$1pop(x) | double | すべての入力値の母集団標準偏差を返します。 SELECT stddev_pop(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
結果の例: `2.4166091947189146` |
| stddev\$1samp(x) stddev(x) | double | すべての入力値の標本標準偏差を返します。 SELECT stddev_samp(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
結果の例: `2.701851217221259` |
| var\$1pop(x) | double | すべての入力値の母分散を返します。 SELECT var_pop(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
結果の例: `5.840000000000001` |
| var\$1samp(x) variance(x) | double | すべての入力値の標本分散を返します。 SELECT var_samp(t.c1) FROM (VALUES 1, 2, 3, 4, 8) AS t(c1)
結果の例: `7.300000000000001` |
# ウィンドウ関数
ウィンドウ関数は、クエリ結果の行全体で計算を実行します。HAVING 句の後、ORDER BY 句の前に実行されます。ウィンドウ関数を呼び出すには、OVER 句を使用してウィンドウを指定する特別な構文が必要です。ウィンドウには次の 3 つのコンポーネントがあります。
+ 入力行を異なるパーティションに分割するパーティション指定。これは、GROUP BY 句が集計関数のために行を異なるグループに分割する方法に似ています。
+ ウィンドウ関数によって入力行が処理される順序を決定する順序指定。
+ ウィンドウフレーム。特定の行の関数によって処理される行のスライディングウィンドウを指定します。フレームが指定されていない場合、デフォルトで RANGE UNBOUNDED PRECEDING になります。これは、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と同じです。このフレームには、パーティションの先頭から現在行の最後のピアまでのすべての行が含まれます。
すべての集計関数は、OVER 句を追加することでウィンドウ関数として使用できます。集計関数は、現在行のウィンドウフレーム内の行にわたって各行に対して計算されます。Timestream for LiveAnalytics は、集計関数に加えて、次のランク付け関数と値関数をサポートしています。
| 関数 | 出力データ型 | 説明 |
| --- | --- | --- |
| cume\$1dist() | bigint | 値グループ内の値の累積分布を返します。結果は、ウィンドウパーティションのウィンドウの順序において、対象となる行の前または同列の行数を、ウィンドウパーティションの行の合計数で割ったものです。したがって、順序のタイ値は同じ分散値に評価されます。 |
| dense\$1rank() | bigint | 値グループ内の値のランクを返します。これは rank() に似ていますが、タイ値がシーケンスにギャップを生成しない点が異なります。 |
| ntile(n) | bigint | 各ウィンドウパーティションの行を 1 から最大 n の範囲の n 個のバケットに分割します。バケット値は最大 1 だけ異なります。パーティション内の行数がバケット数に均等に分割されない場合、残りの値は最初のバケットから始めてバケットごとに 1 つずつ分散されます。 |
| percent\$1rank() | double | 値グループ内の値のパーセンテージ順位を返します。結果は (r - 1) / (n - 1) です。ここで、r は行の rank() で、n はウィンドウパーティションの行の合計数です。 |
| rank() | bigint | 値グループ内の値のランクを返します。ランクは、対象となる行の前にある行のうち、その行と同列でない行の数に 1 を加えたものです。このため、順序のタイ値により、シーケンスにギャップが発生します。ランク付けはウィンドウパーティションごとに実行されます。 |
| row\$1number() | bigint | ウィンドウパーティション内の行の順序に従って、行ごとに 1 から始まる一意の連番を返します。 |
| first\$1value(x) | [入力と同じ] | ウィンドウの最初の値を返します。この関数はウィンドウフレームに限定されます。関数は、式またはターゲットをパラメータとして受け取ります。 |
| last\$1value(x) | [入力と同じ] | ウィンドウの最後の値を返します。この関数はウィンドウフレームに限定されます。関数は、式またはターゲットをパラメータとして受け取ります。 |
| nth\$1value(x, offset) | [入力と同じ] | ウィンドウの開始から指定されたオフセットの値を返します。オフセットは 1 から始まります。オフセットは任意のスカラー式にすることができます。オフセットが null であるか、ウィンドウの値の数より大きい場合、null が返されます。オフセットがゼロまたは負の場合は、エラーとなります。関数は、式またはターゲットを最初のパラメータとして受け取ります。 |
| lead(x[, offset[, default\$1value]]) | [入力と同じ] | ウィンドウの現在行の後におけるオフセット行の値を返します。オフセットは、現在の行である 0 から始まります。オフセットは任意のスカラー式にすることができます。デフォルトのオフセットは 1 です。オフセットが null またはウィンドウより大きい場合、default\$1value が返されるか、指定されていない場合は null が返されます。関数は、式またはターゲットを最初のパラメータとして受け取ります。 |
| lag(x[, offset[, default\$1value]]) | [入力と同じ] | ウィンドウの現在の行より前のオフセット行の値を返します。オフセットは現在の行である 0 から始まります。オフセットは任意のスカラー式にすることができます。デフォルトのオフセットは 1 です。オフセットが null またはウィンドウより大きい場合、default\$1value が返されるか、指定されていない場合は null が返されます。関数は、式またはターゲットを最初のパラメータとして受け取ります。 |
# サンプルクエリ
このセクションでは、Timestream for LiveAnalytics クエリ言語のユースケースの例を示します。
**Topics**
+ [
# シンプルなクエリ
](sample-queries.basic-scenarios.md)
+ [
# 時系列関数を使用したクエリ
](sample-queries.devops-scenarios.md)
+ [
# 集計関数を使用したクエリ
](sample-queries.iot-scenarios.md)
# シンプルなクエリ
以下は、テーブルに最近追加された 10 個のデータポイントを取得します。
```
SELECT * FROM .
ORDER BY time DESC
LIMIT 10
```
以下は、特定のメジャーの最も古い 5 つのデータポイントを取得します。
```
SELECT * FROM .
WHERE measure_name = ''
ORDER BY time ASC
LIMIT 5
```
以下は、ナノ秒の粒度のタイムスタンプで機能します。
```
SELECT now() AS time_now
, now() - (INTERVAL '12' HOUR) AS twelve_hour_earlier -- Compatibility with ANSI SQL
, now() - 12h AS also_twelve_hour_earlier -- Convenient time interval literals
, ago(12h) AS twelve_hours_ago -- More convenience with time functionality
, bin(now(), 10m) AS time_binned -- Convenient time binning support
, ago(50ns) AS fifty_ns_ago -- Nanosecond support
, now() + (1h + 50ns) AS hour_fifty_ns_future
```
マルチメジャーレコードのメジャー値は、列名によって識別されます。単一メジャーレコードのメジャー値は `measure_value::` によって識別されます。ここで、`` は、「[サポートされているデータ型](supported-data-types.md)」で説明されているように `double`、`bigint`、`boolean`、`varchar` のいずれかです。メジャー値のモデル化方法の詳細については、「[単一テーブルとマルチテーブル](https://docs.aws.amazon.com/timestream/latest/developerguide/data-modeling.html#data-modeling-multiVsinglerecords)」を参照してください。
以下は、`measure_name` が `IoTMulti-stats` のマルチメジャーレコードから `speed` というメジャーの値を取得します。
```
SELECT speed FROM . where measure_name = 'IoTMulti-stats'
```
以下は、`measure_name` が `load` の単一メジャーレコードから `double` 値を取得します。
```
SELECT measure_value::double FROM . WHERE measure_name = 'load'
```
# 時系列関数を使用したクエリ
**Topics**
+ [
## データセットとクエリの例
](#sample-queries.devops-scenarios.example)
## データセットとクエリの例
Timestream for LiveAnalytics は、サービスとアプリケーションのパフォーマンスと可用性を理解し改善するために使用できます。以下は、テーブルの例と、そのテーブルで実行されるサンプルクエリです。
このテーブル `ec2_metrics` には、CPU 使用率といった EC2 インスタンスのメトリクスなどのテレメトリデータが保存されます。以下の表を表示できます。
| Time | リージョン | az | Hostname | measure\$1name | measure\$1value::double | measure\$1value::bigint |
| --- | --- | --- | --- | --- | --- | --- |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | cpu\$1utilization | 35.1 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | memory\$1utilization | 55.3 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1in | null | 1,500 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1a | frontend01 | network\$1bytes\$1out | null | 6,700 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | cpu\$1utilization | 38.5 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | memory\$1utilization | 58.4 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1in | null | 23,000 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1b | frontend02 | network\$1bytes\$1out | null | 12,000 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | cpu\$1utilization | 45.0 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | memory\$1utilization | 65.8 | null |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1in | null | 15,000 |
| 2019-12-04 19:00:00.000000000 | us-east-1 | us-east-1c | frontend03 | network\$1bytes\$1out | null | 836,000 |
| 2019-12-04 19:00:05.000000000 | us–east–1 | us-east-1a | frontend01 | cpu\$1utilization | 55.2 | null |
| 2019-12-04 19:00:05.000000000 | us–east–1 | us-east-1a | frontend01 | memory\$1utilization | 75.0 | null |
| 2019-12-04 19:00:05.000000000 | us–east–1 | us-east-1a | frontend01 | network\$1bytes\$1in | null | 1,245 |
| 2019-12-04 19:00:05.000000000 | us–east–1 | us-east-1a | frontend01 | network\$1bytes\$1out | null | 68,432 |
| 2019-12-04 19:00:08.000000000 | us–east–1 | us-east-1b | frontend02 | cpu\$1utilization | 65.6 | null |
| 2019-12-04 19:00:08.000000000 | us–east–1 | us-east-1b | frontend02 | memory\$1utilization | 85.3 | null |
| 2019-12-04 19:00:08.000000000 | us–east–1 | us-east-1b | frontend02 | network\$1bytes\$1in | null | 1,245 |
| 2019-12-04 19:00:08.000000000 | us–east–1 | us-east-1b | frontend02 | network\$1bytes\$1out | null | 68,432 |
| 2019-12-04 19:00:20.000000000 | us–east–1 | us-east-1c | frontend03 | cpu\$1utilization | 12.1 | null |
| 2019-12-04 19:00:20.000000000 | us–east–1 | us-east-1c | frontend03 | memory\$1utilization | 32.0 | null |
| 2019-12-04 19:00:20.000000000 | us–east–1 | us-east-1c | frontend03 | network\$1bytes\$1in | null | 1,400 |
| 2019-12-04 19:00:20.000000000 | us–east–1 | us-east-1c | frontend03 | network\$1bytes\$1out | null | 345 |
| 2019-12-04 19:00:10.000000000 | us–east–1 | us-east-1a | frontend01 | cpu\$1utilization | 15.3 | null |
| 2019-12-04 19:00:10.000000000 | us–east–1 | us-east-1a | frontend01 | memory\$1utilization | 35.4 | null |
| 2019-12-04 19:00:10.000000000 | us–east–1 | us-east-1a | frontend01 | network\$1bytes\$1in | null | 23 |
| 2019-12-04 19:00:10.000000000 | us–east–1 | us-east-1a | frontend01 | network\$1bytes\$1out | null | 0 |
| 2019-12-04 19:00:16.000000000 | us–east–1 | us-east-1b | frontend02 | cpu\$1utilization | 44.0 | null |
| 2019-12-04 19:00:16.000000000 | us–east–1 | us-east-1b | frontend02 | memory\$1utilization | 64.2 | null |
| 2019-12-04 19:00:16.000000000 | us–east–1 | us-east-1b | frontend02 | network\$1bytes\$1in | null | 1,450 |
| 2019-12-04 19:00:16.000000000 | us–east–1 | us-east-1b | frontend02 | network\$1bytes\$1out | null | 200 |
| 2019-12-04 19:00:40.000000000 | us–east–1 | us-east-1c | frontend03 | cpu\$1utilization | 66.4 | null |
| 2019-12-04 19:00:40.000000000 | us–east–1 | us-east-1c | frontend03 | memory\$1utilization | 86.3 | null |
| 2019-12-04 19:00:40.000000000 | us–east–1 | us-east-1c | frontend03 | network\$1bytes\$1in | null | 300 |
| 2019-12-04 19:00:40.000000000 | us–east–1 | us-east-1c | frontend03 | network\$1bytes\$1out | null | 423 |
過去 2 時間における特定の EC2 ホストの p90、p95、p99 の平均 CPU 使用率を調べます。
```
SELECT region, az, hostname, BIN(time, 15s) AS binned_timestamp,
ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization,
ROUND(APPROX_PERCENTILE(measure_value::double, 0.9), 2) AS p90_cpu_utilization,
ROUND(APPROX_PERCENTILE(measure_value::double, 0.95), 2) AS p95_cpu_utilization,
ROUND(APPROX_PERCENTILE(measure_value::double, 0.99), 2) AS p99_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY region, hostname, az, BIN(time, 15s)
ORDER BY binned_timestamp ASC
```
過去 2 時間のフリート全体の平均 CPU 使用率と比較して、CPU 使用率が 10% 以上高い EC2 ホストを特定します。
```
WITH avg_fleet_utilization AS (
SELECT COUNT(DISTINCT hostname) AS total_host_count, AVG(measure_value::double) AS fleet_avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND time > ago(2h)
), avg_per_host_cpu AS (
SELECT region, az, hostname, AVG(measure_value::double) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND time > ago(2h)
GROUP BY region, az, hostname
)
SELECT region, az, hostname, avg_cpu_utilization, fleet_avg_cpu_utilization
FROM avg_fleet_utilization, avg_per_host_cpu
WHERE avg_cpu_utilization > 1.1 * fleet_avg_cpu_utilization
ORDER BY avg_cpu_utilization DESC
```
過去 2 時間における特定の EC2 ホストの 30 秒間隔でビニングされた平均 CPU 使用率を調べます。
```
SELECT BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
ORDER BY binned_timestamp ASC
```
過去 2 時間における特定の EC2 ホストの 30 秒間隔でビニングされた平均 CPU 使用率を調べ、線形補間を使用して欠落している値を入力します。
```
WITH binned_timeseries AS (
SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
SELECT hostname,
INTERPOLATE_LINEAR(
CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
FROM binned_timeseries
GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```
過去 2 時間における特定の EC2 ホストの 30 秒間隔でビニングされた平均 CPU 使用率を調べ、locf に基づく補間を使用して欠落値を入力します。
```
WITH binned_timeseries AS (
SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
AND hostname = 'host-Hovjv'
AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
SELECT hostname,
INTERPOLATE_LOCF(
CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
FROM binned_timeseries
GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```
# 集計関数を使用したクエリ
以下は、集計関数を使用したクエリを示すための IoT シナリオデータセットの例です。
**Topics**
+ [
## サンプルデータ
](#sample-queries.iot-scenarios.example-data)
+ [
## クエリの例
](#sample-queries.iot-scenarios.example-queries)
## サンプルデータ
Timestream を使用すると、1 つ以上のトラックフリートの場所、燃料消費量、速度、積載量などの IoT センサーデータを保存および分析して、効果的なフリート管理が可能になります。以下は、トラックの場所、燃料消費量、速度、積載量などのテレメトリを保存するテーブル iot\$1trucks のスキーマとデータの一部です。
| Time | truck\$1id | Make | モデル | Fleet | fuel\$1capacity | load\$1capacity | measure\$1name | measure\$1value::double | measure\$1value::varchar |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | fuel\$1reading | 65.2 | null |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | load | 400.0 | null |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | speed | 90.2 | null |
| 2019-12-04 19:00:00.000000000 | 123456781 | GMC | Astro | Alpha | 100 | 500 | location | null | 47.6062 N, 122.3321 W |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1,000 | fuel\$1reading | 10.1 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1,000 | load | 950.3 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1,000 | speed | 50.8 | null |
| 2019-12-04 19:00:00.000000000 | 123456782 | Kenworth | W900 | Alpha | 150 | 1,000 | location | null | 40.7128 degrees N, 74.0060 degrees W |
## クエリの例
フリート内の各トラックでモニタリングされているすべてのセンサー属性と値のリストを取得します。
```
SELECT
truck_id,
fleet,
fuel_capacity,
model,
load_capacity,
make,
measure_name
FROM "sampleDB".IoT
GROUP BY truck_id, fleet, fuel_capacity, model, load_capacity, make, measure_name
```
過去 24 時間におけるフリート内トラックそれぞれの直近の燃料測定値を取得します。
```
WITH latest_recorded_time AS (
SELECT
truck_id,
max(time) as latest_time
FROM "sampleDB".IoT
WHERE measure_name = 'fuel-reading'
AND time >= ago(24h)
GROUP BY truck_id
)
SELECT
b.truck_id,
b.fleet,
b.make,
b.model,
b.time,
b.measure_value::double as last_reported_fuel_reading
FROM
latest_recorded_time a INNER JOIN "sampleDB".IoT b
ON a.truck_id = b.truck_id AND b.time = a.latest_time
WHERE b.measure_name = 'fuel-reading'
AND b.time > ago(24h)
ORDER BY b.truck_id
```
過去 48 時間に低燃料 (10% 未満) で走行しているトラックを特定します。
```
WITH low_fuel_trucks AS (
SELECT time, truck_id, fleet, make, model, (measure_value::double/cast(fuel_capacity as double)*100) AS fuel_pct
FROM "sampleDB".IoT
WHERE time >= ago(48h)
AND (measure_value::double/cast(fuel_capacity as double)*100) < 10
AND measure_name = 'fuel-reading'
),
other_trucks AS (
SELECT time, truck_id, (measure_value::double/cast(fuel_capacity as double)*100) as remaining_fuel
FROM "sampleDB".IoT
WHERE time >= ago(48h)
AND truck_id IN (SELECT truck_id FROM low_fuel_trucks)
AND (measure_value::double/cast(fuel_capacity as double)*100) >= 10
AND measure_name = 'fuel-reading'
),
trucks_that_refuelled AS (
SELECT a.truck_id
FROM low_fuel_trucks a JOIN other_trucks b
ON a.truck_id = b.truck_id AND b.time >= a.time
)
SELECT DISTINCT truck_id, fleet, make, model, fuel_pct
FROM low_fuel_trucks
WHERE truck_id NOT IN (
SELECT truck_id FROM trucks_that_refuelled
)
```
過去 1 週間の各トラックの平均負荷と最大速度を調べます。
```
SELECT
bin(time, 1d) as binned_time,
fleet,
truck_id,
make,
model,
AVG(
CASE WHEN measure_name = 'load' THEN measure_value::double ELSE NULL END
) AS avg_load_tons,
MAX(
CASE WHEN measure_name = 'speed' THEN measure_value::double ELSE NULL END
) AS max_speed_mph
FROM "sampleDB".IoT
WHERE time >= ago(7d)
AND measure_name IN ('load', 'speed')
GROUP BY fleet, truck_id, make, model, bin(time, 1d)
ORDER BY truck_id
```
過去 1 週間の各トラックの負荷効率を取得します。
```
WITH average_load_per_truck AS (
SELECT
truck_id,
avg(measure_value::double) AS avg_load
FROM "sampleDB".IoT
WHERE measure_name = 'load'
AND time >= ago(7d)
GROUP BY truck_id, fleet, load_capacity, make, model
),
truck_load_efficiency AS (
SELECT
a.truck_id,
fleet,
load_capacity,
make,
model,
avg_load,
measure_value::double,
time,
(measure_value::double*100)/avg_load as load_efficiency -- , approx_percentile(avg_load_pct, DOUBLE '0.9')
FROM "sampleDB".IoT a JOIN average_load_per_truck b
ON a.truck_id = b.truck_id
WHERE a.measure_name = 'load'
)
SELECT
truck_id,
time,
load_efficiency
FROM truck_load_efficiency
ORDER BY truck_id, time
```