Athenaで基礎からしっかり入門 分析SQL（Pythonコード付き） #4

今まで複雑なデータ操作・分析などはPythonでやっており、SQLは普通のアプリ開発程度のライトなものしか触って来なかったのですが、やはり分析用の長いSQLなども書けた方がやりとり等で便利・・・という印象なので、復習も兼ねて記事にしておきます。

また、SQLに加えて検算も兼ねてPythonやPandasなどを使ったコードもSQLと併記していきます（Pythonで書くとどういった記述が該当するのかの比較用として使います）。

※長くなるのでいくつかの記事に分割します。本記事は4記事目となります。

# 特記事項

本記事では配列関係を良く扱っていきます。その都合、Pandasなどでapplyメソッドなどを絡めた形で触れている節もあればビルトインでの記述のみしている節もあります（本記事からPandas色が少し薄くなります）。

# 他のシリーズ記事

Athenaとはなんぞやという方はこちらをご確認ください:

https://qiita.com/simonritchie/items/069c75059af3ba2ba914

※過去の記事で既に触れたものは本記事では触れません。

`#1`:

用語の説明・SELECT、WHERE、ORDER BY、LIMIT、AS、DISTINCT、基本的な集計関係（COUNTやAVGなど）、Athenaのパーティション、型、CAST、JOIN、UNION（INTERSECTなど含む）など。

https://qiita.com/simonritchie/items/0d84646e3db940e98b14

`#2`:

GROUP BY・HAVING・サブクエリ・CASE・COALESCE・NULLIF・LEAST・GREATEST・四則演算などの基本的な計算・日付と日時の各操作など。

https://qiita.com/simonritchie/items/367e63468e9c2f96b44f

`#3`:

文字列操作全般・正規表現関係など。

https://qiita.com/simonritchie/items/ce6792e9b3f6b4b3f63d

# この記事で触れること

- コメント関係
- 配列操作全般
- ラムダ式

# 環境の準備

以下の#1の記事でS3へのAthena用のデータの配置やテーブルのCREATE文などのGitHubに公開しているものに関しての情報を記載していますのでそちらをご参照ください。

https://qiita.com/simonritchie/items/0d84646e3db940e98b14#%E7%92%B0%E5%A2%83%E3%81%AE%E6%BA%96%E5%82%99

# SQLを読みやすくする: コメント

SQL中にコメントを残すことで、なんのSQLなのか、どういった処理なのかを各所に追加していくことができます。引き継ぎや他人との共有時などにSQLを読む負担が減ります。

Athena（Presto）では以下のようなコメントの書き方がサポートされています。

## インラインコメント

`--`以降の文字列はインラインコメントとして扱われ、SQLには影響しません。

以下のように「ログインログを10行読み込みます。」といったような説明を入れてもそのままSQLを実行できます。

```sql
-- ログインログを10行読み込みます。
SELECT * FROM athena_workshop.login LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/1fba3153-f10d-39f6-d525-d4e6a9c4667a.png)

行中にコメントを追加したい場合には、行末であれば追加することができます。

```sql
SELECT * FROM athena_workshop.login LIMIT 10 -- ログインログを10行読み込みます。
```

ただし、セミコロンが付いている行に関してはその後にSQL関係を入力することができなくなり、コメントなども書かれているとエラーになります。Athenaではセミコロンが無くても動くので、そういった場合はセミコロンを消してしまってもいいかもしれません。

```sql
SELECT * FROM athena_workshop.login LIMIT 10; -- ログインログを10行読み込みます。
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/2a588496-52b9-d658-aeba-8f0d732659a2.png)

複数行のコメントを追加したい場合には複数の`--`の記述を各行にするか、もしくは後の節で触れるブロックコメントを利用します。

```sql
-- ログインログを10行読み込みます。
-- 日付は2021-01-01を対象とします。
SELECT * FROM athena_workshop.login
WHERE dt = '2021-01-01'
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/bc5e38f4-9fe3-9ac7-cc2a-0dd7794be326.png)

## ブロックコメント

`/**/`の記述のアスタリスクの間に挟んだコメントはブロックコメント（複数行に対応できるコメント）になります。例えば以下のように書きます。

```sql
/* ログインログを10行読み込みます。 */
SELECT * FROM athena_workshop.login
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d084bd23-c0cb-8ad0-d022-55317dc7e61e.png)

アスタリスクの間は自由に改行などを含めることができます。毎行`--`などの記述をしなくて済みますし、コメント部分のテキストのコピーなどをしたい場合などにも`--`などが紛れ込まないので複数行の場合にはこちらの方が快適なケースがあります。

```sql
/*
ログインログを10行読み込みます。

日付は2021-01-01を対象とします。
*/
SELECT * FROM athena_workshop.login
WHERE dt = '2021-01-01'
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/1e2ae183-b728-dfa2-5955-718d0be2f8a3.png)

# 配列の操作

この節以降では配列（Array）操作について色々触れていきます。

## 配列の固定値の扱い方

配列の固定値を作りたい場合には`ARRAY [配列の値]`という書き方をすることでカラムを設定することができます。例えば`1, 2`という値を格納した配列と`3, 4`という値を格納した配列の2つを得たい場合には以下のようにSQLを実行することで配列の固定値を取得することができます。

```sql
SELECT ARRAY [1, 2] AS arr_1, ARRAY [3, 4] AS arr_2
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/57479064-2b3a-f527-0d10-8f4d7caadfce.png)

特定のテーブルと一緒に使った場合、各行に対してこの固定値が設定されます。

```sql
SELECT ARRAY [1, 2] AS arr_1, ARRAY [3, 4] AS arr_2, user_id
FROM athena_workshop.login LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/c35277ca-1d06-22bf-10cd-38178dd3e8c7.png)

### Pythonでの書き方

Pandasを初期化する際にカラムに配列などが含まれていればそのまま配列の値を持ったデータフレームが作れるのですが、途中から配列の固定値のカラムを追加する場合、固定のスカラ値などを直接代入するケースと同じようにはストレートにいきません。配列の各値を各行に設定する・・・みたいな挙動になるため、リストなどを代入しようとするとデータフレームとリストの行数が一致していないとエラーになります。

例えば以下のように`[1, 2]`という配列の固定値を`arr_1`というカラムに設定しようとすると行数の不一致によるエラーになります。

```py
import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['arr_1'] = [1, 2]
```

```
ValueError: Length of values (2) does not match length of index (24453)
```

色々対策として書き方はあると思いますが、1つの対応としてリスト内包表記などを使って行数を揃えてしまう・・・というのがシンプルかもしれません。この辺は探せばもっとシンプルなPandasのインターフェイスがあるような気がしないでもないです。

```py
import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['arr_1'] = [[1, 2] for _ in range(len(df))]
print(df[['user_id', 'arr_1']].head())
```

```
   user_id   arr_1
0     8590  [1, 2]
1      568  [1, 2]
2    17543  [1, 2]
3    15924  [1, 2]
4     5243  [1, 2]
```

## ||記号による配列の連結

※以降の節では`user_weapon_and_armor_ids`というテーブルを扱っていきます。基本的なカラムに加えて`weapon_ids`と`armor_ids`という2つのカラムを持っており、それぞれ所持武器と所持防具のマスタIDを想定した整数の配列を格納させています。

以下のようなデータになっています（`armor_ids`カラムの方がスクショ内に収まらないのでスクロールして2枚スクショを貼っています）。分かりやすいように`armor_ids`の方が大きな整数値のIDを設定しています。

```sql
SELECT * FROM athena_workshop.user_weapon_and_armor_ids LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/8c969d03-c961-d382-94a8-ff75fe601051.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/dcfe7175-e5de-91e9-cf57-4a78b3535ab1.png)

---

配列同士は`||`記号で連結することができます。

例えば2つの配列のカラムを連結したい・・・といった場合には以下のように書きます（`concatenated_ids`という1つのカラムに変換しています）。

```sql
SELECT weapon_ids || armor_ids AS concatenated_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b7843703-58e9-296c-5324-a44a23a90b99.png)

後半が`armor_ids`カラムの大きな値になっていることが確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/5bd83432-52b5-ad0e-c6a9-9a6aab0eb4c1.png)

また、固定値との連結もできます。

以下のSQLでは`[1, 2, 3]`という配列と`weapon_ids`というカラムの配列を連結しています。連結されて結果の配列の先頭が`1, 2, 3`からスタートしていることが確認できます。

```sql
SELECT ARRAY [1, 2, 3] || weapon_ids AS concatenated_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/2a7d468e-d129-d349-7b3d-3c51186c03ee.png)

