Руководство 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)
num_awards = models.IntegerField()
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)
pubdate = models.DateField()
class Store(models.Model):
name = models.CharField(max_length=300)
books = models.ManyToManyField(Book)
registered_users = models.PositiveIntegerField()
Спешите? Вот как выполнить стандартные запросы агрегации для моделей представленных выше:
# 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')}
# Cost per page
>>> Book.objects.all().aggregate(
... price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()))
{'price_per_page': 0.4470664529184653}
# 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
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73
# 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
Django предоставляет два способа использовать агрегацию. Первый способ заключается в использовании агрегации для всех объектов QuerySet. Например, мы хотим вычислить среднюю цену для всех книг. Чтобы получить все книги, необходимо выполнить такой запрос:
>>> 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.
Обобщение отношения можно выполнить с помощью 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() может привести к неправильному результату, т.к. объединяются несколько таблиц. Из-за использования LEFT OUTER JOIN могут создаваться дублирующиеся записи, если объединенные таблицы содержат разное количество записей:
>>> Book.objects.first().authors.count()
2
>>> Book.objects.first().chapters.count()
3
>>> q = Book.objects.annotate(Count('authors'), Count('chapters'))
>>> q[0].authors__count
6
>>> q[0].chapters__count
6
Для большинства агрегаций нет способа избежать этой проблемы. Однако, агрегация Count принимает аргумент distinct, который может помочь:
>>> q = Book.objects.annotate(Count('authors', distinct=True), Count('chapters', distinct=True))
>>> q[0].authors__count
2
>>> q[0].chapters__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 Count, Min, Sum, Avg
>>> 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 в результате будет содержать атрибут total_pages. Если имя атрибута не указано, оно будет - book__pages__sum.)
Или получим средний рейтинг книг каждого автора:
>>> Author.objects.aggregate(average_rating=Avg('book__rating'))
(В результате получим словарь с ключом 'average__rating'. Если не указать имя ключа, получим 'book__rating__avg'.)
Фильтры могут использоваться вместе с агрегацией. Любой filter() (или exclude()) повлияет на выборку объектов, используемых для агрегации.
При использовании с annotate(), фильтр ограничит выборку объектов, для которых необходимо выполнить “аннотацию”. Например, чтобы получить список книг, название которых начинается с “Django”, и количество авторов книги, используйте:
>>> from django.db.models import Count, Avg
>>> 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)
Этот запрос вычисляет “аннотированное” значение, потом применяет фильтр по этому значению.
При создании сложного запроса с использованием 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 преобразует сложный QuerySet в SQL запрос. По этому изучайте созданный SQL в случае сомнений, используя str(queryset.query), и покрывайте тестами.
Результат “аннотации” может быть использован для сортировки. При определении order_by(), вы можете использовать параметр, указанный в annotate().
Например, чтобы отсортировать книги из QuerySet по количеству авторов, используйте запрос:
>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
Обычно, аннотация вычисляется для каждого объекта - QuerySet вернет одно значение для каждого объекта в изначальном QuerySet. Однако, при использовании values() “аннотация” вычисляется немного по другому. Вместо того, чтобы вычислить значение для каждого объекта QuerySet, сначала все объекты результата будут разделены на группы по уникальному значению полей, указанных в values(). “Аннотация” будет использована для каждой группы и будут использованы значения всех объектов группы.
Например, возьмем запрос для получения среднего рейтинга всех книг написанным автором:
>>> Author.objects.annotate(average_rating=Avg('book__rating'))
Этот запрос вернет результат для каждого автора в базе данных, “аннотированный” средним рейтингом книг автора.
Однако, результат будет другим при использовании values():
>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))
В этом примере, авторы будут сгруппированы по имени, поэтому вы получите средний рейтинг для уникального имени автора. Это означает, что, если в базе есть два автора с одинаковым именем, их результаты будут объединены вместе; среднее значение будет вычислено как среднее значение книг обоих авторов.
Так же, как и с 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() (или используемые в сортировке по-умолчанию), используются при получении результата, даже если они не указаны в 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 stability политику стабильности API).
Вы можете использовать агрегацию для результата “аннотации”. При определении aggregate(), можно указать имя результата, указанное в annotate() этого запроса.
Например, если вы хотите посчитать среднее количество авторов для каждой книги, сначала используйте “аннотацию” для количества авторов, потом агрегацию для этого значения:
>>> from django.db.models import Count, Avg
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}
Mar 31, 2016