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

Руководство по 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
[<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() приведет к неправильным результатам, так как несколько таблиц перекрестно соединяются. Из-за использования LEFT OUTER JOIN будут созданы дубликаты записей, если некоторые из соединенных таблиц содержат больше записей, чем другие:

>>> 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' для указания обратного ключа foreign key Publisher -> Book):

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

Это относится не только к foreign key. Это также работает с отношениями многие-ко-многим. Например, мы можем запросить каждого автора, аннотированного общим количеством страниц, учитывая все книги, которые автор соавторство (обратите внимание, как мы используем 'book' для указания обратного ключа many-to-many 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 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() — не коммутативные операции.

Дано:

• Издатель 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}