Агрегация ¶
В руководстве по 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
. Например, если вы получаете список книг, может быть полезно знать количество авторов, которые внесли свой вклад в каждую книгу. Каждый объект Book
имеет отношение "многие ко многим" с моделью Author
; мы хотим резюмировать эту взаимосвязь для каждой книги в запросе.
Резюме по объектам могут быть созданы с помощью предложения annotate()
. Когда это упоминается, каждый объект запроса аннотируется указанными значениями.
Синтаксис этих аннотаций идентичен синтаксису, используемому для предложений aggregate()
. Каждый параметр d » 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
, например 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
(через отношение «многие ко многим») и выполнить агрегирование в поле цены модели 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'
для установки обратной связи внешнего ключа):
>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count('book'))
(каждый Publisher
результирующий набор запросов будет иметь дополнительный именованный атрибут 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
результирующий набор запросов будет иметь дополнительный именованный атрибут 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.
- У «Издателя» Б есть две книги с рейтингами 1 и 4.
- У «Издателя» А есть книга с рейтингом 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()
в запросе.
Например, чтобы отсортировать запрос книги по количеству авторов, которые внесли свой вклад в книгу, вы можете написать следующий запрос:
>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
values()
¶
В принципе, аннотации создаются для каждого объекта; аннотированный набор запросов возвращает один результат для каждого объекта исходного набора запросов. Однако, когда предложение values()
используется для ограничения столбцов, возвращаемых в наборе запроса, метод оценки аннотаций немного отличается. Вместо того, чтобы возвращать аннотированный результат для каждого результата в исходном наборе запросов, исходные результаты группируются в соответствии с уникальными комбинациями полей, указанных в предложении 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()
class Meta:
ordering = ["name"]
Важным элементом здесь является сортировка по полю по умолчанию name
. Если вы хотите подсчитать количество различных вхождений значения data
, вы можете написать это:
# Warning: not quite correct!
Item.objects.values("data").annotate(Count("id"))
… Что группирует объекты Item
по их data
общим значениям , а затем подсчитывает количество значений id
в каждой группе. Только это не сработает так, как ожидалось. Сортировка по умолчанию name
также вмешивается в группировку, так что запросы группируются по отдельным парам , что не соответствует исходному намерению. Вот собственно, что написать, чтобы получить ожидаемый запрос:(data, name)
Item.objects.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}