如何在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 (商家)这两个字段进行分组，得到了三组合计:
() all
(device, merchant)
(device)
ROLLUP会“向上”进行合计，因此字段的顺序非常重要。我们来翻转字段的顺序:
这次我们得到了以下几组:
() all
(merchant, device)
(merchant)
Cube
下一个分组表达式很可能是从OLAP借鉴来的，OLAP中经常提到cube(多维数据集)。CUBE表达式会合计所有可能的组合:
运行结果包括以下分组:
() all
(device, merchant)
(merchant)
(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
译者：一瞬