### Pythonでの書き方

Pandasは基本的に各インターフェイスが行列前提のものが多く、リストの値を含んだデータフレームのための連結関係のインターフェイスは無い・・・？気がしますので、applyなりループを回したりで対応する必要があるかなという所感です（しっかり探せばその辺のインターフェイスもあるかもしれません）。

その辺は別の記事で書いたり、もしくはループで回したときのパフォーマンス関係をご共有いただいたりしているので必要に応じてそちらもご確認ください。

https://qiita.com/kishiyama/items/cf3f4dca5ee73dacc551

https://qiita.com/simonritchie/items/dd737a52cf32b662675c

以下はループで回す場合の書き方例です。

```py
from typing import List

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_and_armor_ids/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
weapon_ids: List[List[int]] = df['weapon_ids'].tolist()
armor_ids: List[List[int]] = df['armor_ids'].tolist()
concatenated_ids: List[List[int]] = []

for single_weapon_ids, single_armor_ids in zip(weapon_ids, armor_ids):
    single_weapon_ids.extend(single_armor_ids)
    concatenated_ids.append(single_weapon_ids)
df['concatenated_ids'] = concatenated_ids

print(df['concatenated_ids'].head())
print(df.at[0, 'concatenated_ids'])
```

```
Name: concatenated_ids, dtype: object
0    [240, 65, 157, 257, 51, 266, 211, 208, 257, 35...
1    [45, 123, 75, 271, 87, 298, 156, 243, 80, 57, ...
2    [85, 30, 163, 151, 142, 93, 229, 258, 101, 6, ...
3    [7, 128, 288, 157, 277, 279, 257, 202, 230, 97...
4    [20, 216, 19, 7, 189, 283, 131, 63, 48, 281, 5...

[240, 65, 157, 257, 51, 266, 211, 208, 257, 35, 187, 94, 264, 267, 18, 248, 70, 185, 156, 293, 96, 65, 143, 195, 115, 117, 107, 160, 299, 249, 85, 39, 259, 12, 207, 92, 290, 297, 152, 121, 57, 134, 182, 38, 60, 138, 244, 215, 223, 79, 208, 97, 87, 38, 160, 80, 170, 240, 206, 225, 155, 210, 245, 36, 55, 117, 14, 112, 215, 252, 236, 232, 284,
...
450, 343, 386, 425, 492, 456, 332, 475, 413, 452, 469, 477, 386, 452, 346, 444, 392, 418, 311, 466, 470, 442, 368, 319, 378, 476, 368, 491, 498, 497, 433, 337, 359, 346, 398, 446, 411, 333, 494, 500, 397, 402, 355, 317, 392, 498, 407, 499, 431, 466, 441, 458, 444, 338, 350, 440, 500, 392, 339, 447, 343, 326, 422, 345, 351]
```

## CONCAT関数による配列の連結

`CONCAT`関数でも`||`の記号を使った場合と同じように複数の配列を連結することができます。各引数に連結したい配列を指定します。2つ以上の配列も指定することができます。

以下のSQLでは`[1, 2, 3]`という固定値の配列と`weapon_ids`カラム、`armor_ids`カラムと3つの配列を連結しています。

```sql
SELECT CONCAT(ARRAY [1, 2, 3], weapon_ids, armor_ids) AS concatenated_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/918bdfd0-9818-676c-7be2-ab90b3cc3d1d.png)

## []記号による配列の特定の位置の値へのアクセス

配列のカラムや固定値などに対して`[]`の括弧と整数を指定することで、配列の特定の位置の値にアクセスすることができます（添え字 / インデックスによるアクセス）。

なお、Athena（Presto）では配列のインデックスは1スタートとなります。jsやPythonのように0スタートではなく、Juliaなどのような挙動になるので注意が必要です。

```sql
SELECT weapon_ids[1] AS first_value, weapon_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10;
```

以下のSQLでは配列の先頭の値を取得しています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/eef82944-e7ff-e62a-26ca-fd739280d503.png)

配列の固定値を使った場合にもインデックスの指定が効くようです。以下のSQLでは`1, 2, 3`という固定値の配列の2番目の値を取得しています。

```sql
SELECT ARRAY [1, 2, 3][2] AS second_value
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d2760897-75b6-9ca4-06c5-31f6a87cbc0a.png)

なお、Pythonなどのように負のインデックスを指定して配列の末尾からアクセスするといったことはできません。エラーになります。

```sql
SELECT weapon_ids[-1] AS first_value, weapon_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/935014b5-2b96-4e1e-22e9-a4cf0b6b1a82.png)

## ELEMENT_AT関数による配列の特定の位置の値へのアクセス

`[]`の括弧による特定の位置の配列の値へのアクセスと同じようなことが`ELEMENT_AT`関数で出来ます。

第一引数に対象のカラムもしくは固定値、第二引数に値の位置のインデックスを指定します。こちらも値の位置のインデックスは1からスタートします。

```sql
SELECT ELEMENT_AT(weapon_ids, 1) AS first_value, weapon_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b60275c9-298a-e722-8ab2-cf0b685adfd5.png)

特筆すべき点として、`[]`の括弧では使えなかった負のインデックスの指定がPythonと同様に使えます。負の値を指定した場合は配列の末尾からカウントされます。例えば`-1`を指定すれば配列の最後の値が対象となり、`-2`を指定すれば配列の最後から二番目の値の位置となります。

```sql
SELECT ELEMENT_AT(weapon_ids, -1) AS last_value, weapon_ids
FROM athena_workshop.user_weapon_and_armor_ids
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/decb6416-84fa-ea14-5cd8-efb1287af7cf.png)

### Pythonでの書き方

※Pandasが基本的に行列を扱うという性質上、ここからしばらくはデータフレーム内の配列への処理としてPythonビルトインの処理と`apply`メソッドなどを絡めた書き方が多くなると思います。

特定の配列のインデックスの値を取得する関数を定義し、データフレームのapplyメソッドでそちらの関数を指定することで同じようなことができます。`apply`メソッドには任意のキーワード引数で固定値も渡すことができるので、今回は`index`という引数を設定しています。

```py
from typing import List, Optional

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_and_armor_ids/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')


def element_at(list_value: List[int], index: int) -> Optional[int]:
    """
    指定されたインデックス位置の値をリストから取得する。

    Parameters
    ----------
    list_value : list of int
        対象のリスト。
    index : int
        対象のインデックス。

    Returns
    -------
    value : int or None
        指定されたインデックス位置の値。インデックス範囲外の場合には
        Noneが返却される。
    """
    if index >= len(list_value):
        return None
    value: int = list_value[index]
    return value


df['first_value'] = df['weapon_ids'].apply(element_at, index=0)
print(df[['first_value', 'weapon_ids']].head())
```

```
   first_value                                         weapon_ids
