2021-09-21

Djangoの集計について

Djangoの集計についてわかったことを書いていきます。この辺は食わず嫌いでまったく理解してませんでした。

info

2021/09追記
当時この記事を執筆したとき(2014年)のDjango のバージョンは 1.7 くらいですが、手元で試してみた結果、Django3.1.12, Python 3.9.7 でも動作しました。
ただ、今見るとテスト用テーブルのフィールドが無機質でわかりにくいのでコメントを追加しました。

レコードの準備

テーブル定義

# dbtest/models.py
# coding: utf-8

from django.db import models

class Test(models.Model):
    def __str__(self):
        return u'%s/%s(%s)' % (
            self.a,
            self.__dict__.get('rounded_b', self.b),
            self.id,
        )

    a = models.IntegerField()
    b = models.FloatField()
    c = models.ForeignKey('C')


class C(models.Model):
    def __str__(self):
        return unicode(self.id)


class D(models.Model):
    def __str__(self):
        return unicode(self.id)

    m2m = models.ManyToManyField(Test)


class E(models.Model):
    def __str__(self):
        return unicode(self.id)

    test = models.ForeignKey(Test)

テストレコードを追加

多分10秒くらいで終わります

from dbtest.models import *

from django.db import connection, transaction, models

from random import choice, randint

l1 = range(5, 30, 5)
l2 = map((lambda i: float(i)/10), range(10, 100))
l3 = [C.objects.create() for i in range(5)]


with transaction.commit_on_success():
    for i in xrange(1000):
        if i % 100 == 0: print(i)
        t = Test.objects.create(
            a=choice(l1), b=choice(l2), c=choice(l3),
        )

    for i in range(100):
        print(i)
        d = D.objects.create()
        for j in range(randint(0, 10)):
            try:
                test = Test.objects.get(id=randint(1, 1000))
                d.m2m.add(test)
            except Test.DoesNotExist:
                pass

    for i in range(5000):
        if i % 100 == 0: print(i)
        try:
            test = Test.objects.get(id=randint(1, 1000))
            e = E.objects.create(test=test)

        except Test.DoesNotExist:
            pass

info

テストレコードはランダムに投入しているので以下の結果は人によって異なります。

集計関数

最初から集計に使える関数は以下があります。

平均

django.db.models.Avg

合計

django.db.models.Sum

カウント

django.db.models.Count

最大

django.db.models.Max

最小

django.db.models.Min

引数はSQLの引数と同じはずで、大抵はフィールドを指定すればよいでしょう。

info

集計関数は django.db.models.Aggregate を継承して自作できるようです。興味のある方はぜひやってみてください。
Creating your own Aggregate Functions

では実際に使っていきましょう。

aggregate

aggregateはQuerySetに対して集計を行う機能です。

返却値はQuerySetではなく辞書を返すため、メソッドチェーンの最終句で指定する必要があります。

>>> # 単純なcount文
>>> Test.objects.filter(a__gt=15).count()
381

# Count('列')はcount()とほとんど同じ
>>> Test.objects.filter(a__gt=15).aggregate(models.Count('id'))
{'id__count': 381}

>>> # 同時に複数の列や種類の集計を行うこともできる(ここが一番の違い)
>>> Test.objects.filter(a__gt=15).aggregate(avg_a=models.Avg('a'), sum_b=models.Sum('b'))
{'avg_a': 22.7165, 'sum_b': 2107.2000000000016}

>>> # 発行したSQL文の表示
>>> for sql in map((lambda r: r['sql']), connection.queries[-3:]):
...     print(sql)
...
SELECT COUNT(*) FROM `dbtest_test` WHERE `dbtest_test`.`a` > 15

SELECT COUNT(`dbtest_test`.`id`) AS `id__count` FROM `dbtest_test` WHERE `dbtest_test`.`a` > 15

SELECT AVG(`dbtest_test`.`a`) AS `avg_a`, SUM(`dbtest_test`.`b`) AS `sum_b` FROM `dbtest_test` WHERE `dbtest_test`.`a` > 15

annotate

aggregateが全体の集計を行うのに対し、annotateは各レコードごとの集計を行うことができます。

>>> # (11~15番目のTestレコードに対する)中間テーブルからの外部参照数が知りたい場合
>>> for record in Test.objects.filter(a=15).annotate(count=models.Count('d'))[10:15]:
...     print(record.id, record.count)
...
48 1 # ID:48 への外部参照を持つ中間テーブルのレコード数は1
58 1 # ID:58 への外部参照を持つ中間テーブルのレコード数は1
67 0 # ID:67 への外部参照を持つ中間テーブルのレコード数は0
69 0 # ID:69 への外部参照を持つ中間テーブルのレコード数は0
71 2 # ID:71 への外部参照を持つ中間テーブルのレコード数は2

