Разбираемся с Group by в Django с SQL

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

В этой статье я поставил QuerySets и SQL рядом и рассказать о каждом типе группировке отдельно.

Table of Contents

---

Как работает Group By в Django

Для демонстрации различных запросов GROUP BY я буду использовать модель User из встроенного в Django приложения django.contrib.auth.

>>> from django.contrib.auth.models import User


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

SQL LOGGING

Чтобы увидеть, какой SQL фактически выполняется в Django, вы можете включить ведение журнала SQL в настройках Django (turn on SQL logging in the Django settings).

Как считать строки

Давайте посчитаем, сколько у нас пользователей:

>>> User.objects.count()
20

SELECT
    COUNT(*)
FROM
    auth_user;

Подсчет строк настолько распространен, что Django включает специальную функцию count для него прямо в QuerySet. В отличие от других QuerySets, которые мы увидим далее, count возвращает число.

Как использовать агрегатные функции

Django предлагает еще два способа подсчета строк в таблице.

Начнем с  aggregate:

from django.db.models import Count

>>> User.objects.aggregate(Count('id'))
{'id__count': 20}


SELECT
    COUNT(id) AS id__count
FROM
    auth_user;

Для использования aggregate мы импортировали функцию агрегирования Count. Функция принимает выражение для подсчета. В нашем случае мы использовали имя идентификатора столбца id для подсчета всех строк в таблице.

AGGREGATE NULL

Агрегаты игнорируют значения NULL. Подробнее о том, как агрегаты обрабатывают NULL, см. В разделе 12 Common Mistakes and Missed Optimization Opportunities in SQL.

Результатом aggregate будет словарь:

>>> from django.db.models import Count
>>> User.objects.aggregate(Count('id'))
{'id__count': 20}


Имя ключа происходит от имени поля и имени агрегата. В данном случае это id__count. Рекомендуется не полагаться на это соглашение об именах, а вместо этого указывать свое имя:

SELECT
    COUNT(id) as total
FROM
    auth_user;

>>> from django.db.models import Count
>>> User.objects.aggregate(total=Count('id'))
{'total': 20}


Имя аргумента для aggregate — это также имя ключа в результирующем словаре.

Как использовать Group By

Используя aggregate, мы получили результат применения агрегатной функции ко всей таблице. Это полезно, но обычно мы хотим применить агрегацию к группам строк.

Давайте посчитаем пользователей по их активному статусу:

(User.objects
.values('is_active')
.annotate(total=Count('id')))

<QuerySet [{'is_active': True, 'total': 20}]>

SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active

На этот раз мы использовали функцию annotate. Для создания GROUP BY мы используем комбинацию values и annotate:

• values('is_active'): что группировать
• annotate(total=Count('id')): что агрегировать

Порядок важен: невозможность вызова values до annotate не приведет к агрегированным результатам.

Как и aggregate, имя аргумента для annotate является ключом в результате оцененного QuerySet. В этом случае это total.

Как использовать Filter в QuerySet с Group By

Чтобы применить агрегацию к отфильтрованному запросу, вы можете использовать filter в любом месте запроса. Например, учитывайте только штатных пользователей по их активному статусу:

(User.objects
.values('is_active')
.filter(is_staff=True)
.annotate(total=Count('id')))

<QuerySet [{'is_active': True, 'total': 1}]>

SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
WHERE
    is_staff = True
GROUP BY
    is_active

Как сортировать QuerySet с Group By

Как и filter, для сортировки набора запросов используйте order_by в любом месте запроса:

(User.objects
.values('is_active')
.annotate(total=Count('id'))
.order_by('is_staff', 'total'))

<QuerySet [{'is_active': True, 'total': 19}, {'is_active': True, 'total': 1}]>

SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active
ORDER BY
    is_active,
    total

Обратите внимание, что вы можете сортировать как по ключу GROUP BY, так и по полю агрегирования.

Как комбинировать несколько Aggregations

Чтобы создать несколько агрегатов одной группы, добавьте несколько аннотаций:

from django.db.models import Max

(User.objects
.values('is_active')
.annotate(
    total=Count('id'),
    last_joined=Max('date_joined'),
))

<QuerySet [{'is_active': True, 'total': 20, 'last_joined': datetime.datetime(2020, 2, 20, 12, 40, 28, tzinfo=<UTC>)}]>

