Агрегирующие функции в Django ORM
С агрегирующими функциями в SQL вы знакомы: они возвращают из БД сумму, максимум или среднее арифметическое по определённому полю для всех записей в таблице или для выборки: «SQL, выбери в таблице joke все записи за 1.04.2001 и примени к ним агрегирующую функцию: найди запись с максимальным значением в столбце likes».
База данных подсчитает и выведет полученные данные в отдельный столбец результирующей выборки.
Для группировки строк используется специальный запрос GROUP BY, в котором перечисляются столбцы, по которым должна идти группировка. Это значит, что если в таблице несколько строк с одним и тем же значением в указанном поле, то такие строки объединятся в одну, а над другими столбцами можно провести групповые операции.
Слоноводы несколько дней кормят слонов ивовыми вениками и записывают, кто сколько съел.
Затем слоноводы хотят найти для каждого слона максимальное количество съеденного за один день, и для этого делают запрос к базе.
Скопировать кодSQL
SELECT date, name, MAX(broom)
FROM brooms
GROUP BY name;
«Под капотом» происходит следующее: сначала в колонке name будут найдены и сгруппированы все совпадающие записи, получится три группы:
все записи по Аристарху:
все записи по Джульетте:
и все записи по Кузе:
Затем функция MAX(broom) в каждой из этих групп найдет максимальное значение в колонке broom, и в результирующую выборку будут выведены имена и найденные значения.
Результат запроса будет таким:
Если в БД какого-нибудь магазина в таблице хранятся чеки покупок, можно сгруппировать записи по id покупателя, после чего применить агрегирующую функцию к полю, где указана цена чека — и посчитать максимальную или среднюю сумму заказов определённого покупателя.
А можно просто сложить все value по отдельности для A, B и C:
Если не указать GROUP BY — агрегирующая функция будет применена ко всей таблице. Например SELECT SUM(Value) FROM sample_table; вернет значение 140 для примера на иллюстрации, а SELECT MAX(broom) FROM brooms; вернёт 44, максимальное значение во всей колонке broom из таблицы со слонами и вениками.
Метод count()
Задача подсчёта записей в таблице или в выборке столь популярна, что в Django ORM она получила несколько вариантов решения. Чтобы узнать количество полученных строк, можно вызвать метод count() для objects, дописав его к конструкции, делающей выборку:
Скопировать кодPYTHON
>>> Post.objects.filter(pub_date__month__gt=6, pub_date__year=1854).count()
(0.012) SELECT COUNT(*) AS "__count" FROM "posts_post" WHERE (django_datetime_extract('month', "posts_post"."pub_date", 'UTC') > 6 AND "posts_post"."pub_date" BETWEEN '1854-01-01
00:00:00' AND '1854-12-31 23:59:59.999999'); args=(6, '1854-01-01 00:00:00', '1854-12-31 23:59:59.999999')
30
Когда нужно узнать лишь число записей, но сами записи не нужны — не вызывайте метод all(), применяйте count().
Метод all() заставит базу прочитать и передать в код весь объем данных, а count() выполнит всю работу на стороне базы и вернет лишь одно число. Загрузка и обработка всех данных в такой ситуации — это пустой расход ресурсов.
Скопировать кодPYTHON
if User.objects.all():
print("Пользователи есть")
if User.objects.count():
print("Пользователи есть")
Метод aggregate()
Метод aggregate() применяет агрегирующие функции к определённой выборке или ко всей таблице.
В Django есть несколько агрегирующих функций, вот самые популярные из них:
- Avg: вернёт среднее значение по указанной колонке в выборке
- Count: вернёт количество записей в выборке, как и метод
count(), описанный выше - Max: вернёт максимальное значение по указанной колонке в выборке
- Min: вернёт минимальное значение по указанной колонке в выборке
- Sum: вернёт сумму значений по указанной колонке в выборке
Эти функции хранятся в модуле django.db.models, перед применением их надо импортировать в код.
Найдём самый большой id и пересчитаем объекты в модели Post при помощи метода aggregate():
Скопировать кодPYTHON
>>> from django.db.models import Max, Count
>>> Post.objects.aggregate(Max("id"))
(0.000) SELECT MAX("posts_post"."id") AS "id__max" FROM "posts_post"; args=()
{'id__max': 43}
>>> Post.objects.aggregate(Count("id"))
(0.000) SELECT COUNT("posts_post"."id") AS "id__count" FROM "posts_post"; args=()
{'id__count': 37}
37
Связи между таблицами
При создании модели Post мы добавили в неё ссылку на автора, на модель User, и указали related_name="posts".
Скопировать кодPYTHON
class Post(models.Model):
author = models.ForeignKey(
User, on_delete=models.CASCADE, related_name="posts"
)
У модели User автоматически появится свойство posts, оно ссылается на все записи текущего автора.
Скопировать кодPYTHON
>>> leo = User.objects.get(id=2)
(0.000) SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 2; args=(2,)
>>> leo.username
'leo'
>>> leo.posts.count()
(0.000) SELECT COUNT(*) AS "__count" FROM "posts_post" WHERE "posts_post"."author_id" = 2; args=(2,)
36
Свойство posts у объекта модели User появилось в результате того, что модель Post ссылается на User. И, благодаря магии ORM, мы можем получить все записи автора, обратившись к свойству posts.
Как только вы создаёте в модели поле со ссылкой на другую модель — у второй модели появляется новое свойство, «обратная связь», и к этому свойству можно обращаться, как к любому другому.
Свойства, ссылающиеся на объекты, имеют специальный тип: менеджер объектов. До сих пор мы работали с менеджером объектов objects, делая запросы вида User.objects.get(id=2) или Post.objects.all().
Свойство posts — такой же менеджер объектов, как и objects, разница лишь в том, что оно было создано при связывании моделей.
Для менеджеров объектов можно вызывать метод count(), который пересчитает связанные объекты. Django сам разберется, как правильно составить запрос в этом случае.
Чтобы применить агрегирующие функции к связанным данным из других таблиц, запрос делается через аннотирование, методом annotate().
Метод annotate()
Чтобы получить количество записей, созданных каждым пользователем, нужно сгруппировать несколько таблиц и добавить объекту новое свойство, которое будет содержать количество связанных с ним объектов в другой таблице.
Конечно, можно было бы в цикле пройтись по всем пользователям, вызывая метод count() менеджера объектов posts. Но это создало бы огромную нагрузку на базу и каскад однотипных запросов.
В чистом SQL вопрос решается так: в результирующую выборку попадают запрошенные столбцы исходных таблиц и дополнительные столбцы с результатами вычислений.
Похожим образом работает метод annotate() в ORM, но в результате к полученным объектам добавляется новое свойство, содержащее результат вычисления.
В следующем примере аргумент posts_count — это имя нового свойства объекта, оно появится у объектов модели User:
Скопировать кодPYTHON
>>>> annotated_results = User.objects.annotate(posts_count = Count('posts'))
>>>
>>> annotated_results
(0.001) SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined", COUNT("posts_post"."id") AS "posts_count" FROM "auth_user" LEFT OUTER JOIN "posts_post" ON ("auth_user"."id" = "posts_post"."author_id") GROUP BY "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" LIMIT 21; args=()
<QuerySet [<User: admin>, <User: leo>]>
Скопировать кодPYTHON
>>> for item in annotated_results:
... print(f"Постов у пользователя {item.username}: {item.posts_count}")
...
(0.000) SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined", COUNT("posts_post"."id") AS "posts_count" FROM "auth_user" LEFT OUTER JOIN "posts_post" ON ("auth_user"."id" = "posts_post"."author_id") GROUP BY "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined"; args=()
Постов у пользователя admin: 2
Постов у пользователя leo: 36
Разница между annotate() и aggregate()
Метод annotate() возвращает объекты и добавляет к ним новые свойства:
Скопировать кодPYTHON
>>> rez = User.objects.annotate(written_posts = Count('posts'))
>>> rez[1].written_posts
36
Метод aggregate() отдает только значение, результат работы агрегирующей функции:
Скопировать кодPYTHON
>>> Checks.objects.aggregate(average_price=Avg('price'))
{'average_price': 127.01}