>>> # (11~15番目のTestレコードに対する)Eテーブルからの外部参照数が知りたい場合
>>> for record in Test.objects.filter(a=15).annotate(count=models.Count('e'))[10:15]:
...     print(record.id, record.count)
...
48 2 # ID:48 への外部参照を持つEテーブルのレコード数は2
58 7 # ID:58 への外部参照を持つEテーブルのレコード数は7
67 7 # ID:67 への外部参照を持つEテーブルのレコード数は7
69 7 # ID:69 への外部参照を持つEテーブルのレコード数は7
71 11 # ID:71 への外部参照を持つEテーブルのレコード数は11

>>> # 発行したSQL文の表示
>>> for sql in map((lambda r: r['sql']), connection.queries[-2:]):
...     print(sql)
...

SELECT `dbtest_test`.`id`, `dbtest_test`.`a`, `dbtest_test`.`b`, `dbtest_test`.`c_id`, COUNT(`dbtest_d_m2m`.`d_id`) AS `count` FROM `dbtest_test` LEFT OUTER JOIN `dbtest_d_m2m` ON ( `dbtest_test`.`id` = `dbtest_d_m2m`.`test_id` ) WHERE `dbtest_test`.`a` = 15 GROUP BY `dbtest_test`.`id` ORDER BY NULL LIMIT 5 OFFSET 10

SELECT `dbtest_test`.`id`, `dbtest_test`.`a`, `dbtest_test`.`b`, `dbtest_test`.`c_id`, COUNT(`dbtest_e`.`id`) AS `count` FROM `dbtest_test` LEFT OUTER JOIN `dbtest_e` ON ( `dbtest_test`.`id` = `dbtest_e`.`test_id` ) WHERE `dbtest_test`.`a` = 15 GROUP BY `dbtest_test`.`id` ORDER BY NULL LIMIT 5 OFFSET 10

また、valuesと組み合わせることで特定のキーでグルーピング(GROUP BY)できます。

多分 annotate だけで使うよりもこっちのほうが一般的ですね。

>>> # valuesはannotateより前に設置する必要がある(sum列も自動的に追加される)
>>> Test.objects.filter(a__gt=15).values('a').annotate(sum=models.Sum('b'))
[
  # a == 20 の b 合計は949.30...
  {'a': 20, 'sum': 949.3000000000003},
  # a == 25 の b 合計は1157.90...
  {'a': 25, 'sum': 1157.9000000000012}
]

>>> # valuesがannotateよりも後ろにあると列が制限されるのみでグルーピングされないため注意
>>> Test.objects.filter(a__gt=15).annotate(sum=models.Sum('b')).values('a')
[{'a': 20}, {'a': 20}, {'a': 25}, {'a': 20}, {'a': 20}, {'a': 20}, {'a': 25}, {'a': 25}, {'a': 20}, {'a': 20}, {'a': 20}, {'a': 20}, {'a': 25}, {'a': 25}, {'a': 20}, {'a': 25}, {'a': 25}, {'a': 20}, {'a': 20}, {'a': 25}, '...(remaining elements truncated)...']

>>> # valuesで指定した全カラムによってグルーピングされる
>>> Test.objects.filter(a__gt=15).values('a', 'c').annotate(sum=models.Sum('b'))
[
  # (a == 20 and c == 2) の b合計は126.9
  {'a': 20, 'c': 2, 'sum': 126.9},
  # (a == 20 and c == 4) の b合計は167.9...
  {'a': 20, 'c': 4, 'sum': 167.99999999999997},
  # (a == 25 and c == 4) の b合計は228.79...
  {'a': 25, 'c': 4, 'sum': 228.79999999999998},
  # (a == 20 and c == 5) の b合計は257.6
  {'a': 20, 'c': 5, 'sum': 257.6},
  # (a == 20 and c == 3) の b合計は158.39...
  {'a': 20, 'c': 3, 'sum': 158.39999999999998},
  # (a == 25 and c == 3) の b合計は241.6
  {'a': 25, 'c': 3, 'sum': 241.6},
  # (a == 25 and c == 2) の b合計は189.6
  {'a': 25, 'c': 2, 'sum': 189.6},
  # (a == 25 and c == 1) の b合計は295.69...
  {'a': 25, 'c': 1, 'sum': 295.69999999999993},
  # (a == 20 and c == 5) の b合計は202.2
  {'a': 25, 'c': 5, 'sum': 202.2},
  # (a == 20 and c == 1) の b合計は238.39...
  {'a': 20, 'c': 1, 'sum': 238.39999999999998}
]

>>> # 発行したSQL文の表示
>>> for sql in map((lambda r: r['sql']), connection.queries[-3:]):
...     print(sql)
...
SELECT `dbtest_test`.`a`, SUM(`dbtest_test`.`b`) AS `sum` FROM `dbtest_test` WHERE `dbtest_test`.`a` > 15 GROUP BY `dbtest_test`.`a` ORDER BY NULL LIMIT 21

