【SQL】AWS Athena データ抽出入門 Vol.2【初心者向け】

# 内容
AWS Athenaへ用意されているテーブルのデータを、SQLを使って取得できるよう目指す方向け「Vol.2」です。

その他ボリューム
- [【SQL】AWS Athena データ抽出入門 Vol.1【初心者向け】](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68)
- [【SQL】AWS Athena データ抽出入門 Vol.3【初心者向け】](https://qiita.com/nijigen_plot/items/7a48e4c015cef67691a1)



# 練習用データ

練習用データは[こちら](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#%E7%B7%B4%E7%BF%92%E7%94%A8%E3%83%87%E3%83%BC%E3%82%BF)の3つのテーブルを用います。
Athenaコンソール上で実行すると作成できます。


# 複数検索
`IN`句を使うと、条件を複数書くことができます。
いずれかの条件に合致していれば取得できます。

【例】practice_userテーブルから、pref(都道府県)が東京都もしくは大阪府もしくは京都府のデータを取得したい

```:sql
SELECT
    *
FROM 
    practice_user
WHERE
    pref IN ('東京都', '大阪府', '京都府')
```
結果
![SnapCrab_NoName_2022-4-11_0-6-47_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/bcf18ebe-90fc-9f38-b57a-cadf55471cc8.png)

条件が沢山ある場合は、[OR](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#or)を使わずに、IN句にして書くと良いと思います。


# あいまい検索
`LIKE`句を使うと、部分的に一致するデータを取得することができます。

## 前方一致
`LIKE 'ほにゃらら%'`とすると、`ほにゃらら`の後に何かしらついていても、最初に`ほにゃらら`がついている行を取得してくれます。

【例】practice_userテーブルからsex(性別)の最初に「男」が付くデータを取得したい
```:sql
SELECT
    *
FROM 
    practice_user
WHERE 
    sex LIKE '男%'
```
結果
![SnapCrab_NoName_2022-4-10_13-51-56_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/9eb1fecc-8fdb-bd6c-78d1-17a2c5f78419.png)

## 後方一致
`LIKE '%ほにゃらら'`とすると、`ほにゃらら`の前に何かしらついていても、最後に`ほにゃらら`がついている行を取得してくれます。

【例】practice_userテーブルからpref(都道府県)の最後に「県」が付くデータを取得したい
```:sql
SELECT
    *
FROM 
    practice_user
WHERE 
    pref LIKE '%県'
```
結果
![SnapCrab_NoName_2022-4-10_13-47-4_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/8a1de369-5a53-4b15-bda7-cc5f3c70ace4.png)


## 部分一致

`LIKE '%ほにゃらら%'`とすると、データに`ほにゃらら`が含む行を取得してくれます。

【例】practice_userテーブルからpref(都道府県)のに「都」が含むデータを取得したい
```:sql
SELECT
    *
FROM 
    practice_user
WHERE 
    pref LIKE '%都%'
```
結果

![SnapCrab_NoName_2022-4-10_13-54-17_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/a76c871a-af77-1824-a5ad-3f379f2914b6.png)

## 部分否定
`LIKE`と否定の`NOT`は組み合わせて使うことができます。


【例】practice_userテーブルからpref(都道府県)のに「都」が**含まない**データを取得したい
```:sql
SELECT
    *
FROM 
    practice_user
WHERE 
    pref NOT LIKE '%都%'
```
結果
![SnapCrab_NoName_2022-4-10_13-55-59_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/cf335d5f-a61f-e1b0-5515-e04de3a1a45f.png)


# データ型

データには**型**があり、集計や表の結合をするとき、見た目は同じでも型が違うと思い通りに実行できない場合があります。

前回の「[データの整列](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E6%95%B4%E5%88%97)」で[文字列型に対してORDER BYをした例を出しました](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#%E6%B3%A8%E6%84%8F%E7%82%B9)が、ORDER BY以外にもこのような実行結果がおかしくなったり、エラーを吐くことがあります。

データ型には次のようなものがあります。
詳しくはAWS公式マニュアル「[Amazon Athena のデータ型](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/data-types.html)」を参照ください。


| データ型|説明 |実行できる関数 |
| ---|--- | ---|
| int/integer |-2147483648から2147483647までの整数を表現できる |[集計関数](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#%E9%9B%86%E8%A8%88)/[四則演算や計算用関数](https://prestodb.io/docs/0.217/functions/math.html)等 |
|float/real |1.40129846432481707e-45 から 3.40282346638528860e+38 の範囲の正または負の値を表現できる |[集計関数](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#%E9%9B%86%E8%A8%88)/[四則演算や計算用関数](https://prestodb.io/docs/0.217/functions/math.html)等 |
|varchar |1から65535までの長さの文字データを表現できる | LIKE句での[あいまい検索](#あいまい検索)や[文字の置き換え、切り取り](https://prestodb.io/docs/0.217/functions/string.html)等|
| date| YYYY-MM-DD の日付データを表現できる| [日数を足す。月別に均す。年月日の好きな部分を取り出す](https://prestodb.io/docs/0.217/functions/datetime.html)等|
|timestamp | YYYY-MM-DD HH:<zero-width space>mm\:ss[.f...]のミリ秒単位までの日付と時刻を表現できる|  [時間を足す。時間別に均す。日時の好きな部分を取り出す](https://prestodb.io/docs/0.217/functions/datetime.html)等|


## データ型確認

### コンソールから見る

表の列ごとにデータ型は決まっていて、Athenaコンソールではそれをすぐに確認できます。
左側の「テーブル」→「＋ボタンクリック」→列名の右側がデータ型です。

![SnapCrab_NoName_2022-4-10_14-2-46_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/95590962-65bb-48c1-84c3-78640a20bca7.png)



### クエリで見る

`typeof()`で、型を見ることができます。

```:sql
SELECT typeof(3) -- integer
```
![SnapCrab_NoName_2022-4-10_14-7-5_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/3e3e07a9-b0c3-7df6-398d-22cf30a25c78.png)

```:sql
SELECT typeof(3 + 0.01) -- double
```
![SnapCrab_NoName_2022-4-10_14-9-9_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/cee6a51d-56a8-4431-d8e7-5079f4b4d893.png)

```:sql
SELECT typeof('こんにちは') -- varchar(5)
```

![SnapCrab_NoName_2022-4-10_14-9-58_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/075508a9-7b38-8012-b76a-5f8be9deaeaa.png)

## データ型を変換する

`CAST`や`TRY_CAST`でデータ型を変更することができます。

### CAST

【例】practice_productのitem_idをintegerへ変換したい
```
SELECT 
    item_id as varchar_item_id,
    cast(item_id as integer) as integer_item_id
FROM 
    practice_product
```
![SnapCrab_NoName_2022-4-10_14-13-35_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/4dc61552-07a7-c2bd-98fe-1e864d5bf3f6.png)


### TRY_CAST
`TRY_CAST`は、データ型を変換できるものは変換し、変換できないものはNULLとする関数です。

【例】practice_userのage(年齢)を変換できるものはintegerへ変換し、できないものはNULLとしたい
```:sql
SELECT
    age as varchar_age,
    try_cast(age as integer) as integer_age
FROM 
    practice_user
```

![SnapCrab_NoName_2022-4-10_14-16-34_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/fe9c5834-5a9a-88b0-6a93-2376b96257fe.png)

「不明」はintegerへは変換できないので、NULLになりました。
`TRY_CAST`ではなく`CAST`で、このクエリを実行すると、変換できずエラーになります。
![SnapCrab_NoName_2022-4-10_14-17-47_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e439b744-a91f-1173-3ac2-538a062b5496.png)


# NULL
`NULL`とは、「データが無い」ことを示します。

前回の[LEFT JOINでの結合](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#left-join)をした結果、列の右側がほとんど空白になっていましたが、あれが`NULL`です。

赤枠がNULL
![68747470733a2f2f71696974612d696d6167652d73746f72652e73332e61702d6e6f727468656173742d312e616d617a6f6e6177732e636f6d2f302f3633353037392f38393566313838312d333533352d643563632d356466342d653039616.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/0e715c78-98bc-f1c3-8d8d-45c8cc201a2b.png)


「データが無い」ので、**集計関数でも対象とされません**。上の画像の表からproduct_nameとsale_idをそれぞれカウントしてみます。

```:sql
WITH left_join_table AS -- LEFT JOINをした画像の表
(SELECT
	*
FROM 
    practice_product
LEFT JOIN 
    (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON 
    practice_product.item_id = practice_sales.item_id
)

SELECT
    count(product_name) as count_product_name,
    count(sale_id) as count_sale_id
FROM 
    left_join_table
```
結果
![SnapCrab_NoName_2022-4-10_14-27-8_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/fc6f0827-254a-7141-f262-6a90e940eeb9.png)

product_nameは10,sale_idは3と出てきました。NULLはデータが無いことを示すので、集計の対象になっていないことが分かります。


## xxx is NULL

「データが無い」NULLを条件として、結果を取得することもできます。

【例】LEFT JOINしたテーブルからsale_idがNULLのデータを取得する
```:sql
WITH left_join_table AS -- LEFT JOINをした画像の表
(SELECT
	*
FROM 
    practice_product
LEFT JOIN 
    (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON 
    practice_product.item_id = practice_sales.item_id
)

SELECT
    *
FROM 
    left_join_table
WHERE 
    sale_id is NULL
```
結果
![SnapCrab_NoName_2022-4-10_14-31-14_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e6c23607-0155-3163-e23f-939b50eaa915.png)

## COALESCE
NULLになってしまったデータを、何かに置き換えたい場合があります。
その場合は`COALESCE`関数を使います。

【例】practice_userのageを`TRY_CAST`でintegerへ変換する際、NULLになってしまったデータは`40`としたい
```:sql
SELECT
    age as varchar_age,
    COALESCE(try_cast(age as integer), 40) as integer_age
FROM 
    practice_user
```
結果
![SnapCrab_NoName_2022-4-10_14-33-58_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/29e20738-eaf8-f583-c437-ad088121f209.png)

[先ほど](#try_cast)は変換できずにNULLになりましたが、40で置き換えることができました。

# 条件式


## IF
`IF`のあとに条件を書き、その条件に合致している場合と合致していない場合の値を書くことができます。

【例】practice_productテーブルのunit_priceが200以下のものは`low_price_group`,そうでないものは`high_proce_group`という値を付与したい
```:sql
SELECT
    *,
    IF(unit_price <=200, 'low_price_group', 'high_price_group') as price_group
FROM 
    practice_product
```
結果
![SnapCrab_NoName_2022-4-10_22-42-58_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/bb1d38b9-5981-7d07-7b4c-a788d65eb93a.png)

## CASE WHEN
IFのような条件を**いくつも書きたい**とき、`CASE WHEN`が役に立ちます。
年齢から年代を作るような時に使えますね。
最初に`CASE`を書き、その後に`WHEN`で条件式、`THEN`へ条件が合致したときの値を。
最後すべての条件に合致しなかった場合の値を`ELSE`で書き、`END`で閉じます。


【例】practice_userテーブルのageを以下のように振り分けたい

|年齢 |振り分け |
| ---|--- |
|29歳まで |20代以下 |
| 30 ~ 39歳| 30代|
|40 ~ 49歳 | 40代|
|50 ~ 59歳 |50代 |
|60 ~ 69歳 | 60代|
|70歳以上 | 70代以上|
|不明 |不明 |

```:sql
SELECT
    *,
    CASE
    WHEN try_cast(age as integer) <= 29 THEN '20代以下'
    WHEN try_cast(age as integer) BETWEEN 30 AND 39 THEN '30代'
    WHEN try_cast(age as integer) BETWEEN 40 AND 49 THEN '40代'
    WHEN try_cast(age as integer) BETWEEN 50 AND 59 THEN '50代'
    WHEN try_cast(age as integer) BETWEEN 60 AND 69 THEN '60代'
    WHEN try_cast(age as integer) >= 70 THEN '70代以上'
    ELSE '不明' END as age_group
FROM 
    practice_user
```
結果
![SnapCrab_NoName_2022-4-10_22-51-29_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e89c0f23-f5fa-cc21-47d6-80392a51b8f3.png)


[集計関数](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#%E9%9B%86%E8%A8%88)と合わせて書くこともできます。

【例】practice_productテーブルのunit_priceが200以下の商品とそうでない商品の個数をカウントしたい
```:sql
SELECT
    count(
        CASE
        WHEN unit_price <= 200 THEN unit_price
        ELSE NULL END
    ) as low_price_item_count,
    count(
        CASE
        WHEN unit_price > 200 THEN unit_price
        ELSE NULL END
    ) as high_price_item_count
FROM 
    practice_product
```

![SnapCrab_NoName_2022-4-10_22-56-38_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/71e3f7e3-7cae-7904-d862-920cefe5f738.png)
`ELSE NULL`とすることで条件に合致しないものは`NULL`として、集計対象から外すことができます。

それぞれの価格に合致する商品の個数をカウントすることができました。
`GROUP BY`っぽいですが、列を分けてそれぞれ集計することができるので、違った使い道があります。

# 文字列データ操作
[データ型](#データ型)でいう、varcharを対象として使える関数の一部を紹介します。
全ては、マニュアルの[6.9. String Functions and Operators](https://prestodb.io/docs/0.217/functions/string.html)に載っています。

|関数 | 説明|
|--- | ---|
| CONCAT() | 文字をくっつけることができる (`\|\|`でも代用できる) | 
|LENGTH() |文字数をカウントできる | 
|REPLACE() | 選択した文字を置き換え、削除できる| 
|SUBSTR() |文字列の好きな部分を表示できる | 

## CONCAT

`CONCAT()`の括弧の中に、対象となる列や文字を入れると、くっついた新たな列ができます。

【例】practice_userのsexとprefをくっつけて、新しい列を作りたい。結合時は`_`を間に挟みたい
```:sql
SELECT
    *,
    CONCAT(sex, '_', pref) as sex_and_pref
FROM 
    practice_user
```
結果
![SnapCrab_NoName_2022-4-10_23-7-27_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/4a532db0-7376-8685-82e4-297e532afbfa.png)

また、`||`でも代用することができます
```:sql
SELECT
    *,
    sex || '_' || pref as sex_and_pref
FROM 
    practice_user
```


## LENGTH
`LENGTH()`の括弧の中に、対象となる列や文字を入れると、文字数をカウントできます。

【例】practice_userテーブルのprefの文字数をカウントしたい
```:sql
SELECT
    *,
    LENGTH(pref) as pref_length
FROM 
    practice_user
```
結果
![SnapCrab_NoName_2022-4-10_23-11-44_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/cb41a4f1-5b5c-47f4-b6fc-785401019f27.png)



## REPLACE
`REPLACE()`は、括弧の中に、「対象となる列や文字」「置き換えたい文字」「置き換え後の文字」を入れます。「置き換え後の文字」は省略することができ、省略すると「置き換えたい文字」に合致するものは消えます。

【例】practice_userテーブルのprefの「県」を「けん」にしたい
```:sql
SELECT
    *,
    REPLACE(pref, '県', 'けん') as replace_pref
FROM 
    practice_user
```
結果
![SnapCrab_NoName_2022-4-10_23-14-59_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/930ec2f9-96f6-99b6-bb88-40db8b1b4fd1.png)

「置き換え後の文字」を省略してみます。
【例】practice_userテーブルのprefの「都」を消したい
```:sql
SELECT
    *,
    REPLACE(pref, '都') as replace_pref
FROM 
    practice_user
```

![SnapCrab_NoName_2022-4-10_23-16-26_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/485c4e45-2a8e-45ce-b8c6-7270bb935556.png)


## SUBSTR
`SUBSTR()`は、括弧の中に、「対象となる列や文字」「取得開始したい位置」「取得開始したい位置から何文字取得するか」を入れます。
「取得開始したい位置から何文字取得するか」は省略することができ、省略すると残りの文字全てを取得対象とします。

【例】practice_salesテーブルのsales_date列を月日だけの表記にしたい
```:sql
SELECT
    *,
    SUBSTR(sales_date,6) as date_month
FROM 
    practice_sales
```
結果
![SnapCrab_NoName_2022-4-10_23-21-18_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/3c15b206-0997-fcaf-4963-5d8617455a2f.png)


【例】practice_productのproduct_nameの4文字目から2文字取得したい
```:sql
SELECT
    *,
    SUBSTR(product_name,4, 2) as product_name_substr
FROM 
    practice_product
```
結果
![SnapCrab_NoName_2022-4-10_23-24-21_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/fb5491a7-e066-ead4-6750-d1872c04f4a3.png)
4文字目まで存在しないproduct_nameのデータは、空白になりました。
紛らわしいですがこれは`NULL`ではなく、`''`（空白というデータがある）です。


# 副問合せ

副問合せは、SELECT,FROM,WHERE等データを取得したい条件を書くとき、その条件にSELECT,FROM,WHEREを用いた結果を使うことです。

【例】practice_userテーブルのprefが東京都の人のみのデータをpractice_salesから取得したい
```:sql
SELECT
    *
FROM 
    practice_sales
WHERE 
    user_id IN (SELECT user_id FROM practice_user WHERE pref = '東京都')
```
結果
![SnapCrab_NoName_2022-4-10_23-33-56_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/aa8126fe-c6e5-bf46-c39b-5e3dca479970.png)


`user_id IN`の条件に
`SELECT user_id FROM practice_user WHERE pref = '東京都'`というクエリが更に入っています。
このようにして、他のテーブルから条件を持ってきたりすることが可能です。
前回の[FULL JOIN](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68#full-join)では、`FROM`と`FULL JOIN`部分にも副問合せを用いています。

# クエリ内テーブル
`WITH`を使うと、クエリの中だけで使えるテーブルを作ることができます。
[副問合せ](#副問合せ)で使った
`SELECT user_id FROM practice_user WHERE pref = '東京都'`を一つのテーブルとして作ってあげることで、可読性を高めることができます。

【例】**WITH句を使って**、practice_userテーブルのprefが東京都の人のみのデータをpractice_salesから取得したい
```sql
WITH tokyo_users AS
(SELECT * FROM practice_user WHERE pref = '東京都')

SELECT
    *
FROM 
    practice_sales
WHERE 
    user_id IN (SELECT user_id FROM tokyo_users)
```
結果
![SnapCrab_NoName_2022-4-10_23-49-44_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/bec85f1b-dbbf-51c8-8a52-7b60cd94a150.png)

tokyo_usersテーブルとして、practice_userテーブルからprefが東京都のユーザーだけを取得した後に、tokyo_usersテーブルを`user_id IN`の条件として使用しています。


## 複数作成する場合

クエリ内テーブルですが、**2つ目以降にはWITH句は不要**です。
更に、最後以外のクエリ内テーブルの終わりにカンマをつける必要があります。

【例】クエリ内テーブルを2つ作成
```:sql
WITH tokyo_users AS
(SELECT
    *
FROM 
    practice_user 
WHERE 
    pref = '東京都'
), -- カンマを付ける

aomori_users AS -- WITHは不要
(SELECT
    *
FROM 
    practice_user 
WHERE 
    pref = '青森県'
)


SELECT
    *
FROM 
    practice_sales
WHERE 
    user_id IN (SELECT user_id FROM tokyo_users) or user_id IN (SELECT user_id FROM aomori_users)
```

![SnapCrab_NoName_2022-4-10_23-55-11_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/42b01ec2-1be5-f173-f2f6-54a4f57159a6.png)