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 中以姓名分類,查詢每個姓名的數量。」。

#Django #annotate #aggregate






Related Posts

程式導師計畫 4th / 第 15 週心得

程式導師計畫 4th / 第 15 週心得

[MTR04] W2 D15 練習五:判斷大小寫

[MTR04] W2 D15 練習五:判斷大小寫

文字方向 - 垂直/水平 文字書寫

文字方向 - 垂直/水平 文字書寫






Comments