【SpringBoot】集計関数使用時に生じる、連番の歯抜けを解消する【MySQL】

#はじめに
現在、SpringBootで家計簿アプリを作成しているのですが、Spring Data JPAでの、集計関数使用時のデータ受け渡しが難しかったのでまとめます。

#今回やりたいこと

Spring Data JPAで、カテゴリ毎の合計金額を集計し、その結果を取得したい。


#歯抜けとは？
例えば、出入金のカテゴリが以下のテーブルに様に設定されていたとする。
　
＜categoriesテーブル＞

|category_id |category_code(大項目用コード) |subcategory_name |
|:-----------:|:-----------:|:--------------:|
|         101 |             1 | 食費 |
|         102 |             1 | 食料品           |
|         201 |             2 | 日用品           |
|         301 |             3 | 電車             |
|         401 |             4 | 飲み会           |
|         501 |             5 | レジャー         |
|         601 |             6 | 新聞             |
|        9901 |            99 | 給与             |


で、以下の様な出入金記録があるとします。
　
＜moner_recordsテーブル＞

|record_id |category_id |income_and_expense（金額） |record_date（日付） |user_id|
|:-----:|:-----:|:-----:|:-----:|:-----:|
|         5 |         101 |              44444 |2020-11-12  |xxx|
|        11 |         102 |               2222 |2020-12-12  |xxx|
|        30 |        9901 |             666666 | 2020-12-16 |xxx|
|        31 |         201 |               4466 |2020-12-23  |xxx|
　
　
```category_code``` 毎の合計金額を算出したい時、ただsum関数を使用しただけだと、下図のように、<b>出入金記録のあるcategory_codeしか表示されない。</b>

|sum(income_and_expense) |category_code|
|:------:|:-----:|
|                    2322 |             1 |
|                    4466 |             2 |
|                  666666 |            99 |

　
これが<b>歯抜け</b>の状態です。
本当は、下記のようなデータを取得したい。

|sum(income_and_expense) |category_code|
|:------:|:-----:|
|                    2322 |             1 |
|                    4466 |             2 |
|                    0 |             3 |
|                    0 |             4 |
|                  0 |            5 |
|     0|    6|
|                  666666 |            99 |


 

#今回の問題点
最初かなり悩みましたが、答えは簡単でした。

＜問題点＞

- 出入金テーブルに記録のない（合計が0円の）カテゴリは認識されない。

＜解決策＞

- 記録のないカテゴリも含め、一覧形式で結果を取得したいのなら、<b>別テーブルから情報を持ってくる必要がある。</b>
 
 
 
#解決方法
##１　外部結合で、別テーブルからカテゴリ情報を持ってくる。
出入金テーブルとカテゴリテーブルを外部結合して、カテゴリ一覧が取得できるようにする。

※　外部結合の説明についてはこちら：[外部結合を行う(LEFT JOIN句, RIGHT JOIN句](https://www.dbonline.jp/mysql/join/index2.html)
　
＜left join関数の構文例＞

```sql
SELECT * FROM table1
  LEFT JOIN table2
  ON table1.col1 = table2_col1;
```
　　
　
##２　ifnull関数で、出入金記録の無いカテゴリの金額を「0」に設定
　
このままだと、出入金テーブルに記録の無いカテゴリは合計額が「NULL」になってしまうので、ifnull関数で「0」を代入する。
　
＜ifnull関数の構文例＞

```sql

ifnull(値1, 値2)
```

※　1 番目の引数の値が NULL だった場合、 2番目の引数の値を返す。
[MySQL の IFNULL 関数を SUM 関数と組み合わせるといい感じに集計してくれる | tamulab.jp] (https://tamulab.jp/mysql-sum-with-ifnull-method/)
　
　　

##３　実際のSQL文
　
上記を踏まえた結果、こんな感じのsql文に。

```sql

//集計関数にifnull関数を適用
select category_code, ifnull(sum(income_and_expense), 0) from categories C
//left joinでテーブルを結合
//ON句で条件を絞る
left join money_records M on M.user_id = 'xxx' 
and M.record_date like '2020-12%' and C.category_id = M.category_id
//category_codeでグループ分け 
group by category_code order by category_code asc;
```

<b>※　外部結合する際の、ONとWHEREの使い分け</b>

絞込の条件をON句に書くか、WHERE句に書くかで結果が変わってくるらしい。

- <b>ON句に記載すると、結合前のレコードのテーブルに対して、絞り込みをかける。</b>
- <b>WHERE句に記載すると、結合後のテーブルから絞り込む。</b>

今回の場合、WHERE句に条件を書いてしまうと、出入金テーブルに記録のないカテゴリは除外されてしまうので、必ずON句に記載する。

この話の詳細は、このページがわかりやすかったです。
[JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件 - なからなLife](https://atsuizo.hatenadiary.jp/entry/2016/12/12/163921:title)

　
 
 
#これをSpring Data JPAに落とし込む
　
上記のSQL文を元に、Spring Data JPAの方でメソッドを用意。
ネイティブクエリで作成しました。

```java:MoneyRecordRepository.java
public interface MoneyRecordRepository extends JpaRepository<MoneyRecord, Long> {
  // 特定の月の、カテゴリー毎の合計を算出
  @Query(value = "select category_code, ifnull(sum(income_and_expense), 0) "
	+ "from categories C left join money_records M "
	+ "on M.user_id = :username and M.record_date like concat(:month, '%') and C.category_id = M.category_id "
	+ "group by category_code order by category_code asc", nativeQuery = true)
  public List<Object[]> getCategorySummaries(@Param("username") String username, @Param("month") String month);

  default List<SummaryByCategory> findCategorySummaries(String username, String month) {
    return getCategorySummaries(username, month)
      .stream()
      .map(SummaryByCategory::new)
      .collect(Collectors.toList());
  }
}
```
　
Spring JPAでネイティブクエリを使用し、別エンティティに結果を代入する方法は、以下のページでまとめています。
[【Spring Boot】Spring Data JPAで集計処理を行い、その結果を別エンティティで受け取る](https://kumagoro-95.hatenablog.com/entry/2021/01/05/170711)
　
　
　
#参考

- [JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件 - なからなLife](https://atsuizo.hatenadiary.jp/entry/2016/12/12/163921)

- [外部結合を行う(LEFT JOIN句, RIGHT JOIN句) | MySQLの使い方](https://www.dbonline.jp/mysql/join/index2.html)

- [MySQL の IFNULL 関数を SUM 関数と組み合わせるといい感じに集計してくれる | tamulab.jp](https://tamulab.jp/mysql-sum-with-ifnull-method/)