Агрегация

В тематическом руководстве по 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()является терминальным предложением для a, 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

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

Те же правила применяются к 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атрибуты. См. Также Условное агрегирование .

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

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

Порядок annotate()и filter()пункты

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

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

Дано:

  • У издателя А есть две книги с рейтингом 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()разговора.

Примечание

Вы можете резонно спросить, почему 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}

СОДЕРЖАНИЕ

Просматривать

Вы здесь:

Django Docs

Copyright ©2021 All rights reserved