Агрегация

Руководство 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)

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)

Шпаргалка

In a hurry? Here’s how to do common aggregate queries, assuming the models above:

# 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

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}

If you want to generate more than one aggregate, you add another argument to the aggregate() clause. So, if we also wanted to know the maximum and minimum price of all books, we would issue the query:

>>> 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

The second way to generate summary values is to generate an independent summary for each object in a QuerySet. For example, if you are retrieving a list of books, you may want to know how many authors contributed to each book. Each Book has a many-to-many relationship with the Author; we want to summarize this relationship for each book in the QuerySet.

Per-object summaries can be generated using the annotate() clause. When an annotate() clause is specified, each object in the QuerySet will be annotated with the specified values.

The syntax for these annotations is identical to that used for the aggregate() clause. Each argument to annotate() describes an aggregate that is to be calculated. For example, to annotate books with the number of authors:

# 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().

Объединение нескольких агрегаций

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries:

>>> book = Book.objects.first()
>>> book.authors.count()
2
>>> book.store_set.count()
3
>>> q = Book.objects.annotate(Count('authors'), Count('store'))
>>> q[0].authors__count
6
>>> q[0].store__count
6

Для большинства агрегаций нет способа избежать этой проблемы. Однако, агрегация Count принимает аргумент distinct, который может помочь:

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

Если вы сомневаетесь, изучите SQL запрос!

Запрос можно получить из свойства query экземпляра QuerySet.

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

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

Для указания поля для агрегации, 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 Avg, Count, Min, Sum
>>> 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 Avg, Count
>>> 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)

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

Если вам необходимы две аннотации с двумя различными фильтрами, вы можете использовать аргумент filter с любой агрегацией. Например, чтобы сгенерировать список авторов с количеством популярных книг:

>>> highly_rated = Count('book', filter=Q(book__rating__gte=7))
>>> Author.objects.annotate(num_books=Count('book'), highly_rated_books=highly_rated)

Каждый Author в результате будет содержать атрибуты num_books и highly_rated_books.

Выбор между filter и QuerySet.filter()

Не используйте аргумент filter с одной аннотацией или агрегацией. Эффективнее использовать QuerySet.filter() для фильтрации записей. Аргумент filter полезен только при нескольких агрегаций для одной связи с различными условиями выборки.

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

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

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

Берем следующий набор данных:

  • У издателя A есть две книги с рейтингом 4 и 5.
  • У издателя B есть две книги с рейтингом 1 и 4.
  • У издателя C есть одна книга с рейтингом 1.

Пример с агрегацией Count:

>>> a, b = Publisher.objects.annotate(num_books=Count('book', distinct=True)).filter(book__rating__gt=3.0)
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 2)

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 1)

Оба запроса вернули список издателей, у которых есть хотя бы одна книга с рейтингом выше 3.0, по этому издатель C исключен из выборки.

В первом запросе аннотация следует перед фильтрацией, по этому фильтрация не влияет на аннотацию. distinct=True необходим, чтобы избежать бага с объединением таблиц.

Второй запрос вычисляет количество книг с рейтингом выше 3.0 для каждого издателя. Фильтрация следует перед аннотацией, тем самым влияет на данные, которые используются при вычислении аннотации.

Еще один пример с Avg:

>>> a, b = Publisher.objects.annotate(avg_rating=Avg('book__rating')).filter(book__rating__gt=3.0)
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 2.5)  # (1+4)/2

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(avg_rating=Avg('book__rating'))
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 4.0)  # 4/1 (book with rating 1 excluded)

Первый запрос вычисляет средний рейтинг книг для издателей, у которых есть хотя бы одна книга с рейтингом выше 3.0. Второй вычисляет средний рейтинг для книг, рейтинг которых выше 3.0.

Тяжело интуитивно догадаться как ORM преобразует сложный QuerySet в SQL запрос. По этому изучайте созданный SQL в случае сомнений, используя str(queryset.query), и покрывайте тестами.

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()

Не рекомендуется, начиная с версии 2.2: Starting in Django 3.1, the ordering from a model’s Meta.ordering won’t be used in GROUP BY queries, such as .annotate().values(). Since Django 2.2, these queries issue a deprecation warning indicating to add an explicit order_by() to the queryset to silence the warning.

Поля, упомянутые в 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 Avg, Count
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}