Djangoの集計について
2014-09-21

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

Warning

Django のバージョンは 多分 1.7 とかです。

正直すまんかった。

レコードの準備

テーブル定義

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

from django.db import models

class Test(models.Model):
    def __unicode__(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 __unicode__(self):
        return unicode(self.id)


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

    m2m = models.ManyToManyField(Test)


class E(models.Model):
    def __unicode__(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

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は各レコードごとの集計を行うことができます。

>>> # 中間テーブルからの外部参照数が知りたい場合
>>> for record in Test.objects.filter(a=15).annotate(count=models.Count('d'))[10:15]:
...     print record.id, record.count
...
48 1
58 1
67 0
69 0
71 2

>>> # Eテーブルからの外部参照数が知りたい場合
>>> for record in Test.objects.filter(a=15).annotate(count=models.Count('e'))[10:15]:
...     print record.id, record.count
...
48 2
58 7
67 7
69 7
71 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)できます。

>>> # valuesはannotateより前に設置する必要がある(sum列も自動的に追加される)
>>> Test.objects.filter(a__gt=15).values('a').annotate(sum=models.Sum('b'))
[{'a': 20, 'sum': 949.3000000000003}, {'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, 'c': 2, 'sum': 126.9}, {'a': 20, 'c': 4, 'sum': 167.99999999999997}, {'a': 25, 'c': 4, 'sum': 228.79999999999998}, {'a': 20, 'c': 5, 'sum': 257.6}, {'a': 20, 'c': 3, 'sum': 158.39999999999998}, {'a': 25, 'c': 3, 'sum': 241.6}, {'a': 25, 'c': 2, 'sum': 189.6}, {'a': 25, 'c': 1, 'sum': 295.69999999999993}, {'a': 25, 'c': 5, 'sum': 202.2}, {'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'))
[, , , , ]

>>> # group_byは先に設定しなければ、idを含めてグルーピングしてしまう模様
>>> queryset2 = Test.objects.annotate(sum=models.Sum('b'))
>>> queryset2.query.group_by = ['a']
>>> queryset2
[, , , , , , , , , , , , , , , , , , , , '...(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では最後のレコードっぽいです。