Агрегация аннотации в GROUP BY в Джанго

Question

Агрегация аннотации в GROUP BY в Джанго

ОБНОВИТЬ

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

Эта проблема

Я пытаюсь перевести запрос 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's   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

Джанго запрос

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

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 () предшествует annotate(), аннотация будет вычислена с использованием группировки, описанной предложением values ().

что является основой того, как Django реализует SQLGROUP 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) SELECT список) и (2) группировка + агрегирование по группам (SQL GROUP BY) - и в этом случае эти два способа кажутся противоречивыми.

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

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

43

sql django django-models django-orm

Источник

user565518 24 мар '17 в 19:23

4 ответа

Другие вопросы по тегам sql django django-models django-orm

user6627564 04 июл '17 в 23:39 2017-07-04 23:39 · Answer 1 · 2017-07-04 23:39

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

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

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

Решение сделать все агрегации, которые сначала требуют аннотированных полей, а затем вызвать .values() и включить эту агрегацию в поля param(это не добавит GROUP BYпотому что они являются скоплениями). Тогда мы должны позвонить .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/

Кроме того, вы можете захотеть удалить эту ложную аннотацию из вашего вывода, поэтому снова вызовите.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')

user2485594 29 мар '17 в 18:10 2017-03-29 18:10 · Answer 2 · 2017-03-29 18:10

Это ожидается от того, как group_by работает в 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(я надеюсь!).

user1327002 24 мар '17 в 21:20 2017-03-24 21:20 · Answer 3 · 2017-03-24 21:20

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

.values('pub_id') ограничить поле набора запросов pub_id, Так что вы не можете комментировать income

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

user6323666 28 мар '17 в 15:19 2017-03-28 15:19 · Answer 4 · 2017-03-28 15:19

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

from django.db.models import Sum

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

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

1

Источник

user6323666 28 мар '17 в 15:19