跨越多个表的Django查询中的表达式聚合 [英] Aggregation of an expression in Django query spanning multiple tables
问题描述
能否在Django ORM查询中从相关表中计算字段的表达式(例如:减法)的合计(例如:总和)?
Is it possible to compute an aggregation (ex.: sum) of an expression (ex.: subtraction) of fields from a related table in a Django ORM query?
有关我要实现的目标的完整示例,请参见此工作 SQL Fiddle .我将其分解为两个问题(其他问题在这里).在这种情况下,我有一个代表数字序列的模型Sequence
和一个代表该序列中链接"的模型Part
:
For a full example of what I'm trying to achieve, see this working SQL Fiddle. I broke it down into two questions (other one here). In this case, I have a model Sequence
that represents a sequence of numbers, and a model Part
that represent a "link" in this sequence:
Sequence Sequence Sequence Sequence ...
0 5 20 15 ...
|-- Part --|-- Part --|-- Part --|-- ...
每个Part
因此表示一个 delta ,它是序列中两个值之间的差.我有一组(非连续的)部分,想要计算这些增量的总和.像这样:
Each Part
thus represents a delta, which is the difference between the two values in the sequence. I have a set of (non-contiguous) parts and want to calculate the sum of those deltas. Something like this:
sum([p.after.value - p.before.value for p in Part.objects.filter(...)])
但是在单个查询中(原因是我想将此作为更复杂查询的子查询).在SQL中很容易做到:
But in a single query (the reason is that I want to use this as a subquery for a more complex one). It's easy to do it in SQL:
select sum(a.value - b.value)
from part p
join sequence a on p.after_id = a.id
join sequence b on p.before_id = b.id
where condition
group by p.other_field;
我不知道如何使用Django ORM做到这一点.我可以将其设置为单个值:
I don't know how to do it using Django ORM. I can do it for a single value:
Part.objects.filter(condition).aggregate(Sum('before__value')).values()
但不适用于涉及多个值的表达式.目前尚不支持使用F()
,因此,我正在寻找一种替代方法.我还查看了这个问题,它也与聚合表达式有关,但
But not for an expression involving multiple values. Using F()
is not supported yet, so I'm looking for an alternative way. I also looked at this question, which is also about expressions in aggregates, but the accepted answer (using extra
) is not applicable to my case AFAIK, since the fields I'm interested in are not in the same table, but in a related one.
>>> Part.objects.filter(condition).extra(select={
... 'delta':'sum(after__value - before__value)'
... })
DatabaseError: no such column: after__value
这里是SSCCE,以防万一有人想尝试一下:下载或浏览.它具有与上面链接的 SQL提琴相同的模型和数据.
Here's a SSCCE, in case anyone want to experiment with it: Download or Browse. It has the same models and data as the SQL fiddle linked above.
推荐答案
现在,该版本1.8已实现对aggregate
Now that 1.8 implemented support for F
expressions in aggregate
, the answer became straightforward:
Part.objects.filter(condition).aggregate(sum=Sum(F('after__value')-F('before__value'))).values()
这篇关于跨越多个表的Django查询中的表达式聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!