在任何类型的ORM中，聚合都是造成混乱的根源，而Django也是如此。 该文档提供了各种示例和备忘单，它们演示了如何使用ORM对数据进行分组和聚合，但是我决定从另一个角度进行研究。
在本文中，我将QuerySet和SQL并排放置。 如果您最喜欢SQL，那么这是适合您的Django GROUP BY速查表。
如何在Django中分组
为了演示不同的GROUP BY查询，我将使用Django内置的django.contrib.auth应用程序中的模型。
|
1 |
>>> from django.contrib.auth.models import User |
Django ORM生成带有长别名的SQL语句。 为简便起见，我将展示Django执行的内容的经过整理的等效版本。
要查看Django实际执行的SQL，可以在Django设置中打开SQL日志记录。
如何计算行数▶
让我们计算一下我们有多少用户：
|
1 2 3 4 5 6 7 8 |
SELECT COUNT(*) FROM auth_user; ----------------- User.objects.count() |
对行进行计数非常普遍，以至于Django在QuerySet上就为其包含了一个函数。 与其他查询集不同，计数将返回一个数字。
如何使用汇总功能▶
Django提供了两种其他方法来对表中的行进行计数。
我们将从聚合开始：
|
1 2 3 4 5 6 7 8 9 |
SELECT COUNT(id) AS id__count FROM auth_user; --------------------------------- from django.db.models import Count User.objects.aggregate(Count('id')) |
为了使用聚合，我们导入了聚合函数Count。 该函数接受要计数的表达式。 在这种情况下，我们使用主键列ID的名称来计数表中的所有行。
聚合的结果是一个字典：
|
1 2 3 |
>>> from django.db.models import Count >>> User.objects.aggregate(Count('id')) {"id__count": 891} |
键的名称是从字段名称和聚合名称派生的。 在这种情况下，它是id__count。 最好不要使用此命名约定，而要提供自己的名称：
|
1 2 3 4 5 6 7 8 9 10 |
SELECT COUNT(id) as total FROM auth_user; --------------------------------- >>> from django.db.models import Count >>> User.objects.aggregate(total=Count('id')) {"total": 891} |
要聚合的参数的名称也是结果字典中键的名称。
如何分组
使用聚合，我们得到了将聚合函数应用于整个表的结果。 这很有用，但通常我们希望将聚合应用于行组。
让我们根据用户的活动状态来对其进行计数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT is_active, COUNT(id) AS total FROM auth_user GROUP BY is_active ------------------------------------ (User.objects .values('is_active') .annotate(total=Count('id'))) |
这次我们使用了函数注释。 为了产生GROUP BY，我们使用值和注释的组合：
values（’is_active’）：分组依据
annotate（total = Count（’id’））：要汇总的内容
顺序很重要：在注释前调用值不会产生合计结果。
就像聚合一样，要注释的参数名称是求值的QuerySet的键。 在这种情况下，总计。
如何使用分组依据筛选查询集▶
要将聚合应用于过滤的查询，可以在查询中的任何位置使用过滤器。 例如，仅按职员用户的活动状态计数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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'))) |
如何使用分组依据对查询集进行排序▶
像过滤器一样，要对查询集进行排序，请在查询中的任何位置使用order_by：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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键和聚合字段进行排序。
如何合并多个聚合▶
要产生同一组的多个聚合，请添加多个注释：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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'), )) |
该查询将产生活动和不活动用户的数量，以及每个组中用户加入的最后日期。
如何按多个字段分组▶
就像执行多个聚合一样，我们可能还想按多个字段分组。 例如，按活动状态和人员状态分组：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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的另一个常见用例是按表达式分组。 例如，计算每年加入的用户数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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'))) |
请注意，为了从日期开始获取年份，我们在第一次调用values（）时使用了特殊表达式<field> __ year。 查询的结果是一个dict，键的名称将为date_joined__year。
有时，内置表达式还不够，您需要汇总更复杂的表达式。 例如，对自注册后登录的用户进行分组：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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构建表达式，然后在以下对values（）的调用中引用该表达式，将其标记为GROUP BY键。 从这里开始，它是完全一样的。
如何使用条件汇总▶
使用条件聚合，您只能聚合组的一部分。 当您有多个聚合时，条件会派上用场。 例如，按签约年份计算职员和编外用户的数量：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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个以上的用户加入的年份：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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)) |
带注释的字段总数上的过滤器在生成的SQL中添加了HAVING子句。
如何按区分分组▶
对于某些总计函数（例如COUNT），有时希望仅对不同的事件进行计数。 例如，每个用户活动状态有多少个不同的姓氏：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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), )) |
请注意，在对Count的调用中使用了distinct = True。
如何使用汇总字段创建表达式▶
汇总字段通常只是一个更大问题的第一步。 例如，按用户活动状态的唯一姓氏百分比是多少：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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，我们想按配置文件的类型对用户进行计数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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表达式一样，在值中使用关系将按该字段分组。 请注意，结果中用户配置文件类型的名称将为“ user_profile__type”。
如何按多对多关系分组▶
关系的一种更复杂的类型是多对多关系。 例如，计算每个用户是多少个组成员：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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')) |
一个用户可以是多个组的成员。 为了计算用户所属的组数，我们在用户模型中使用了相关的名称“组”。 如果未显式设置（并且未显式禁用）相关名称，则Django会自动以{相关模型模型} _set的格式生成名称。 例如，group_set。
原文：https://hakibenita.com/django-group-by-sql#how-to-group-by-an-expression