Агрегирование аннотации в GROUP BY в Django

UPDATE

Благодаря опубликованному ответу, я нашел гораздо более простой способ сформулировать проблему. Оригинальный вопрос можно увидеть в истории изменений.

Проблема

Я пытаюсь перевести SQL-запрос в Django, но получаю ошибку, которую я не понимаю.

Вот модель Django у меня:

class Title(models.Model):
  title_id = models.CharField(primary_key=True, max_length=12)
  title = models.CharField(max_length=80)
  publisher = models.CharField(max_length=100)
  price = models.DecimalField(decimal_places=2, blank=True, null=True)

У меня есть следующие данные:

publisher                    title_id      price  title
---------------------------  ----------  -------  -----------------------------------
New Age Books                PS2106         7     Life Without Fear
New Age Books                PS2091        10.95  Is Anger the Enemy?
New Age Books                BU2075         2.99  You Can Combat    Computer Stress!
New Age Books                TC7777        14.99  Sushi, Anyone?
Binnet & Hardley             MC3021         2.99  The Gourmet Microwave
Binnet & Hardley             MC2222        19.99  Silicon Valley   Gastronomic Treats
Algodata Infosystems         PC1035        22.95  But Is It User Friendly?
Algodata Infosystems         BU1032        19.99  The Busy Executive   Database Guide
Algodata Infosystems         PC8888        20     Secrets of Silicon Valley

Вот что я хочу сделать: введите аннотированное поле dbl_price, которое в два раза больше цены, затем сгруппируйте полученный запрос на publisher и для каждого издателя вычислите общее количество всех значений dbl_price для всех названия, опубликованные этим издателем.

Запрос SQL, который делает это, выглядит следующим образом:

SELECT SUM(dbl_price) AS total_dbl_price, publisher
FROM (
  SELECT price * 2 AS dbl_price, publisher
  FROM title
) AS A 
GROUP BY publisher

Желаемый результат:

publisher                    tot_dbl_prices
---------------------------  --------------
Algodata Infosystems                 125.88
Binnet & Hardley                      45.96
New Age Books                         71.86

Запрос Django

Запрос будет выглядеть так:

Title.objects
 .annotate(dbl_price=2*F('price'))
 .values('publisher')
 .annotate(tot_dbl_prices=Sum('dbl_price'))

но дает ошибку:

KeyError: 'dbl_price'.

который указывает, что он не может найти поле dbl_price в запросе.

Причина ошибки

Вот почему эта ошибка происходит: в документации говорится

Вы также должны заметить, что в среднем      в списке возвращаемых значений. Это необходимо из-за упорядочивания предложений values ​​() и annotate().

Если предложение values ​​() предшествует предложению annotate(), любые аннотации      будет автоматически добавлен в результирующий набор. Однако, если      values ​​() применяется после предложения annotate(), вам нужно явно указать столбец агрегата.

Итак, dbl_price не удалось найти в агрегации, потому что он был создан предыдущим annotate, но не был включен в values().

Однако я не могу включить его в values, потому что я хочу использовать values (за которым следует другой annotate) в качестве группирующего устройства, поскольку

Если предложение values ​​() предшествует аннотации(), аннотация будет вычисляться с использованием группировки, описываемой предложением values ​​().

который является основой того, как Django реализует SQL GROUP BY. Это означает, что я не могу включить dbl_price внутри values(), потому что тогда группировка будет основана на уникальных комбинациях обоих полей publisher и dbl_price, тогда как мне нужно группировать только publisher.

Таким образом, следующий запрос, который отличается от вышеприведенного в том, что я агрегировал над полем модели price вместо аннотированного поля dbl_price, фактически работает:

Title.objects
 .annotate(dbl_price=2*F('price'))
 .values('publisher')
 .annotate(sum_of_prices=Count('price'))

потому что поле price находится в модели, а не является аннотированным полем, поэтому нам не нужно включать его в values, чтобы сохранить его в наборе запросов.

Вопрос

Итак, вот оно: мне нужно включить аннотированное свойство в values, чтобы сохранить его в наборе запросов, но я не могу этого сделать, потому что values также используется для группировки (что будет неправильно с дополнительное поле). Проблема в основном связана с двумя очень разными способами, в которых values используется в Django, в зависимости от контекста (следует ли за values следовать annotate) - который является (1) извлечением значения (SQL plain SELECT) и (2) группировка + агрегация по группам (SQL GROUP BY) - и в этом случае эти два способа конфликтуют.

Мой вопрос: есть ли способ решить эту проблему (без таких вещей, как возврат к необработанному sql)?

