Рассчитать Макс Сумма аннотированного поля над сгруппированным по запросу в Django ORM?

Для простоты у меня есть четыре таблицы (A, B, Category и Relation), таблица Relation хранит Intensity A в B и Категория хранит тип B.

A <--- Отношение ---> B ---> Категория

(Таким образом, отношение между A и B равно n к n, когда отношение между B и Category равно n к 1)

Мне нужно ORM для группировки записей отношений по категориям и A, а затем рассчитать Sum из Intensity в каждом (Категория, A) (кажется простым до сих пор), то я хочу аннотировать Макс рассчитывается Sum в каждой категории.

Мой код что-то вроде:

 A.objects.values('B_id').annotate(AcSum=Sum(Intensity)).annotate(Max(AcSum))

Который выдает ошибку:

django.core.exceptions.FieldError: Cannot compute Max('AcSum'): 'AcSum' is an aggregate

Пакет Django-group-by с той же ошибкой.

Для получения дополнительной информации, пожалуйста, также посмотрите этот вопрос stackru.

Я использую Django 2 и PostgreSQL.

Есть ли способ добиться этого с помощью ORM, если нет, каким было бы решение с использованием необработанного выражения SQL?

Обновить

После долгих попыток я обнаружил, что то, что я написал, действительно было агрегацией, однако я хочу выяснить максимальное значение AcSum для каждого A в каждой категории. Поэтому я полагаю, что мне нужно сгруппировать результаты еще раз после вычисления AcSum. Основываясь на этом понимании, я нашел вопрос о переполнении стека, который задает ту же концепцию (вопрос задавался 1 год, 2 месяца назад без какого-либо принятого ответа). Привязка других значений ('id') к набору не работает ни как group_by, ни как фильтр для выходных атрибутов. Она удаляет AcSum из набора. Добавление AcSum в values ​​() также недоступно из-за изменений, сгруппированных по результирующему набору. Я думаю, что я пытаюсь сделать, это перегруппировать сгруппированные по запросу на основе полей внутри столбца (то есть идентификатор). Какие-нибудь мысли?

Источник

2 ответа

Решение

Вы не можете сделать совокупность совокупности Max(Sum())в SQL это недопустимо, используете ли вы ORM или нет. Вместо этого вы должны присоединить таблицу к себе, чтобы найти максимум. Вы можете сделать это с помощью подзапроса. Приведенный ниже код выглядит правильно для меня, но имейте в виду, что у меня нет чего-то для этого, поэтому он может быть не идеальным.

from django.db.models import Subquery, OuterRef

annotation = {
    'AcSum': Sum('intensity')
}
# The basic query is on Relation grouped by A and Category, annotated
# with the Sum of intensity
query = Relation.objects.values('a', 'b__category').annotate(**annotation)

# The subquery is joined to the outerquery on the Category
sub_filter = Q(b__category=OuterRef('b__category'))
# The subquery is grouped by A and Category and annotated with the Sum
# of intensity, which is then ordered descending so that when a LIMIT 1
# is applied, you get the Max.
subquery = Relation.objects.filter(sub_filter).values('a', 'b__category').annotate(**annotation).order_by('-AcSum').values('AcSum')[:1]

query = query.annotate(max_intensity=Subquery(subquery))

Это должно генерировать SQL как:

SELECT a_id, category_id,
       (SELECT SUM(U0.intensity) AS AcSum
        FROM RELATION U0
        JOIN B U1 on U0.b_id = U1.id
        WHERE U1.category_id = B.category_id
        GROUP BY U0.a_id, U1.category_id
        ORDER BY SUM(U0.intensity) DESC
        LIMIT 1
       ) AS max_intensity
FROM Relation
JOIN B on Relation.b_id = B.id
GROUP BY Relation.a_id, B.category_id

Может быть более эффективным устранение объединения в подзапросе с использованием специфической для бэкенда функции, например array_agg (Postgres) или GroupConcat (MySQL), для сбора Relation.id, которые сгруппированы во внешнем запросе. Но я не знаю, какой бэкэнд вы используете.

Источник

Нечто подобное должно работать для вас. Я не мог проверить это сам, поэтому, пожалуйста, дайте мне знать результат:

Relation.objects.annotate(
   b_category=F('B__Category')
).values(
   'A', 'b_category'
).annotate(
   SumInensityPerCategory=Sum('Intensity')
).values(
   'A', MaxIntensitySumPerCategory=Max('SumInensityPerCategory')
)
Источник
Другие вопросы по тегам