sql - 在 Django 中对 ValuesQuerySet 使用 extra()

Question

我正在尝试使用两个本身聚合的值来计算百分比。解释我所追求的 SQL 查询如下:

SELECT (SUM(field_a) / SUM(field_b) * 100) AS percent
FROM myapp_mymodel 
GROUP BY id
ORDER BY id

我尝试使用以下内容来构造一个 QuerySet，但不幸的是它不包含额外的字段:

MyModel.objects.values('id').annotate(
   sum_field_a=Sum('field_a'),
   sum_field_b=Sum('field_b')).extra(
      select={'percent': 'sum_field_a / sum_field_b * 100'})

令我恼火的是 - 根据 Django 文档 - 这似乎是要走的路:

When a values() clause is used toconstrain the columns that arereturned in the result set […]instead of returning anannotated result for each result inthe original QuerySet, the originalresults are grouped according to theunique combinations of the fieldsspecified in the values() clause. Anannotation is then provided for eachunique group; the annotation iscomputed over all members of thegroup.

Source: http://docs.djangoproject.com/en/dev/topics/db/aggregation/#values

If you use a values() clause after an extra() clause, any fields defined by a select argument in the extra() must be explicitly included in the values() clause. However, if the extra() clause is used after the values(), the fields added by the select will be included automatically.

Source: http://docs.djangoproject.com/en/dev/ref/models/querysets/#values

Answer 1

Aggregate expressions 轻松允许在聚合函数上使用此类表达式 since Django 1.8没有有问题的 'extra()' 方法。

qs = (
    MyModel.objects.values('id')
    .annotate(percent=Sum('field__a') / Sum('field__b') * 100)
    .order_by('id')
)

>>> print(str(qs.query))
SELECT id, ((SUM(field_a) / SUM(field_b)) * 100) AS percent
FROM app_mymodel GROUP BY id ORDER BY id ASC

(提到的问题 #15546 很快就被文档说明关闭了，在 values() 之后 extra() 将不起作用 - commit a4a250a 。)