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

Spread the love

Агрегация является источником путаницы в любом типе 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, просмотрите следующие ссылки:

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

Была ли вам полезна эта статья?
[2 / 5]

Spread the love
Подписаться
Уведомление о
guest
3 Комментарий
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Виталий
Виталий
20 дней назад

Отличная статья!
Но есть вопрос: как быть, когда нужна группировка по полю подзапроса с union? — никак не осилю. annotate после union работать отказывается.

Last edited 20 дней назад by Виталий
Editorial Team
Администратор
Editorial Team
20 дней назад
Reply to  Виталий

Вроде что то похожее есть на stackoverflow: https://stackoverflow.com/questions/48028411/django-queryset-union-appears-not-to-be-working-when-combined-with-annotate

И есть официальное описание этой проблемы https://code.djangoproject.com/ticket/30659:
Да, annotate () не поддерживается. Согласно документации, «только LIMIT, OFFSET, COUNT (*), ORDER BY и указание столбцов (например, slicing, count (), order_by () и values () / values_list ()] разрешены в результирующем QuerySet. «.

Анонимно
Анонимно
14 дней назад

РазбЕраемся.))
Мало быть программистом, надо еще быть грамотным.

3
0
Будем рады вашим мыслям, пожалуйста, прокомментируйте.x
()
x