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

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

過去の記事
- [【SQL】AWS Athena データ抽出入門 Vol.1【初心者向け】](https://qiita.com/nijigen_plot/items/9132404b13d0d2d3aa68)
- [【SQL】AWS Athena データ抽出入門 Vol.2【初心者向け】](https://qiita.com/nijigen_plot/items/98580788a7f87535bba7)


# 練習用データ

練習用データは[こちら](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コンソール上で実行すると作成できます。


# Window関数

Window関数は、クエリ結果の行全体へ計算を実行する関数です。
`OVER`句を用いた特別な構文が必要です。

関数の結果をクエリの行それぞれに与えるようなことができて、Window関数()とOVER句それぞれの記載が必要です。
用途は色々ありますが、ユーザーごとの購入履歴に順序を付けたり、ひとつ前の履歴を持ってきたりできます。

【例】practice_salesテーブルから、ユーザーごとにsales_at昇順で番号を付与したい
```:sql
SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY sales_at asc) as purchase_rank
FROM
    practice_sales
ORDER BY
    user_id, sales_at
```
結果
![SnapCrab_NoName_2022-4-18_23-0-8_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/772c0b35-1cc4-aed9-35a2-e9cad0258faf.png)

purchase_rank列を見ると、user_idごとにsales_at昇順で番号が付けられていることが分かります。
上記の例でいう`ROW_NUMBER()`の部分がWindow関数です。

以降はよく使うWindow関数について説明します。
すべてのWindow関数については、マニュアルの[7.15. Window Functions](https://prestodb.io/docs/0.231/functions/window.html)に載っています。

## RANK(),DENSE_RANK(),ROW_NUMBER()



| 関数名|説明 |
| ---|--- |
|RANK() | OVER(ORDER BY)で指定した順番に、順位を付けます。同率の場合は順位は同じになり、その次の順位は前の順位の同率の数だけ値を飛ばします。|
|DENSE_RANK() | OVER(ORDER BY)で指定した順番に順位を付けます。同率の場合は順位は同じになり、その次の順位は+1されます。|
|ROW_NUMBER() | OVER(ORDER BY)で指定した順番に順位を付けます。同率の場合でも順位は被らず、一意の連続した番号を返します。|

上の３つを比べてみるとこんな感じです。
【例】practice_salesテーブルのquantity昇順で3つのWindow関数を使う
```:sql
SELECT
    quantity,
    RANK() OVER(ORDER BY quantity asc) as purchase_rank,
    DENSE_RANK() OVER(ORDER BY quantity asc) as purchase_dense_rank,
    ROW_NUMBER() OVER(ORDER BY quantity asc) as purchase_row
FROM
    practice_sales
```
結果
![SnapCrab_NoName_2022-4-18_23-15-45_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/cd1f3620-f0cc-11b3-331c-24f76c70f270.png)



## FIRST_VALUE()

| 関数名|説明 |
| ---|--- |
|FIRST_VALUE() | OVER()で指定した条件で文字列の最小値を返します|

`FIRST_VALUE()`は`MIN()`の文字列版のようなものです。文字列に対してWindow関数として`MIN()`を用いると、期待した結果にならないことがあるので、`FIRST_VALUE()`を用います。
【例】practice_sales,practice_productテーブルからユーザーIDごとに最初に購入した(sales_atが昇順)時のproduct_nameを表示する
```:sql
SELECT
    user_id,
    sales_at,
    product_name,
    MIN(product_name) OVER(PARTITION BY user_id ORDER BY sales_at) as min_window,
    FIRST_VALUE(product_name) OVER(PARTITION BY user_id ORDER BY sales_at) as first_value_window
FROM 
    practice_sales, practice_product
WHERE 
    practice_sales.item_id = practice_product.item_id
ORDER BY 
    user_id, sales_at
```
結果
![SnapCrab_NoName_2022-4-18_23-40-7_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/ed8e3d31-0c19-fd04-16a6-3f0b90cd89a3.png)


## LEAD()

| 関数名|説明 |
| ---|--- |
|LEAD() |OVER()で指定した条件で、次の行となるデータを返します |

行ごとに順位付けした後、その次の順位を持ってくるようなイメージです。
【例】practice_salesテーブルのuser_id毎にsales_at昇順で、次のsales_atのデータを表示する
```:sql
SELECT
    user_id,
    sales_at,
    LEAD(sales_at) OVER(PARTITION BY user_id ORDER BY sales_at asc) as next_sales_at
FROM 
    practice_sales
ORDER BY
    user_id, sales_at!
```
結果
![SnapCrab_NoName_2022-4-18_23-47-37_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/147bd63a-b8a7-918d-f807-fca734eb27ae.png)



# 日付/時刻データ操作

[データ型](https://qiita.com/nijigen_plot/items/98580788a7f87535bba7#%E3%83%87%E3%83%BC%E3%82%BF%E5%9E%8B)の`date`,`timestamp`を対象として使える関数の一部を紹介します。
全ては、マニュアルの[6.13. Date and Time Functions and Operators](https://prestodb.io/docs/0.217/functions/datetime.html)に載っています。


|関数 |説明 |
|---| ---|
|DATE_TRUNC() |時間データを任意の時間単位で切り捨てる |
| DATE_ADD()| 時間データに任意の時間単位、時間数を足す| 
| DATE_DIFF()|時間データと時間データの任意の時間単位での差を求める | 


## DATE_TRUNC()
説明の通り、時間データを任意の時間単位で切り捨てます。
週別や月別等で切り捨てて、集計したりするのに使います。
`DATE_TRUNC(時間単位,対象列)`のように使います。

【例】 practice_salesテーブルのsales_atを月単位で切り捨てる
```:sql
SELECT
    user_id,
    sales_at,
    DATE_TRUNC('MONTH', sales_at) as date_trunc_month
FROM
    practice_sales
```
結果
![SnapCrab_NoName_2022-4-19_0-2-10_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/2912b05e-0aba-0a3e-84dc-b36bc38da391.png)

date_trunc_month列は日にちが01に統一されて、月別に均されています。
`DATE_TRUNC('MONTH', sales_at)`を`GROUP BY`に用いて、月次集計・・・のようなことをよく行います。


## DATE_ADD()
対象の時間データに、任意の時間単位、時間数を足します。
`DATE_ADD(時間単位,時間数,対象列)`のように使います。

【例】practice_salesテーブルのsales_atに3年を足す
```:sql
SELECT
    user_id,
    sales_at,
    DATE_ADD('YEAR', 3, sales_at) as date_add_sales
FROM
    practice_sales
```
結果
![SnapCrab_NoName_2022-4-19_0-6-59_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e3832873-02b2-7484-2265-b339cb909d50.png)

見事に3年後になっています。
ちなみに、時間数をマイナスにすると時間を引くこともできます。

また、この操作は `対象列 + interval '時間数' 時間単位`としても行うことができます。
【例】practice_salesテーブルのsales_atに3年を足す
```:sql
SELECT
    user_id,
    sales_at,
    sales_at + INTERVAL '3' YEAR as date_add_sales
FROM
    practice_sales
```
結果
![SnapCrab_NoName_2022-4-19_0-9-24_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/9b2f09b3-2f07-f67a-d6e0-2ce3bcf620ef.png)


## DATE_DIFF()
時間データと時間データの任意の時間単位での差を求めます。
`DATE_DIFF(時間単位, 引く時間, 引かれる時間)`のように使います。
[LEAD()関数](#lead)の例として用いたテーブルを使って、next_sales_at - sales_atの日数の差を求めてみます。
【例】practice_salesテーブルのuser_id毎にsales_at昇順で、次のsales_atのデータを表示し、日数差を求める
```:sql
WITH lead_exsample_table AS (
    SELECT
        user_id,
        sales_at,
        LEAD(sales_at) OVER(PARTITION BY user_id ORDER BY sales_at asc) as next_sales_at
    FROM 
        practice_sales
    ORDER BY
        user_id, sales_at
    )

SELECT
    user_id,
    sales_at,
    next_sales_at,
    DATE_DIFF('DAY', sales_at, next_sales_at) as diff_days
FROM
    lead_exsample_table
```
結果
![SnapCrab_NoName_2022-4-19_0-18-52_No-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/fe291fb6-1e5f-7bd4-36d1-039dfb62e67f.png)

next_sales_at - sales_atの日数の差が出てきました。
余りは切り捨てされます。