概要
総勘定元帳や出納帳、おこづかい帳などの残高欄を算出すSQL文はいくつか考えられる。それぞれのQuerysetをDjangoでどう書くか、またその計算量や記述量を検討した。
背景と課題
総勘定元帳や出納帳は下の表のように、残高は前行の残高に収入が加算、支出が減算され求められる。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 月日 | 摘要 | 収入 | 支出 | 残高 |
| 2 | 1月1日 | 前月繰越 | 10,000 | 10,000 | |
| 3 | 1月2日 | お年玉 | 5,000 | 15,000 | |
| 4 | 1月3日 | コーヒー代 | 980 | 14,020 | |
| 5 | 1月4日 | 昼食代 | 1,420 | 12,600 |
表計算ソフトなら以下のような計算式で実現できる。例えば$E$3の計算式は、
=$E$2+$C$3-$D$3
でよい。しかし、データベースでこれを行うのはあまり奨められない。その理由は考察で述べる。
SQLで残高計算する方法はいくつか考えられる。本稿では、各実現方法について考察する。
実現方法
アプリケーション名をjournal、モデル名をEntryとし、以下の各方法についてSQL文とQuerysetを示す。
- 各レコードの収入と支出の差分を集計する方法
- 収入の集計と支出の集計の差分をとる方法
モデル
モデルは以下のとおりとする。簡便化のため各Entryは1日1Entryとする。本質と関係ない摘要フィールド、各フィールドの引数などは省略する。
class Entry(models.Model):
date = models.DateField(...)
income = models.IntegerField(...)
expence = models.IntegerField(...)
各レコードの収入と支出の差分を集計する方法
SQLとQuersetは以下のとおりである。
SELECT
*,
SUM((COALESCE(income, 0) - COALESCE(expence, 0)))
OVER (ORDER BY date) AS balance
FROM jounal_entry;
queryset = Entry.objects.annotate(balance=Coalesce(F('income'),0)-Coalesce(F('expence'),0)).annotate(balance=Window(expression=Sum('balance'),order_by='date'))
収入の集計と支出の集計の差分をとる方法
SQLとQuersetは以下のとおりである。
SELECT
*,
(COALESCE(SUM(income) OVER (ORDER BY date), 0) -
COALESCE(SUM(expence) OVER (ORDER BY date), 0)) AS balance
FROM journal_entry;
queryset = Entry.objects.annotate(total_income=Coalesce(Window(expression=Sum('income'),order_by='date'),0),total_expence=Coalesce(Window(expression=Sum('expence'),order_by='date'),0)).annotate(balance=F('total_income')-F('total_expence'))
なお、以下のようなSQLも考えられる。この方法ではサブクエリ内で外部参照しているが、DjangoではSubquery内にOuterRefがあってもそれ解決できないため[1]、このSQLを実現するためにはraw()を使わなければいけない。
SELECT
*, (
SELECT
SUM(COALESCE(t2.income,0))-SUM(COALESCE(t2.expence,0))
FROM journal_entry AS t2
WHERE t2.date <= t1.date
) AS balance
FROM journal_entry AS t1
ORDER BY date ASC;
rawqueryset = Entry.objects.raw('select *, (select sum(coalesce(t2.income,0))-sum(coalesce(t2.expence,0)) from journal_entry as t2 where t2.date <= t1.date) as balance from journal_entry as t1 order by date asc')
考察
SQL1とSQL2の計算量はどちらも$O(N^2)$であるが、SQL2のほうが集計ループが1つ多いので現実的にはSQL1のほうが良い。また、Querysetの記述もSQL1の方が記述量は少ない。
SQL3の計算量はSQL2と同じであるが、素のSQLを使うためあまり奨められるものではない[2]。
なお、表計算ソフトの計算式のように、1つ前のレコードの残高をもとに計算する方法は、残高フィールドを持たない方法では実現できない。なぜなら、1つ前のレコードを取得するLAG関数の引数にエイリアスを使うことができないためである。一方、
残高フィールドbalanceを用意し残高をテーブルに保持する方法も考えられる。しかし、この方法ではinsertやupdate、deleteのときにbalanceを再計算し、値を格納しなければいけない。再計算には結局上で述べたいずれかのSQLを発行しなければいけない。また、値の格納には大量のupdateを実行する可能性がある。したがって現実的にはあまり奨められる方法ではない。
まとめ
総勘定元帳や出納帳、おこづかい帳などの残高欄を計算するSQL文とそれを実現するDjangoのQuerisetを検討した。実現方法はいくつかあるが、$O(N^2)$で求められる「各レコードの収入と支出の差分を集計する方法」がもっとも良いことがわかった。