Агрегация¶
В руководстве по теме 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.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. Например, вы хотите рассчитать среднюю цену всех книг, имеющихся в наличии. Синтаксис запросов 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
Если сомневаетесь, проверьте 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)
Each Author in the result set will have the num_books and
highly_rated_books attributes. See also Условная агрегация.
Выбор между 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(), вам необходимо явно включить агрегированный столбец.
Interaction with order_by()¶
Fields that are mentioned in the order_by() part of a queryset are used
when selecting the output data, even if they are not otherwise specified in the
values() call. These extra fields are used to group «like» results together
and they can make otherwise identical result rows appear to be separate. This
shows up, particularly, when counting things.
В качестве примера предположим, что у вас есть такая модель:
from django.db import models
class Item(models.Model):
name = models.CharField(max_length=10)
data = models.IntegerField()
If you want to count how many times each distinct data value appears in an
ordered queryset, you might try this:
items = Item.objects.order_by('name')
# Warning: not quite correct!
items.values('data').annotate(Count('id'))
…which will group the Item objects by their common data values and
then count the number of id values in each group. Except that it won’t
quite work. The ordering by name will also play a part in the grouping, so
this query will group by distinct (data, name) pairs, which isn’t what you
want. Instead, you should construct this queryset:
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}