如何在Django中使用分組集(Grouping Set)

最近，我有幸參與了一個對舊儀錶板進行優化的項目。我們提出的解決方案需要使用一些Django不支持的高級SQL。在本文中，我將介紹這個問題的解決方案、我們是如何實現它的，並提出一些注意事項。

儀錶板

這個儀錶板是一個銷售模型。它包括一個簡單的表，其中包含按商家及其設備分組的指標，以及一個匯總行。

生成該表的代碼大致如下:

生成匯總行的代碼使用相同的指標，看起來像這樣:

我們的admin頁面會得到一個漂亮的儀錶板，大致如下:

Django Admin 頁面中的一個匯總行

請參閱：

有關如何創建上面的儀錶板的內容請查看：如何將 Django Admin 轉換成一個輕量級的儀錶板

（鏈接地址：https://hakibenita.com/how-to-turn-django-admin-into-a-lightweight-dashboard ）

存在的問題

這個儀錶板在大約三年的時間裡運行良好。我們得到了良好的響應時間和準確的信息。然而，隨著數據的堆積，它的性能已經下降到頁面無法使用的程度。

為了分析這個問題，我們檢查了SQL，並對其進行計時測試。生成表的查詢如下:

最壞的情況下，這個查詢需要大約30秒的時間才能完成。

儀錶板執行的下一個查詢用於生成匯總行:

這個查詢花費了大約相同的時間，大約30秒。在最糟糕的情況下，這兩個查詢總共花費了超過一分鐘的時間才完成。

在內存中進行合計

這兩個查詢處理的是完全相同的數據，唯一的區別是GROUP BY 鍵。第一個查詢在商家和設備級別上生成結果，第二個查詢為整個數據集生成相同的合計。

我們首先想到的是通過在內存中合計結果來計算匯總。

第一個指標，total，很容易計算:

第二個指標是平均收費金額。我們不能簡單地把每個設備和商家的平均收費金額加起來，我們需要更多的信息。

要計算所有商家和設備的平均收費金額，我們需要將總收費金額除以銷售數量。我們已經有了銷售數量，所以我們需要為總收費金額添加一個指標:

現在我們已經有了total和total_charged_amount，我們就可以計算avg_charged_amount了:

我們還剩下一個指標，unique_users。這個指標會計數訪問每個商家的每個設備的唯一用戶數。同一用戶可以訪問不同商家的多個設備。如果我們對unique_users進行求和，我們就不能得到整個集合的正確指標。

從合計的結果中計算出特值是不可能的，因此解決方案肯定在資料庫中。

在資料庫中進行合計

大多數SQL實現都提供了幾個有用的函數來在不同的級別上合計數據。

資料庫支持：

在本文中，我使用PostgreSQL資料庫。在Oracle、MySQL和MSSQL中也有類似的函數。據我所知，SQLite不支持我將要使用的函數。

讓我們從一些數據開始:

我們在儀錶板中使用的查詢會生成以下結果:

生成匯總行的查詢：

ROLLUP (匯總)

第一個特殊的GROUP BY表達式是ROLLUP。顧名思義，ROLLUP在最低級別並向上進行合計:

我們按device (設備)和merchant (商家)這兩個欄位進行分組，得到了三組合計:

1. () all
2. (device, merchant)
3. (device)

ROLLUP會「向上」進行合計，因此欄位的順序非常重要。我們來翻轉欄位的順序:

這次我們得到了以下幾組:

1. () all
2. (merchant, device)
3. (merchant)

Cube

下一個分組表達式很可能是從OLAP借鑒來的，OLAP中經常提到cube(多維數據集)。CUBE表達式會合計所有可能的組合:

運行結果包括以下分組:

1. () all
2. (device, merchant)
3. (merchant)
4. (device)

分組集

分組集允許我們提供所需的精確合計分組。例如，要重新創建上面ROLLUP的結果，我們可以提供以下分組集:

GROUPING SETS中括弧內的每個欄位列表在結果中都是一個組。

