Как в Django выполнить GROUP BY?

GROUP BY в Django ORM

GROUP BY — это SQL операция для группировки строк по одному или нескольким полям и расчёта агрегатных функций. В Django это реализуется через метод .values() с annotate().

Базовый GROUP BY

SQL запрос:

SELECT category, COUNT(*) as count
FROM products
GROUP BY category;

Django ORM:

from django.db.models import Count

results = Product.objects.values("category").annotate(count=Count("id"))
for row in results:
    print(f"{row['category']}: {row['count']}")
# Output: Electronics: 150, Books: 200, Clothing: 100

GROUP BY с несколькими полями

SQL:

SELECT category, brand, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category, brand;

Django:

from django.db.models import Count, Avg

results = Product.objects.values("category", "brand").annotate(
    count=Count("id"),
    avg_price=Avg("price")
)

for row in results:
    print(f"{row['category']} - {row['brand']}: {row['count']} items")

Агрегатные функции в Django

from django.db.models import (
    Count,      # Количество
    Sum,        # Сумма
    Avg,        # Среднее
    Min,        # Минимум
    Max,        # Максимум
    StdDev,     # Стандартное отклонение
    Variance    # Дисперсия
)

results = Product.objects.values("category").annotate(
    count=Count("id"),
    total_price=Sum("price"),
    average_price=Avg("price"),
    min_price=Min("price"),
    max_price=Max("price")
)

Фильтрация перед GROUP BY (WHERE)

SQL:

SELECT category, COUNT(*) as count
FROM products
WHERE price > 100
GROUP BY category;

Django:

results = Product.objects.filter(price__gt=100).values("category").annotate(
    count=Count("id")
)

Фильтрация после GROUP BY (HAVING)

SQL:

SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 50;

Django:

results = Product.objects.values("category").annotate(
    count=Count("id")
).filter(count__gt=50)

for row in results:
    print(f"{row['category']}: {row['count']}")

GROUP BY с JOIN (внешние ключи)

Модели:

class Category(models.Model):
    name = models.CharField(max_length=100)

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    price = models.DecimalField(max_digits=10, decimal_places=2)

Django:

from django.db.models import Count, Avg

results = Category.objects.annotate(
    product_count=Count("product"),
    avg_price=Avg("product__price")
)

for category in results:
    print(f"{category.name}: {category.product_count} products")

Условные агрегации

Django (с использованием Case/When):

from django.db.models import Case, When, Value, Count, IntegerField

results = Product.objects.values("category").annotate(
    total=Count("id"),
    expensive_count=Count(
        Case(
            When(price__gt=100, then=Value(1)),
            output_field=IntegerField()
        )
    )
)

Raw SQL для сложных GROUP BY

Если GROUP BY в Django ORM слишком сложный:

from django.db import connection

query = """
    SELECT category, COUNT(*) as count, AVG(price) as avg_price
    FROM products
    WHERE price > %s
    GROUP BY category
    HAVING COUNT(*) > %s
    ORDER BY count DESC
"""

with connection.cursor() as cursor:
    cursor.execute(query, [100, 10])
    columns = [col[0] for col in cursor.description]
    results = [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

for row in results:
    print(row)

Производительность GROUP BY

Хорошо (не загружает весь запрос):

results = Product.objects.values("category").annotate(
    count=Count("id")
)

for row in results:
    print(row)

Хорошо для больших результатов:

results = Product.objects.values("category").annotate(
    count=Count("id")
).iterator()

for row in results:
    process(row)

Полный практический пример

from django.db.models import Count, Avg, Case, When, Value, DecimalField, Sum

report = Product.objects.values("category__name").annotate(
    total_products=Count("id"),
    avg_price=Avg("price"),
    expensive_count=Count(
        Case(
            When(price__gt=100, then=Value(1)),
        )
    ),
    expensive_total=Sum(
        Case(
            When(price__gt=100, then="price"),
            default=Value(0),
            output_field=DecimalField()
        )
    )
).order_by("-total_products")

for item in report:
    print(f"Category: {item['category__name']}")
    print(f"Total: {item['total_products']}")
    print(f"Avg price: {item['avg_price']}")

Вывод

GROUP BY в Django реализуется через values() + annotate(). Для простых случаев это работает отлично. Для сложных — используй raw SQL. Помни о производительности и фильтруй данные как можно раньше.