使用SQL理解Django中的Group By

2021-03-02 Python程式設計師

聚合在任何類型的ORM中都是混亂的根源，Django也不例外。其文檔提供了各種示例和備忘表，演示了如何使用ORM對數據進行分組和聚合，但我決定從另一個角度來處理這個問題。

在本文中，我將查詢集和SQL並排放在一起。如果你更熟悉SQL，那麼這就是你的Django GROUP BY速查表。


如何在Django中進行分組

為了演示不同的GROUP BY查詢，我將使用Django內置Django .contrib.auth應用程式中的模型。

Django ORM會生成帶有長別名的SQL語句。為了簡單起見，我將展示一個Django所執行操作的清理後的、但等效的版本。

要查看Django實際執行SQL的過程，你可以在Django設置中打開SQL日誌。如何計數行

我們來看看我們有多少用戶:

計數行是如此常見，以至於Django在QuerySet上為它提供了一個函數。與我們接下來將看到的其他QuerySet不同，count會返回一個數字。

如何使用聚合函數

Django還提供了另外兩種計數表中行數的方法。

我們將從aggregate開始:

為了使用aggregate，我們導入了聚合函數Count。該函數接受一個表達式進行計數。在本例中，我們使用主鍵列id的名稱來計數表中的所有行。

聚合會忽略NULL值。有關聚合如何處理NULL的更多信息，請參見《SQL中12個常見錯誤和容易錯過的優化機會》一文。

aggregate的結果是一個字典:

鍵的名稱派生自欄位名稱和聚合名稱。在本例中，它是id_count。最好不要依賴這種命名約定，而是提供你自己的名稱:

要aggregate的參數的名稱也就是生成的字典中的鍵的名稱。

如何按欄位分組

使用aggregate，我們得到了對整個表應用聚合函數後的結果。這很有用，但通常我們希望對行的分組應用聚合過程。

讓我們通過用戶的活動狀態來對它們進行計數:

這次我們使用了函數annotate。我們使用values和annotate的一個組合去生成一個GROUP BY :

順序很重要:在annotate之前對values的調用失敗的話將不會產生聚合結果。

與aggregate一樣，要annotate的參數的名稱是經過計算的的QuerySet結果中的鍵。在本例中是total。

如何使用Group By篩選一個QuerySet

要對一個篩選後的查詢應用聚合，你可以在查詢中的任何位置使用filter。例如，僅根據員工用戶的活動狀態來對他們進行計數:

如何使用Group By對一個QuerySet進行排序

就像篩選器一樣，你可以在查詢的任何地方使用order_by來對一個查詢集進行排序:

注意，你可以按照Group By鍵和聚合欄位對該查詢集進行排序。

如何合併多個聚合

要生成同一個分組的多個聚合，請添加多個註解:

該查詢將生成活動用戶和非活動用戶的數量，以及用戶加入每個組的最後日期。

如何按多個欄位分組

就像執行多個聚合一樣，我們可能也想要按照多個欄位進行分組。例如，按活動狀態和員工狀態進行分組:

這個查詢的結果包括is_active、is_staff和每個分組中的用戶數量。

如何按一個表達式分組

GROUP BY的另一個常見用例是按照一個表達式進行分組。例如，計數每年加入的用戶數:

請注意，為了獲取數據的年份，我們在第一次values()調用中使用了特別的表達式<field>__year。此查詢的結果是一個字典，該字典的鍵名稱將是date_joined__year。

有時，內置表達式還不夠，你需要聚合一個更複雜的表達式。例如，按註冊後已經登錄的用戶進行分組:

這裡的表達式相當複雜。我們首先使用annotate來構建這個表達式，並通過在接下來的values()調用中引用該表達式來將其標記為一個GROUP BY鍵。從這裡開始，一切都是一樣的了。

如何使用條件聚合

使用條件聚合，你只能聚合分組的一部分。當你有多個聚合時，條件就派上用場了。例如，通過註冊的年份來計數員工和非員工用戶:

上面的SQL語句來自PostgreSQL，它和SQLite是目前唯一支持FILTER語法快捷方式(正式名稱為「選擇性聚合」)的資料庫後端。對於其他資料庫後端，ORM則使用CASE ... WHEN來代替。

我之前寫過關於聚合和篩選器的文章。請參閱我的《處理資料庫的9個Django提示》如何使用Having

HAVING子句用於對一個聚合函數的結果進行篩選。例如，查找超過100個用戶加入的年份:

註解後的欄位total上的filter在生成的SQL中添加了一個HAVING子句。