0          240  [240, 65, 157, 257, 51, 266, 211, 208, 257, 35...
1           45  [45, 123, 75, 271, 87, 298, 156, 243, 80, 57, ...
2           85  [85, 30, 163, 151, 142, 93, 229, 258, 101, 6, ...
3            7  [7, 128, 288, 157, 277, 279, 257, 202, 230, 97...
4           20  [20, 216, 19, 7, 189, 283, 131, 63, 48, 281, 5...
```

## 重複を除いた形での2つの配列の連結を行う: ARRAY_UNION

`ARRAY_UNION`関数を使うと2つの配列を重複を除いた形で連結した配列を取得することができます。第一引数に1つ目の配列、第二引数に2つ目の配列を指定します。

例えば以下のSQLでは`1, 2, 3`という値を含んだ配列と`2, 3, 4`という値を含んだ配列を指定しています。`2`と`3`の部分は重複していますが連結結果は`1, 2, 2, 3, 3, 4`といった値にはならずに`1, 2, 3, 4`という配列になります。

```sql
SELECT ARRAY_UNION(ARRAY [1, 2, 3], ARRAY [2, 3, 4]) AS union_array
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/47de645d-5370-fc14-3b17-2ab1a548efe1.png)

### Pythonでの書き方

処理自体はapplyもしくはループで回すとして（以下のサンプルではループで回しています）、ビルトインの`set`関数にリストを渡すと一意な値が取れます。型は`list`ではなくなるので、`set`で変換後に再度`list`に変換しています。`[*a, *b]`といった記述で二つ以上のリストを連結したコピーを得られるのでそちらを`set`の引数に使っています。

```py
from typing import List
import pandas as pd

df: pd.DataFrame = pd.DataFrame(
    data=[{'a': [1, 2, 3], 'b': [2, 3, 4]}])
a_list: List[List[int]] = df['a'].tolist()
b_list: List[List[int]] = df['b'].tolist()
union_list: List[List[int]] = []
for a, b in zip(a_list, b_list):
    union_list.append(list(set([*a, *b])))
df['union_array'] = union_list
print(df)
```

```
           a          b   union_array
0  [1, 2, 3]  [2, 3, 4]  [1, 2, 3, 4]
```

## 値を繰り返した配列を取得する: REPEAT

`REPEAT`関数は第一引数で指定された値を第二引数に指定された回数分繰り返した配列を取得します。第一引数には色々な型の値を指定できます。第二引数には整数が必要になります。

例えば以下のSQLでは整数の3という値を5回繰り返すSQLを実行しています。

```sql
SELECT REPEAT(3, 5) AS repeated_array
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/51a9259b-9f36-698b-7683-3b582685c319.png)

整数以外でも、例えば配列の繰り返しなどもできます。以下のSQLでは`[1, 2, 3]`という配列を3回繰り返しています。結果は`[1, 2, 3, 1, 2, 3, 1, 2, 3]`という1次元の配列ではなく`[[1, 2, 3], [1, 2, 3], [1, 2, 3]]`という2次元の配列になります。

```sql
SELECT REPEAT(ARRAY [1, 2, 3], 3) AS repeated_array
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/a5ecb56d-678f-397f-798b-c603bc16c6f9.png)

### Pythonでの書き方

※以降の節ではデータフレームを絡めない形で触れるケースも出てきますが、前節までのapplyやループなどでデータフレームに設定することでデータフレーム側にも同じようなことができます。

---

リストを乗算するとリストの値を繰り返した値になります。例えば以下のようにすると`3`の値を5回繰り返したリストになります。

```py
print([3] * 5)
```

```
[3, 3, 3, 3, 3]
```

SQLのように`[[1, 2, 3], [1, 2, 3], [1, 2, 3]]`といった二次元配列を得たければ与えるリストも2次元にしておくことで対応ができます。

```py
print([[1, 2, 3]] * 3)
```

```
[[1, 2, 3], [1, 2, 3], [1, 2, 3]]
```

## ARRAY_AVERAGE や ARRAY_SUM などの配列への要約統計量用の関数について

Prestoのドキュメントには配列の平均値を計算する`ARRAY_AVERAGE`や合計値を計算する`ARRAY_SUM`などの関数があるのですが、これらはどうやらAthena上ではまだ利用できないようです。将来のアップデートで反映されるかもとは思いますが、本記事でもそれらの関数は触れずに進めます。

## 配列の各値の最大値を取る: ARRAY_MAX

※この節以降では`battle_party_power`というテーブルを説明のために使っていきます。クエスト（バトル）ごとのパーティーメンバーのIDと装備やレベルに応じた戦闘力を想定した値を格納しています。基本的なカラムに加えて以下のようなカラムを持っています。

- quest_id -> 対象のクエストのIDの整数。
- user_ids -> パーティーメンバーのユーザーIDの整数の配列。
- powers -> パーティーメンバーの戦闘力の整数の配列。

以下のようなデータになっています。

```sql
SELECT * FROM athena_workshop.battle_party_power LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b67f1f7d-bb6a-17ca-ba65-9c8acaae45f0.png)

---

`ARRAY_MAX`関数を使うと配列の値の中での最大値を取得することができます。

以下のSQLではパーティーの戦闘力の中で一番戦闘力が高い値を取得しています。

```sql
SELECT powers, ARRAY_MAX(powers) AS party_max_power
FROM athena_workshop.battle_party_power LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/5c7d1acf-1c20-a765-c62a-ac65f59f877a.png)

### Pythonでの書き方

シンプルにビルトインの`max`関数を`apply`メソッドに指定するだけで対応ができます。

```py
import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/battle_party_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['max_power'] = df['powers'].apply(max)
print(df[['max_power', 'powers']].head())
```

```
   max_power                     powers
0      41013             [41013, 34983]
1      30252      [26151, 29020, 30252]
2      10269  [10269, 8786, 8179, 6633]
3      18561                    [18561]
4      50078      [34067, 50078, 37768]
```

## 配列の各値の最小値を取る: ARRAY_MIN

`ARRAY_MIN`関数を使うと配列内の最小値を取得することができます。

以下のSQLではパーティーの戦闘力の中で一番戦闘力が低い値を取得しています。

```sql
SELECT powers, ARRAY_MIN(powers) AS party_min_power
FROM athena_workshop.battle_party_power LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/5f70b8f9-111f-8d82-7f4e-191c6b9a7f9b.png)

### Pythonでの書き方

こちらも最大値算出の時と同じように、ビルトインの`min`関数を`apply`メソッドの引数に指定するだけで対応ができます。

```py
import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/battle_party_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['min_power'] = df['powers'].apply(min)
print(df[['min_power', 'powers']].head())
```

```
   min_power                     powers
0      34983             [41013, 34983]
1      26151      [26151, 29020, 30252]
2       6633  [10269, 8786, 8179, 6633]
3      18561                    [18561]
4      34067      [34067, 50078, 37768]
```

## 配列の特定範囲の値の配列を取得する: SEQUENCE

`SEQUENCE`関数は特定の値の範囲の配列を取得することができます。連番生成などに使います。

第一引数には開始値となる数値（後述しますが、日付や日時でも対応ができます）、第二引数に終了値となる数値を指定します。返却値は配列となります。

例えば第一引数に1、第二引数に5を指定すると`[1, 2, 3, 4, 5]`という配列が返ってきます。

```sql
SELECT SEQUENCE(1, 5) AS sequence_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d19f9a8a-7863-cb64-4a68-6531a4af01cc.png)

終了値が開始値よりも小さい場合は減算処理が実行される形で連番が生成されます。例えば開始値に3、終了値に-3を指定すれば`[3, 2, 1, 0, -1, -2, -3]`という配列結果が得られます。

```sql
SELECT SEQUENCE(3, -3) AS sequence_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/3cbef98e-1870-48f6-b41f-cf4a38973eee.png)

また、第三引数に整数を指定した場合連番のステップを設定することができます。例えは2ずつ増加させるといった制御になります。

```sql
SELECT SEQUENCE(1, 5, 2) AS sequence_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/35599161-9be9-d924-c54d-ce2d3e83110b.png)

### Pythonでの書き方

ビルトインの`range`関数を使うと連番の値を取得できます。主にループ処理などで使われますが、`list`関数を通してリストにキャストすれば連番の配列としても使えます。注意点として、第二引数に指定した値の1つ手前の値が最終値となるため、最終値に対して1大きい値を第二引数に指定する必要があります（Pythonだとインデックスが0からスタートするので、`range(0, 5)`とすれば最終値は4となり件数も5件となる・・・といった挙動になります）。

```py
print(list(range(1, 6)))
```

```
[1, 2, 3, 4, 5]
```

第三引数もSQL側と同様にステップの設定となります。

```py
print(list(range(1, 6, 2)))
```

```
[1, 3, 5]
```

## 特定の日付範囲の配列を取得する: SEQUENCE

`SEQUENCE`関数には日付を開始値と終了値に指定することもできます。開始日～終了日の範囲の配列を得られます。ただし結果の配列の値はDATEではなくTIMESTAMPになるようです。

第三引数には`INTERVAL`で種類（日数や月数など）や数値（日数値など）を指定します。この辺は以前の記事で触れた日時関係の操作のものと同じなのでそちらをご確認ください。

https://qiita.com/simonritchie/items/367e63468e9c2f96b44f#%E6%97%A5%E6%95%B0%E3%82%92%E5%8A%A0%E7%AE%97%E3%81%99%E3%82%8B

```sql
SELECT SEQUENCE(DATE('2021-01-01'), DATE('2021-01-03'), INTERVAL '1' DAY)
AS sequence_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/93dab987-a81a-e027-b131-6ac707e1094f.png)

なお、第三引数は整数を指定した時とは異なり省略できません（日付を指定したから1日ずつ・・・とはなりません）。省略するとエラーになります。

```sql
SELECT SEQUENCE(DATE('2021-01-01'), DATE('2021-01-03'))
AS sequence_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/3a6ffa38-5455-1d53-0eb9-f7bd672d6f62.png)

### Pythonでの書き方

Pandasの`date_range`関数を使うと日付の範囲を取れます。

```py
import pandas as pd

print(pd.date_range(start='2021-01-01', end='2021-01-03'))
```

