Агрегация

Руководство API для доступа к данным описывает, как создавать запросы с помощью Django для создания, обновления, получения и удаления отдельных объектов. Но иногда необходимы данные полученные через обобщение или агрегацию данных нескольких объектов. Этот раздел расскажет как создавать такие запросы с помощью Django.

В данном руководстве мы будем ссылаться на следующие модели. Эти модели хранят информацию для книжного магазина:

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)
    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')}

# All the following queries involve traversing the Book<->Publisher
# many-to-many relationship backward

# 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
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73

# 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

Django предоставляет два способа использовать агрегацию. Первый способ заключается в использовании агрегации для всех объектов QuerySet. Например, мы хотим вычислить среднюю цену для всех книг. Чтобы получить все книги, необходимо выполнить такой запрос:

>>> Book.objects.all()

Нам нужно вычислить среднее значение для всех объектов в QuerySet. Это можно сделать добавив aggregate() в QuerySet:

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

all() не обязательно использовать в данном примере, так что можно упростить:

>>> Book.objects.aggregate(Avg('price'))
{'price__avg': 34.35}

Аргумент для aggregate() определяет что нам нужно вычислить - в данном примере среднее значение поля price для модели Book. Полный список функций агрегации можно найти в разделе о QuerySet.

aggregate() завершающая инструкция для QuerySet, которая возвращает словарь с результатом. Ключ словаря - идентификатор вычисленного значения; значение - результат. Название создается автоматически из поля и функции агрегации. Если вы хотите самостоятельно определить имя результата, вы можете указать его при определении функции агрегации:

>>> Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 34.35}

Если вам необходимо вычислить больше одного значения, добавьте еще один аргумент в 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

Второй способ получения обобщенных данных – вычислить обобщенное значение для каждого объекта в QuerySet. Например, при получении списка книг, вы хотите знать количество их авторов. Каждая модель книги имеет связь много-ко-многому с моделью автора, нам нужно обобщить это отношение для каждой книги в QuerySet.

Обобщение отношения можно выполнить с помощью annotate(). annotate() для каждого объекта QuerySet добавит вычисленное значение.

Синтаксис совпадает с синтаксисом aggregate(). Каждый аргумент 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

Как и для aggregate(), название будет вычислено из названия поля и функции агрегации. Вы можете переопределить это имя добавив его при определении аннотации:

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

В отличии от aggregate(), annotate() не завершающая функция. Результат функции annotate() будет QuerySet; этот QuerySet может быть изменен любой другой операцией QuerySet, включая filter(), order_by, или еще одним вызовом annotate().

Объединения и агрегация

До этого мы работали с агрегацией для полей модели запроса. Однако, иногда данные для агрегации находятся в связанной модели.

Для указания поля для агрегации, Django позволяет использовать двойное нижнее подчеркивание, которое используется для фильтрации по связанным полям. Django автоматически добавит необходимые объединения таблиц.

Например, чтобы найти диапазон цен на книги в каждом магазине:

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))

Django получит модель Store, сделает объединение (через связь многое-ко-многим) с моделью Book, и агрегирует значение цены, чтобы получить минимальное и максимальное значение.

Те же правила действуют и для aggregate(). Если вы хотите узнать максимальную и минимальную цену книги доступной в магазине, вы можете использовать такой код:

>>> Store.objects.aggregate(min_price=Min('books__price'), max_price=Max('books__price'))

Объединение может быть любой вложенности. Например, чтобы получить возраст самого молодого автора, используйте такой запрос:

>>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))

Использование обратных связей

Аналогично Фильтры по связанным объектам, можно выполнить агрегацию и аннотацию по полям модели или связанных моделей используя “обратные” связи. Аналогично используйте название модели в нижнем регистре и два символа нижнего подчеркивания.

Например, мы хотим получить всех издателей и количество их книг (обратите внимание как мы используем ‘book’ указывая на связь обратную связь Publisher->Book):

>>> from django.db.models import Count, Min, Sum, Avg
>>> Publisher.objects.annotate(Count('book'))

(Каждый объект Publisher в QuerySet будет содержать дополнительный атрибут book__count.)

Мы также можем получить самую старую книгу издателя:

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

(В результате получим словарь с ключом 'oldest_pubdate'. Если имя ключа не было указано, оно будет слудующим - 'book__pubdate__min'.)

Это относиться не только в внешним ключам и работает также для связей многое-ко-многим. Например, мы можем получить всех авторов и общее количество страниц в книгах, которые он написал (обратите внимание как мы используем ‘book’ для указания на обратную связть Author->Book):

>>> Author.objects.annotate(total_pages=Sum('book__pages'))

(Каждый объект Author в результате будет содержать атрибут total_pages. Если имя атрибута не указано, оно будет - book__pages__sum.)

Или получим средий рейтинг книг каждого автора:

>>> Author.objects.aggregate(average_rating=Avg('book__rating'))

(В результате получим словарь с ключом 'average__rating'. Если не указать имя ключа, получим 'book__rating__avg'.)

Агрегация и другие методы QuerySet

filter() и exclude()

Фильтры могут использоваться вместе с агрегацией. Любой filter() (или exclude()) повлияет на выборку объектов используемых для агрегации.

При использовании с annotate(), фильтр ограничит выборку объектов, для которых необходимо выполнить “аннотацию”. Например, чтобы получить список книг, название которых начинается с “Django”, и количество авторов книги, используйте:

>>> from django.db.models import Count, Avg
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

