日付でソートして売上を表示するとして、その日付になるまでの累計(累積)売上金額を表示したいとする。

そういう時はWindowを使えば、累計(累積)値を表示させることができる。

ソースコード

前回の月ごとに売上を表示するコードから流用。ビューとテンプレートの処理だけ書き換える。

from django.db.models import Sum,Window,F

menus   = Menu.objects.annotate(accumulate=Window(Sum("amount"), order_by=[ F("date").desc(),F("id").asc() ] ) ).values("id","name","date","amount","accumulate").order_by("-date","id")

累積計算時(Windowの中)のorder_byとvaluesを実行した後のorder_byは揃えなければならない点に注意。Windowの中のorder_byは若干仕様が変わっている。複数のフィールドを指定したい場合は上記のようにリスト型で書く。

今回はdateはDateFieldを使用しており、同日に複数のレコードが存在する可能性があるため、idでもソーティングを行った。これにより、同日のレコードをまとめて加算されてしまう問題がなくなる。

動かすとこうなる

まず、先ほどのクエリを実行したときの処理結果。綺麗に累積計算できている。

続いて、order_byにdateしか指定していないパターン。同日のデータがまとめて累積に加算され、若干おかしなことになっている。

menus   = Menu.objects.annotate(accumulate=Window(Sum("amount"), order_by=F("date").desc()) ).values("id","name","date","amount","accumulate").order_by("-date")

【注意】SQLite3のバージョンによってはこのWindow関数は機能しない

下記のStackoverflowによると、DjangoのWindow関数が動作しない問題はSQLiteのバージョン問題であるそうだ。

https://stackoverflow.com/questions/57650437/how-to-make-work-django-window-expression-with-sqlite

一番簡単な方法はWindowsの場合Anacondaをインストールする方法であるが、私の環境(Ubuntu 18.04)ではSqlite3がOSに直にインストールされているため、コードからDLしてmakeする。

参照元:https://stackoverflow.com/questions/64861331/how-can-i-install-or-upgrade-to-sqlite-3-33-0-on-ubuntu-18-04

wget https://sqlite.org/2021/sqlite-autoconf-3340100.tar.gz
tar -xvf sqlite-autoconf-3340100.tar.gz && cd sqlite-autoconf-3340100
./configure
make
sudo make install
export PATH="/usr/local/lib:$PATH"
sqlite3 --version

SQLiteを使わず、PostgreSQLを使うことで対処することもできる。

結論

Window関数はorder_byを合わせないといけない点と、環境によっては動作すらしない点で、やや扱いが難しい。おまけにvaluesを使っているため、辞書型になってしまう。テンプレート側から外部キーの参照は難しくなる。

とは言え、累積、累計の表示はよくあるものだから、使えなければ苦しい。

ゴリ押しで、埋め込み型のカスタムテンプレートタグを使う方法や、forループ使って1レコードずつ代入加算する方法でも実現できるが、そうするとビューやテンプレートの負担が増える。

これだけでビューの負担が軽減できるのであれば、実装したほうが良いだろう。

https://docs.djangoproject.com/en/3.2/ref/models/expressions/#window-functions

https://stackoverflow.com/questions/43517901/cumulative-running-sum-with-django-orm-and-postgresql