【SQL】データ分析で便利な集計関数 JSON_ARRAYAGG()

# サンプルクエリ
Billingテーブルは、顧客がいついくらの課金を行ったかを記録しています。

(レコードのサンプル)
```text
+------+-------------+--------+---------------+
| id   | customer_id | amount | purchase_date |
+------+-------------+--------+---------------+
|    1 |           1 |   1980 | 2024-06-01    |
|    2 |           2 |  20000 | 2024-06-01    |
|    3 |           2 |   1980 | 2024-06-02    |
|    4 |           3 |   3000 | 2024-06-03    |
|    5 |           1 |   1980 | 2024-06-04    |
+------+-------------+--------+---------------+
```

このようなテーブルで、「顧客ごとに購入日と金額を分析したい」というとき、JSON_ARRAYAGGという関数を使うととても便利です。実際にどんな結果が得られるのか試してみましょう。

```sql
SELECT 
customer_id "顧客ID", 
JSON_ARRAYAGG(amount) "課金額", 
JSON_ARRAYAGG(purchase_date) "購入日"
FROM billing
GROUP BY customer_id
;
```
↓実行
```text
+----------+---------------+------------------------------+
| 顧客ID    | 課金額         | 購入日                       |
+----------+---------------+------------------------------+
|        1 | [1980, 1980]  | ["2024-06-01", "2024-06-04"] |
|        2 | [20000, 1980] | ["2024-06-01", "2024-06-02"] |
|        3 | [3000]        | ["2024-06-03"]               |
+----------+---------------+------------------------------+
```

このように、JSON_ARRAYAGG()はグループ化された単位で特定のカラムを配列として返却する式となります。

:::note
だいたいどのSQLでもJSON_ARRAYAGG()はサポートしており、基本的な文法も同じです。オーダーの指定など、オプショナルな記述については各SQLのドキュメントを参照してください。
:::

# バッチ処理と相性◎
普通、こういったファクトデータは大量にあります。それこそ、数万行ではきかず、数十万、数百万行に及ぶこともあります(数億行とかのレベルならリレーショナル・データベースからの卒業を真剣に考えましょう。)

当然、なんのLIMITもなしにクエリすると処理を行う(アプリケーション)サーバーのメモリが一瞬で容量オーバーになり、最悪サーバーが止まってしまうこともあります。

```sql:リミットをかけただけのクエリ
SELECT customer_id "顧客ID", amount "課金額" , purchase_date "購入日"
FROM billing
LIMIT 5000 OFFSET 0
;

SELECT customer_id "顧客ID", amount "課金額" , purchase_date "購入日"
FROM billing
LIMIT 5000 OFFSET 5000
;

-- 以下オフセットをバッチサイズごとにインクリメントしていく
```

ただ、これだと顧客IDごとにグルーピングされていないので、プログラムの方が煩雑になってしまいます。プログラムは処理が終わったデータを破棄してメモリを空けるという別の責任もあるので、それも考えると非常に面倒です。

一方GROUP BY句とJSON_ARRAYAGG()を利用したクエリでは、1行が顧客ID単位で返却されるのでLIMIT,OFFSETは当然顧客IDごとにきれいに割れますし、

1)ループが楽
2)プログラム側のzip関数で課金額と課金日を二重配列化(+構造体としてインスタンス化)することで使い勝手も◯
3)ループごとに不要になったデータを捨てられる

などなど、使っていて心地よいデータ構造を提供してくれます。

# JSON_OBJECT()とのコラボ
上記ではzipによる二重配列化が若干面倒でしたが、JSON_OBJECT()を用いるとSQL側でJSONオブジェクトを生成し、それを返却することが可能です。

```sql
SELECT 
    customer_id "顧客ID",
    JSON_ARRAYAGG(JSON_OBJECT('課金額', amount, '購入日', purchase_date)) "課金データ"
FROM
    billing
GROUP BY customer_id
;
```
↓実行結果
```text
+----------+---------------------------------------------------------------------------------------------------+
| 顧客ID   | 課金データ                                                                                        |
+----------+---------------------------------------------------------------------------------------------------+
|        1 | [{"課金額": 1980, "購入日": "2024-06-01"}, {"課金額": 1980, "購入日": "2024-06-04"}]              |
|        2 | [{"課金額": 20000, "購入日": "2024-06-01"}, {"課金額": 1980, "購入日": "2024-06-02"}]             |
|        3 | [{"課金額": 3000, "購入日": "2024-06-03"}]                                                        |
+----------+---------------------------------------------------------------------------------------------------+
```

これでもはや、JSONをパースするだけである程度構造体として扱えるようになります。プログラム側でドメインモデルや値オブジェクトとして扱わなくてもいい程度のデータであれば、JSONオブジェクトとして楽に扱えます。

# appendix1: group_concatではだめなのか?

場合によってはありです。例えば、ただ「顧客IDごとに日付の配列がほしい」という場合、GROUP_CONCAT()で十分です(パース処理は頑張るとして...)

```sql
SELECT 
	customer_id "顧客ID",
    GROUP_CONCAT(purchase_date) "購入日"
FROM
    billing
GROUP BY customer_id
;
```
↓実行結果
```text
+----------+-----------------------+
| 顧客ID    | 購入日                 |
+----------+-----------------------+
|        1 | 2024-06-01,2024-06-04 |
|        2 | 2024-06-01,2024-06-02 |
|        3 | 2024-06-03            |
+----------+-----------------------+
```

ただし、**GROUP_CONCATの最大文字数設定を上回ると文字が途中で切れる**ことに注意です。GROUP_CONCATをこういった集計系のクエリで使う際はSQLの設定も合わせて確認するといいと思います。

# appendix2: 複数のJSON_ARRAYAGG()をSELECTで使用したときに行ごとの順列は保持されるのか?

上記の例だと、customer_idが1のレコードで、課金額や購入日の対応が崩れてしまうケースは発生するのか?ということですが、どうやら発生する可能性はありそうです。(集約時に順序が保持されない)

https://dba.stackexchange.com/questions/319287/do-multiple-mysql-json-arrayaggs-preserve-order

https://dev.mysql.com/doc/refman/8.0/ja/aggregate-functions.html#function_json-arrayagg

なので、万全を期すのであれば、最初から単一の集計関数(とJSON_ARRAY式)を使って二重配列としてクエリするのが良さそうですね。

```sql
SELECT 
    customer_id "顧客ID",
    JSON_ARRAYAGG(JSON_ARRAY(purchase_date, amount)) "課金データ"
FROM
    billing
GROUP BY customer_id
;
```

ただ、mysql5.7、8.0で実際のクエリや自動テストなどでテストを行っていても順列がめちゃくちゃになるケースはまだ発生したことがありません。もしこのあたりの挙動についてご存知な方がいらっしゃればコメントで教えていただけると非常に助かります...。