# SQL 関数 **重要** 新規のお客様への Amazon S3 Select の提供は終了しました。Amazon S3 Select をご利用の既存のお客様は、今後も通常どおり使用できます。[詳細情報](https://aws.amazon.com/blogs/storage/how-to-optimize-querying-your-data-in-amazon-s3/) Amazon S3 Select は、以下の SQL 関数をサポートしています。 **Topics** + [集計関数](s3-select-sql-reference-aggregate.md) + [条件関数](s3-select-sql-reference-conditional.md) + [変換関数](s3-select-sql-reference-conversion.md) + [日付関数](s3-select-sql-reference-date.md) + [文字列関数](s3-select-sql-reference-string.md) # 集計関数 **重要** 新規のお客様への Amazon S3 Select の提供は終了しました。Amazon S3 Select をご利用の既存のお客様は、今後も通常どおり使用できます。[詳細情報](https://aws.amazon.com/blogs/storage/how-to-optimize-querying-your-data-in-amazon-s3/) Amazon S3 Select では、以下の集計関数がサポートされています。 | 関数 | 引数の型 | 戻り型 | | --- | --- | --- | | `AVG(expression)` | `INT`, `FLOAT`, `DECIMAL` | `INT` 引数の場合は `DECIMAL`、浮動小数点の引数の場合は `FLOAT`。それ以外の場合は、引数のデータ型と同じです。 | | `COUNT` | `-` | `INT` | | `MAX(expression)` | `INT`, `DECIMAL` | 引数の型と同じです。 | | `MIN(expression)` | `INT`, `DECIMAL` | 引数の型と同じです。 | | `SUM(expression)` | `INT`, `FLOAT`, `DOUBLE`, `DECIMAL` | `INT` 引数の場合は `INT`、浮動小数点の引数の場合は `FLOAT`。それ以外の場合は、引数のデータ型と同じです。 | ## SUM の例 [S3 インベントリレポート](https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage-inventory.html)内のフォルダの合計オブジェクトサイズを集計するには、`SUM` 式を使用します。次の S3 インベントリレポートは GZIP で圧縮された CSV ファイルです。3 つの列があります。 + 最初の列は、S3 インベントリレポートの対象となる S3 バケットの名前 (*`DOC-EXAMPLE-BUCKET`*) です。 + 2 番目の列は、バケット内のオブジェクトを一意に識別するオブジェクトキー名です。最初の行の `example-folder/` 値は、フォルダ `example-folder` 用です。Amazon S3 で、バケットにフォルダを作成すると、S3 は、指定したフォルダ名に設定されたキーを持つ 0 バイトのオブジェクトを作成します。 2 行目の `example-folder/object1` 値は、`example-folder` フォルダ内のオブジェクト `object1` 用です。 3 行目の `example-folder/object2` 値は、`example-folder` フォルダ内のオブジェクト `object2` 用です。 S3 フォルダの詳細については、「[フォルダを使用して Amazon S3 コンソールのオブジェクトを整理する](using-folders.md)」を参照してください。 + 3 番目の列は、バイト単位のオブジェクトサイズです。 ``` "DOC-EXAMPLE-BUCKET","example-folder/","0" "DOC-EXAMPLE-BUCKET","example-folder/object1","2011267" "DOC-EXAMPLE-BUCKET","example-folder/object2","1570024" ``` `SUM` 式を使用してフォルダ `example-folder` の合計サイズを計算するには、Amazon S3 Select で SQL クエリを実行します。 ``` SELECT SUM(CAST(_3 as INT)) FROM s3object s WHERE _2 LIKE 'example-folder/%' AND _2 != 'example-folder/'; ``` クエリ結果: ``` 3581291 ``` # 条件関数 **重要** 新規のお客様への Amazon S3 Select の提供は終了しました。Amazon S3 Select をご利用の既存のお客様は、今後も通常どおり使用できます。[詳細情報](https://aws.amazon.com/blogs/storage/how-to-optimize-querying-your-data-in-amazon-s3/) Amazon S3 Select は、以下の条件関数をサポートしています。 **Topics** + [CASE](#s3-select-sql-reference-case) + [COALESCE](#s3-select-sql-reference-coalesce) + [NULLIF](#s3-select-sql-reference-nullif) ## CASE `CASE` 式は条件関数であり、他の言語で使われている `if/then/else` ステートメントと似ています。`CASE` は、複数の条件がある場合に、結果を指定するために使用します。`CASE` 式には、簡易と検索の 2 種類があります。簡易 `CASE` 式では、式は値と比較されます。一致が検出された場合、`THEN` 句で指定されたアクションが適用されます。一致が検出されない場合、`ELSE` 句のアクションが適用されます。検索 `CASE` 式では、各 `CASE` がブール式に基づいて評価され、`CASE` ステートメントが最初の一致する `CASE` を返します。一致する `CASE` が `WHEN` 句で検出されない場合、`ELSE` 句のアクションが返されます。 ### 構文 **注記** 現時点で、Amazon S3 Select は `ORDER BY` または新しい行を含むクエリサポートしていません。必ず、改行のないクエリを使用してください。条件の一致に使用する簡易 `CASE` ステートメントを以下に示します。 ``` CASE expression WHEN value THEN result [WHEN...] [ELSE result] END ``` 各条件の評価に使用する検索 `CASE` ステートメントを以下に示します。 ``` CASE WHEN boolean condition THEN result [WHEN ...] [ELSE result] END ``` ### 例 **注記** Amazon S3 コンソールを使用して以下の例を実行し、CSV ファイルにヘッダー行が含まれている場合は、**[CSV データの最初の行を除外する]** を選択します。 **例 1:** 簡易 `CASE` 式を使用して、クエリの `New York City` を `Big Apple` に置き換えます。その他すべての都市名を `other` に置換します。 ``` SELECT venuecity, CASE venuecity WHEN 'New York City' THEN 'Big Apple' ELSE 'other' END FROM S3Object; ``` クエリ結果: ``` venuecity | case -----------------+----------- Los Angeles | other New York City | Big Apple San Francisco | other Baltimore | other ... ``` **例 2:** 検索 `CASE` 式を使用し、それぞれのチケット販売の `pricepaid` 値に基づいてグループ番号を割り当てます。 ``` SELECT pricepaid, CASE WHEN CAST(pricepaid as FLOAT) < 10000 THEN 'group 1' WHEN CAST(pricepaid as FLOAT) > 10000 THEN 'group 2' ELSE 'group 3' END FROM S3Object; ``` クエリ結果: ``` pricepaid | case -----------+--------- 12624.00 | group 2 10000.00 | group 3 10000.00 | group 3 9996.00 | group 1 9988.00 | group 1 ... ``` ## COALESCE `COALESCE` は、引数を順に評価し、最初の不明でない値 (最初の非 Null 値または欠落していない値) を返します。この関数は、Null および欠落している値を伝達しません。 ### 構文 ``` COALESCE ( expression, expression, ... ) ``` ### パラメータ *`expression`* 関数の対象となる式。 ### 例 ``` COALESCE(1) -- 1 COALESCE(null) -- null COALESCE(null, null) -- null COALESCE(missing) -- null COALESCE(missing, missing) -- null COALESCE(1, null) -- 1 COALESCE(null, null, 1) -- 1 COALESCE(null, 'string') -- 'string' COALESCE(missing, 1) -- 1 ``` ## NULLIF 2 つの式があり、2 つの式が同じ値に評価される場合、`NULLIF` は `NULL` を返し、それ以外の場合、`NULLIF` は最初の式を評価した結果を返します。 ### 構文 ``` NULLIF ( expression1, expression2 ) ``` ### パラメータ `expression1, expression2` 関数の対象となる式。 ### 例 ``` NULLIF(1, 1) -- null NULLIF(1, 2) -- 1 NULLIF(1.0, 1) -- null NULLIF(1, '1') -- 1 NULLIF([1], [1]) -- null NULLIF(1, NULL) -- 1 NULLIF(NULL, 1) -- null NULLIF(null, null) -- null NULLIF(missing, null) -- null NULLIF(missing, missing) -- null ``` # 変換関数 **重要** 新規のお客様への Amazon S3 Select の提供は終了しました。Amazon S3 Select をご利用の既存のお客様は、今後も通常どおり使用できます。[詳細情報](https://aws.amazon.com/blogs/storage/how-to-optimize-querying-your-data-in-amazon-s3/) Amazon S3 Select は、次の変換関数をサポートしています。 **Topics** + [CAST](#s3-select-sql-reference-cast) ## CAST `CAST` 関数は、エンティティ (1 つの値を返す式など) を 1 つの型から別の型へと変換します。 ### 構文 ``` CAST ( expression AS data_type ) ``` ### パラメータ *`expression`* 1 つまたは複数の値、演算子、および値を返す SQL 関数の組み合わせです。 *`data_type`* `INT` のような、式のキャスト先となる対象データ型です。サポートされているデータ型のリストについては、「[データ型](s3-select-sql-reference-data-types.md)」を参照してください。 ### 例 ``` CAST('2007-04-05T14:30Z' AS TIMESTAMP) CAST(0.456 AS FLOAT) ``` # 日付関数 **重要** 新規のお客様への Amazon S3 Select の提供は終了しました。Amazon S3 Select をご利用の既存のお客様は、今後も通常どおり使用できます。[詳細情報](https://aws.amazon.com/blogs/storage/how-to-optimize-querying-your-data-in-amazon-s3/) Amazon S3 Select では、以下の日付関数がサポートされています。 **Topics** + [DATE\$1ADD](#s3-select-sql-reference-date-add) + [DATE\$1DIFF](#s3-select-sql-reference-date-diff) + [EXTRACT](#s3-select-sql-reference-extract) + [TO\$1STRING](#s3-select-sql-reference-to-string) + [TO\$1TIMESTAMP](#s3-select-sql-reference-to-timestamp) + [UTCNOW](#s3-select-sql-reference-utcnow) ## DATE\$1ADD 日付部分、数量、タイムスタンプを指定すると、`DATE_ADD` は日付部分を数量に応じて変更し、更新されたタイムスタンプを返します。 ### 構文 ``` DATE_ADD( date_part, quantity, timestamp ) ``` ### パラメータ *`date_part`* 変更する日付の部分を指定します。これには、次のいずれかを指定できます。 + 年 + か月 + 日 + 時間 + 分 + 秒 *`quantity`* 更新されたタイムスタンプ値に適用する値。`quantity` が正の値である場合は、タイムスタンプの date\$1part に加算し、負の値の場合は減算します。 *`timestamp`* 関数の対象となるタイムスタンプ。 ### 例 ``` DATE_ADD(year, 5, `2010-01-01T`) -- 2015-01-01 (equivalent to 2015-01-01T) DATE_ADD(month, 1, `2010T`) -- 2010-02T (result will add precision as necessary) DATE_ADD(month, 13, `2010T`) -- 2011-02T DATE_ADD(day, -1, `2017-01-10T`) -- 2017-01-09 (equivalent to 2017-01-09T) DATE_ADD(hour, 1, `2017T`) -- 2017-01-01T01:00-00:00 DATE_ADD(hour, 1, `2017-01-02T03:04Z`) -- 2017-01-02T04:04Z DATE_ADD(minute, 1, `2017-01-02T03:04:05.006Z`) -- 2017-01-02T03:05:05.006Z DATE_ADD(second, 1, `2017-01-02T03:04:05.006Z`) -- 2017-01-02T03:04:06.006Z ``` ## DATE\$1DIFF 日付部分と 2 つの有効なタイムスタンプを指定すると、`DATE_DIFF` は日付部分の差を返します。戻り値は、`date_part` の値 `timestamp1` が `date_part` の値 `timestamp2` より大きい場合に負の整数となります。戻り値は、`date_part` の値 `timestamp1` が `date_part` の値 `timestamp2` より少ない場合に正の整数となります。 ### 構文 ``` DATE_DIFF( date_part, timestamp1, timestamp2 ) ``` ### パラメータ **`date_part`** 比較するタイムスタンプの部分を指定します。`date_part` の定義については、「[DATE\$1ADD](#s3-select-sql-reference-date-add)」を参照してください。 **`timestamp1`** 比較する最初のタイムスタンプ。 **`timestamp2`** 比較する 2 番目のタイムスタンプ。 ### 例 ``` DATE_DIFF(year, `2010-01-01T`, `2011-01-01T`) -- 1 DATE_DIFF(year, `2010T`, `2010-05T`) -- 4 (2010T is equivalent to 2010-01-01T00:00:00.000Z) DATE_DIFF(month, `2010T`, `2011T`) -- 12 DATE_DIFF(month, `2011T`, `2010T`) -- -12 DATE_DIFF(day, `2010-01-01T23:00`, `2010-01-02T01:00`) -- 0 (need to be at least 24h apart to be 1 day apart) ``` ## EXTRACT 日付部分とタイムスタンプを指定すると、`EXTRACT` はタイムスタンプの日付部分の値を返します。 ### 構文 ``` EXTRACT( date_part FROM timestamp ) ``` ### パラメータ **`date_part`** 抽出するタイムスタンプの部分を指定します。これには、次のいずれかを指定できます。 + `YEAR` + `MONTH` + `DAY` + `HOUR` + `MINUTE` + `SECOND` + `TIMEZONE_HOUR` + `TIMEZONE_MINUTE` **`timestamp`** 関数の対象となるタイムスタンプ。 ### 例 ``` EXTRACT(YEAR FROM `2010-01-01T`) -- 2010 EXTRACT(MONTH FROM `2010T`) -- 1 (equivalent to 2010-01-01T00:00:00.000Z) EXTRACT(MONTH FROM `2010-10T`) -- 10 EXTRACT(HOUR FROM `2017-01-02T03:04:05+07:08`) -- 3 EXTRACT(MINUTE FROM `2017-01-02T03:04:05+07:08`) -- 4 EXTRACT(TIMEZONE_HOUR FROM `2017-01-02T03:04:05+07:08`) -- 7 EXTRACT(TIMEZONE_MINUTE FROM `2017-01-02T03:04:05+07:08`) -- 8 ``` ## TO\$1STRING タイムスタンプと形式パターンを指定すると、`TO_STRING` は、指定された形式でタイムスタンプの文字列表現を返します。 ### 構文 ``` TO_STRING ( timestamp time_format_pattern ) ``` ### パラメータ *`timestamp`* 関数の対象となるタイムスタンプ。 *`time_format_pattern`* 次の特殊文字の解釈を持つ文字列。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/AmazonS3/latest/userguide/s3-select-sql-reference-date.html) ### 例 ``` TO_STRING(`1969-07-20T20:18Z`, 'MMMM d, y') -- "July 20, 1969" TO_STRING(`1969-07-20T20:18Z`, 'MMM d, yyyy') -- "Jul 20, 1969" TO_STRING(`1969-07-20T20:18Z`, 'M-d-yy') -- "7-20-69" TO_STRING(`1969-07-20T20:18Z`, 'MM-d-y') -- "07-20-1969" TO_STRING(`1969-07-20T20:18Z`, 'MMMM d, y h:m a') -- "July 20, 1969 8:18 PM" TO_STRING(`1969-07-20T20:18Z`, 'y-MM-dd''T''H:m:ssX') -- "1969-07-20T20:18:00Z" TO_STRING(`1969-07-20T20:18+08:00Z`, 'y-MM-dd''T''H:m:ssX') -- "1969-07-20T20:18:00Z" TO_STRING(`1969-07-20T20:18+08:00`, 'y-MM-dd''T''H:m:ssXXXX') -- "1969-07-20T20:18:00+0800" TO_STRING(`1969-07-20T20:18+08:00`, 'y-MM-dd''T''H:m:ssXXXXX') -- "1969-07-20T20:18:00+08:00" ``` ## TO\$1TIMESTAMP 文字列を指定すると、`TO_TIMESTAMP` は、それをタイムスタンプに変換します。`TO_TIMESTAMP` は `TO_STRING` の逆演算です。 ### 構文 ``` TO_TIMESTAMP ( string ) ``` ### パラメータ *`string`* 関数の対象となる文字列。 ### 例 ``` TO_TIMESTAMP('2007T') -- `2007T` TO_TIMESTAMP('2007-02-23T12:14:33.079-08:00') -- `2007-02-23T12:14:33.079-08:00` ``` ## UTCNOW `UTCNOW` は、現在の時刻をタイムスタンプとして UTC で返します。 ### 構文 ``` UTCNOW() ``` ### パラメータ `UTCNOW` はパラメータを受け取りません。 ### 例 ``` UTCNOW() -- 2017-10-13T16:02:11.123Z ``` # 文字列関数 **重要** 新規のお客様への Amazon S3 Select の提供は終了しました。Amazon S3 Select をご利用の既存のお客様は、今後も通常どおり使用できます。[詳細情報](https://aws.amazon.com/blogs/storage/how-to-optimize-querying-your-data-in-amazon-s3/) Amazon S3 Select では、以下の文字列関数がサポートされています。 **Topics** + [CHAR\$1LENGTH, CHARACTER\$1LENGTH](#s3-select-sql-reference-char-length) + [LOWER](#s3-select-sql-reference-lower) + [SUBSTRING](#s3-select-sql-reference-substring) + [TRIM](#s3-select-sql-reference-trim) + [UPPER](#s3-select-sql-reference-upper) ## CHAR\$1LENGTH, CHARACTER\$1LENGTH `CHAR_LENGTH` (または `CHARACTER_LENGTH`) は、指定された文字列の文字数をカウントします。 **注記** `CHAR_LENGTH` と `CHARACTER_LENGTH` はシノニムです。 ### 構文 ``` CHAR_LENGTH ( string ) ``` ### パラメータ *`string`* 関数の対象となる文字列。 ### 例 ``` CHAR_LENGTH('') -- 0 CHAR_LENGTH('abcdefg') -- 7 ``` ## LOWER 文字列を指定すると、`LOWER` はすべての大文字を小文字に変換します。大文字以外の文字は変更されません。 ### 構文 ``` LOWER ( string ) ``` ### パラメータ **`string`** 関数の対象となる文字列。 ### 例 ``` LOWER('AbCdEfG!@#$') -- 'abcdefg!@#$' ``` ## SUBSTRING 文字列、起動インデックス、長さ (オプション) を指定すると、`SUBSTRING` は開始インデックスから文字列の末尾、または指定された長さまでの部分文字列を返します。 **注記** 入力文字列の先頭文字は、インデックス位置が 1 になります。 `start` が 1 未満で、長さを指定しない場合、インデックス位置は 1 に設定されます。 `start` が 1 未満で、長さを指定した場合、インデックス位置は `start + length -1` に設定されます。 `start + length -1` が 0 未満の場合は、空の文字列が返されます。 `start + length -1` が 0 以上の場合は、インデックス位置 1 から始まる長さ `start + length - 1` の部分文字列が返されます。 ### 構文 ``` SUBSTRING( string FROM start [ FOR length ] ) ``` ### パラメータ **`string`** 関数の対象となる文字列。 **`start`** 文字列の開始位置。 **`length`** 返す部分文字列の長さ。存在しない場合は、文字列の末尾に進みます。 ### 例 ``` SUBSTRING("123456789", 0) -- "123456789" SUBSTRING("123456789", 1) -- "123456789" SUBSTRING("123456789", 2) -- "23456789" SUBSTRING("123456789", -4) -- "123456789" SUBSTRING("123456789", 0, 999) -- "123456789" SUBSTRING("123456789", 1, 5) -- "12345" ``` ## TRIM 文字列の先頭または末尾の文字を切り捨てます。削除するデフォルトの文字はスペース (`' '`) です。 ### 構文 ``` TRIM ( [[LEADING | TRAILING | BOTH remove_chars] FROM] string ) ``` ### パラメータ **`string`** 関数の対象となる文字列。 `LEADING` \$1 `TRAILING` \$1 `BOTH` このパラメータは、先頭または末尾の文字、またはその両方を切り捨てるかどうかを指定します。 **`remove_chars`** 削除する文字のセット。`remove_chars` は、長さが 1 より大きい文字列とすることができます。この関数は、削除された文字列の先頭または末尾で見つかった `remove_chars` から、任意の文字を含む文字列を返します。 ### 例 ``` TRIM(' foobar ') -- 'foobar' TRIM(' \tfoobar\t ') -- '\tfoobar\t' TRIM(LEADING FROM ' foobar ') -- 'foobar ' TRIM(TRAILING FROM ' foobar ') -- ' foobar' TRIM(BOTH FROM ' foobar ') -- 'foobar' TRIM(BOTH '12' FROM '1112211foobar22211122') -- 'foobar' ``` ## UPPER 文字列を指定すると、`UPPER` はすべての小文字を大文字に変換します。小文字以外の文字は変更されません。 ### 構文 ``` UPPER ( string ) ``` ### パラメータ **`string`** 関数の対象となる文字列。 ### 例 ``` UPPER('AbCdEfG!@#$') -- 'ABCDEFG!@#$' ```