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

Руководство по 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

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

Соединения и агрегаты

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

При указании поля, подлежащего агрегированию в функции агрегирования, 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('books', filter=Q(books__rating__gte=7))
>>> Author.objects.annotate(num_books=Count('books'), highly_rated_books=highly_rated)

Каждый Author в наборе результатов будет иметь атрибуты num_books и highly_rated_books.

Порядок annotate() и filter() фрагментов

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

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

Дано:

• Издатель А имеет две книги с рейтингами 4 и 5.
• Издатель B имеет две книги с рейтингами 1 и 4.
• Издатель C имеет одну книгу с рейтингом 1.

Вот пример с агрегацией %%%CODE_BLOCK_104%%:

>>> 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, для каждого издателя. Фильтр предшествует аннотации, поэтому фильтр ограничивает объекты, рассматриваемые при вычислении аннотации.

Вот ещё один пример с агрегацией %%%CODE_BLOCK_107%%:

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

Аннотации могут использоваться в качестве основы для сортировки. При определении клаузы %%%CODE_BLOCK_111%%, агрегаты, которые вы предоставляете, могут ссылаться на любые псевдонимы, определённые в рамках клаузы 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'))

Это вернёт один результат для каждого автора в базе данных, аннотированный его средним рейтингом книг.

Однако результат будет немного отличаться, если вы используете клаузу %%%CODE_BLOCK_122%%:

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

В этом примере авторы будут сгруппированы по имени, поэтому вы получите аннотированный результат только для каждого уникального имени автора. Это означает, что если у вас есть два автора с одинаковым именем, их результаты будут объединены в один результат в выводе запроса; среднее значение будет вычислено как среднее значение по книгам, написанным обоими авторами.

Порядок клаузы annotate() и values()

Как и в случае с клаузой %%%CODE_BLOCK_126%%, порядок применения клауз annotate() и values() к запросу имеет значение. Если клауза values() предшествует клаузе annotate(), аннотация будет вычислена с использованием группировки, описанной клаузой %%%CODE_BLOCK_131%%.

Однако, если клауза annotate() предшествует клаузе values(), аннотации будут сгенерированы для всего набора запросов. В этом случае клауза values() только ограничивает поля, которые генерируются на выходе.

Например, если мы изменим порядок клаузы values() и annotate() из предыдущего примера:

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

Теперь это даст один уникальный результат для каждого автора; однако в выходные данные будут возвращены только имя автора и аннотация %%%CODE_BLOCK_138%%.

Вы также должны обратить внимание, что average_rating явно включено в список возвращаемых значений. Это необходимо из-за порядка клаузы values() и %%%CODE_BLOCK_141%%.

Если клауза values() предшествует клаузе annotate(), любые аннотации будут автоматически добавлены в набор результатов. Однако, если клауза values() применяется после клаузы annotate(), вам нужно явно включить столбец агрегации.

Взаимодействие с сортировкой по умолчанию или order_by()

Поля, упомянутые в части order_by() набора запросов (или используемые в сортировке по умолчанию в модели) используются при выборе выходных данных, даже если они не указаны в вызове %%%CODE_BLOCK_148%%. Эти дополнительные поля используются для группировки похожих результатов, и они могут заставить иначе идентичные строки результатов отображаться как отдельные. Это особенно проявляется при подсчёте.

В качестве примера, предположим, что у вас есть модель такого типа:

from django.db import models

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

Важная часть здесь — сортировка по умолчанию по полю %%%CODE_BLOCK_150%%. Если вы хотите подсчитать, сколько раз появляется каждое уникальное значение %%%CODE_BLOCK_151%%, вы можете попробовать это:

# Warning: not quite correct!
Item.objects.values("data").annotate(Count("id"))

…что будет группировать объекты Item по общим значениям data и затем подсчитывать количество значений id в каждой группе. Кроме того, это не совсем сработает. Сортировка по умолчанию по name также будет участвовать в группировке, поэтому этот запрос будет группировать по уникальным парам %%%CODE_BLOCK_157%%, что не то, что вам нужно. Вместо этого, вы должны построить этот набор запросов:

Item.objects.values("data").annotate(Count("id")).order_by()

…очистив любую сортировку в запросе. Вы также можете отсортировать по, скажем, data без каких-либо вредных последствий, так как это уже играет роль в запросе.

Это поведение совпадает с тем, что отмечено в документации набора запросов для distinct(), и общий принцип тот же: обычно вы не хотите, чтобы дополнительные столбцы участвовали в результате, поэтому очистите сортировку или, по крайней мере, убедитесь, что она ограничена только теми полями, которые вы также выбираете в вызове %%%CODE_BLOCK_161%%.

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

Вы также можете сгенерировать агрегат на результате аннотации. При определении клаузы %%%CODE_BLOCK_163%%, агрегаты, которые вы предоставляете, могут ссылаться на любые псевдонимы, определённые в рамках клаузы annotate() в запросе.

Например, если вы хотите вычислить среднее количество авторов на книгу, вы сначала аннотируете набор книг количеством авторов, а затем агрегируете это количество авторов, ссылаясь на поле аннотации:

>>> from django.db.models import Avg, Count
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}