```
DatetimeIndex(['2021-01-01', '2021-01-02', '2021-01-03'], dtype='datetime64[ns]', freq='D')
```

返却値は`DatetimeIndex`という特殊な値になっているので、リストの方が良ければシリーズやndarrayなどと同じように`tolist`メソッドで変換できます。

```py
import pandas as pd

print(pd.date_range(start='2021-01-01', end='2021-01-03').tolist())
```

```
[Timestamp('2021-01-01 00:00:00', freq='D'), Timestamp('2021-01-02 00:00:00', freq='D'), Timestamp('2021-01-03 00:00:00', freq='D')]
```

## 特定の日時範囲の配列を取得する: SEQUENCE

日時（TIMESTAMP）を第一引数と第二引数に指定する形でも`SEQUENCE`関数は使えます。使い方と挙動はほぼ日付を指定した時と同じです。

以下のSQLでは10時～13時の間で1時間ごとに4件の配列を取得しています。

```sql
SELECT SEQUENCE(CAST('2021-01-01 10:00:00' AS TIMESTAMP), CAST('2021-01-01 13:00:00' AS TIMESTAMP), INTERVAL '1' HOUR)
AS sequence_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/7af46315-8003-ad3d-4b69-1a8c7c30336e.png)

### Pythonでの書き方

日付と同様に`date_range`関数を使います。日付の時に加えて`freq`引数に`H`を指定すると1時間単位で値が返ってきます。

```py
import pandas as pd

date_range: pd.DatetimeIndex = pd.date_range(
    start='2021-01-01 10:00:00',
    end='2021-01-01 13:00:00',
    freq='H')
print(date_range)
```

```
DatetimeIndex(['2021-01-01 10:00:00', '2021-01-01 11:00:00',
               '2021-01-01 12:00:00', '2021-01-01 13:00:00'],
              dtype='datetime64[ns]', freq='H')
```

## 配列の特定の位置から特定の件数の値を格納した配列を取得する: SLICE

`SLICE`関数は配列の特定の開始位置から任意の件数分の値を格納した配列を抽出することができます。第一引数には対象の配列もしくは固定値、第二引数には配列の開始位置の整数、第三引数には値の件数を指定します。

第二引数の開始位置は1からスタートします。0ではありません。

対象が存在しない場合（1件の値しかない配列で2番目以降の値を指定した場合など）には結果の行の値は空の配列となります。

以下のSQLでは配列の2番目の位置から最大2件分の値を抽出した配列を取得しています。

```sql
SELECT SLICE(powers, 2, 2) AS second_and_third_powers, powers
FROM athena_workshop.battle_party_power LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/7353bb19-db70-8fef-662c-2871d5dbd3ec.png)

第二引数の開始位置には負の値も指定できます。Pythonと同じように-1を指定した場合は配列の最後、-2を指定した場合は最後から2番目の位置・・・といった挙動になります。

以下のSQLでは配列の最後から2番目の位置から1件分の値を抽出しています。

```sql
SELECT SLICE(powers, -2, 1) AS sliced_power, powers
FROM athena_workshop.battle_party_power LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/7b4834b1-73bc-34bc-32df-3a5fd1be4afb.png)

### Pythonでの書き方

以下のコードのようにビルトインのリストでスライスの記法が使えるのでそちらを`apply`メソッド用の関数で設定して対応しています。SQLと異なりPythonでは開始インデックスが0なのでそちらに合わせてあります。

```py
from typing import List

import pandas as pd


def get_sliced_list(
        list_val: List[int], start: int, length: int) -> List[int]:
    """
    指定されたリストの特定の位置から任意の件数の値を格納したリストを
    取得する。

    Parameters
    ----------
    list_val : list of int
        対象のリスト。
    start : int
        リストの開始位置。
    length : int
        取得件数。

    Returns
    -------
    list_val : list of int
        スライス処理後のリスト。
    """
    list_val = list_val[start:start + length]
    return list_val

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/battle_party_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['second_and_third_powers'] = df['powers'].apply(
    get_sliced_list, start=1, length=2)
print(df[['second_and_third_powers', 'powers']].head(10))
```

```
  second_and_third_powers                        powers
0                 [34983]                [41013, 34983]
1          [29020, 30252]         [26151, 29020, 30252]
2            [8786, 8179]     [10269, 8786, 8179, 6633]
3                      []                       [18561]
4          [50078, 37768]         [34067, 50078, 37768]
5          [34217, 22725]  [25882, 34217, 22725, 21635]
6                      []                       [13968]
7            [1205, 2094]      [1573, 1205, 2094, 2021]
8                      []                       [22217]
9                      []                       [19061]
```

## 一意な値のみに変換した配列を取得する: ARRAY_DISTINCT

`ARRAY_DISTINCT`関数を使うと指定されたカラムもしくは固定値の配列の値から、一意（重複の存在しない）配列を取得できます。

```sql
SELECT ARRAY_DISTINCT(ARRAY [1, 2, 2, 1, 3, 4, 2]) AS unique_array
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/10b98097-bbd3-77aa-3a57-1546a4024125.png)

### Pythonでの書き方

ビルトインのリストであれば一度`set`関数を通すと一意な値になるので、そちらをさらに`list`関数を通してリストに戻すと対応ができます。

```py
from typing import List

unique_list: List[int] = list(set([1, 2, 2, 1, 3, 4, 2]))
print(unique_list)
```

```
[1, 2, 3, 4]
```

NumPyのndarrayなどであれば`unique`関数が用意されています。

```py
import numpy as np

arr: np.ndarray = np.array([1, 2, 2, 1, 3, 4, 2])
arr = np.unique(arr)
print(arr)
```

```
[1 2 3 4]
```

## 配列が特定の値を含むかどうかの真偽値を取得する: CONTAINS

`CONTAINS`関数は配列の値の中に特定の値が含まれているかどうかの真偽値を返します。

以下のSQLではパーティーのユーザーIDの配列内に`25456`のIDが含まれているかどうかの真偽値を取得しています。

```sql
SELECT CONTAINS(user_ids, 25456) AS user_id_25456_contains, user_ids
FROM athena_workshop.battle_party_power
WHERE dt = '2021-01-01'
LIMIT 3;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/ef9b6604-fc65-da58-50ea-d1ea4f7380a5.png)

### Pythonでの書き方

ビルトインのリストなどに対して`in`のキーワードを使うことで真偽値を取得することができます。

```py
print(25456 in [96607, 14806])
```

```
False
```

```py
print(25456 in 	[82053, 25456, 34646])
```

```
True
```

## 2つの配列で値の重複が存在するかの真偽値を取得する: ARRAYS_OVERLAP

`ARRAYS_OVERLAP`関数は2つの配列の値で重複している値が存在するかどうかの真偽値を返却します。それぞれの配列内に欠損値が含まれる場合にはtrueとはなりません。もし配列内の値が欠損値のみになっていれば返却値も欠損値（null）になります。

以下のSQLでは1つ目の配列の値が`1, 2, 3`で2つ目の配列の値が`4, 5, 6`であり、重複している値は存在しないのでfalseとなります。

```sql
SELECT ARRAYS_OVERLAP(ARRAY[1, 2, 3], ARRAY[4, 5, 6]) AS is_overlap
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/0f129e3d-8000-952b-2472-aeb7cb4cd5f0.png)

一方で以下のSQLでは1つ目の配列が`1, 2, 3`、2つ目の配列の値が`3, 4, 5`となっており3が重複しているのでtrueとなります。

```sql
SELECT ARRAYS_OVERLAP(ARRAY[1, 2, 3], ARRAY[3, 4, 5]) AS is_overlap
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/40c5c28d-c281-a37b-68a7-082a610845ba.png)

### Pythonでの書き方

シンプルにループを使って`in`で判定ができます。

```py
from typing import List

arr_1: List[int] = [1, 2, 3]
arr_2: List[int] = [3, 4, 5]

is_overlap: bool = False
for value in arr_1:
    if value in arr_2:
        is_overlap = True
        break
print(is_overlap)
```

```
True
```

が、それぞれのリストが巨大になってくると1件ずつリストに対してinを使うのは結構処理時間が気になるようになってきます。そのためもしも対象が巨大なリストの場合には2つ目のリストの方を辞書に変換しておいて、辞書のキーに存在するかどうかをinで判定するようにすると計算時間をぐぐっと抑えられます（メモリは食いますが）。

```py
from typing import Dict, List

arr_1: List[int] = [1, 2, 3]
arr_2: List[int] = [3, 4, 5]
dict_val: Dict[int, None] = {value: None for value in arr_2}

