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

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

Допустим:

• Издательство 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() пункта в запросе.

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

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

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