Агрегирование

В руководстве по 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. Список доступных агрегирующих функций можно найти в Справочнике по наборам объектов.

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() части запроса — набор объектов; этот набор объектов может быть изменён с помощью любых других операций с наборами объектов, включая 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

Объединения и агрегаты

До сих пор мы работали с агрегатами по полям, принадлежащим модели, по которой ведется запрос. Однако иногда значение, которое вы хотите агрегировать, будет принадлежать модели, связанной с моделью, по которой вы делаете запрос.

При указании поля, подлежащего агрегированию в агрегирующей функции, 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)

У каждого Author в наборе результатов будут атрибуты num_books и highly_rated_books. См. также Условное агрегирование.

Порядок 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, поэтому издатель С исключается.

В первом запросе аннотация предшествует фильтру, поэтому фильтр не влияет на аннотацию. 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() вызове.

Агрегирование аннотаций

Вы также можете сгенерировать агрегат на основе результата аннотации. При определении 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}