is_overlap: bool = False
for value in arr_1:
    if value in dict_val:
        is_overlap = True
        break
print(is_overlap)
```

```
True
```

## 配列から特定の値を取り除く: ARRAY_REMOVE

`ARRAY_REMOVE`関数は配列から指定された値を取り除いた配列を返却します。第一引数に対象のカラム名もしくは固定値、第二引数に任意の取り除きたい値を指定します。

以下のSQLでは配列から`2`の値を取り除いているため、結果は`[1, 3]`という配列になります。

```sql
SELECT ARRAY_REMOVE(ARRAY[1, 2, 3], 2) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b732d14c-c1ff-32c9-2cff-9dcfcddb135c.png)

複数の削除対象の値が含まれている場合には全て削除されます。

```sql
SELECT ARRAY_REMOVE(ARRAY[1, 2, 2, 3], 2) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/9e8c24af-8e9d-9645-014a-ba576b5bc35a.png)

### Pythonでの書き方

ビルトインのリストであれは`remove`メソッドで削除が利きます。ただしリストに含まれていないとエラーになるのと1回呼んだだけでは先頭の値しか削除されないのでSQL側と同じようなことをしようとするとwhile文などで対象の値が含まれているかといった判定が必要になります。

```py
from typing import Dict, List

arr: List[int] = [1, 2, 2, 3]
while 2 in arr:
    arr.remove(2)
print(arr)
```

```
[1, 3]
```

## 配列内で指定された値の最初の出現位置を取得する: ARRAY_POSITION

`ARRAY_POSITION`関数は配列内で指定された値が初めて出現する位置を返します。複数対象の値が存在する場合は最初の値の位置のみが返却されます。

例えば以下のSQLでは配列内で`2`の値が配列の何番目に存在するかを確認しています。2番目に存在するので結果は2となります。

```sql
SELECT ARRAY_POSITION(ARRAY[1, 2, 3, 3, 2], 2) AS first_position
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/34b1db12-dc97-4d50-1b67-5a932418a716.png)

配列内に対象の値が存在しない場合返却値は0になります。

```sql
SELECT ARRAY_POSITION(ARRAY[1, 2, 3, 3, 2], 5) AS first_position
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/c4c58e63-6ce7-cb97-3296-6056198db768.png)

※Prestoのドキュメントでは第三引数で何番目の値かどうかが指定できるようになっていますがAthenaではまだ使えないようです。この記事を書いている時点では値が最初に出現する位置しか取れません。将来Athena側のアップデートで変わるかもしれません。

### Pythonでの書き方

ビルトインのリストであれば`index`メソッドで同じようなことができます。他と同様Pythonではインデックスが0からスタートするのでSQLと比べて結果が-1した値になります。

```py
from typing import List

arr: List[int] = [1, 2, 3, 3, 2]
first_position: int = arr.index(2)
print(first_position)
```

```
1
```

## 特定の値が含まれない配列を取得する: ARRAY_EXCEPT

`ARRAY_EXCEPT`関数は2つの配列を用いて、1つ目の配列の中で2つ目の配列に含まれない値の配列を返します。

以下のSQLでは1つ目の配列の値は`1, 2, 3, 3, 2, 4, 5, 5`、2つ目の配列の値は`2, 3`としています。結果的に2と3が取り除かれた`1, 4, 5`の値が結果の配列に設定されます。結果に重複分は含まれません（`1, 4, 5, 5`といったような値は返却されません）。

```sql
SELECT ARRAY_EXCEPT(ARRAY[1, 2, 3, 3, 2, 4, 5, 5], ARRAY[2, 3]) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/f583fc61-4828-0542-510a-29ae3a5787c9.png)

### Pythonでの書き方

ダイレクトに`ARRAY_EXCEPT`関数に該当するビルトイン関数などは無い（？）ためループで回しています。前節と同じようにリストが巨大になった際のことを考えて2つ目の配列は一旦辞書にしています。また、ループの回数を減らすために最初の方で`set`関数を使って配列を一意にしています。2つ目の配列に含まれる値は結果に含めずにループ内で`continue`しています。

```py
from typing import Dict, List

arr_1: List[int] = [1, 2, 3, 3, 2, 4, 5, 5]
arr_2: List[int] = [2, 3]

arr_1 = list(set(arr_1))
arr_2 = list(set(arr_2))

result_list: List[int] = []
dict_value: Dict[int, None] = {value: None for value in arr_2}
for value in arr_1:
    if value in dict_value:
        continue
    result_list.append(value)

print(result_list)
```

```
[1, 4, 5]
```

## 配列の値を連結して文字列を取得する: ARRAY_JOIN

`ARRAY_JOIN`関数は配列の値を特定の文字列で連結した文字列を取得することができます。たとえばコンマで連結すればCSV的な文字列を取得することができます。

第一引数に対象の配列のカラム名もしくは固定値、第二引数には区切り文字を指定します。

以下のSQLではコンマ区切りで配列の値を連結しています。

```sql
SELECT ARRAY_JOIN(powers, ',') AS joined_powers, powers
FROM athena_workshop.battle_party_power
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/53055dfe-06fd-e149-f014-01856ddcbc0e.png)

なお、配列に欠損値（`NULL`）が含まれている場合、その値は結果の文字列には含まれません。

以下のSQLでは`1, NULL, 3`という値の配列を指定していますが、結果は`1,3`とNULLを除いた値になっていることを確認できます。

```sql
SELECT ARRAY_JOIN(ARRAY[1, NULL, 3], ',') AS joined
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/85af5781-7f55-82b4-c046-c104edc3e730.png)

欠損値部分を別の値で埋めたい場合には第三引数を指定します。以下ではNULL部分が取り除かれてしまうのではなく、`NaN`という文字列で置換しています。

```sql
SELECT ARRAY_JOIN(ARRAY[1, NULL, 3], ',', 'NaN') AS joined
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/39cb4d23-b962-f135-c2a8-1eb6dde99b55.png)

### Pythonでの書き方

ビルトインの文字列がリストを引数に取る`join`メソッドを持っているためそちらで対応ができます。ただしリストの値を文字列に先に変換しておく必要があります。

```py
from typing import List

int_arr: List[int] = [1, 2, 3]
str_arr: List[str] = [str(value) for value in int_arr]
joined: str = ','.join(str_arr)
print(joined)
```

```
1,2,3
```

## 配列のソートを行う: ARRAY_SORT

`ARRAY_SORT`関数を使うと配列の値の昇順ソートを行うことができます。

```sql
SELECT ARRAY_SORT(powers) AS sorted_powers, powers
FROM athena_workshop.battle_party_power
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/dfc521f3-7cc6-04d7-8a5f-8543e256c1f1.png)

配列に欠損値（`NULL`）が含まれている場合にはその欠損値は配列の末尾に配置される形でソートされます。

```sql
SELECT ARRAY_SORT(ARRAY[5, 3, 6, NULL, 7, NULL, 10]) AS sorted
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/c81440aa-0e09-9952-6e72-9a110bc9d00f.png)

### Pythonでの書き方

ビルトインのリストであれば`sort`メソッドで昇順ソートすることができます。

```py
from typing import List, Optional

arr: List[int] = [5, 3, 6, 10, 7]
arr.sort()
print(arr)
```

```
[3, 5, 6, 7, 10]
```

もしくはコピーされたリストが欲しければビルトインの`sorted`関数で取得することができます。

```py
from typing import List, Optional

