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

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

Генерация агрегатов по набору объектов

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() из клаузы annotate(); этот набор объектов 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 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 -> 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'.)

Агрегации и другие клаузы наборов объектов

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}