Обратите внимание: конкретный пример может быть решен путем перемещения всех операторов annotate после values, что было отмечено несколькими ответами. Однако меня больше интересуют решения (или обсуждения), которые сохраняли бы инструкции annotate до values() по трем причинам: 1. Существуют также более сложные примеры, где предлагаемое обходное решение не будет работать. 2. Я могу представить ситуации, когда аннотированный запрос был передан другой функции, которая фактически выполняет GROUP BY, так что единственное, что мы знаем, это набор имен аннотированных полей и их типов. 3. Ситуация кажется довольно простой, и это меня удивило бы, если бы это столкновение двух разных видов использования values() не было замечено и обсуждалось ранее.

Ответы

Ответ 1

Возможно, это слишком поздно, но я нашел решение (протестировано с Django 1.11.1).

Проблема в том, что вызов .values('publisher'), который требуется для обеспечения группировки, удаляет все аннотации, которые не включены в param полей .values().

И мы не можем включить dbl_price в поля param, потому что это добавит еще один оператор GROUP BY.

Решение .values() в том, чтобы выполнить все агрегации, для которых сначала требуются аннотированные поля, затем вызвать .values() и включить эти агрегации в поля param (это не добавит GROUP BY, потому что они являются агрегатами). Затем мы должны вызвать .annotate() с .annotate() выражением - это заставит django добавить оператор GROUP BY к запросу SQL, используя единственное поле неагрегирования в запросе - издатель.

Title.objects
    .annotate(dbl_price=2*F('price'))
    .annotate(sum_of_prices=Sum('dbl_price'))
    .values('publisher', 'sum_of_prices')
    .annotate(titles_count=Count('id'))

Единственный минус в этом подходе - если вам не нужны никакие другие агрегаты, кроме той, которая содержит аннотированное поле, - вам все равно придется включить некоторые. Без последнего вызова .annotate() (и он должен включать хотя бы одно выражение!), Django не добавит GROUP BY в SQL-запрос. Один из подходов к решению этой проблемы - создать копию поля:

Title.objects
    .annotate(dbl_price=2*F('price'))
    .annotate(_sum_of_prices=Sum('dbl_price')) # note the underscore!
    .values('publisher', '_sum_of_prices')
    .annotate(sum_of_prices=F('_sum_of_prices')

Также отметим, что вы должны быть осторожны с заказом QuerySet. Вам лучше вызвать .order_by() либо без параметров для очистки порядка, либо с вашим полем GROUP BY. Если результирующий запрос будет содержать упорядочение по любому другому полю, группировка будет неправильной. https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#interaction-with-default-ordering-or-order-by

Кроме того, вы можете захотеть удалить эту ложную аннотацию из вашего вывода, поэтому снова вызовите .values(). Итак, финальный код выглядит так:

Title.objects
    .annotate(dbl_price=2*F('price'))
    .annotate(_sum_of_prices=Sum('dbl_price'))
    .values('publisher', '_sum_of_prices')
    .annotate(sum_of_prices=F('_sum_of_prices')
    .values('publisher', 'sum_of_prices')
    .order_by('publisher')

Ответ 2

Это ожидается от пути group_by works в Django. Все аннотированные поля добавляются в предложение GROUP BY. Тем не менее, я не могу прокомментировать, почему это было написано таким образом.

Вы можете заставить свой запрос работать следующим образом:

Title.objects
  .values('publisher')
  .annotate(total_dbl_price=Sum(2*F('price'))

который создает следующий SQL:

SELECT publisher, SUM((2 * price)) AS total_dbl_price
FROM title
GROUP BY publisher

который просто срабатывает в вашем случае.

Я понимаю, что это может быть не полное решение, которое вы искали, но некоторые даже сложные аннотации также могут быть размещены в этом решении, используя CombinedExpressions (надеюсь!).

Ответ 3

Ваша проблема исходит от values() следовать за annotate(). Порядок важен. Это объясняется в документации по [порядку аннотата и предложениям значений] ( https://docs.djangoproject.com/en/1.10/topics/db/aggregation/#order-of-annotate-and-values-clauses)

.values('pub_id') ограничьте поле queryset с помощью pub_id. Поэтому вы не можете комментировать на income

Метод values ​​() принимает необязательные позиционные аргументы, * поля, которые определяют имена полей, к которым должен быть ограничен SELECT.

Ответ 4

Это решение @alexandr обращается к нему правильно.

fooobar.com/info/335543/...

Что вам нужно, это:

from django.db.models import Sum

Title.objects.values('publisher').annotate(tot_dbl_prices=2*Sum('price'))

В идеале я поменял сценарий здесь, сначала суммируя их, а затем удваивая. Вы пытались удвоить это, а затем подвести итог. Надеюсь, это хорошо.