如何按照不同之處分組

對於一些聚合函數，比如COUNT，有時最好是只計數不同的出現特徵。例如，每個用戶的活動狀態有多少個不同的姓氏:

請注意Count調用中distinct=True的使用。

如何使用聚合欄位創建表達式

聚合欄位通常只是一個更大問題的第一步。例如，不同的姓氏除以用戶活動狀態的百分比是多少:

第一個annotate()定義了聚合欄位。第二個annotate()使用聚合函數來構造表達式。

如何跨關係分組

到目前為止，我們只在單個模型中使用了數據，但是聚合通常是跨關係使用的。比較簡單一點的場景是一對一或外鍵關係。例如，假設我們有一個UserProfile，它與用戶是一對一的關係，我們想要根據profile的類型來計數用戶數量:

就像GROUP BY表達式一樣，在值中使用關係將會根據該關係欄位進行分組。請注意，結果中的用戶profile類型名稱將會是「user_profile_ type」。

如何按多對多關係分組

一個更複雜的關係類型是多對多關係。例如，計數每個用戶是多少個分組的成員:

一個用戶可以是多個分組的成員。為了計數該用戶所屬分組的數量，我們在User模型中使用了相關的名稱「groups」。如果我們沒有顯式地設置相關名稱(也沒有顯式地禁用)，Django將會自動以{related model model}_set的格式生成一個名稱。例如,group_set。

延伸閱讀

想要更深入地了解ORM和GROUP BY，請查看以下連結:

《如何在Django中使用分組集》:一篇關於高級分組技術的文章，比如按cube分組、按rollup分組和按分組集分組。

《如何在SQL中使用Group By獲取一個分組中的第一個或最後一個值》:在PostgreSQL中使用數組的一個小技巧。

《SQL中常見的12個錯誤和易錯過的優化機會》:你在處理數據和編寫SQL時需要知道的一些SQL注意事項。

《Django聚合速查表頁面》:如何執行常見的聚合查詢。

英文原文：https://hakibenita.com/django-group-by-sql
譯者：好酒不上頭

