Совокупное аннотированное поле из подзапроса в Django

Я пытаюсь реализовать подзапрос с Django ORM, но не могу найти работающее решение. SQL-запрос, который мне нужно было бы перепроектировать:

select t1.location, sum(t1.value_relative::numeric) as total
from (
    select
        administrative_division_id as "location",
        jsonb_array_elements("values"->'loss'->'values')->>'dim_value' as dim_value,
        jsonb_array_elements("values"->'loss'->'values')->>'value_relative' as value_relative
    from assessments_entrybaserisk
    where "values"->'scenario'->>'value' = 'Total'
) t1
where t1.dim_value::numeric = 2
group by t1.location

Я попытался использовать подзапрос Django, как показано ниже:

subq = EntryBaseRisk.objects \
    .filter(id=OuterRef('pk'), values__scenario__value='Total') \
    .annotate(
    _dim_value=Cast(
        KeyTextTransform('dim_value',
                         JsonbArrayElements(KeyTransform('values', KeyTransform('loss', 'values')))),
        IntegerField()),
    _value_relative=Cast(
        KeyTextTransform('value_relative',
                         JsonbArrayElements(KeyTransform('values', KeyTransform('loss', 'values')))),
        FloatField())) \
    .values('assessment', 'administrative_division', '_dim_value', '_value_relative')

data = EntryBaseRisk.objects \
    .annotate(
    dim_value=Subquery(subq.values('_dim_value'), output_field=IntegerField()),
    total_relative_value=Sum(Subquery(subq.values('_value_relative'),
                                      output_field=FloatField()))) \
    .filter(**filters) \
    .values('administrative_division', 'total_relative_value') \
    .order_by('administrative_division')

но я получаю сообщение об ошибке ProgrammingError: more than one row returned by a subquery used as an expression. Проблема в том, что SQL, созданный Django, отличается от моего SQL выше, потому что он не выбирает из выбора и работает, только если подзапрос возвращает только одну строку. Я не могу выполнить агрегацию Sum в подзапросе, потому что вызовы агрегатных функций не могут содержать вызовы функций, возвращающих множество. Есть ли другой способ заставить Django генерировать SQL в соответствии с моими потребностями?


django subquery django-aggregation django-annotate django-subquery
person Stefano Luoni    schedule 06.08.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете попробовать что-то вроде этого:

relative_subquery = subq.values('_value_relative').annotate(total=Sum("*")).values('total')
dim_subquery = subq.values('_dim_value')

data = EntryBaseRisk.objects.annotate(
    dim_value=Subquery(dim_subquery[:1], output_field=IntegerField()),
    total_relative_value=Subquery(relative_subquery, output_field=FloatField())) \
    .filter(**filters) \
    .values('administrative_division', 'total_relative_value') \
    .order_by('administrative_division')

Здесь я суммирую значение _value_relative из subq и использую первое значение _dim_value для аннотации с помощью EntryBaseRisk набора запросов.

person ruddra    schedule 06.08.2020
comment
К сожалению, это не сработает, потому что агрегация пытается быть разрешена внутри подзапроса, поэтому вызовы агрегированных функций с ошибкой не могут содержать вызовы функций, возвращающих наборы, из базы данных. - person Stefano Luoni; 06.08.2020
comment
Вы переместили срез, примененный к полю dim_value, но основная проблема связана с Sum(), потому что подзапрос берет строки из массива jsonb, используя jsonb_array_elements, и невозможно выполнить агрегирование в рамках одного запроса. Вот почему необходим внутренний запрос. Я думаю, что нет способа решить эту проблему с помощью подзапроса Django, потому что он работает по-другому... - person Stefano Luoni; 06.08.2020