Django aggregate and annotate

一樣是在面試被問到的題目。
通常在面試被問到回答不出來的就是工作時不會用到的東西，吱吱嗚嗚答不太上來則是下班有摸過但是沒有常常複習。
在剛開始學 Django 時接觸到 Annotate 的時候我會一直用 SQL 的 group 的概念來帶入，結果不知道為什麼鬼打牆一直搞不懂 Django 在 orm 下要怎麼弄，再加上目前維護的專案架構無法使用 Annotate 來獲得結果（因為資料庫 schema 設計有問題），面試被問到的時候直接放推（這根本送分題），之後看了一下發現其實不難，只是當初不知道。

Aggregate 跟 Annotate 在 Django 滿常被使用到的，可以直接對資料庫進行操作。
Aggregate 裡面提供的大多數方法都可以對應到 SQL 的 sum、max、min 這類操作，而 Annotate 則是可以用來分組（對應 SQL 中的 Group_by）。而 Aggregate 跟 Annotate 可以互相搭配著使用。

建立示範專案

首先我們在 django 中有三個 model，一個是 User，直接繼承 django AbstractUser，紀錄使用者。

class User(AbstractUser):
    class Meta:
        db_table = "auth_user"

一個紀錄廠牌。

class Brand(models.Model):
    name = models.CharField(max_length=50)

一個是 Moto，紀錄摩托車種類。

class Moto(models.Model):
    name = models.CharField(max_length=50)

    brand = models.ForeignKey(
        to="Accounts.Brand", related_name="moto", on_delete=models.CASCADE
    )

    buyer = models.ManyToManyField(to=settings.AUTH_USER_MODEL, related_name="moto")

    price = models.PositiveIntegerField(
        validators=[MinValueValidator(50000)], default=50000
    ) # price can't lower than 50k

在專案中倒入資料會變成：

User

Brand

Moto

使用 Aggregate 以及 Annotate。

我們用幾個範例來示範如何使用 Aggregate 以及 Annotate。
可以使用 django-extensions 的 notebook 進行測試。

(1) 找出同樣廠牌中最高的價位

依照使用習慣，首先會先選擇 Brand 這個 model，之後使用 Annotate 對廠牌進行分組，最後挑出最高的價位。

依序是：

1. 選擇 model
2. 篩選目標的條件
3. 列出每一項

以下用 orm 表示

querysets = Brand.objects.annotate(Max('moto__price'))

for queryset in querysets:
    print(f"""{queryset.name}\t{queryset.moto__price__max}""")

續 (1) 分別挑出最高、最低、平均的價位

from Accounts.models import Brand

querysets = Brand.objects.annotate(max_price=Max('moto__price'), min_price=Min('moto__price'), avg_price=Avg('moto__price'))

for queryset in querysets:
    print(f"""{queryset.name}\t{queryset.max_price}\t{queryset.min_price}\t{queryset.avg_price}""")

(2) 找出有兩個以上產品的廠牌

因為多出了一個條件，所以我們需要搭配 filter 進行過濾。

querysets = Brand.objects.annotate(product_count=Count('moto__price')).filter(product_count__gt=2)
print(querysets)

續(2) 讓返回得資料以 dict 呈現

我們可以利用 values 回傳 dict 的物件。

querysets = Brand.objects.values('name').annotate(product_count=Count('moto__price')).filter(product_count__gt=2)
print(querysets)

結尾

看過之後發現 annotate 跟 aggregate 的概念不難懂，不過要構成複雜的 SQL 語句卻是要在好好思索一下用法，有些狀況下我會有種「不如直接寫 raw SQL 好了」的想法出現。

django_annotate_aggregate 是這篇文章中用到的範例專案，連帶 sqlit 附上，直接在 jupyter 中就試驗範例了。

Raw SQL 參考：https://www.runoob.com/mysql/mysql-group-by-statement.html

裡面有個例子「按照名字來分組，那重複姓名的人數有多少。」。
使用的 SQL 是SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

首先選定要查詢的 table，再來選擇你要分組的 Column，呈現的時候選擇該 Column 以及預計分組後想要獲得的結果。
在上面那個例子中可以用一段話解釋「從表 employee_tbl 中以姓名分類，查詢每個姓名的數量。」。