arr_1: List[int] = [5, 3, 6, 10, 7]
arr_2: List[int] = sorted(arr_1)
print(arr_2)
```

```
[3, 5, 6, 7, 10]
```

### ラムダ式（Lambda Expressions）

以降の節の関数ではラムダ式が必要になってきたりするため先に軽く触れておきます。詳細は各関数で必要な分だけ説明を加えていきます。

ラムダ式は無名関数などとも呼ばれたりしますが、特定の値を受け取ってその値を使った返却値を記述することで、独自の関数定義のようなことをすることができます。

`->`の記号を使い、特定の値を引数として左側に記述し、右側に返却値を記述します。例えば`x`という値を引数で受け取って、それを1加算する形の返却値として`x + 1`という表現をしたい場合には`x -> x + 1`という書き方になります。複数の引数（`x`と`y`）を受け付けて1つの返却値（`z`）を返す場合には`(x, y) -> z`といったように書きます。

このラムダ式は関数内でしか使えません。ラムダ式を引数に取る関数があるためそれらの関数で使います。

### ソート条件にラムダ式を使う

`ARRAY_SORT`関数の第三引数にはラムダ式を指定することができ、独自のソート方法を自身で組むことができます。

引数には2つの値が渡されます。ここでは`x`と`y`という引数名を使っています。この`x`と`y`の2つの値での比較条件をラムダ式で書くことで比較条件を配列の各値に反映する形でソートが実行されます。ラムダ式の返却値には1つの整数が必要になり、`-1`, `0`, `1`のいずれかの値のみを受け付けます。`x`と`y`をソート用に比較した時に`x`の方を前にしたい条件であれば`-1`、`x`の方を後にしたい条件であれば`1`、`x`と`y`が同じ位置（同値条件など）になるべき条件であれば`0`を返すようにします。

条件文は`IF`関数を使って書きます。第一引数に条件式（`x > y`など）、第二引数に条件を満たす場合の返却値、第三引数に条件を満たさない場合の返却値を指定します。第二引数や第三引数にはさらに`IF`関数もしくは他の関数などを入れ子にすることもできます。例えばA条件を満たす場合、さらにその中でB条件を満たすか満たさないか・・・といったように複数の条件を入れ子にしていくことができます。

例としてラムダ式を使わない`ARRAY_SORT`の昇順ソートのと同じソートの計算をラムダ式を使って書いてみましょう（第三引数を省略する形で実務では問題無いのですがシンプルな説明用として使いえます）。

以下のようなSQLとなります。

```sql
SELECT ARRAY_SORT(ARRAY[5, 3, 6, 10, 7], (x, y) -> IF(x < y, -1, IF(x = y, 0, 1))) AS sorted
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/cc5b984f-ffad-7b72-b4ac-193df14b2451.png)

ラムダ式部分は`(x, y) -> IF(x < y, -1, IF(x = y, 0, 1)))`が該当します。まず引数部分ですがソートで2つの値が必要になるので`(x, y)`という記述にしています。

`IF`関数の最初の条件と条件を満たす場合の指定ですが`IF(x < y, -1,`としています。これは`x`が`y`よりも小さければ`-1`を返す（`x`が`y`よりも小さければ`x`を`y`よりも前に配置する）という制御になります。

前述の条件を満たさない場合にはさらに`IF`関数を入れ子にして`IF(x = y, 0, 1)`と分岐を書いています。これは、

- `x`が`y`よりも小さいという条件を満たさず、且つ`x`と`y`が同値（`x = y`）である場合には0を返す（`x`と`y`はソートで同じ位置になる）。
- `x`が`y`よりも小さいという条件を満たさず、且つ`x`と`y`が同値（`x = y`）でもない場合には1を返す（`x`が`y`よりも大きいので`x`が`y`の後の位置にする）。

という条件になっています。

今度は降順ソートを書いてみましょう。現在Athenaでは配列を逆順にする`REVERSE`関数が使えない（Presto側のドキュメントには追加されているのでその内アップデートで使える形になると思われます）ため自前で書く必要があります。

やることとしてはシンプルで、`x < y`の箇所を`x > y`と比較演算子の向きを変えているだけです。これで`x`が`y`よりも大きければ前の位置に配置するという挙動になります。

```sql
SELECT ARRAY_SORT(ARRAY[5, 3, 6, 10, 7], (x, y) -> IF(x > y, -1, IF(x = y, 0, 1))) AS sorted
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/cfbe7ddd-83de-bd5c-a2d3-06df32d0ea1a.png)

### ラムダ式と関数などを組み合わせてソートする

`ARRAY_SORT`関数の第二引数のラムダ式には関数などを組み合わせて使うこともできます。例えば文字列の配列に対して文字数でソートしたい・・・というケースには以下のように書くことができます。`x`や`y`の部分を文字数を取得する`LENGTH`関数で囲っています。

```sql
SELECT ARRAY_SORT(
  ARRAY['ねこ', 'うさぎ', 'いぬ', 'おおかみ', 'ひつじ', 'らいおん'],
  (x, y) -> IF(LENGTH(x) < LENGTH(y), -1, IF(LENGTH(x) = LENGTH(y), 0, 1))) AS sorted
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/f3dd0a6b-862f-87ae-434e-ac8592a80fb6.png)

## 配列をシャッフルする: SHUFFLE

`SHUFFLE`関数は配列の内容をランダムにシャッフルします。結果は実行する度に変わります。

```sql
SELECT SHUFFLE(powers) AS shuffled_powers, powers
FROM athena_workshop.battle_party_power
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/f46be9fb-51b2-8b4c-8367-5850a305dd34.png)

### Pythonでの書き方

ビルトインの`random`パッケージの`shuffle`関数でリストなどをシャッフルすることができます。

```py
from typing import List
from random import shuffle

arr: List[int] = [5, 3, 6, 10, 7]
shuffle(x=arr)
print(arr)
```

```
[7, 6, 3, 5, 10]
```

## 2つの配列で双方に存在する値を格納した配列を取得する: ARRAY_INTERSECT

`ARRAY_INTERSECT`関数は第一引数と第二引数に指定された2つの配列間で、両方の配列に存在する値のみを格納した配列を返却します。返却される値は重複の無い形で設定されます。

以下のSQLでは`1, 2, 3`という値の配列と`2, 3, 4`という値の配列の2つを指定しています。2と3部分がそれぞれの配列に存在するため結果は`2, 3`という値の配列となります。片側にしかない値の1と4は含まれません。

```sql
SELECT ARRAY_INTERSECT(ARRAY[1, 2, 3], ARRAY[2, 3, 4]) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/860534b4-4996-398b-7668-c8df22222e17.png)

余談ですが、Prestoのドキュメントを見ていると2次元配列を渡す形での`ARRAY_INTERSECT`関数の記述がありましたがまだAthenaでは使えない？ようです。

### Pythonでの書き方

2つの`set`を`&`記号で繋げばそれぞれの`set`で両方に値があるもののみの`set`が得られるのでそちらをリストにキャストすれば結果のリストが得られます。

以下のコードでは一度リストを`set`関数で`set`に変換しています。

```py
from typing import List

arr_1: List[int] = [1, 2, 3]
arr_2: List[int] = [2, 3, 4]
result: List[int] = list(set(arr_1) & set(arr_2))
print(result)
```

```
[2, 3]
```

## 任意の条件を満たした値のみを格納した配列を取得する: FILTER

`FILTER`関数では特定の条件を満たす値のみを残した配列を返却します。第一引数には対象の配列のカラムもしくは配列の固定値、第二引数にはラムダ式を指定します。ラムダ式には1つの引数（サンプルでは`x`という名前を使います）を受け付け、返却値には（等値や大なりなどの比較表現などによって）真偽値が返るようにします。返却値でtrueが返る条件の箇所のみ結果の配列に残ります。

以下のSQLではラムダ式で`x >= 0`という条件を指定しており、結果的に値が0以上のもののみ結果の配列に含まれるようにしています。

```sql
SELECT FILTER(ARRAY[-5, 3, 2, -1, 0, 8, -3], x -> x >= 0) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/bf0dc531-1023-c718-7297-aad49194b695.png)

### Pythonでの書き方

ビルトインの`filter`関数で同じようなことができます。返却値は`filter`オブジェクトになるのでリストにキャストする処理を加えてあります。

```py
from typing import List

arr: List[int] = [-5, 3, 2, -1, 0, 8, -3]
arr = list(filter(lambda x: x >= 0, arr))
print(arr)
```

```
[3, 2, 0, 8]
```

## 2次元配列を1次元の配列に変換する: FLATTEN

`FLATTEN`関数は以下のような行列（2次元配列）を`1, 2, 3, 4, 5, 6, 7, 8, 9`といったように1次元の配列に変換します。

```math
\begin{array}{ccc}
1 & 2 & 3\\
4 & 5 & 6\\
7 & 8 & 9
\end{array}
```

SQL上では第一引数に2次元配列が必要になります。

```sql
SELECT FLATTEN(ARRAY[ARRAY[1, 2, 3], ARRAY[4, 5, 6], ARRAY[7, 8, 9]]) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b553ac94-ab68-d2d5-2f28-d43244714d73.png)

### Pythonでの書き方

ビルトインのリストの場合は今まで使ったことがなかったのですが`itertools.chain.from_iterable`を使うとビルトインのもので完結する形でストレートにいくようです。

```py
from typing import List
import itertools

arr: List[List[int]] = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
flattened: List[int] = list(itertools.chain.from_iterable(arr))
print(flattened)
```

```
[1, 2, 3, 4, 5, 6, 7, 8, 9]
```

