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屋!