Django 的 filter、exclude 等方法使得對資料庫的查詢很方便了。這在資料量較小的時候還不錯,但如果資料量很大,或者查詢條件比較複雜,那麼查詢效率就會很低。
提高資料庫查詢效率可以透過原生 SQL 語句來實現,但是它的缺點就是需要開發者熟練掌握 SQL。倘若查詢條件是動態變化的,則編寫 SQL 會更加困難。
對於以便捷著稱的 Django,怎麼能忍受這樣的事。於是就有了Aggregation聚合。
聚合最好的例子就是官網給的案例了:
# models.py
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=100)
age = models.IntegerField()
class Publisher(models.Model):
name = models.CharField(max_length=300)
class Book(models.Model):
name = models.CharField(max_length=300)
pages = models.IntegerField()
price = models.DecimalField(max_digits=10, decimal_places=2)
rating = models.FloatField()
authors = models.ManyToManyField(Author)
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
pubdate = models.DateField()
class Store(models.Model):
name = models.CharField(max_length=300)
books = models.ManyToManyField(Book)
接下來可以這樣求所有書籍的平均價格:
>>> from django.db.models import Avg, Max, Min
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': Decimal('30.67')}
實際上可以省掉 all()
:
>>> Book.objects.aggregate(Avg('price'))
{'price__avg': Decimal('30.67')}
還可以指定返回的鍵名:
>>> Book.objects.aggregate(price_avg=Avg('price'))
{'price_avg': Decimal('30.67')}
如果要獲取所有書籍中的最高價格:
>>> Book.objects.aggregate(Max('price'))
{'price__max': Decimal('44')}
獲取所有書籍中的最低價格:
>>> Book.objects.aggregate(Min('price'))
{'price__min': Decimal('12')}
aggregate()
方法返回的不再是 QuerySet
了,而是一個包含查詢結果的字典。如果我要對 QerySet
中每個元素都進行聚合計算、並且返回的仍然是 QuerySet
,那就要用到 annotate()
方法了。
annotate
翻譯過來就是註解,它的作用有點像給 QuerySet
中的每個元素臨時貼上一個臨時的欄位,欄位的值是分組聚合運算的結果。
比方說要給查詢集中的每本書籍都增加一個欄位,欄位內容是外鏈到書籍的作者的數量:
>>> from django.db.models import Count
>>> q = Book.objects.annotate(Count('authors'))
>>> q[0].authors__count
3
與 aggregate()
的語法類似,也可以給這個欄位自定義個名字:
>>> q = Book.objects.annotate(a_count=Count('authors'))
跨外鏈查詢欄位也是可以的:
>>> s = Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))
>>> s[0].min_price
Decimal('12')
>>> s[0].max_price
Decimal('44')
既然 annotate()
返回的是查詢集,那麼自然也可以和 filter()
、exclude()
等查詢方法組合使用:
>>> b = Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))
>>> b[0].num_authors
4
聯用的時候 filter
、annotate
的順序會影響返回結果,所以邏輯要想清楚。
也可以排序:
>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
總而言之,aggregate
和 annotate
用於組合查詢。當你需要對某些欄位進行聚合操作時(比如Sum, Avg, Max),請使用 aggregate
。如果你想要對資料集先進行分組(Group By)然後再進行某些聚合操作或排序時,請使用 annotate
。
進行此類查詢有時候容易讓人迷惑,如果你對查詢的結果有任何的疑問,最好的方法就是直接檢視它所執行的 SQL 原始語句,像這樣:
>>> b = Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
>>> print(b.query)
SELECT "aggregation_book"."id", "aggregation_book"."name",
"aggregation_book"."pages", "aggregation_book"."price",
"aggregation_book"."rating", "aggregation_book"."publisher_id",
"aggregation_book"."pubdate", COUNT("aggregation_book_authors"."author_id")
AS "num_authors" FROM "aggregation_book" LEFT OUTER JOIN "aggregation_book_authors"
ON ("aggregation_book"."id" = "aggregation_book_authors"."book_id")
GROUP BY "aggregation_book"."id", "aggregation_book"."name",
"aggregation_book"."pages", "aggregation_book"."price",
"aggregation_book"."rating", "aggregation_book"."publisher_id",
"aggregation_book"."pubdate"
ORDER BY "num_authors" ASC
相關文件:Aggregation
複合使用聚合時的相互干擾問題:Count and Sum annotations interfere with each other
本作品採用《CC 協議》,轉載必須註明作者和本文連結