相關焦點

  • django模型使用
    之前寫過一篇django的模型關係，今天騰出時間把上篇的兄弟篇補上，來學習下django orm, 利用django的模型可以很方便的對資料庫進行操作
  • Django QuerySet查詢基礎與技巧.有了她,再也不用擔心SQL注入了.
    等到使用它的時候，才解析該對象得到數據集。而且解析過一次會被緩存起來，下次使用時直接返回緩存中的數據，緩存的使用提高多次查詢的效率。另外，使用SQL查詢，我們還可以通過SQL語句給RawQuerySet對象添加額外的屬性。raw方法會解析SQL語句中的欄位，將欄位轉成屬性的方式方便調用。
  • 一篇文章帶你了解Django ORM操作(高端篇)
    原生sql是可以指定顯示的列名的,同樣,ORM也可以。但是上述ORM對應的原生SQL確實如上,所以那樣理解就行了。通過研究表結構發現,每出版的書,都在book表中記錄,並且每本書會外鍵一個出版社id。分組再篩選分組再篩選本質就是原生sql的group by .. having,將壓縮完的數據在進行條件判斷。但是對壓縮的數據進行判斷只能通過having。
  • 優化Django ORM查詢
    >>> from django.db import connection>>> Post.objects.all()>>> connection.queries[ { 'sql': 'SELECT "blogposts_post"."id", "blogposts_post"."
  • 深入理解Django的Form表單
    一、深入理解Django的Form表單1.1、話不多說，先看示例1.1.1、創建Form類from django.forms import Formfrom django.forms import widgetsfrom django.forms import fields class MyForm(Form
  • java中有沒有類似sql的group by的功能呢
    我們現在做的很多系統都是離不開資料庫的，所以經常會使用到sql語句做數據增刪改查，而其中查詢使用的應該也是最多的。在sql查詢中有一個分組查詢的功能，就是通過「group by」將數據進行分組處理。那java中有沒有類似這樣的分組功能呢。
  • Django入門案例:圖書管理系統
    很多同學想了解這方面的學習案例，今天我們就給大家分享一個：用Django實現圖書管理系統無需寫sql，不需寫前端，利用Django自帶的Admin和ORM框架就能輕鬆實現一個多對多表關係的增刪改查。Requires: pytz, asgiref, sqlparseRequired-by:(django) E:\python_Projects\django_demo>python -VPython 3.6.12 :: Anaconda, Inc.
  • Python——用 Django 寫 restful api 接口
    我用的 pymsql，pymsql 是 Python 中操作MySQL 的模塊，其使用方法和 MySQLdb 幾乎相同。但目前在 python3.x 中，PyMySQL 取代了 MySQLdb。views.py （https://code.ziqiangxuetang.com/django/django-views-urls.html）—— 處理用戶發出的請求，從 urls.py 中對應過來, 通過渲染 templates 中的網頁可以將顯示內容，比如登陸後的用戶名，用戶請求的數據，輸出到網頁。
  • ubuntu18.04 django 連接 mysql 資料庫方法和常見錯誤總結
    本人電腦系統 ubuntu18.04 , django版本 2.2在ubuntu電腦使用django連接mysql資料庫的時候遇到了一些問題
  • Django入門學習教程
    import render,HttpResponse,redirectfrom django.views.generic import Viewimport pymysqldef classes(request):    conn=pymysql.connect(        host="127.0.0.1",        port=3306
  • 一篇文章帶你了解Django ORM操作(基礎篇)
    回復「書籍」即可獲贈Python從入門到進階共10本電子書前言在日常開發中,
  • 一次神奇的 sql 查詢經歷,group by 慢查詢優化記錄
    二、看執行計劃　　可以看到，group by欄位上我是加了索引的，也用到了。　　我當然是不信了，去測試電腦上執行sql，還真是30多秒。。。　　我又回我的電腦上，連接同一個資料庫，一執行sql，0.8秒！？　　什麼情況，同一個庫，同一個sql，怎麼在兩臺電腦執行的差距這麼大！
  • 「原創」Django第五章、模型建立與遷移
    通過這個類我們可以創建一個專門用來保存博客文章的資料庫表，代碼如下：from django.db import modelsfrom django.utils import timezone # 新增from django.contrib.auth.models import
  • 10道題教你使用python Django框架來實現web應用,值得收藏
    這個時候可以嘗試使用2to3工具對第三包的python原始碼進行轉換，然後使用。這樣存在一定風險。無法轉換的，需要尋找替代包，應用伺服器上對應的代碼需要修改。關於django升級：django1.5開始支持python3。同時django1.11是支持python2的最後的版本。如果使用的django版本大於等於1.5，則django版本可以不升級。
  • HiveSql基礎函數使用(三)
    去重函數：distinct與group bydistinct與group by :兩者都可以實現對結果重複記錄的去重。從執行順序來說，兩者主要存在以下幾點區別 ① distinct只是將重複的行從結果中剔除出去；group by是按指定的列分組，一般這時在select中會用到聚合函數。 ②distinct是把不同的記錄顯示出來；group by是在查詢時先把紀錄按照類別分出來再查詢。
  • 使用 Docker 讓部署 Django 項目更加輕鬆
    接著使用 WORKDIR /app 設置工作目錄，以後在基於此鏡像啟動的 Docker 容器中執行的命令，都會以這個目錄為當前工作目錄。然後我們使用命令 RUN pip install pipenv 安裝 pipenv，-i 參數指定 pypi 源，國內一般指定為豆瓣源，這樣下載 pipenv 安裝包時更快，國外網絡可以省略 -i 參數，使用官方的 pypi 源即可。
  • 【資料庫】group by的用法實例
    如圖有這樣一張成績表：首先要理解group by 含義：「Group By」從字面意義上理解就是根據「By」指定的規則對數據進行分組，所謂的分組就是將一個「數據集」劃分成若干個「小區域」，然後針對若干個「小區域」進行數據處理。
  • SQL中去重的三種方法
    在使用SQL提數的時候，常會遇到表內有重複值的時候，比如我們想得到 uv （獨立訪客），就需要做去重。在 MySQL 中通常是使用 distinct 或 group by子句，但在支持窗口函數的 sql（如Hive SQL、Oracle等等） 中還可以使用 row_number 窗口函數進行去重。
  • Django Form表單完整使用流程
    表單系統重點知識回顧表單系統的核心是 Form 對象，它將表單中的欄位封裝成了一系列的 Field 和驗證規則，以此來自動生成 HTML 表達標籤。本節中首先使用 Form 對象實現書籍的搜索功能，然後再詳細講解 Form 對象的構成。那麼下面我們首先對前面學過的知識做一下簡單的重點知識回顧。
  • Django ORM源碼解析(一)
    import osimport sysif __name__ == "__main__": os.environ.setdefault("DJANGO_SETTINGS_MODULE", "OpsManage.settings") from django.core.management import execute_from_command_line