Агрегирование

В руководстве по 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, provide default to be returned instead
# of None if no books exist.
>>> from django.db.models import Avg
>>> Book.objects.aggregate(Avg("price", default=0))
{'price__avg': 34.35}

# Max price across all books, provide default to be returned instead of
# None if no books exist.
>>> from django.db.models import Max
>>> Book.objects.aggregate(Max("price", default=0))
{'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. Это делается путем добавления 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().

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

Объединение нескольких агрегаций с 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

Соединения и агрегаты

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

При указании поля для агрегирования в агрегатной функции 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. См. также Условное агрегирование.

Порядок применения 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 переведёт сложные наборы запросов в 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().

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

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

Агрегирование на пустых наборах запросов или группах

Когда агрегация применяется к пустому набору запросов или группе, результат по умолчанию устанавливается в его параметр по умолчанию, обычно None. Это поведение возникает потому, что агрегатные функции возвращают NULL при отсутствии строк в выполняемом запросе.

Вы можете указать возвращаемое значение, предоставив аргумент по умолчанию для большинства агрегаций. Однако, поскольку Count не поддерживает аргумент по умолчанию, он всегда будет возвращать 0 для пустых наборов запросов или групп.

Например, предполагая, что ни одна книга не содержит web в своём названии, вычисление общей цены для этого набора книг вернёт None так как нет совпадающих строк для вычисления агрегации Sum:

>>> from django.db.models import Sum
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
{"price__sum": None}

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

>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
{"price__sum": Decimal("0")}

Внутри аргумент по умолчанию реализуется с помощью обертки агрегатной функции с Coalesce.