Понимание `group by` в Django с помощью SQL

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

В этой статье я поместил QuerySets и SQL рядом. Если вам удобнее всего использовать SQL, это шпаргалка по Django GROUP BY для вас.

Как сгруппировать `group by` в Django

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

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

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

Логирование SQL

Чтобы увидеть SQL, фактически выполняемый Django, вы можете включить ведение журнала SQL в настройках Django.

Как подсчитывать строки

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

SELECT
    COUNT(*)
FROM
    auth_user;

User.objects.count()

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

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

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

Начнем с aggregate:

SELECT
    COUNT(id) AS id__count
FROM
    auth_user;

from django.db.models import Count

User.objects.aggregate(Count('id'))

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

Агрегации игнорируют значения NULL.

Результатом агрегирования является словарь:

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

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

SELECT
    COUNT(id) as total
FROM
    auth_user;

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

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

Как делать группировку Group By

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

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

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

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

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

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

Порядок важен: отказ от вызова значений перед аннотацией не приведет к совокупным результатам.

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

Как отфильтровать QuerySet с помощью Group By

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

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

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

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

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

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

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

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

Как объединить несколько агрегатов

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

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

from django.db.models import Max

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

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

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

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

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

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

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

Как сгруппировать по выражению

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

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

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

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

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

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

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

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

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

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

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)

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))
    ),
))

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

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

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

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

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

The filter on the annotated field total added an HAVING clause in the generated SQL.

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

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

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

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

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

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

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

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

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

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())
))

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

Как группировать по взаимосвязям

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

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

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

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

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

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

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

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

A user can be a member of more than one group. To count the number of groups the user is member of we used the related name "groups" in the User model. If the related name is not explicitly set (and not explicitly disabled), Django will automatically generate a name in the format {related model model}_set. For example, group_set.

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

 

Перевод https://django.fun/tutorials/ponimanie-group-v-django-s-pomoshyu-sql/

Вернуться на верх