Агрегация¶
Руководство 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 provides two ways to generate aggregates. The first way is to generate
summary values over an entire QuerySet. For example, say you wanted to
calculate the average price of all books available for sale. Django’s query
syntax provides a means for describing the set of all books:
>>> Book.objects.all()
What we need is a way to calculate summary values over the objects that
belong to this QuerySet. This is done by appending an aggregate()
clause onto the QuerySet:
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}
The all() is redundant in this example, so this could be simplified to:
>>> Book.objects.aggregate(Avg('price'))
{'price__avg': 34.35}
Аргумент для aggregate() определяет, что нам нужно вычислить - в данном примере среднее значение поля price для модели Book. Полный список функций агрегации можно найти в разделе о QuerySet.
aggregate() is a terminal clause for a QuerySet that, when invoked,
returns a dictionary of name-value pairs. The name is an identifier for the
aggregate value; the value is the computed aggregate. The name is
automatically generated from the name of the field and the aggregate function.
If you want to manually specify a name for the aggregate value, you can do so
by providing that name when you specify the aggregate clause:
>>> 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¶
Второй способ получения обобщенных данных – вычислить обобщенное значение для каждого объекта в QuerySet. Например, при получении списка книг, вы хотите знать количество их авторов. Каждая модель книги имеет связь много-ко-многому с моделью автора, нам нужно обобщить это отношение для каждой книги в QuerySet.
Обобщение отношения можно выполнить с помощью annotate(). annotate() для каждого объекта QuerySet добавит вычисленное значение.
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
As with aggregate(), the name for the annotation is automatically derived
from the name of the aggregate function and the name of the field being
aggregated. You can override this default name by providing an alias when you
specify the annotation:
>>> 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 автоматически добавит необходимые объединения таблиц.
For example, to find the price range of books offered in each store, you could use the annotation:
>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))
Django получит модель Store, сделает объединение (через связь многое-ко-многим) с моделью Book, и агрегирует значение цены, чтобы получить минимальное и максимальное значение.
The same rules apply to the aggregate() clause. If you wanted to
know the lowest and highest price of any book that is available for sale
in any of the stores, you could use the aggregate:
>>> Store.objects.aggregate(min_price=Min('books__price'), max_price=Max('books__price'))
Join chains can be as deep as you require. For example, to extract the age of the youngest author of any book available for sale, you could issue the query:
>>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))
Использование обратных связей¶
Аналогично Фильтры по связанным объектам, можно выполнить агрегацию и аннотацию по полям модели или связанных моделей, используя «обратные» связи. Аналогично используйте название модели в нижнем регистре и два символа нижнего подчеркивания.
For example, we can ask for all publishers, annotated with their respective
total book stock counters (note how we use 'book' to specify the
Publisher -> Book reverse foreign key hop):
>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count('book'))
(Каждый объект Publisher в QuerySet будет содержать дополнительный атрибут book__count.)
We can also ask for the oldest book of any of those managed by every publisher:
>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))
(В результате получим словарь с ключом 'oldest_pubdate'. Если имя ключа не было указано, оно будет следующим - 'book__pubdate__min'.)
This doesn’t apply just to foreign keys. It also works with many-to-many
relations. For example, we can ask for every author, annotated with the total
number of pages considering all the books the author has (co-)authored (note how we
use 'book' to specify the Author -> Book reverse many-to-many hop):
>>> Author.objects.annotate(total_pages=Sum('book__pages'))
(Каждый объект Author в результате будет содержать атрибут total_pages. Если имя атрибута не указано, оно будет - book__pages__sum.)
Or ask for the average rating of all the books written by author(s) we have on file:
>>> Author.objects.aggregate(average_rating=Avg('book__rating'))
(В результате получим словарь с ключом 'average__rating'. Если не указать имя ключа, получим длинный 'book__rating__avg'.)
Агрегация и другие методы QuerySet¶
filter() и exclude()¶
Фильтры могут использоваться вместе с агрегацией. Любой filter() (или exclude()) повлияет на выборку объектов, используемых для агрегации.
When used with an annotate() clause, a filter has the effect of
constraining the objects for which an annotation is calculated. For example,
you can generate an annotated list of all books that have a title starting
with «Django» using the query:
>>> from django.db.models import Avg, Count
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))
When used with an aggregate() clause, a filter has the effect of
constraining the objects over which the aggregate is calculated.
For example, you can generate the average price of all books with a
title that starts with «Django» using the query:
>>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))
Фильтрация по «аннотаци軶
«Аннотированные» значения могут быть использованы для фильтрации. Псевдонимы для «аннотированных» значений могут быть использованы в filter() и exclude() так же, как и другие поля модели.
For example, to generate a list of books that have more than one author, you can issue the query:
>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)
Этот запрос вычисляет «аннотированное» значение, потом применяет фильтр по этому значению.
If you need two annotations with two separate filters you can use the
filter argument with any aggregate. For example, to generate a list of
authors with a count of highly rated books:
>>> highly_rated = Count('book', filter=Q(book__rating__gte=7))
>>> Author.objects.annotate(num_books=Count('book'), highly_rated_books=highly_rated)
Каждый Автор в наборе результатов будет иметь атрибуты 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.
Here’s an example with the Count aggregate:
>>> 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 для каждого издателя. Фильтрация следует перед аннотацией, тем самым влияет на данные, которые используются при вычислении аннотации.
Here’s another example with the Avg aggregate:
>>> 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().
For example, to order a QuerySet of books by the number of authors
that have contributed to the book, you could use the following query:
>>> 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'))
Этот запрос вернет результат для каждого автора в базе данных, «аннотированный» средним рейтингом книг автора.
However, the result will be slightly different if you use a values() clause:
>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))
В этом примере, авторы будут сгруппированы по имени, поэтому вы получите средний рейтинг для уникального имени автора. Это означает, что, если в базе есть два автора с одинаковым именем, их результаты будут объединены вместе; среднее значение будет вычислено как среднее значение книг обоих авторов.
Порядок annotate() и values()¶
Так же, как и с filter(), порядок использования annotate() и values() важен. Если values() используется перед annotate(), «аннотация» будет вычислена, используя группирование values() описанное выше.
Однако, если annotate() используется перед values(), «аннотация» будет вычислена для каждого объекта. В этом случае values() просто ограничивает возвращаемые поля.
For example, if we reverse the order of the values() and annotate()
clause from our previous example:
>>> 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()
Если вы хотите подсчитать, сколько раз каждое отдельное значение данных появляется в упорядоченном наборе запросов, вы можете попробовать следующее:
items = Item.objects.order_by('name')
# Warning: not quite correct!
items.values('data').annotate(Count('id'))
…который группирует объекты Item по их общим значениям data, а затем подсчитывает количество значений id в каждой группе. Вот только это не совсем сработает. Порядок по имени также будет играть роль в группировке, поэтому этот запрос будет группироваться по отдельным парам (данные, имя), а это не то, что вам нужно. Вместо этого вам следует создать этот набор запросов:
items.values('data').annotate(Count('id')).order_by()
…убирая любую сортировку из запроса. Вы можете отсортировать по полю data без какого-либо влияния на результат, т.к. оно уже сыграло свою роль в запросе.
Это поведение идентично поведению метода distinct() и общее правило аналогично: лишние поля не должны быть использованы в запросе, поэтому очистите любую сортировку, или, по крайней мере, убедитесь что эти поля добавляются в результат вызовом values().
Примечание
Вы можете спросить, почему Django не заботится об этом. Причина та же, что и для distinct() и др.: Django никогда не удаляет сортировку, определенную вами (и мы не может изменить такое поведение, т.к. это нарушает нашу Стабильность API политику стабильности API).
Аннотация агрегации¶
Вы можете использовать агрегацию для результата «аннотации». При определении aggregate(), можно указать имя результата, указанное в annotate() этого запроса.
For example, if you wanted to calculate the average number of authors per book you first annotate the set of books with the author count, then aggregate that author count, referencing the annotation field:
>>> from django.db.models import Avg, Count
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}