annotate()、aggregate() 与 SQL 聚合

aggregate 与 annotate

实测 · 本机执行输出

aggregate → {'total': 210, 'avg': 35.0, 'n': 6}
aggregate SQL: SELECT SUM("lab_book"."price") AS "total", AVG("lab_book"."price") AS "avg", COUNT("lab_book"."id") AS "n" FROM "lab_book"
annotate → [('ann', 3, 60), ('bob', 2, 90), ('cat', 1, 60), ('dan', 0, None)]
annotate SQL: SELECT "lab_author"."id", "lab_author"."name", COUNT("lab_book"."id") AS "n", SUM("lab_book"."price") AS "total" FROM "lab_author" LEFT OUTER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") GROUP BY "lab_author"."id", "lab_author"."name"
dan 没有书:Count 是 0,Sum 是 None → 用 Coalesce: [('ann', 60), ('bob', 90), ('cat', 60), ('dan', 0)]

数据:ann 3 本书(10/20/30),bob 2 本(40/50),cat 1 本(60),dan 没有书;每本书 2~3 个标签。aggregate 折叠成一行 dict;annotate 保留每个对象并加一列,靠 LEFT OUTER JOIN + GROUP BY。

多个多值关系一起聚合

实测 · 本机执行输出

Count("books") + Count("books__tags"): [('ann', 7, 7), ('bob', 5, 5), ('cat', 2, 2), ('dan', 0, 0)]
都加 distinct=True(注意 nt 现在是「不同标签数」,不是「书-标签对数」): [('ann', 3, 3), ('bob', 2, 3), ('cat', 1, 2), ('dan', 0, 0)]
Count("books") + Sum("books__price"): [('ann', 3, 60), ('bob', 2, 90), ('cat', 1, 60), ('dan', 0, None)]
再多加一个标签的 Count(nb, sp 也被放大): [('ann', 7, 140, 7), ('bob', 5, 230, 5), ('cat', 2, 120, 2), ('dan', 0, None, 0)]
SQL:  FROM "lab_author" LEFT OUTER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") LEFT OUTER JOIN "lab_book_tags" ON ("lab_book"."id" = "lab_book_tags"."book_id") GROUP BY "lab_author"."id", "lab_author"."name"

每多一个多值 JOIN,行数就相乘。Sum、Count 都会被放大,distinct=True 只能修计数类,而且语义会变。可靠的做法:分开查、用子查询,或者只聚合一个多值关系。

filter 的位置与条件聚合

实测 · 本机执行输出

filter(books__price__gt=15).annotate(n=Count("books")): [('ann', 2), ('bob', 2), ('cat', 1)]
  SQL:  FROM "lab_author" INNER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") WHERE "lab_book"."price" > 15 GROUP BY "lab_author"."id", "lab_author"."name"
annotate(n=Count("books")).filter(books__price__gt=15): [('ann', 6), ('bob', 4), ('cat', 1)]
  SQL:  FROM "lab_author" LEFT OUTER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") INNER JOIN "lab_book" "T3" ON ("lab_author"."id" = "T3"."author_id") WHERE "T3"."price" > 15 GROUP BY "lab_author"."id", "lab_author"."name"
annotate(n=Count("books")).filter(n__gt=1): [('ann', 3), ('bob', 2)]
  SQL:  FROM "lab_author" LEFT OUTER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") GROUP BY "lab_author"."id", "lab_author"."name" HAVING COUNT("lab_book"."id") > 1
Count("books", filter=Q(price>15)): [('ann', 2), ('bob', 2), ('cat', 1), ('dan', 0)]
  SQL: SELECT "lab_author"."id", "lab_author"."name", COUNT("lab_book"."id") FILTER (WHERE "lab_book"."price" > 15) AS "n" FROM "lab_author" LEFT OUTER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") GROUP BY "lab_author"."id", "lab_author"."name"

文档里把这叫「filter 与 annotate 的顺序」:先 filter 缩小参与聚合的行;后 filter 过滤聚合后的结果(用聚合别名时是 HAVING,用关系字段时会再 JOIN)。想保留所有对象、又只统计一部分,用 Count(..., filter=Q(...))。

分组与默认排序

实测 · 本机执行输出

[{'author__name': 'ann', 'n': 3, 's': 60}, {'author__name': 'bob', 'n': 2, 's': 90}, {'author__name': 'cat', 'n': 1, 's': 60}]
SQL:  FROM "lab_book" INNER JOIN "lab_author" ON ("lab_book"."author_id" = "lab_author"."id") GROUP BY 1 ORDER BY 1 ASC
Author 的 Meta.ordering = [name];annotate 的 SQL 尾部:  FROM "lab_author" LEFT OUTER JOIN "lab_book" ON ("lab_author"."id" = "lab_book"."author_id") GROUP BY "lab_author"."id", "lab_author"."name"

values(...).annotate(...) 决定 GROUP BY 的列。分组查询里没有出现 Meta.ordering 的 ORDER BY,需要顺序就显式 order_by。

分步演示:聚合的陷阱

annotate 与 aggregate

本步的对象

本文用到的工具与边界

本机 macOS 27 / Python 3.14.7 / Django 6.1.1(默认用 SQLite,其它数据库会在文中注明),用 django.test.Client 或真实的本机服务器进程实测;计时用 time.perf_counter,数字每次运行会有小幅抖动。SQLite;数据量很小,只为看语义与 SQL
没有验证:大数据量下 GROUP BY 的代价与索引、子查询聚合(Subquery + OuterRef)的写法、窗口函数、PostgreSQL 的 ArrayAgg / StringAgg、annotate 与 select_related 的组合

下一篇 · DJANGO 31

F() 表达式实现并发安全计数