More than 3 years have passed since last update.

家計簿のデータ集計から学ぶSQLの基礎

Last updated at 2021-05-16Posted at 2020-06-28

家計簿のデータを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で作成可能です。

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の基本は十分身に付いているのではないかと思います。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up