NumPyなどであれば`flattern`メソッドがあるのでそのままそれを呼ぶだけで対応ができます。こちらはデータ方面のお仕事などされている方々はビルトインよりも使う機会が結構あると思います。

```py
import numpy as np

arr: np.ndarray = np.array([
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9],
])
arr = arr.flatten()
print(arr)
```

```
[1 2 3 4 5 6 7 8 9]
```

## 他のカラムの値を残しつつ、配列のネストされたデータを行列にする: UNNEST

ケースによっては配列のカラムではなく行列で扱った方が色々とシンプルな時があります。そういった場合には`UNNEST`関数と`CROSS JOIN`による連結を使うと他のカラムの値を残したまま行列に変換することができます。

指定された配列の値を1行1行展開し、他のカラムの値は一通りそのまま残す（元の値を連結する）みたいな挙動になります。

例えば以下のような行列があったとします。武器のIDのカラムは配列の値を含んでいます。

```math
\begin{array}{rr}
ユーザーID & 武器のID\\
1 & [10, 11]\\
2 & [12, 13]
\end{array}
```

`UNNEST`関数と武器のIDを指定した`CROSS JOIN`を使うと上記の行列が以下のような行列に変換されるイメージです。

```math
\begin{array}{rr}
ユーザーID & 武器のID\\
1 & 10\\
1 & 11\\
2 & 12\\
2 & 13
\end{array}
```

CROSS JOIN自体は以前の記事で触れましたが、組み合わせによる連結を行う都合元の行数よりもぐぐっと行数が多くなります。

参考 :

> 左側のテーブルの行数がN行、右側のテーブルの行数がM行とすると交差結合の結果の行数は$N × M$行となります。

https://qiita.com/simonritchie/items/0d84646e3db940e98b14#%E4%BA%A4%E5%B7%AE%E7%B5%90%E5%90%88%E3%82%AF%E3%83%AD%E3%82%B9%E7%B5%90%E5%90%88-cross-join

今回のUNNEST関数の例で言うとNが元々の行数、Mが対象とする配列の値の件数となります。例えば元々の行が10万行、配列が平均して10件の値を含んでいた場合は10万 × 10で結果は100万行となります。処理に時間がかかったり扱うデータが巨大化したりするケースがあるので扱う際には少し気を付ける必要があります。

書き方としては`CROSS JOIN UNNEST(<対象の配列のカラム名>) AS <UNNESTしたテーブルに付ける名前>(配列の値に付けるカラム名)`みたいな書き方になります。少々独特です。

例えば`battle_party_power`テーブルの`powers`という配列を展開したい場合に、展開後のテーブル名を`unnested_powers`とし、配列の値のカラム名に`power`という名前を付ける場合は`CROSS JOIN UNNEST(athena_workshop.battle_party_power.powers) AS unnested_powers(power)`といったような書き方になります。

実際にSQLで実行してみましょう。

```sql
SELECT * FROM
athena_workshop.battle_party_power
CROSS JOIN UNNEST(athena_workshop.battle_party_power.powers) AS unnested_powers(power)
LIMIT 50
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/0fe0b544-3a39-5145-e434-8b751603141e.png)

SQL実行結果では元々のカラムに加えて連結された`power`というカラムが追加になっていることが分かります。また、`powers`の配列のカラムの各値が1行ずつに展開され、`power`カラム以外のカラムの値はそのまま残っていることが確認できます。

また、UNNEST関数はCROSS JOINなどと一緒に使わない書き方もあります。書き方としてはFROM句にて`<対象のテーブル>, <そのテーブルの配列のカラムに対するUNNESTの指定>`といったように書きます。UNNESTの前にテーブルの指定とコンマが必要になる書き方となります。例えば`athena_workshop.battle_party_power`テーブルの`powers`という配列のカラムに対して使う場合には以下のようになります。

```sql
SELECT * FROM
athena_workshop.battle_party_power, UNNEST(athena_workshop.battle_party_power.powers) AS unnested_powers(power)
LIMIT 50
```

結果はCROSS JOINしたときと似たような形になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/cbe238d0-f209-60a0-2c5e-5707ce6f4f72.png)

ただしこちらの書き方の場合、UNNESTしたものに対して他のJOIN（LEFT JOINやINNER JOINなど）をするとエラーで弾かれるようです。そういったケースではCROSS JOINとUNNESTを使ってからその後にLEFT JOINなどを使うか、もしくはWITH句などでのサブクエリで分割する必要があるようです（将来Athena内部のPrestoのアップデートでLEFT JOINなどもできるようになるかもしれません）。

> Athena is based on Presto .172 LEFT JOIN with UNNEST was added in Presto 319
> ...
> one can rewrite the left join to a cross join with coalesce:

https://stackoverflow.com/questions/63990337/aws-athena-unnest-with-left-join-not-working

例えばクエストでドロップした複数の武器などがクエストのログの行に配列などで格納されていて、武器のIDと武器のマスタをJOINしたい場合が該当します。今回テーブルを追加したりはしませんが、仮に`log_db.quest`というクエストログのテーブルがあり、`dropped_weapons`という配列のカラムが含まれており、且つ`mst_weapon`というマスタテーブルがあると過程するとCROSS JOIN UNNESTと他のJOINを使うと（JOIN部分だけ抽出すると）以下のような書き方になります。

```sql
...
CROSS JOIN UNNEST(log_db.quest.dropped_weapons) AS unnested_weapons(weapon_id)
INNER JOIN mst_weapon
ON unnested_weapons.weapon_id = mst_weapon.id
...
```

辞書を格納した配列に関してはさらに色々と触れることがありますが、そちらは別の辞書について詳しく触れる記事で書いていこうと思います。

### Pythonでの書き方

この辺は大分昔の記事となりますが、Pandasの`json_normalize`関数などが用意されており記事にしていますのでそちらをご確認ください。

https://qiita.com/simonritchie/items/7e50b47ccadbbb0fba57

## 配列をn件ずつの2次元配列に変換する: NGRAMS

`NGRAMS`関数は1次元の配列を2次元の配列に変換します。自然言語方面で結構出てきたりします。

> n-gramとはn個の連続する単位
> 一言で説明すると、「n-gram」とは連続するn個の単語や文字のまとまりを表します。

https://mojitoba.com/2019/09/23/what-is-ngram/

まとまりごとに2次元目の配列化されます。例えば`[1, 2, 3, 4, 5]`という配列に対してn=2で`NGRAM`関数を反映すると、`[[1, 2], [2, 3], [3, 4], [4, 5]]`といった2次元配列に変換されます。n=3で反映すると`[[1, 2, 3], [2, 3, 4], [3, 4, 5]]`といった値になります。

第一引数には対象の配列のカラムもしくは配列の固定値、第二引数にはnの値を指定します。

```sql
SELECT NGRAMS(ARRAY[1, 2, 3, 4, 5], 3) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/11de1e4e-e2c0-cace-342b-1b73338ca0af.png)

### Pythonでの書き方

Pandas自体にはn-gram関係の機能は無さそうですが、`nltk`などの別のライブラリを絡めるとシンプルにいきそうな印象です。試していないのでここではリンクのみ貼っておきます。

https://towardsdatascience.com/from-dataframe-to-n-grams-e34e29df3460

## 配列の各値で計算を行い、計算結果を取得する: REDUCE

`REDUCE`関数は配列の値に対して1つ1つ計算を行い、計算結果を単一の値（次元数が減った状態の値）を取得します。ラムダ式をがっつり使う形となります。

第一引数には対象とする配列のカラム名もしくは配列の固定値、第二引数には初期値、第三引数には各値ごとの入力のラムダ式、第四引数には全体の出力時のラムダ式が必要になります。引数が複雑なので順番に触れていきます。

まずは第二引数の初期値です。これは集計処理の初期値です。0を指定して0からスタートするケースが結構多くなると思います。

第三引数は各値ごとの入力のラムダ式となります。各値ごとに処理がされます。引数はその値までの計算結果（今回の記事では`s`と表記します）と対象の配列の値（今回の記事では`x`と表記します）の2つです。先頭の値に関しては`s`の値は第二引数に指定した初期値になります。

例えば`[2, 3, 4]`という配列に対して処理を行うとして、第二引数の初期値が1だった場合は

- 1回目の処理 -> s = 1, x = 2
- 2回目の処理 -> s = 1回目の処理の計算結果, x = 3
- 3回目の処理 -> s = 2回目の処理の計算結果, x = 4

といったように3回計算がこのラムダ式で実行されます。返却値は1つの値となる必要があります（計算結果として次の処理のsに渡されます）。

