Django聚合带有表达式的查询 [英] Django aggregate queries with expressions

问题描述

我有一个XYZ模型，我需要获取给定查询集的字段a，b和表达式x / y的最大值。

I have a model XYZ and I need to get the max value for fields a, b, and expression x/y for a given queryset.

它的工作原理非常漂亮对于领域。诸如此类：

It works beautifully for fields. Something like:

>>> XYZ.all().aggregate(Max('a'))

... {'a__max': 10}

但是，我找不到用于表达式的方法。尝试类似的事情：

However, I can't find a way to do it for expressions. Trying something like:

>>> XYZ.all().aggregate(Max('x/y'))

给出一个错误：

*** FieldError: Cannot resolve keyword 'x/y' into field. Choices are: a, b, x, y, id

尝试类似操作：

>>> XYZ.all().aggregate(Max(F('x')/F('y')))

给出错误：

*** AttributeError: 'ExpressionNode' object has no attribute 'split'

甚至是类似的东西：

XYZ.all().extra(select={'z':'x/y'}).aggregate(Max('z'))

也无法正常工作，并给出与上面相同的错误：

Also doesn't work and gives the same error as above:

FieldError: Cannot resolve keyword 'z' into field. Choices are: a, b, x, y, id

我发现可以做到这一点：

The one hack I found to do it is:

XYZ.all().extra(select={'z':'MAX(x/y)'})[0].z

这实际上是有效的，因为它会生成正确的SQL，但由于我这样做会造成混淆

Which actually works because it generates the right SQL, but it's confusing because I do get the right value at the z atttribute, but not the right instance, the one with that max value.

当然，我还可以使用原始查询或技巧来获得z属性的正确值，但不能获得正确的实例。 （）和order_by（），但对我来说，Django一直以一种不错的方式支持聚合查询实在没有任何意义，但即使拥有自己的F表达式也无法支持表达式。

Of course, I could also use raw queries or tricks with extra() and order_by(), but it really doesn't make sense to me that Django goes all the way to support aggregate queries in a nice way, but can't support expressions even with its own F expressions.

有什么办法吗？

推荐答案

在SQL中，您想要的是

In SQL, what you want is actually

SELECT x/y, * FROM XYZ ORDER BY x/y DESC LIMIT 1;
# Or more verbose version of the #1
SELECT x/y, id, a, b, x, y FROM XYZ GROUP BY x/y, id, a, b, x, y ORDER BY x/y DESC LIMIT 1;
# Or
SELECT * FROM XYZ WHERE x/y = (SELECT MAX(x/y) FROM XYZ) LIMIT 1;

因此在Django ORM中：

Thus in Django ORM:

XYZ.objects.extra(select={'z':'x/y'}).order_by('-z')[0]
# Or
XYZ.objects.extra(select={'z':'x/y'}).annotate().order_by('-z')[0]
# Or x/y=z => x=y*z
XYZ.objects.filter(x=models.F('y') * XYZ.objects.extra(select={'z':'MAX(x/y)'})[0].z)[0]

版本

XYZ.all().extra(select={'z':'MAX(x/y)'})[0].z

没有正确的x，y和实例，因为计算了 MAX 函数在所有行中，当没有 GROUP BY 时，返回的QuerySet中的所有实例将具有相同的 z 值为 MAX（x / y）。

does not have correct x,y and instance because the MAX function is evaluated among all rows, when there is no GROUP BY, thus all instances in the returned QuerySet will have same value of z as MAX(x/y).

这篇关于Django聚合带有表达式的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！