SELECT
    is_active,
    COUNT(id) AS total,
    MAX(date_joined) AS last_joined
FROM
    auth_user
GROUP BY
    is_active

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

Как группировать несколько полей

Так же, как и при выполнении нескольких агрегаций, мы также можем сгруппировать по нескольким полям. Например, сгруппировать по активному статусу и статусу персонала:

(User.objects
.values('is_active', 'is_staff')
.annotate(total=Count('id')))

<QuerySet [{'is_active': True, 'is_staff': False, 'total': 19}, {'is_active': True, 'is_staff': True, 'total': 1}]>

SELECT
    is_active,
    is_staff,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active,
    is_staff

Результат этого запроса включает is_active, is_staff и количество пользователей в каждой группе.

Как группировать с «Выражением» (Expression)

Другим распространенным вариантом использования GROUP BY является группирование по выражению. Например, подсчет количество пользователей, которые присоединились каждый год:

(User.objects
.values('date_joined__year')
.annotate(total=Count('id')))

<QuerySet [{'date_joined__year': 2020, 'total': 20}]>

SELECT
    EXTRACT('year' FROM date_joined),
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    EXTRACT('year' FROM date_joined)

Обратите внимание, что для получения года от даты мы использовали специальное выражение <field>__year при первом вызове values(). Результатом запроса является dict, а имя ключа будет date_joined__year.

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

from django.db.models import (
    ExpressionWrapper,
    Q, F, BooleanField,
)

(User.objects
.annotate(
    logged_since_joined=ExpressionWrapper(
        Q(last_login__gt=F('date_joined')),
        output_field=BooleanField(),
    )
)
.values('logged_since_joined')
.annotate(total=Count('id'))
.values('logged_since_joined', 'total'))


<QuerySet [{'logged_since_joined': 2, 'total': 19}, {'logged_since_joined': True, 'total': 1}]>

SELECT
  last_login > date_joined AS logged_since_joined,
  COUNT(id) AS total
FROM
  auth_user
GROUP BY
  last_login > date_joined

Выражение здесь довольно сложное. Сначала мы используем annotate для построения выражения и помечаем его как ключ GROUP BY, ссылаясь на выражение через logged_since_joined в следующем вызове values(). А далее уже действуем как обычно.

Как использовать условную агрегацию

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

from django.db.models import F, Q

(User.objects
.values('date_joined__year')
.annotate(
    staff_users=(
        Count('id', filter=Q(is_staff=True))
    ),
    non_staff_users=(
        Count('id', filter=Q(is_staff=False))
    ),
))


<QuerySet [{'date_joined__year': 2020, 'staff_users': 1, 'non_staff_users': 19}]>

SELECT
    EXTRACT('year' FROM date_joined),

    COUNT(id) FILTER (
        WHERE is_staff = True
    ) AS staff_users,

    COUNT(id) FILTER (
        WHERE is_staff = False
    ) AS non_staff_users

FROM
    auth_user
GROUP BY
    EXTRACT('year' FROM date_joined)

Приведенный выше SQL взят из PostgreSQL, который наряду с SQLite в настоящее время является единственным бэкэндом базы данных, который поддерживает ярлык синтаксиса FILTER (формально называемый «выборочные агрегаты» «selective aggregates»). Для других серверных баз данных ORM вместо этого будет использовать CASE … WHEN.

Совет

Я ранее писал про агрегаты с фильтрами. Посмотрите мои 9 советов по Django для работы с базами данных (9 Django tips for working with databases).

Как использовать Having

Предложение HAVING используется для фильтрации результатов статистической функции. Например, найдите годы, в которые вступили более 100 пользователей:

(User.objects
.annotate(year_joined=F('date_joined__year'))
.values('is_active')
.annotate(total=Count('id'))
.filter(total__gt=100))


SELECT
    is_active,
    COUNT(id) AS total
FROM
    auth_user
GROUP BY
    is_active
HAVING
    COUNT(id) > 100

Фильтр по аннотированному итоговому полю total добавил предложение HAVING в сгенерированный SQL.

Как группировать по Distinct

Для некоторых агрегатных функций, таких как COUNT, иногда желательно учитывать только определенные случаи. Например, сколько разных фамилий существует для каждого активного статуса пользователя:

(User.objects
.values('is_active')
.annotate(
    total=Count('id'),
    unique_names=Count('last_name', distinct=True),
))


