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

こんにちは、くまごろーです。
また家計簿アプリの話です。
「特定の月の、カテゴリ毎の合計金額一覧を算出」したかったのですが、普通にやると、その月に記録が無いカテゴリは無視されてしまいます。いわゆる「歯抜け」をどう解消するのか、中々苦戦したのでここでまとめます。
　
　

１　歯抜けってどういう状況？

家計簿を例に話します。
例えば、出入金のカテゴリが以下のテーブルに様に設定されていたとする。
　
＜categoriesテーブル＞

category_id	category_code	subcategory_name
101	1	食費
102	1	食料品
103	1	朝食
104	1	昼食
105	1	夕食
106	1	カフェ
107	1	外食
108	1	その他食費
201	2	日用品
202	2	子育て用品
203	2	ペット用品
204	2	タバコ
205	2	その他日用品
301	3	電車
401	4	飲み会
501	5	レジャー
601	6	新聞
701	7	衣服
801	8	フィットネス
901	9	携帯電話
1001	10	水道
1101	11	家賃
1201	12	自動車
1301	13	所得税・住民税
1401	14	旅行
1501	15	引き落とし
1601	16	貯金
1701	17	未分類
9901	99	給与

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

record_id	category_id	income_and_expense（金額）	record_date（日付）	user_id
5	101	44444	2020-11-12	xxx
11	101	2222	2020-12-12	xxx
18	2001	8888	2020-12-29	xxx
30	9901	666666	2020-12-16	xxx
31	204	4466	2020-12-23	xxx

　
category_codeを基準にして、category_code毎の合計金額を算出したいとします。
出入金テーブルだけに対してsum関数を使用すると、↓みたいに出入金記録のあるcategory_codeしか表示されないんだよね。
　

sum(income_and_expense)	category_code
2322	1
666666	99
4466	2

２　外部結合（left join, right join）でカテゴリテーブルと出入金テーブルを結合させる。

（１）今回の問題点
なんでうまくいかないんだろう？と思ったけど、答えは明白でした。
　
・出入金テーブルにのみsum関数をかけても、当該テーブルには出入金記録のあるcategory_idしか記録がないので、記録のないカテゴリは認識されない。
・カテゴリ一覧の形式で結果を取得したいのなら、カテゴリテーブルから情報を持ってくる必要がある。

　
（２）外部結合と内部結合の違い
それでたどり着いたのが「テーブル結合」なんですが、外部結合と内部結合って何が違うんだろう？
調べてみたらこんな感じでした。

・外部結合

2つのテーブルをくっつけて1つのテーブルっぽくする際のやり方のひとつで、基準となるテーブルにデータがあれば、もう一方のテーブルになくても取り出すやり方

・内部結合

2つのテーブルをくっつけて1つのテーブルっぽくする際のやり方のひとつで、両方のテーブルの合体可能なデータのみを取り出すやり方

https://wa3.i-3-i.info/diff294db.html
　
　
（３）今回は外部結合を使用します
今回は、「出入金テーブルには無い情報をカテゴリテーブルから引っ張ってくる」のが目的なので、外部結合を使用することになります。
　
＜left join関数の構文例＞

SELECT * FROM table1
  LEFT JOIN table2
  ON table1.col1 = table2_col1;

外部結合を行う(LEFT JOIN句, RIGHT JOIN句) | MySQLの使い方
　
　
　
３　ifnull関数で、出入金記録の無いカテゴリの金額を「0」に設定
　
このままだと、記録の無いカテゴリの合計額が「NULL」になってしまうので、ifnull関数で「0」を代入します。
　
＜ifnull関数の構文例＞

ifnull(値1, 値2)

※　1 番目の引数の値が NULL だった場合、 2番目の引数の値を返す。
MySQL の IFNULL 関数を SUM 関数と組み合わせるといい感じに集計してくれる | tamulab.jp
　
　　

４　実際こんな感じになりました
　
色々踏まえてみて、こんな感じのsql文に。

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

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

ここで注意すべきなのが、絞込の条件をON句に書くか、WHERE句に書くかです。
簡潔に言うと、

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

今回の場合、何が起こりうるかというと、

・出入金記録の無いカテゴリーには、当然ユーザーID・登録日等の記録もない
・WHERE句でユーザーID等の絞込をかけると、記録の無いカテゴリは除外されてしまう。

こんな現象が起きてしまいます。

この話の詳細は、こちらのページでわかりやすく説明されています。
JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件 - なからなLife

　
で、紆余曲折あって、この様な結果になりました。

category_code	ifnull(sum(income_and_expense), 0)
1	98863
2	6332
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
99	666666

　
連番の歯抜けを解消して、カテゴリ毎の合計金額を表示させることができました！
　

５　これをSpring JPAに落とし込むと？
　
上記のSQL文を元に、Spring JPAの方でメソッドを用意。
今回もネイティブクエリで行きました。

＜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でネイティブクエリを使用し、別エンティティに結果を代入する方法は、以下のページでまとめています。
kumagoro-95.hatenablog.com
　
　
　
６　参考

JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件 - なからなLife

外部結合を行う(LEFT JOIN句, RIGHT JOIN句) | MySQLの使い方

MySQL の IFNULL 関数を SUM 関数と組み合わせるといい感じに集計してくれる | tamulab.jp