Djangoの集計について

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

レコードの準備

テーブル定義

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# 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'))
[<Test: 15/1.8(1.0)>, <Test: 20/8.2(2.0)>, <Test: 5/6.6(6.0)>, <Test: 25/4.5(13.0)>, <Test: 10/8.9(14.0)>]
 
>>> # group_byは先に設定しなければ、idを含めてグルーピングしてしまう模様
>>> queryset2 = Test.objects.annotate(sum=models.Sum('b'))
>>> queryset2.query.group_by = ['a']
>>> queryset2
[<Test: 15/1.8(1)>, <Test: 20/8.2(2)>, <Test: 20/9.6(3)>, <Test: 15/4.0(4)>, <Test: 15/7.6(5)>, <Test: 5/6.6(6)>, <Test: 15/3.7(7)>, <Test: 5/2.7(8)>, <Test: 15/1.8(9)>, <Test: 15/7.7(10)>, <Test: 5/1.2(11)>, <Test: 5/2.4(12)>, <Test: 25/4.5(13)>, <Test: 10/8.9(14)>, <Test: 10/1.2(15)>, <Test: 15/1.0(16)>, <Test: 15/4.1(17)>, <Test: 5/7.8(18)>, <Test: 10/2.0(19)>, <Test: 20/5.5(20)>, '...(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では最後のレコードっぽいです。