第四引数は出力用のラムダ式です。このラムダ式は第三引数とは異なり配列の各値ごとに実行されたりはしません。各値の計算結果の最後の値に対してのみ反映されます（配列の件数に関わらず1回のみ実行されます）。引数は全体の計算結果のsのみ、返却値も1つの値のみとなります。

---

いくつかサンプルのSQLを書いていきます。まずはシンプルに配列の値の合計値を出すという記述を`REDUCE`関数を使ってしてみましょう。前節までで触れた通り、配列の合計値を出す関数自体はPrestoには`ARRAY_SUM`関数が存在しますが、Athenaではまだこの記事を執筆時点では使えない点とサンプルとしてシンプルなので使っていきます。

まずは第二引数の初期値ですが、これは合計値を出す計算なので0を指定します。

続いて第三引数のラムダ式ですが、引数の指定は2つの値が必要になるため`(s, x)`といった記述になります。返却値側は配列の各値の合計を出す必要があるため、前の処理までの計算結果sに対して今回の配列の値xを加えるという形で`s + x`としています。

第四引数の処理に関しては今回は制御が必要ないため、`s -> s`として値をそのまま返却するようにしています。

`1, 2, 3`という3つの値を含んでいる配列に対して試してみます。

```sql
SELECT REDUCE(ARRAY[1, 2, 3], 0, (s, x) -> s + x, s -> s) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b7f55799-5ac8-f155-f678-e80289c47e35.png)

無事配列の合計値の6を得ることができました。

今度は第四引数の出力部分のラムダ式を調整していきましょう。このラムダ式は第三引数のラムダ式とは異なり配列の値1つ1つで計算はされずに最後の計算結果にのみ（最後の1回だけ）反映されます。

以下のSQLでは計算結果に対して4で割ったときの剰余（余り）を`s % 4`という記述で求めています。結果が2になることが確認できます。

```sql
SELECT REDUCE(ARRAY[1, 2, 3], 0, (s, x) -> s + x, s -> s % 4) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/a0d1bf9e-1de3-6413-3a26-0e4bb7d49249.png)

他にも比較演算子を使って比較結果を真偽値を取得したり、関数を挟んだりと色々できます。以下のSQLでは計算結果が5よりも大きいか（`s > 5`）という判定にして結果の真偽値を取得しています。

```sql
SELECT REDUCE(ARRAY[1, 2, 3], 0, (s, x) -> s + x, s -> s > 5) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/140a4bf2-1881-092f-9b09-b0504047a6a2.png)

### Pythonでの書き方

ビルトインの`functools`パッケージに`reduce`関数があり、そちらで同じような制御を行うことができます。第一引数には反映する関数もしくはラムダ式、第二引数にリストなどの値を指定します。

```py
from typing import List
from functools import reduce

arr: List[int] = [1, 2, 3]
result: int = reduce(lambda s, x: s + x, arr)
print(result)
```

```
6
```

## 特定の変換処理を加えた配列を取得する: TRANSFORM

`TRANSFORM`関数も`REDUCE`関数のように配列の値1件1件に対して処理を加えます。ただし結果の値はそのまま配列で返却され、配列の値の件数も基本的に変動しません。「配列の全ての値に対して加工を行う」といった制御に向いています。

第一引数には対象の配列のカラム名もしくは配列の固定値、第二引数には加工処理用のラムダ式を指定します。第二引数のラムダ式には1つの引数（配列内の個々の値）と1つの返却値を受け付けます。今回は`x`という名前を使っています。

以下のSQLでは`1, 2, 3`という値の配列に対して1加算するラムダ式（`x + 1`）を指定しています。結果が`2, 3, 4`となることを確認できます。

```sql
SELECT TRANSFORM(ARRAY[1, 2, 3], x -> x + 1) AS result
```

### Pythonでの書き方

色々書き方がありますが、1つ目としては`map`関数による処理が`TRANSFORM`関数に近い挙動になっています。

```py
from typing import List

arr: List[int] = [1, 2, 3]
arr = list(map(lambda x: x + 1, arr))
print(arr)
```

```
[2, 3, 4]
```

他にもリスト内包表記やもちろん普通にループを回してしまっても良いと思います。

```py
from typing import List

arr: List[int] = [1, 2, 3]
arr = [x + 1 for x in arr]
print(arr)
```

```
[2, 3, 4]
```

## 2つ以上の配列を統合した配列を取得する: ZIP

`ZIP`関数は2つ以上の配列の値をセットにした配列を返します。返却値は`ROW`という型の値を格納した配列になるようです。`ROW`は辞書のような形の型となります（1行分のデータといった形になります）。

実行後のカラム名は`field0`, `field1`, ... と設定されます。

```sql
SELECT ZIP(ARRAY[1, 2, 3], ARRAY['猫', '犬', '兎']) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/e008b5c8-2bb1-36f6-8c38-b157b30d92f5.png)

`ROW`型の値はドット記号（`.`）でアクセスすることができます。例えば`field1`のカラムにアクセスしたい場合には`.field1`といった書き方でその値を参照することができます。

配列へのアクセスは`[]`の括弧を使えば行えるため、例えば配列の最初の`ROW`の値の`field1`にアクセスしたい場合には`[1].field1`といった書き方になります（括弧内のインデックスの数字はAthena（Presto）上では1からのスタートになります）。

```sql
SELECT ZIP(ARRAY[1, 2, 3], ARRAY['猫', '犬', '兎'])[1].field1 AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/f6a55cda-cbc3-d46e-904a-9e91dcf2b483.png)

### Pythonでの書き方

ループなどの際にビルトインの`zip`関数を使うことで複数のリストなどの値をひとまとめにしつつループを行うことができます。結果はタプルで渡されます。

```py
from typing import List

arr_1: List[int] = [1, 2, 3]
arr_2: List[str] = ['猫', '犬', '兎']
for tpl in zip(arr_1, arr_2):
    print(tpl)
```

```
(1, '猫')
(2, '犬')
(3, '兎')
```

タプルはコンマ区切りでそのまま展開することもできるため、それぞれを変数に分けることもできます。

```py
from typing import List

arr_1: List[int] = [1, 2, 3]
arr_2: List[str] = ['猫', '犬', '兎']
for id_, name in zip(arr_1, arr_2):
    print(id_, name)
```

```
1 猫
2 犬
3 兎
```

なお、Prestoのドキュメントには2つの配列に対してラムダ式で処理を加えつつ制御が可能な`ZIP_WITH`という関数もあったのですがこの記事の執筆時点ではまだAthena上では利用できないようです。

# 参考文献・参考サイトまとめ

- [The Applied SQL Data Analytics Workshop: Develop your practical skills and prepare to become a professional data analyst, 2nd Edition](https://www.amazon.com/Applied-SQL-Data-Analytics-Workshop-ebook/dp/B085D91RNK/)
- [[AWS]Athenaのコメントアウトの仕方とフォーマットについて](https://qiita.com/montama/items/637d64dec80d45acec60)
- [Array Functions and Operators](https://prestodb.io/docs/current/functions/array.html)
- [Lambda Expressions](https://prestodb.io/docs/current/functions/lambda.html)
- [pandas.date_range](https://pandas.pydata.org/docs/reference/api/pandas.date_range.html)
- [Pythonでリスト（配列）の要素を削除するclear, pop, remove, del](https://note.nkmk.me/python-list-clear-pop-remove-del/)
- [How to find list intersection?](https://stackoverflow.com/a/3697438)
- [Pythonでflatten（多次元リストを一次元に平坦化）](https://note.nkmk.me/python-list-flatten/)
- [Flatten arrays into rows with UNNEST](https://athena.guide/articles/unnest-arrays-to-rows/)
- [Pandasのjson_normalizeを詳しく調べてみる](https://qiita.com/simonritchie/items/7e50b47ccadbbb0fba57)
- [自然言語処理に出てくるn-gramとはなに？](https://mojitoba.com/2019/09/23/what-is-ngram/)
- [From DataFrame to N-Grams](https://towardsdatascience.com/from-dataframe-to-n-grams-e34e29df3460)
- [Python の基本的な高階関数（ map() filter() reduce() ）](https://www.lifewithpython.com/2013/12/python-higher-order-functions-in-standard-library.html)
- [How to Transform List Elements with Python map() Function](https://www.pythontutorial.net/python-basics/python-map-list/)
- [Data Types](https://prestodb.io/docs/current/language/types.html)
- [AWS Athena unnest with left join not working](https://stackoverflow.com/questions/63990337/aws-athena-unnest-with-left-join-not-working)