Агрегирование
В тематическом руководстве по 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. Для этого к 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, которое при вызове возвращает словарь пар «имя — значение». Имя — это идентификатор агрегированного значения, а значение — вычисленный агрегат. Имя формируется автоматически из названия поля и агрегатной функции. Если вы хотите указать имя агрегированного значения вручную, передайте его при указании предложения агрегации:
>>> 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. Например, если вы получаете список книг, возможно, вам нужно узнать, сколько авторов работали над каждой книгой. Каждая Book связана отношением «многие ко многим» с Author; мы хотим свести это отношение для каждой книги в 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"))
Обратный обход связей
Подобно поиску по связанным моделям, агрегаты и аннотации для полей моделей или моделей, связанных с той, к которой выполняется запрос, могут включать обход «обратных» связей. Здесь также используются имена связанных моделей в нижнем регистре и двойные подчеркивания.
Например, можно запросить все издательства и добавить к ним аннотации с общим количеством книг на складе (обратите внимание, что для указания обратного перехода по внешнему ключу Publisher -> Book используется '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'.)
Это работает не только с внешними ключами, но и со связями «многие ко многим». Например, можно запросить каждого автора и добавить к нему аннотацию с общим количеством страниц всех книг, которые он написал (самостоятельно или в соавторстве) (обратите внимание, что для указания обратного перехода по связи «многие ко многим» Author -> Book используется '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() не являются коммутативными операциями.
Предположим, что:
- У издательства 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().
Примечание
Вы вполне можете спросить, почему Django не удаляет лишние столбцы автоматически. Главная причина — согласованность с distinct() и другими возможностями: Django никогда не удаляет заданные вами ограничения сортировки (мы не можем изменить поведение этих других методов, поскольку это нарушило бы нашу политику стабильности 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}
Агрегирование пустых наборов запросов или групп
Если агрегация применяется к пустому набору запросов или группе, результатом по умолчанию будет значение параметра default, обычно None. Это происходит потому, что агрегатные функции возвращают NULL, если выполненный запрос не вернул строк.
Для большинства агрегатных функций можно указать возвращаемое значение с помощью аргумента default. Однако Count не поддерживает аргумент default, поэтому для пустых наборов запросов или групп всегда возвращает 0.
Например, если предположить, что в названии ни одной книги нет слова web, вычисление общей цены этого набора книг вернет None, поскольку нет подходящих строк для вычисления агрегата Sum:
>>> from django.db.models import Sum
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
{"price__sum": None}
Однако при вызове Sum можно задать аргумент default, чтобы возвращалось другое значение по умолчанию, если книги не найдены:
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
{"price__sum": Decimal("0")}
Внутри аргумент default реализован путем оборачивания агрегатной функции в Coalesce.
Агрегирование с включенным режимом MySQL ONLY_FULL_GROUP_BY
При использовании предложения values() для группировки результатов запросов с аннотациями в MySQL, если включен режим SQL ONLY_FULL_GROUP_BY, может потребоваться применить AnyValue, если аннотация сочетает агрегатные и неагрегатные выражения.
Рассмотрим следующий пример:
>>> from django.db.models import F, Count, Greatest
>>> Book.objects.values(greatest_pages=Greatest("pages", 600)).annotate(
... num_authors=Count("authors"),
... pages_per_author=F("greatest_pages") / F("num_authors"),
... ).aggregate(Avg("pages_per_author"))
Он создает группы книг на основе столбца SQL GREATEST(pages, 600). В одну уникальную группу входят книги объемом до 600 страниц включительно, а в другие уникальные группы — книги с одинаковым количеством страниц. Аннотация pages_per_author состоит из агрегатных и неагрегатных выражений: num_authors — агрегатное выражение, а greatest_page — нет.
Поскольку группировка основана на выражении greatest_pages, MySQL может не определить, что greatest_pages (используемое в выражении pages_per_author) функционально зависит от сгруппированного столбца. В результате может возникнуть ошибка вида:
OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'book_book.pages' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
Чтобы избежать этого, оберните неагрегатное выражение в AnyValue.
>>> from django.db.models import F, Count, Greatest
>>> Book.objects.values(
... greatest_pages=Greatest("pages", 600),
... ).annotate(
... num_authors=Count("authors"),
... pages_per_author=AnyValue(F("greatest_pages")) / F("num_authors"),
... ).aggregate(Avg("pages_per_author"))
{'pages_per_author__avg': 532.57143333}
Другие поддерживаемые базы данных не сталкиваются с проблемой OperationalError в приведенном выше примере, поскольку могут обнаружить функциональную зависимость. В целом AnyValue полезен при работе со столбцами списка выбора, содержащими неагрегатные функции или сложные выражения, функциональная зависимость которых от столбцов в предложении группировки не распознается базой данных.
Добавлен агрегат AnyValue.
© Django Software Foundation and individual contributors
Licensed under the BSD License.
https://docs.djangoproject.com/en/6.0/topics/db/aggregation/