SELECT `dbtest_test`.`a` FROM `dbtest_test` WHERE `dbtest_test`.`a` > 15 GROUP BY `dbtest_test`.`id`, `dbtest_test`.`a`, `dbtest_test`.`b`, `dbtest_test`.`c_id` ORDER BY NULL LIMIT 21

SELECT `dbtest_test`.`a`, `dbtest_test`.`c_id`, SUM(`dbtest_test`.`b`) AS `sum` FROM `dbtest_test` WHERE `dbtest_test`.`a` > 15 GROUP BY `dbtest_test`.`a`, `dbtest_test`.`c_id` ORDER BY NULL LIMIT 21

しかし、本来入っている値とは違う値によってグルーピングしたいケースもあるでしょう。

extra メソッドによって新たなフィールドを定義することで実現できます。追加されたフィールドはvaluesの列として指定できるためグルーピングの要素として使用できるというわけです。

以下は小数点を丸めた値によってグルーピングする例です。

>>> # 小数点以下を切捨てる例(MySQLではtruncate関数を使う)
>>> Test.objects.extra(select={'int_b': 'truncate(b, 0)'}).values('int_b').annotate(sum=models.Sum('a'))
[{'sum': 1410, 'int_b': 1.0}, {'sum': 1625, 'int_b': 8.0}, {'sum': 1570, 'int_b': 9.0}, {'sum': 1780, 'int_b': 4.0}, {'sum': 1590, 'int_b': 7.0}, {'sum': 1610, 'int_b': 6.0}, {'sum': 1795, 'int_b': 3.0}, {'sum': 1505, 'int_b': 2.0}, {'sum': 1855, 'int_b': 5.0}]

>>> # 少数第一位を四捨五入する例(round関数を使う、MySQLなら整数変換でも実現できる)
>>> Test.objects.extra(select={'int_b': 'round(b, 0)'}).values('int_b').annotate(sum=models.Sum('a'))
[{'sum': 1720, 'int_b': 2.0}, {'sum': 1780, 'int_b': 8.0}, {'sum': 750, 'int_b': 10.0}, {'sum': 2150, 'int_b': 4.0}, {'sum': 1315, 'int_b': 7.0}, {'sum': 1265, 'int_b': 3.0}, {'sum': 650, 'int_b': 1.0}, {'sum': 1500, 'int_b': 9.0}, {'sum': 1760, 'int_b': 6.0}, {'sum': 1850, 'int_b': 5.0}]

>>> # 発行したSQL文の表示
>>> for sql in map((lambda r: r['sql']), connection.queries[-2:]):
...     print(sql)
...
SELECT (truncate(b, 0)) AS `int_b`, SUM(`dbtest_test`.`a`) AS `sum` FROM `dbtest_test` GROUP BY (truncate(b, 0)) ORDER BY NULL LIMIT 21

SELECT (round(b, 0)) AS `int_b`, SUM(`dbtest_test`.`a`) AS `sum` FROM `dbtest_test` GROUP BY (round(b, 0)) ORDER BY NULL LIMIT 21

values以外の方法でグルーピングするには以下のようにすればできるようです。 (参考サイト)

>>> queryset = Test.objects.all()
>>> queryset.query.group_by = ['a']
>>> queryset.annotate(sum=models.Sum('b'))
<QuerySet [[<Test object>, <Test object>, <Test object>, <Test object>, <Test object>]>

>>> # group_byは先に設定しなければ、idを含めてグルーピングしてしまう模様
>>> queryset2 = Test.objects.annotate(sum=models.Sum('b'))
>>> queryset2.query.group_by = ['a']
>>> queryset2
<QuerySet [<Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>, <Test object>'...(remaining elements truncated)...']>

>>> # 発行したSQL文の表示
>>> for sql in map((lambda r: r['sql']), connection.queries[-2:]):
...     print(sql)
...
SELECT `dbtest_test`.`id`, `dbtest_test`.`a`, `dbtest_test`.`b`, `dbtest_test`.`c_id`, SUM(`dbtest_test`.`b`) AS `sum` FROM `dbtest_test` GROUP BY (a) ORDER BY NULL LIMIT 21

SELECT `dbtest_test`.`id`, `dbtest_test`.`a`, `dbtest_test`.`b`, `dbtest_test`.`c_id`, SUM(`dbtest_test`.`b`) AS `sum` FROM `dbtest_test` GROUP BY `dbtest_test`.`id` ORDER BY NULL LIMIT 21

この方法のメリットは各レコードをモデルインスタンスとして取得することができる点です。

選ばれるモデルインスタンスはデータベースシステムによって異なり、MySQLでは先頭のレコード、SQLiteでは最後のレコードっぽいです。

warning

query.group_by をいじってグルーピングするやり方はドキュメントに載っていない仕様なのでご利用は自己責任でお願いします。