При использовании с aggregate(), фильтр ограничит множество объектов, для которых необходимо агрегацию. Например, вы можете получить среднюю цену книг, название которых начинается с “Django”:

>>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))

Фильтрация по “аннотации”

“Аннотированные” значения могут быть использованы для фильтрации. Псевдонимы для “аннотированных” значений могут быть использованы в filter() и exclude() так же как и другие поля модели.

Например, чтобы получить список книг хотя бы с одним автором, используем такой код:

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

Этот запрос вычисляет “аннотированное” значение, потом применяет фильтр по этому значению.

Порядок annotate() и filter()

При создании сложного запроса с использованием annotate() и filter(), необходимо учитывать порядок использования этих методов в QuerySet.

После вызова метода annotate(), “аннотация” добавляется к запросу, запрос используется в таком виде, каким он есть на данный момент. Но filter() и annotate() не независимые операции. По этому есть разница между запросом:

>>> Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0)

и запросом:

>>> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))

Оба запроса вернут список издателей хотя бы с одной хорошей книгой (то есть книгу с рейтингом выше 3.0). Однако, аннотация в первом запросе вернет вернет количество всех книг издателя; второй запрос будет содержать количество хороших книг. В первом запросе аннотация идет перед фильтром, по этому фильтр не влияет на аннотацию. Во втором запросе, фильтр идет перед аннотацией, по этому фильтр ограничит множество объектов используемых при вычислении значения для аннотации.

order_by()

Результат “аннотации” может быть использован для сортировки. При определении order_by(), вы можете использовать параметр указанный в annotate().

Например, чтобы отсортировать книги из QuerySet по количеству авторов, используйте запрос:

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

values()

Обычно, аннотация вычисляется для каждого объекта - QuerySet вернет одно значение для каждого объекта в изначальном QuerySet. Однако, при использовании values() “аннотация” вычисляется немного по другому. Вместо того, чтобы вычислить значение для каждого объекта QuerySet, сначала все объекты результата будут разделены на группы по уникальному значению полей, указанных в values(). “Аннотация” будет использована для каждой группы и будут использованы значения всех объектов группы.

Например, возьмем запрос для получения среднего рейтинга всех книг написанным автором:

>>> Author.objects.annotate(average_rating=Avg('book__rating'))

Этот запрос вернет результат для каждого автора в базе данных, “аннотированный” средним рейтингом книг автора.

Однако, результат будет другим при использовании values():

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

В этом примере, автора будут сгруппированы по имени, по этому вы получите средний рейтинг для уникального имени автора. Это означает что, если в базе есть два автора с одинаковым именем, их результаты будут объединены вместе; среднее значение будет вычислено как среднее значение книг обоих авторов.

Порядок annotate() и values()

Так же как и с filter(), порядок использования annotate() и values() важен. Если values() используется перед annotate(), “аннотация” будет вычислена используя группирование values() описанное выше.

Однако, если annotate() используется перед values(), “аннотация” будет вычислена для каждого объекта. В этом случае values() просто ограничивает возвращаемые поля.

Например, если мы поменяем местами values() и annotate() из предыдущего примера:

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

Будет вычислено одно значение для каждого автора, но результат будет содержать только имя автора и вычисленное значение average_rating.

Заметьте, что average_rating был явно включен в список значений, которые будут возвращены. Это необходимо из-за порядка использования values() и annotate().

Если values() следует перед annotate(), любая “аннотация” будет добавлена в результат. Однако, если values() используется после annotate(), вы должны указать их.

Влияние сортировки по-умолчанию и order_by()

Поля, упомянутые в order_by() (или используемые в сортировке по-умолчанию), используются при получении результата, даже если они не указаны в values(). Это влияет на порядок следования строк, нарушая уникальные группы, по которым вычисляется аннотация. Это влияет на результат, например, при подсчете.

Например, у нас есть такая модель:

from django.db import models

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

Важная часть – сортировка по-умолчанию по полю name. Если вы хотите подсчитать сколько раз встречается каждое уникальное значение поля data, вы могли бы использовать этот запрос:

# Warning: not quite correct!
Item.objects.values("data").annotate(Count("id"))

... который сгруппирует объекты Item по значениям поля data и потом подсчитает id в каждой группе. Но запрос работает не совсем так. Сортировка по-умолчанию по полю name играет свою роль при группировании. Группы будут уникальны по совокупности значений (data, name), и это не совсем то, что вам нужно. По-этому, используйте запрос:

Item.objects.values("data").annotate(Count("id")).order_by()

...убирая любую сортировку из запроса. Вы можете отсортировать по полю data без какого-либо влияния на результат, т.к. оно уже сыграло свою роль в запросе.

Это поведение идентично поведению метода distinct() и общее правило аналогично: лишние поля не должны быть использованы в запросе, по этому очистите любую сортировку, или, по крайней мере, убедитесь что эти поля добавляются в результат вызовом values().

Примечание

Вы можете спросить почему Django не заботится об этом. Причина та же, что и для distinct() и др.: Django никогда не удаляет сортировку определенную вами (и мы не может изменить такое поведение, т.к. это нарушает нашу API stability политику табильности API).

Аннотация агрегации

Вы можете использовать агрегацию для результата “аннотации”. При определении aggregate(), можно указать имя результата указанное в annotate() этого запроса.

Например, если вы хотите посчитать среднее количество авторов для каждой книги, сначала используйте “аннотацию” для количества авторов, потом агрегацию для этого значения:

>>> from django.db.models import Count, Avg
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}