Агрегация

В руководстве по 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 . Список доступных агрегатных функций можно найти в справочнике 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 . Например, если вы получаете список книг, может быть полезно знать количество авторов, которые внесли свой вклад в каждую книгу. Каждый объект Book имеет отношение "многие ко многим" с моделью Author ; мы хотим резюмировать эту взаимосвязь для каждой книги в запросе.

Резюме по объектам могут быть созданы с помощью предложения annotate() . Когда это упоминается, каждый объект запроса аннотируется указанными значениями.

Синтаксис этих аннотаций идентичен синтаксису, используемому для предложений aggregate() . Каждый параметр d » 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 , например 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 (через отношение «многие ко многим») и выполнить агрегирование в поле цены модели 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' для установки обратной связи внешнего ключа):

>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count('book'))

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

Выбор между filter иQuerySet.filter()

Избегайте использования параметра filter с одной аннотацией или агрегацией. Более эффективно использовать QuerySet.filter() для исключения строк. Параметр агрегирования filter полезен только при использовании двух или более агрегатов для одних и тех же отношений с разными условиями.

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

Например, чтобы отсортировать запрос книги по количеству авторов, которые внесли свой вклад в книгу, вы можете написать следующий запрос:

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

values()

В принципе, аннотации создаются для каждого объекта; аннотированный набор запросов возвращает один результат для каждого объекта исходного набора запросов. Однако, когда предложение values() используется для ограничения столбцов, возвращаемых в наборе запроса, метод оценки аннотаций немного отличается. Вместо того, чтобы возвращать аннотированный результат для каждого результата в исходном наборе запросов, исходные результаты группируются в соответствии с уникальными комбинациями полей, указанных в предложении 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 ).

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

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

Copyright ©2021 All rights reserved