Annotate и Aggregate в Django ORM | Курс Django ORM урок 3.3
4
Цель урока
Разобрать annotate() и aggregate(), два механизма вычисления агрегатных значений в Django ORM. Понять что такое GROUP BY и как оно возникает через annotate(). Научиться фильтровать аннотированные значения и избегать проблем с Meta.ordering при агрегациях.
Необходимые знания
- Урок 3.2: F expressions, ExpressionWrapper
- Урок 2.4: order_by(), сброс Meta.ordering
- Базовое понимание GROUP BY, COUNT, SUM, AVG в SQL
aggregate() - агрегация всего QuerySet
aggregate() вычисляет одно или несколько агрегатных значений для всего QuerySet и возвращает словарь. Запрос выполняется немедленно.
Поля моделей, которые используются в примерах ниже:
Product: id, name, price (Decimal), stock (int), is_active (bool)
Order: id, status (str), total_price (Decimal), user (FK → User)
User: id + orders (обратная связь от Order.user)
from django.db.models import Count, Sum, Avg, Min, Max
# Средняя цена всех активных продуктов
result = Product.objects.filter(is_active=True).aggregate(
avg_price=Avg("price") # avg_price это ключ в возвращаемом словаре
)
print(result) # {"avg_price": Decimal("749.50")}
SELECT AVG(price) AS avg_price
FROM shop_product
WHERE is_active = true;
Несколько агрегатных функций за один запрос:
stats = Product.objects.filter(is_active=True).aggregate(
total_count=Count("id"),
avg_price=Avg("price"),
min_price=Min("price"),
max_price=Max("price"),
total_stock=Sum("stock"),
)
SELECT
COUNT(id) AS total_count,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
SUM(stock) AS total_stock
FROM shop_product
WHERE is_active = true;
Один запрос, все результаты.
Агрегация через связи
# Количество заказов у пользователя
# "orders" это обратная связь: Order.user = ForeignKey(User, related_name="orders")
from django.contrib.auth.models import User
User.objects.filter(id=1).aggregate(order_count=Count("orders"))
SELECT COUNT(shop_order.id) AS order_count
FROM auth_user
LEFT OUTER JOIN shop_order ON auth_user.id = shop_order.user_id
WHERE auth_user.id = 1;
# Общая выручка по всем доставленным заказам
Order.objects.filter(status="delivered").aggregate(
revenue=Sum("total_price")
)
SELECT SUM(total_price) AS revenue
FROM shop_order
WHERE status = 'delivered';
Встроенные агрегатные функции
| Функция | SQL | Описание |
|---|---|---|
Count("field") |
COUNT(field) |
Количество непустых значений |
Count("id") |
COUNT(id) |
Количество строк |
Count("id", distinct=True) |
COUNT(DISTINCT id) |
Количество уникальных значений |
Sum("field") |
SUM(field) |
Сумма |
Avg("field") |
AVG(field) |
Среднее |
Min("field") |
MIN(field) |
Минимум |
Max("field") |
MAX(field) |
Максимум |
StdDev("field") |
STDDEV(field) |
Стандартное отклонение |
Variance("field") |
VARIANCE(field) |
Дисперсия |
Django 6.0 добавил два новых агрегата:
StringAgg, конкатенация строк через разделитель (раньше только PostgreSQL, теперь кросс-БД):
from django.db.models import StringAgg, Value
# Список всех slug через запятую
Category.objects.aggregate(
all_slugs=StringAgg("slug", delimiter=Value(", "))
)
SELECT STRING_AGG(slug, ', ') AS all_slugs FROM shop_category;
AnyValue, произвольное непустое значение из группы, полезно в GROUP BY когда поле не в GROUP BY, но нужно его вывести:
from django.db.models import AnyValue
Product.objects.values("category_id").annotate(
sample_name=AnyValue("name"),
count=Count("id"),
)
SELECT category_id, ANY_VALUE(name) AS sample_name, COUNT(id) AS count
FROM shop_product
GROUP BY category_id;
annotate() - агрегация с группировкой
annotate() добавляет вычисляемое поле к каждому объекту в QuerySet. Под капотом это GROUP BY.
# Количество продуктов в каждой категории
Category.objects.annotate(product_count=Count("products"))
SELECT shop_category.*,
COUNT(shop_product.id) AS product_count
FROM shop_category
LEFT OUTER JOIN shop_product ON shop_category.id = shop_product.category_id
GROUP BY shop_category.id
ORDER BY shop_category.name;
Результат, QuerySet объектов Category, каждый с дополнительным атрибутом product_count:
for category in Category.objects.annotate(product_count=Count("products")):
print(f"{category.name}: {category.product_count} продуктов")
Несколько аннотаций
from django.db.models import Count, Avg, Min, Max
products = Product.objects.annotate(
review_count=Count("reviews"),
avg_rating=Avg("reviews__rating"),
min_rating=Min("reviews__rating"),
max_rating=Max("reviews__rating"),
)
SELECT shop_product.*,
COUNT(shop_review.id) AS review_count,
AVG(shop_review.rating) AS avg_rating,
MIN(shop_review.rating) AS min_rating,
MAX(shop_review.rating) AS max_rating
FROM shop_product
LEFT OUTER JOIN shop_review ON shop_product.id = shop_review.product_id
GROUP BY shop_product.id
ORDER BY shop_product.name;
annotate() с values() - GROUP BY по произвольным полям
По умолчанию annotate() группирует по первичному ключу модели (GROUP BY id). Чтобы группировать по другому полю, используйте values() перед annotate():
# Количество заказов по статусу
Order.objects.values("status").annotate(count=Count("id"))
SELECT status, COUNT(id) AS count
FROM shop_order
GROUP BY status;
# Выручка по месяцам
from django.db.models.functions import TruncMonth
Order.objects.filter(status="delivered").annotate(
month=TruncMonth("created_at")
).values("month").annotate(
revenue=Sum("total_price"),
order_count=Count("id"),
).order_by("month")
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total_price) AS revenue,
COUNT(id) AS order_count
FROM shop_order
WHERE status = 'delivered'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
Фильтрация аннотированных значений
filter() после annotate()
# Категории с более чем 3 продуктами
Category.objects.annotate(
product_count=Count("products")
).filter(
product_count__gt=3
)
SELECT shop_category.*, COUNT(shop_product.id) AS product_count
FROM shop_category
LEFT OUTER JOIN shop_product ON shop_category.id = shop_product.category_id
GROUP BY shop_category.id
HAVING COUNT(shop_product.id) > 3
ORDER BY shop_category.name;
filter() после annotate() транслируется в HAVING, а не WHERE. HAVING фильтрует после группировки.
Условная агрегация - параметр filter= внутри агрегатной функции
# Количество отзывов с rating >= 4 для каждого продукта
Product.objects.annotate(
good_review_count=Count("reviews", filter=Q(reviews__rating__gte=4))
)
SELECT shop_product.*,
COUNT(shop_review.id) FILTER (WHERE shop_review.rating >= 4) AS good_review_count
FROM shop_product
LEFT OUTER JOIN shop_review ON shop_product.id = shop_review.product_id
GROUP BY shop_product.id;
COUNT(...) FILTER (WHERE ...), специфичный для PostgreSQL синтаксис для условной агрегации. Это эффективнее чем CASE WHEN.
Параметр filter= принимает только объект Q, передать lookup напрямую (filter=reviews__rating__gte=4) синтаксически невозможно. Q упаковывает условие в один объект, поэтому и здесь, и в сложных случаях с &, |, ~ используется одинаковый механизм.
Параметр filter=Q(...) в агрегатных функциях работает для всех: Count, Sum, Avg, Min, Max.
# Несколько условных агрегатов в одном запросе
Product.objects.annotate(
total_reviews=Count("reviews"),
positive_reviews=Count("reviews", filter=Q(reviews__rating__gte=4)),
negative_reviews=Count("reviews", filter=Q(reviews__rating__lte=2)),
)
SELECT shop_product.*,
COUNT(shop_review.id) AS total_reviews,
COUNT(shop_review.id) FILTER (WHERE shop_review.rating >= 4) AS positive_reviews,
COUNT(shop_review.id) FILTER (WHERE shop_review.rating <= 2) AS negative_reviews
FROM shop_product
LEFT OUTER JOIN shop_review ON shop_product.id = shop_review.product_id
GROUP BY shop_product.id;
Проблема Meta.ordering и GROUP BY
Это классическая ловушка. Если у модели задан Meta.ordering, Django автоматически добавляет поля сортировки в GROUP BY:
class Product(models.Model):
class Meta:
ordering = ["name"] # сортировка по умолчанию
# Кажется количество продуктов по категориям
Product.objects.values("category_id").annotate(count=Count("id"))
-- Meta.ordering добавляет name в GROUP BY!
SELECT category_id, COUNT(id) AS count
FROM shop_product
GROUP BY category_id, name -- неверно! группируем по category_id и name
ORDER BY name;
Вместо одной строки на категорию получаем одну строку на каждую уникальную пару (category_id, name), фактически по одной строке на продукт.
Решение, явный order_by() в конце:
Product.objects.values("category_id").annotate(count=Count("id")).order_by()
SELECT category_id, COUNT(id) AS count
FROM shop_product
GROUP BY category_id; -- правильно
Правило: при использовании values() + annotate() всегда добавляйте .order_by() в конце, если не нужна конкретная сортировка.
aggregate() vs annotate()
aggregate() |
annotate() |
|
|---|---|---|
| Возвращает | Словарь | QuerySet |
| GROUP BY | Нет (весь QuerySet) | Да (по pk или values) |
| Пример использования | Итоговая статистика | Статистика по каждому объекту |
| SQL | SELECT AVG(...) |
SELECT ..., AVG(...) GROUP BY id |
# aggregate() одно число для всего QuerySet
Product.objects.aggregate(avg=Avg("price"))
# {"avg": Decimal("749.50")}
# annotate() значение для каждого объекта
Product.objects.annotate(review_count=Count("reviews"))
# QuerySet[<Product id=1, review_count=5>, <Product id=2, review_count=2>, ...]
Сортировка по аннотации
# Продукты с наибольшим количеством отзывов
Product.objects.annotate(
review_count=Count("reviews")
).order_by("-review_count")
SELECT shop_product.*, COUNT(shop_review.id) AS review_count
FROM shop_product
LEFT OUTER JOIN shop_review ON shop_product.id = shop_review.product_id
GROUP BY shop_product.id
ORDER BY review_count DESC;
# Категории, отсортированные по средней цене продуктов
Category.objects.annotate(
avg_price=Avg("products__price")
).order_by("-avg_price")
Aggregate с order_by
В Django 6.0 добавили поддержку order_by в агрегатные функции. Это полезно для StringAgg и похожих:
from django.db.models import StringAgg, Value
# Список имен продуктов в алфавитном порядке
Category.objects.annotate(
product_names=StringAgg("products__name", delimiter=Value(", "), order_by="products__name")
)
SELECT shop_category.*,
STRING_AGG(shop_product.name, ', ' ORDER BY shop_product.name) AS product_names
FROM shop_category
LEFT OUTER JOIN shop_product ON shop_category.id = shop_product.category_id
GROUP BY shop_category.id;
Практическое задание
Все задания выполняйте в shell с логированием SQL.
-
Посчитайте общее количество продуктов, среднюю цену и суммарный stock одним
aggregate()запросом. -
Аннотируйте каждую категорию тремя значениями: количество продуктов, средняя цена, количество активных продуктов (через
filter=Q(is_active=True)). Посмотрите SQL. -
Найдите все категории с более чем 2 продуктами, отсортированные по убыванию количества продуктов. Убедитесь что в SQL есть HAVING, а не WHERE.
-
Посчитайте количество заказов по каждому статусу. Используйте
values("status").annotate(count=Count("id")).order_by(). Что изменится если убрать.order_by()в конце? -
Найдите 5 продуктов с наибольшим средним рейтингом (только продукты с хотя бы 2 отзывами).
Возможные ошибки
Забыть order_by() при values() + annotate()
# Meta.ordering ломает GROUP BY
Product.objects.values("category_id").annotate(count=Count("id"))
# Правильно
Product.objects.values("category_id").annotate(count=Count("id")).order_by()
Аннотация через несколько связей без distinct=True
# Если у продукта 3 отзыва и 2 тега - COUNT умножается
Product.objects.annotate(
review_count=Count("reviews"),
tag_count=Count("tags"), # неверно, дает перемноженный результат
)
# Правильно - distinct=True для каждого
Product.objects.annotate(
review_count=Count("reviews", distinct=True),
tag_count=Count("tags", distinct=True),
)
Проблема: при нескольких JOIN на разные таблицы результаты перемножаются (cartesian product). distinct=True решает это через COUNT(DISTINCT ...).
aggregate() вместо annotate() когда нужно значение для каждого объекта
# Возвращает одно число, среднее по всем продуктам
Product.objects.aggregate(avg_rating=Avg("reviews__rating"))
# Возвращает среднее для каждого продукта
Product.objects.annotate(avg_rating=Avg("reviews__rating"))
Фильтрация аннотированного поля до annotate()
# FieldError - поле avg_rating ещё не существует
Product.objects.filter(avg_rating__gt=4).annotate(avg_rating=Avg("reviews__rating"))
# Правильно, сначала annotate, потом filter
Product.objects.annotate(avg_rating=Avg("reviews__rating")).filter(avg_rating__gt=4)
Связь со следующим уроком
В уроке 3.4 разбираем Subquery и Exists, подзапросы в Django ORM. Это инструмент для случаев когда JOIN дает дублирование или когда нужно сравнить значения из связанной таблицы с каждой строкой основной выборки.
Подписывайтесь на мой Telegram канал
Если вам нужен ментор и вы хотите научиться веб-разработке, узнать подробнее
Авторизуйтесь, чтобы оставить комментарий.
Нет комментариев.
Тут может быть ваша реклама
Пишите info@aisferaic.ru
Похожие туториалы
