MENU

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

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

1 歯抜けってどういう状況?

家計簿を例に話します。
例えば、出入金のカテゴリが以下のテーブルに様に設定されていたとする。
 
<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

  
 

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

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

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

・外部結合

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

・ 内部結合

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

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

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

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

ifnull(値1, 値2)

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

4 実際こんな感じになりました
 
色々踏まえてみて、こんな感じの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

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


5 これを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
 
 
 
6 参考