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

В руководстве по 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)
    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
>>> from django.db.models import F, FloatField, Sum
>>> 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
<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

Генерация агрегатов над `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 и %%%CODE_BLOCK_54%%):

>>> 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 и %%%CODE_BLOCK_64%%):

>>> 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 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()-клаузы, уделяйте особое внимание порядку, в котором клаузы применяются к 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() условия в запросе.

Например, чтобы отсортировать 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()

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