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

В руководстве по 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)
    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, on_delete=models.CASCADE)
    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')}

# 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

# 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 предоставляет два способа генерации агрегатов. Первый способ — это генерация сводных значений по всему набору результатов запроса. Например, предположим, что вы хотите вычислить среднюю цену всех книг, доступных для продажи. Синтаксис запросов Django предоставляет средства для описания набора всех книг:

>>> Book.objects.all()

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

>>> 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')}

Генерация агрегатов для каждого элемента в наборе результатов запроса

Второй способ генерации сводных значений — это генерация независимой сводки для каждого объекта в наборе результатов запроса. Например, если вы получаете список книг, вы можете узнать, сколько авторов внесли вклад в каждую книгу. Каждая книга имеет многие-ко-многим отношения с автором; мы хотим обобщить эти отношения для каждой книги в наборе результатов запроса.

Сводки по объектам можно генерировать с помощью оператора annotate(). Когда указан оператор annotate(), каждый объект в наборе результатов запроса будет снабжён указанными значениями.

Синтаксис этих аннотаций идентичен синтаксису, используемому для оператора 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 Count, Min, Sum, Avg
>>> 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'.)

Агрегации и другие операторы набора результатов запроса

filter() и exclude()

Агрегаты также могут участвовать в фильтрах. Любые 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()

При разработке сложного запроса, включающего как операторы annotate(), так и filter(), обратите особое внимание на порядок применения этих операторов к набору результатов запроса.

Когда оператор 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 будет транслировать сложные наборы запросов в SQL-запросы, поэтому при необходимости проверьте SQL с помощью str(queryset.query) и напишите много тестов.

order_by()

Аннотации могут использоваться в качестве основы для сортировки. При определении условия order_by() агрегаты, которые вы предоставляете, могут ссылаться на любой псевдоним, определённый в рамках условия annotate() в запросе.

Например, чтобы отсортировать набор книг по количеству авторов, внесших вклад в книгу, можно использовать следующий запрос:

>>> 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()

    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().

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

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