【SQL】家計簿のデータの集計から学ぶSQLの基礎
家計簿のデータを1年分、月別に集計するSQLを考えたところ、これができたらSQLの基本はほぼマスターしたと言えるのでではないかと思ったので、そんな話。
DBはPostgreSQLを想定。
また、分かりやすさを優先してテーブル名とカラム名は日本語にしています。
まずは家計簿のデータとして以下のようなテーブル構造とデータを想定。
本当は科目とか必要だと思うけど、SQLの学習で使いたいだけなのでそのあたりは省略。
家計簿
| 日付 | 金額 | 収支区分 |
|---|---|---|
| 2020/01/01 | 1000 | 支出 |
| 2020/01/02 | 3000 | 支出 |
| 2020/01/03 | 5000 | 支出 |
| 2020/01/10 | 150000 | 収入 |
| 2020/01/15 | 10000 | 支出 |
| 2020/02/04 | 1500 | 支出 |
| 2020/02/05 | 2300 | 支出 |
| 2020/02/06 | 8200 | 支出 |
| 2020/02/10 | 150000 | 収入 |
| 2020/02/15 | 15000 | 支出 |
| 2020/03/10 | 150000 | 収入 |
| 2020/03/11 | 7500 | 支出 |
| 2020/03/12 | 6300 | 支出 |
| 2020/03/13 | 4500 | 支出 |
| 2020/03/15 | 3300 | 支出 |
このデータから、以下のような集計結果を得たい。
集計結果
| 月 | 収入 | 収入累計 | 支出 | 支出累計 | 差額 | 差額累計 |
|---|---|---|---|---|---|---|
| 01 | 150000 | 150000 | 19000 | 19000 | 131000 | 131000 |
| 02 | 150000 | 300000 | 27000 | 46000 | 123000 | 254000 |
| 03 | 150000 | 450000 | 21600 | 67600 | 128400 | 382400 |
| 04 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 05 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 06 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 07 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 08 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 09 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 10 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 11 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
| 12 | 0 | 450000 | 0 | 67600 | 0 | 382400 |
どうすればこのような結果を得られるか、SQL文を考えてみましょう。
これができれば、SQLの基礎はほぼマスターしていると考えて良いでしょう。
テストデータの作成
テストデータ作りたい場合は以下のSQLで作成可能。
必要であれば下記SQLでテーブルとデータを作成して、実際に動かして確かめてください。
create table 家計簿 as ( select '2020/01/01' 日付, 1000 金額, '支出' 収支区分 union all select '2020/01/02' 日付, 3000 金額, '支出' 収支区分 union all select '2020/01/03' 日付, 5000 金額, '支出' 収支区分 union all select '2020/01/10' 日付, 150000 金額, '収入' 収支区分 union all select '2020/01/15' 日付, 10000 金額, '支出' 収支区分 union all select '2020/02/04' 日付, 1500 金額, '支出' 収支区分 union all select '2020/02/05' 日付, 2300 金額, '支出' 収支区分 union all select '2020/02/06' 日付, 8200 金額, '支出' 収支区分 union all select '2020/02/10' 日付, 150000 金額, '収入' 収支区分 union all select '2020/02/15' 日付, 15000 金額, '支出' 収支区分 union all select '2020/03/10' 日付, 150000 金額, '収入' 収支区分 union all select '2020/03/11' 日付, 7500 金額, '支出' 収支区分 union all select '2020/03/12' 日付, 6300 金額, '支出' 収支区分 union all select '2020/03/13' 日付, 4500 金額, '支出' 収支区分 union all select '2020/03/15' 日付, 3300 金額, '支出' 収支区分 )
解答
結論から書くと、以下のSQLで結果が得られる。
select カレンダー.月 , coalesce(収入, 0) 収入 , sum(収入) over(order by カレンダー.月) 収入累計 , coalesce(支出, 0) 支出 , sum(支出) over(order by カレンダー.月) 支出累計 , coalesce(差額, 0) 差額 , sum(差額) over(order by カレンダー.月) 差額累計 from ( /* 1年の月を表すインラインビュー */ select '01' 月 union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '10' union all select '11' union all select '12' ) カレンダー left join ( /* 月ごとの支出と収入の集計結果を取得するインラインビュー */ select substr(日付, 6, 2) 月 , sum(case when 収支区分 = '支出' then 金額 else 0 end) 支出 , sum(case when 収支区分 = '収入' then 金額 else 0 end) 収入 , sum(case when 収支区分 = '収入' then 金額 when 収支区分 = '支出' then 金額 * -1 end) 差額 from 家計簿 where 日付 between '2020/01/01' and '2020/12/31' group by substr(日付, 6, 2) ) 家計簿集計 on カレンダー.月 = 家計簿集計.月 order by カレンダー.月
解説1
ここからは解説。 まずは月ごとの支出と収入の集計結果を取得するSQLを解説。
/* 月ごとの支出と収入の集計結果を取得するインラインビュー */ select substr(日付, 6, 2) 月 /* case式を使った集計 */ , sum(case when 収支区分 = '支出' then 金額 else 0 end) 支出 , sum(case when 収支区分 = '収入' then 金額 else 0 end) 収入 , sum(case when 収支区分 = '収入' then 金額 when 収支区分 = '支出' then 金額 * -1 end) 差額 from 家計簿 /* 1. 範囲指定による絞り込み */ where 日付 between '2020/01/01' and '2020/12/31' /* 2. 月単位での集約 */ group by substr(日付, 6, 2)
この部分だけを実行した結果は以下になります。
| 月 | 支出 | 収入 | 差額 |
|---|---|---|---|
| 01 | 19000 | 150000 | 131000 |
| 02 | 27000 | 150000 | 123000 |
| 03 | 21600 | 150000 | 128400 |
1. 範囲指定による絞り込み
1年間のデータを月ごとに集計したいので、まずは1年分のデータで絞り込みをします。
範囲指定での絞り込みはbwtweenを使います。
2. 月単位での集約
次にgroup byです。
データを集計する場合にはgroup by を使用します。
月単位で集約化したいですが、日付のデータは2020/01/01のように年月日で保持しているので、少し工夫が必要です。
substrは第一引数の文字列から部分文字列を取得する関数です。
ここでは6番目から2文字を切り取ることによって、YYYY/MM/DDのMMの部分を取得しています。
今回のサンプルでは日付を文字型で保持しているため、substrだけで対応できましたが、date型などでデータを保持している場合は、to_char関数などを使って一度文字列に変換してあげる必要があります。
3. case式を使った集計
数値を集計する場合はsum関数を使用します。
ただし、単純に金額をsumの引数にすすると、支出と収入を全て合計した金額になってします。
この問題はsunの中でcase式を使うことで解決できます。
case式で支出の場合だけ金額を返し、それ以外の場合は0を返すことで、支出だけの合計を取得することができます。
収入の場合も同様です。
また、支出には-1を掛けて負の値にすることで、収入 - 支出 で差額を出すことが可能です。
解説2
続いては月を表すSQLの解説。
select '01' 月 /* テーブルを使用しないselect文 */ union all /* unionによるレコードの加算 */ select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '10' union all select '11' union all select '12'
このSQLの実行結果は以下になります。
| 月 |
|---|
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 06 |
| 07 |
| 08 |
| 09 |
| 10 |
| 11 |
| 12 |
これは、家計簿のデータが1年分ない場合でも、強制的に12か月分のデータを表示させるための仮想テーブルと考えて良いでしょう。
サンプルのようにデータが1～3ヵ月分までしかない場合、4～12ヵ月分のデータは取得できません。
それを無理矢理12か月分のデータを取得するために対処として上記のSQLを使用します。
4. テーブルを使用しないselect文
select文は必ずしもテーブルを使用する必要はありません。
select 'ABC'
や
select 100 + 200
のように、from句を使用せずに値だけを指定したり、演算や関数を使った結果を取得することができます。
ただし、この書き方はDB製品に依存します。
Oracleの場合は
select 'ABC' from dual
のようにdualというダミーテーブルを指定する必要があります。
5. unionによるレコードの加算
select文1 union select文2
とすることで、select文1とselect文2のそれぞれの結果のレコードを合算することができます。
サンプルでは'01'から'12'までのselectをunionすることで、12か月分のレコードを作成しています。
ちなみにサンプルではunionではなくunion all にしています。
unionは、レコードに重複があった場合は重複を排除します。
union allはレコード重複も排除せずそのまま出力します。
今回はどちらを使用しても結果は変わりません。
ただし、unionとunion allを比較した場合、union allの方が重複削除の処理がない文高速になります。
そのため、データの重複がないことが明らかな場合はunion allを使用する方が良いでしょう。
解説3
続いては全体のSQLの解説。
select カレンダー.月 /* 8. coalesce関数でnullを回避する */ , coalesce(収入, 0) 収入 /* 9. Window関数で累計を出力する */ , sum(収入) over(order by カレンダー.月) 収入累計 , coalesce(支出, 0) 支出 , sum(支出) over(order by カレンダー.月) 支出累計 , coalesce(差額, 0) 差額 , sum(差額) over(order by カレンダー.月) 差額累計 from ( /* サブクエリとインラインビュー */ /* 解説2のSQL */ /* 中身省略 */ ) カレンダー left join /* 2つのインラインビューを結合する */ ( /* サブクエリとインラインビュー */ /* 解説1のSQL */ /* 中身省略 */ ) 家計簿集計 on カレンダー.月 = 家計簿集計.月 /* 並び順を指定 */ order by カレンダー.月
6. サブクエリとインラインビュー
このSQLでは、解説1で作成したSQLと解説2で作成したSQLを使用します。
このように、select文の中で別のselect文を使用することをサブクエリ（副問い合わせ）と言います。
サブクエリは、where句の中やfrom句の中、select句の中などで使用可能です。
特にfrom句の中で使用する場合、from句の中のselect文の結果をインラインビューと呼ぶことがあります。
7. 2つのインラインビューを結合する
12か月分のデータに、集計した結果を結合することで、データがある場合もない場合もデータを表示できるようになります。
結合は大きく内部結合(inner join)と外部結合(left(right) join)の2つに分かれます。
内部結合の場合、結合されるそれぞれのテーブルで結合可能なレコードのみが表示されます。
外部結合の場合、結合ができなかった場合でも、軸となったテーブル（内部表）のレコードが表示されます。
今回のサンプルの場合、内部結合にしてしまうと家計簿が存在するデータのみしか表示されなくなりますが、1年分の月が表示されるレコードを元にleft join することで、家計簿のデータがなかったとしても強制的に1年分のデータを表示することができます。
8. coalesce関数でnullを回避する
今回のサンプルデータの場合、1～3月分のデータしかないため、4～12月分の収入と支出と差額は、普通に取得するとnullが出力されます。
しかしプログラムと連携する場合などは、nullではない方が何かと扱いやすい場合も多いです。
その場合は、coalesce関数を使用することでnullの場合は0に変換することができます。
ただしnull回避の関数はDB製品によって異なるので注意が必要。
9. Window関数で累計を出力する
累計を出力するにはWindow関数を使用します。
Window関数はgroup byをせずに集約結果を出力することができる関数で、Oracleだと分析関数とも呼ばれます。
sumやmax,countなど、group by と共に使用する集約関数の他、ランキングを求めるrank関数や連番を求めるrow_number関数などがあります。
細かい使い方は他のサイトやマニュアルなどに譲りますが、とりあえず累計を求める場合、sumのWindow関数を使うことで実現できます。
10. 並び順を指定
最後にorder by で並び順を指定
応用編
あーいうSQLをサクッと書けるようになるとSQLはそれなりに身に付いていると言えるでしょう。
がしかし、プログラム言語のソースコードにあのSQLを入れ込むのはさすがに少し見にくいかもしれません。
という事で、ここからは改良できるポイントがないか、SQLの知識をもう少し踏み込んで解説。
ビューを使ってみる
まず、あのSQL文の中でどの部分が冗長か考えると、1月から12月までの月を表すインラインビューの部分でしょう。
select句とunionで毎回改行していたらそれだけで24行分になります。
さすがに冗長に感じますね。
という事で、ビューという仮想的なテーブルを作成して、全体のSQLを短くしてみます。
create view dummy_month as ( select '01' 月 union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all select '07' union all select '08' union all select '09' union all select '10' union all select '11' union all select '12' );
これでdummy_monthという名前でビューが作成されました。
select * from dummy_month;
とすることで同じ結果が得られます。
ビューはあくまでも仮想的なテーブルで、通常のテーブルとは別物です。
一般にはデータを更新することはできません（条件によっては可能）。
また、ビューを作成するとSQL文が短くなりますが、DBの内部ではビューの定義であるselect文を実行しているに過ぎないため、速度は変わりません。
元のSQLでleft join しているところをこのビューに書き換えてみます。
select カレンダー.月 , coalesce(収入, 0) 収入 , sum(収入) over(order by カレンダー.月) 収入累計 , coalesce(支出, 0) 支出 , sum(支出) over(order by カレンダー.月) 支出累計 , coalesce(差額, 0) 差額 , sum(差額) over(order by カレンダー.月) 差額累計 from dummy_month カレンダー left join ( /* 月ごとの支出と収入の集計結果を取得するインラインビュー */ select substr(日付, 6, 2) 月 , sum(case when 収支区分 = '支出' then 金額 else 0 end) 支出 , sum(case when 収支区分 = '収入' then 金額 else 0 end) 収入 , sum(case when 収支区分 = '収入' then 金額 when 収支区分 = '支出' then 金額 * -1 end) 差額 from 家計簿 where 日付 between '2020/01/01' and '2020/12/31' group by substr(日付, 6, 2) ) 家計簿集計 on カレンダー.月 = 家計簿集計.月 order by カレンダー.月
元のSQLよりかなり短くなりました。
家計簿テーブルの集計結果もビューとして保存しておくことでより短いSQLになると考えるかもしれません。
しかし、家計簿はwhere句で条件の絞り込みをしているので、ビューにすることは難しいでしょう。
where句を含めてビューを作成する場合、この例で言うと2020年のデータしか取得できません。
whereを指定せずに集計して、集計結果から絞り込んでも同じ結果を得ることができますが、その場合データ件数によってはレスポンスが低下する恐れがあるためお勧めできません。
