1. TOP
  2. ブログ
  3. Django SQL データベース 操作 まとめ 入門(6)集計 group by(annotate,aggregate),distinct

Django SQL データベース 操作 まとめ 入門(6)集計 group by(annotate,aggregate),distinct

更新日:2018年7月17日

Djangoで、モデルで定義したデータベースのテーブルからデータを取得したり操作したりする方法をDjangoの公式ドキュメントを参考にしてまとめて記載。
Where条件、group by(annotate,aggregate),distinct等の書き方についてまとめています。

環境:Python3.6.5 Django2.0.8

distinct(*fields):重複データを取り除く

記載例

>>> Author.objects.distinct()

>>> Entry.objects.order_by('pub_date').distinct('pub_date')

>>> Entry.objects.order_by('blog').distinct('blog')

>>> Entry.objects.order_by('author', 'pub_date').distinct('author', 'pub_date')

>>> Entry.objects.order_by('blog__name', 'mod_date').distinct('blog__name', 'mod_date')

>>> Entry.objects.order_by('author', 'pub_date').distinct('author')

注意:2つ目以降は、PostgreDBのみで有効です。order_by()を使用する場合は、distinct()に記載しているフィールド順に記載して下さい。


annotate(*args, **kwargs):レコードごとの集計

aggregate()は全体の集計結果を返すのに対して、annotate()は各レコードごとの集計をQuerySetで返します。 例

>>> from django.db.models import Count
>>> q = Blog.objects.annotate(Count('entry'))
# The name of the first blog
>>> q[0].name
'Blogasaurus'
# The number of entries on the first blog
>>> q[0].entry__count
42

Blog.nameが'Blogasaurus'の記事entryが42件あり、他のブログも同様にカウントされています。 下記のように集計数に名前を付けることができます。

>>> q = Blog.objects.annotate(number_of_entries=Count('entry'))
# The number of entries on the first blog, using the name provided
>>> q[0].number_of_entries
42

aggregate():全体の集計

例でオンライン書店の在庫を追跡するために使用するモデルです。
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)
    num_awards = models.IntegerField()

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)
    registered_users = models.PositiveIntegerField()

上記のモデルを使用した一般的な集計クエリは以下のようになります。

# Total number of books.
>>> Book.objects.count()
2452

# Total number of books with publisher=BaloneyPress
>>> Book.objects.filter(publisher__name='BaloneyPress').count()
73

# Average price across all books.
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

# Max price across all books.
>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('81.20')}

# Difference between the highest priced book and the average price of all books.
>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
... price_diff=Max('price', output_field=FloatField()) - Avg('price'))
{'price_diff': 46.85}

# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.

# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
<QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
>>> pubs[0].num_books
73

# Each publisher, with a separate count of books with a rating above and below 5
>>> from django.db.models import Q
>>> above_5 = Count('book', filter=Q(book__rating__gt=5))
>>> below_5 = Count('book', filter=Q(book__rating__lte=5))
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
>>> pubs[0].above_5
23
>>> pubs[0].below_5
12

# The top 5 publishers, in order by number of books.
>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323

全QuerySet に対する集計の例

>>> from django.db.models import Avg
>>> Book.objects.aggregate(Avg('price'))
{'price__avg': 34.35}

1つ以上の集計を生成したい場合は、aggregate() 句に別の引数を追加します。
よって、全書籍の最高の価格と最低の価格を知りたい場合は 、以下のクエリを発行します。

>>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

QuerySet の各アイテムに対する集計を生成

それぞれの書籍に寄稿している著者が何名いるのかを知りたい場合、下記のようにannotate() 句を使うことで集計できます。

# Build an annotated queryset
>>> from django.db.models import Count
>>> q = Book.objects.annotate(Count('authors'))
# Interrogate the first object in the queryset
>>> q[0]
<Book: The Definitive Guide to Django>
>>> q[0].authors__count
2
# Interrogate the second object in the queryset
>>> q[1]
<Book: Practical Django Projects>
>>> q[1].authors__count
1

注釈 (annotation) の名前は集計関数の名前と集計されるフィールドから自動的に作成されます。
注釈付けを指定する時にエイリアス名を指定すると、このデフォルトの名前をオーバーライドすることができます。

>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1

複数の集計を行う

複数の集計を統合するには、Count()とdistinct()を使用できます。 例

>>> q = Book.objects.annotate(Count('authors', distinct=True), Count('store', distinct=True))
>>> q[0].authors__count
2
>>> q[0].store__count
3

モデルを結合して集計する方法もDjango公式ドキュメントに記載されていますので、詳細については下記を参照下さい。
https://docs.djangoproject.com/ja/2.0/topics/db/aggregation/


B!

前の記事:Django SQL データベース 操作 まとめ 入門(5)Where条件、日付、時間

次の記事:Django データベース モデルのフィールド 一覧表によるまとめ