Агрегация

В руководстве по теме API базы данных Django описан способ использования запросов 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)

Шпаргалка

Спешите? Вот как выполнять общие агрегированные запросы, исходя из моделей выше:

# 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.aggregate(Avg("price"))
{'price__avg': 34.35}

# Max price across all books.
>>> from django.db.models import Max
>>> Book.objects.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. Например, необходимо вычислить среднюю цену всех книг, имеющихся в продаже. Синтаксис запросов Django предоставляет средства для описания множества всех книг:

>>> Book.objects.all()

Нам нужен способ вычисления суммарных значений по объектам, принадлежащим данному QuerySet. Для этого к QuerySet добавляется предложение aggregate():

>>> 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, которая при вызове возвращает словарь пар имя-значение. Имя - это идентификатор значения агрегата, а значение - вычисленный агрегат. Имя автоматически генерируется из имени поля и агрегатной функции. Если необходимо вручную задать имя агрегатного значения, то его можно указать в предложении aggregate:

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

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

Объединение нескольких агрегатов с помощью annotate() выдаст даёт неверные результаты, потому что соединения используются вместо подзапросов:

>>> 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 в результирующем QuerySet будет иметь дополнительный атрибут под названием 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() не являются коммутативными операциями.

Дано:

  • У издателя А есть две книги с рейтингом 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 будет переводить сложные наборы запросов в запросы 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()

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

В качестве примера предположим, что у вас есть такая модель:

from django.db import models


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

Если вы хотите подсчитать, сколько раз каждое отдельное значение data появляется в упорядоченном наборе запросов, вы можете попробовать следующее:

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

…которая сгруппирует объекты Item по их общим значениям data, а затем подсчитает количество значений id в каждой группе. Вот только это не совсем сработает. Упорядочение по name также будет играть роль в группировке, поэтому этот запрос будет группировать по отдельным парам (data, name), а это не то, что вам нужно. Вместо этого следует построить такой набор запросов:

items.values("data").annotate(Count("id")).order_by()

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

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

Примечание

Вы можете резонно спросить, почему Django не удаляет за вас лишние столбцы. Основная причина - согласованность с distinct() и другими местами: Django никогда не удаляет ограничения сортировки, которые вы указали (и мы не можем изменить поведение этих других методов, так как это нарушит нашу политику /misc/api-stable).

Агрегирование аннотаций

Вы также можете создать агрегат по результату аннотации. Когда вы определяете предложение 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}
Вернуться на верх