<QuerySet [{'is_active': True, 'total': 20, 'unique_names': 7}]>

SELECT
    is_active,
    COUNT(id) AS total,
    COUNT(DISTINCT last_name) AS unique_names
FROM
    auth_user
GROUP BY
    is_active

Обратите внимание на использование distinct=True в вызове Count.

Как создавать выражения с использованием агрегированных полей

Агрегатные поля часто являются лишь первым шагом к большему вопросу. Например, каков процент уникальных фамилий по активному статусу пользователя:

from django.db.models import FloatField
from django.db.models.functions import Cast

(User.objects
.values('is_active')
.annotate(
    total=Count('id'),
    unique_names=Count('last_name', distinct=True),
)
.annotate(pct_unique_names=(
    Cast('unique_names', FloatField())
    / Cast('total', FloatField())
)))

<QuerySet [{'is_active': True, 'total': 20, 'unique_names': 7, 'pct_unique_names': 0.35}]>

SELECT
    is_active,
    COUNT(id) AS total,
    COUNT(DISTINCT last_name) AS unique_names,
    (COUNT(DISTINCT last_name)::float
        / COUNT(id)::float) AS pct_unique_names
FROM
    auth_user
GROUP BY
    is_active

Первый annotate() определяет совокупные поля. Второй annotate() использует статистическую функцию для создания выражения.

Как группировать по различным отношениям

До сих пор мы использовали только данные в одной модели, но агрегаты часто используются в отношениях. Более простой сценарий — отношение «один к одному» или отношение внешнего ключа. Например, скажем, у нас есть UserProfile с отношением «один к одному» с User, и мы хотим подсчитывать пользователей по типу профиля:

(User.objects
.values('user_profile__type')
.annotate(total=Count('id')))

SELECT
    p.type,
    COUNT(u.id) AS total
FROM
    auth_user u
    JOIN user_profile p ON u.id = p.user_id
GROUP BY
    p.type

Как и выражения GROUP BY, использование отношений в values будет группировать по этому полю. Обратите внимание, что имя типа профиля пользователя в результате будет «user_profile__type».

Как группировать отношения «многие ко многим»

Более сложный тип отношений — это отношение «многие ко многим». Например, подсчитайте, во сколько групп входит каждый пользователь:

(User.objects
.annotate(memberships=Count('groups'))
.values('id', 'memberships'))


<QuerySet [{'id': 14, 'memberships': 0}, {'id': 17, 'memberships': 0}, {'id': 12, 'memberships': 0}, {'id': 8, 'memberships': 0}, {'id': 15, 'memberships': 0}, {'id': 10, 'memberships': 0}, {'id': 11, 'memberships': 0}, {'id': 18, 'memberships': 0}, {'id': 16, 'memberships': 0}, {'id': 6, 'memberships': 0}, {'id': 19, 'memberships': 0}, {'id': 2, 'memberships': 0}, {'id': 3, 'memberships': 0}, {'id': 23, 'memberships': 0}, {'id': 13, 'memberships': 0}, {'id': 5, 'memberships': 0}, {'id': 22, 'memberships': 0}, {'id': 9, 'memberships': 0}, {'id': 24, 'memberships': 0}, {'id': 7, 'memberships': 0}]>

SELECT
    u.id,
    COUNT(ug.group_id) AS memberships
FROM
    auth_user
    LEFT OUTER JOIN auth_user_groups ug ON (
        u.id = ug.user_id
    )
GROUP BY
    u.id

Пользователь может быть членом более чем одной группы. Для подсчета количества групп, членом которых является пользователь, мы использовали связанное имя «groups» в модели User. Если связанное имя не задано явно (и явно не отключено), Django автоматически сгенерирует имя в формате {related model model}_set. Например, group_set.

---

Заключение

Для более глубокого изучения ORM и GROUP BY, просмотрите следующие ссылки:

• How to use grouping sets in Django: Статья о продвинутой группировке по таким техникам, как группировка по cube, группировка по rollup и группировка по grouping sets.
• How to Get the First or Last Value in a Group Using Group By in SQL: Небольшая хитрость с использованием массивов в PostgreSQL.
• 12 Common Mistakes and Missed Optimization Opportunities in SQL: 12 распространенных ошибок и упущенных возможностей оптимизации в SQL.
• Django Aggregation cheat-sheet page: Документация по общим запросам аггрегации.

Оригинальная статья: Understand Group by in Django with SQL