mysql grouping sets_如何在Django中使用分组集(Grouping Set)

Python部落(python.freelycode.com)组织翻译，禁止转载，欢迎转发。

我们如何在Django中使用高级SQL将响应时间减半

最近，我有幸参与了一个对旧仪表板进行优化的项目。我们提出的解决方案需要使用一些Django不支持的高级SQL。在本文中，我将介绍这个问题的解决方案、我们是如何实现它的，并提出一些注意事项。

仪表板

这个仪表板是一个销售模型。它包括一个简单的表，其中包含按商家及其设备分组的指标，以及一个汇总行。

生成该表的代码大致如下:

生成汇总行的代码使用相同的指标，看起来像这样:

我们的admin页面会得到一个漂亮的仪表板，大致如下:

Django Admin 页面中的一个汇总行

请参阅：

有关如何创建上面的仪表板的内容请查看：如何将 Django Admin 转换成一个轻量级的仪表板

存在的问题

这个仪表板在大约三年的时间里运行良好。我们得到了良好的响应时间和准确的信息。然而，随着数据的堆积，它的性能已经下降到页面无法使用的程度。

为了分析这个问题，我们检查了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

译者：一瞬