CUBE 和ROLLUP都可以使用GROUPING SETS來實現。下表顯示了針對ROLLUP和CUBE在兩個欄位a和b上的等價GROUPING SETS表達式:

在我們最初的查詢中，我們有商家和設備級別的指標，並且我們希望得到一個匯總行。使用GROUPING SETS，該查詢將看起來像這樣:

前6行與原始查詢類似。最後一行類似於我們使用的匯總查詢的結果。

使用GROUPING SETS，我們可以在一個查詢獲得所需的結果，而不需要使用兩個查詢。

在Django中使用分組集

現在我們有了這個查詢，我們需要找到一種方法在Django中使用它。不幸的是，Django現在仍然不支持分組集。最重要的是，這個查詢由Django Admin生成，它包括來自列表過濾器和日期層次結構中的predicate(斷言)。因此,我們不能只使用原始SQL。

我們需要找到一種方法來修改給定的Django QuerySet，並向它添加分組集。

由於Django沒有針對分組集的內置支持，所以我們不得不對該查詢進行操作。我們需要操作的基查詢是Django生成的查詢，以及Django Admin添加的任何斷言和注釋。最後，我們希望像Django一樣在資料庫中執行這個查詢。獲取查詢Django QuerySet的一個很不錯的特性是它提供了生成的SQL:

這是一個簡單的查詢，我們可以直接在資料庫中執行它嗎?

這看起來是我們可以處理的，讓我們繼續深入研究……

如前所述，QuerySet是由Django Admin生成的，它可能包含列表過濾器和日期層次結構中的斷言。讓我們嘗試在sold_at 日期欄位上使用斷言來執行一個查詢:

看起來Django無法按原樣執行該查詢。原因是str(qs.query)生成的文本只是查詢的一個文本表示。實際上，Django使用了正確的綁定變數(也可以稱為替換變數)來避免SQL注入。

大部分Django ORM QuerySet邏輯是由一個名為Query的內部類執行的。該類還沒有文檔化，了解它的唯一地方是在源代碼中。Query的一個很有前途的函數是sql_with_params。讓我們在上面的查詢中使用它，看看我們能得到什麼:

sql_with_params函數會返回一個元組。該元組的第一個參數是SQL查詢。第二個是該查詢的參數列表。

敏銳的人可能在查詢文本中發現了佔位符%s:

這個佔位符對應於我們在第二個形參中得到的實參。讓我們嘗試使用佔位符和參數來執行該查詢:

太棒了!現在我們可以像Django那樣來執行一個查詢。我們已經準備好操作這個查詢了。

對查詢進行操作

Django生成的查詢包含一個簡單的GROUP BY子句:

我們想用下面的group by子句來替換它:

這看起來像是re的工作。

我們希望捕獲GROUP BY和ORDER BY之間的分組欄位，並使它們成為GROUPING SET表達式中的第一個組。然後，我們想要為匯總添加分組():

現在我們可以使用修改後的查詢，並使用參數來執行它:

你瞧……我們現在在一個查詢中獲得了結果和匯總行。

結論

考慮使用這種方法時要考慮的幾個重要問題:

• 不要做這些!: 這是最糟糕的。這種方法是一個很好的練習，也是研究ORM內部機制的好機會，但是它的實現太脆弱了。當使用一個內部的、未文檔化的API時，我們並不能保證它在將來不會意外地改變。說到這裡，我們決定在一個內部管理頁面中使用這種方法。這是一個非常特殊的場景，涉及一個查詢集，它不用於任何面向用戶的功能。它幫助我們將頁面響應時間精確地減少了一半，並且我們對結果很滿意。
• 確定排序順序: 當使用GROUPING SETS (以及ROLLUP或CUBE)時，你在一個查詢中混合了多個級別的合計。為了能夠以一個可預測的方式獲取結果，顯式地對結果排序非常重要。例如，在上面的查詢中，要確保匯總行是第一行，並添加以下排序順序 qs.order_by( F("merchant").desc(nulls_last=False) )。

英文原文：https://hakibenita.com/how-to-use-grouping-sets-in-django

譯者：一瞬

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！