Аннотирование и агрегация — Python: Django ORM

• Агрегация и агрегирующие функции
• Аннотирование
• Аннотирование и дубликаты в выдаче
• Агрегация аннотированных значений

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

Представим классический пример предметной области — "Книжный магазин". Главной сущностью в проекте, работающем в данной области, будет "книга":

class Book(models.Model):
    # цену часто хранят в числовом поле фиксированной точности
    price = models.DecimalField(max_digits=10, decimal_places=2)
    # ...

Когда имеешь дело с некоторыми товарами, часто приходится вычислять суммарную цену наборов товаров и среднюю цену товаров набора. Python — язык достаточно выразительный, он позволяет ту же сумму посчитать прямо на месте:

total_price = sum(book.price for book in Book.objects.all())

Средняя цена вычисляется ненамного сложнее. Однако будет ли такое решение оправданным?

ORM честно запросит все книги из базы и поместит данные каждой книги в объект класса Book. А затем в коде потребуется только цена — это уже выглядит как лишняя работа! И СУБД тоже потратит лишние ресурсы на загрузку всех столбцов таблицы, вместо того, чтобы достать ровно один. Когда дело касается крупного магазина книг, подобное использование БД неприемлемо.

Строго говоря, Django ORM умеет запрашивать только часть данных. Как это делается, будет рассказано в последующем уроке про эффективную работу с БД.

Ещё один минус подобной обработки на стороне Python заключается в том, что мы при этом не используем часть возможностей СУБД. Как уже было сказано, подсчёт сумм и средних значений — часто встречающиеся задачи. Поэтому большинство СУБД умеет выполнять такой анализ данных на своей стороне и сам язык SQL содержит средства для описания того, что же СУБД должна вычислить или, как ещё говорят, выполнить агрегацию. И разработчики СУБД вкладывают много сил в то, чтобы агрегация работала быстро. Осталось научиться описывать агрегацию с использованием Django ORM.

Агрегация и агрегирующие функции

Для того чтобы получить уже агрегированные данные, нужно воспользоваться методом .aggregate(), вызвав его у имеющегося менеджера или QuerySet. Этот метод принимает в качестве параметров так называемые агрегирующие функции. Функций этих достаточно много, но все они используются примерно одинаково, поэтому рассмотрим для примера функцию Avg:

from django.db.models import Avg

# Получение средней цены среди всех книг магазина
Book.objects.aggregate(Avg('price'))
# {'price__avg': 34.35}

# Можно задать имя ключа результирующего словаря явно
Book.objects.aggregate(average_price=Avg('price'))
# {'average_price': 34.35}

Если аргументы указываются как позиционные, то имена для ключей генерирует Django ORM на основе имени поля и имени агрегирующей функции. Аргументов можно указать сразу несколько и генерируемые имена не дадут запутаться:

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

Как можно заметить, каждый запрос на агрегацию возвращает не сами книги, а только итоговый результат. Таким образом со стороны Python никаких промежуточных объектов создавать не приходится!

Вернёмся к учебному проекту, который моделирует платформу для ведения блогов. Никаких "цен" в этом проекте нет, но задачи для анализа найдутся. Предположим, что нужно для каждой записи в блоге некоторого автора узнать количество комментариев. Агрегация на первый взгляд не подходит: сами посты тоже нужны. Можно решить задачу "в лоб", написав:

author = User.objects.get(id=1)
posts = [(p, p.postcomment_set.count()) for p in author.post_set.all()]

Такое решение имеет своё собственное название – "N+1 запросов" – поскольку будет выполнен один запрос N постов, а затем N запросов комментариев к каждому. Легко представить, насколько это неэффективно.

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

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

Процесс, при котором к каждому объекту из выборки применяется агрегирующая функция, назвается аннотированием. Он описывается вызовом метода .annotate() применительно к менеджеру или QuerySet. Этот метод принимает те же агрегирующие функции, а возвращает метод QuerySet, объекты которого будут всё теми же экземплярами класса модели, но каждый объект будет иметь дополнительные атрибуты. Каждый атрибут будет хранить результат соответствующей агрегации относительно текущего объекта. Например, .aggregate(Count('postcomment')) подсчитает количество всех комментариев, а .annotate(Count('postcomment')) даст количество комментариев к каждому посту. Так выглядит подсчёт количества тегов, которыми помечен каждый пост:

posts = Post.objects.annotate(Count('tags'))
posts[0].tags__count  # только тут финализируется запрос!
# SELECT "blog_post"."id",
#        ...
#        COUNT("blog_post_tags"."tag_id") AS "tags__count"
#   FROM "blog_post"
#   LEFT OUTER JOIN "blog_post_tags"
#     ON ("blog_post"."id" = "blog_post_tags"."post_id")
#  GROUP BY ...
#  LIMIT 1

# Execution time: 0.000563s [Database: default]

# => 2

Здесь новый атрибут получил имя "tags__count", но имя можно было указать вручную, как и в случае обычной агрегации.

Аннотирование и дубликаты в выдаче

Если вы уже имеете некоторый опыт в SQL, вы можете задаться вопросом: а не добавляет ли OUTER JOIN, который можно заметить в примере выше, в выборку дублирующиеся элементы, если присовокупляемые сущности соотносятся с текущей как "многие к одному"? Добавляет! Более того, агрегация в таких случаях даёт неверные результаты, так как учитывает и повторяющиеся строки. И тем больше дублей вы увидите, чем больше разных связей "многие к одному" задействуете (и даже одну и ту же, но несколько раз).

Увы, в общем виде эту проблему не решить. Но конкретно агрегирующая функция Count имеет опцию distinct=True, которая убирает дублирование, пока вы используете только этот вид аннотаций и каждый Count используете с distinct=True.

Агрегация аннотированных значений

Аннотирование позволяет добавить вычислимые данные к каждому элементу запроса, а это значит, что можно выполнить итоговую агрегацию с использованием этих значений! Получение среднего количества тегов среди всех постов будет выглядеть так:

Post.objects.annotate(Count('tags')).aggregate(Avg('tags__count'))
# SELECT AVG("tags__count")
#   FROM (
#         SELECT COUNT("blog_post_tags"."tag_id") AS "tags__count"
#           FROM "blog_post"
#           LEFT OUTER JOIN "blog_post_tags"
#             ON ("blog_post"."id" = "blog_post_tags"."post_id")
#          GROUP BY "blog_post"."id"
#        ) subquery

# Execution time: 0.000361s [Database: default]

# => {'tags__count__avg': 1.5}

Автоматическое имя уже выглядит длинновато, но при этом описывает значение максимально понятно: "среднее количество тегов".

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

Самостоятельная работа

1. В учебном проекте создайте несколько постов и тегов
2. Пометьте разные посты разным количеством тегов
3. Постройте запрос, который будет возвращать "Top 3" тега по количеству помеченных ими постов.

Дополнительные материалы

1. Django: Aggregation.

---

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Об обучении на Хекслете

• Статья «Как учиться и справляться с негативными мыслями»
• Статья «Ловушки обучения»
• Статья «Сложные простые задачи по программированию»
• Вебинар «Как самостоятельно учиться»