Вложенные подзапросы в Django

Погружаемся в глубокую воду с помощью Subquery. У меня есть набор Carparks. Carpark имеют несколько Booking. В бронированиях есть много BarrierActivity записей, которые представляют собой различные приходы и уходы на барьерах. Это все простые FK вверх по стеку.

Бронирование может быть получено, а камеры барьера не его распознают. Сотрудник звонит им, но это означает, что система по какой-то причине вышла из строя. И это то, что я пытаюсь сделать здесь. Выясните, какой процент моих бронирований был получен с помощью автоматических средств. Я знаю, что есть несколько других способов сделать это, но я хотел бы сделать это с помощью одного набора запросов на основе подзапроса.

Моя цель достаточно проста. Аннотируйте 0 или 1, чтобы показать, существует ли «запись» BarrierActivity для каждого Booking. Аннотируйте среднее значение этих значений за Carpark.

Первая часть неплохая. Я могу сделать простое Exists() между BarrierActivity и Booking, и тогда каждое бронирование будет иметь 0 или 1:

successful_bas = BarrierActivity.objects.order_by().filter(
    booking=OuterRef('pk'),
    activity_type=BarrierActivity.TYPE_ANPR_BOOKING,
    direction='entry'
).values('booking')

Booking.objects.order_by().annotate(
    entry_success=Exists(successful_bas)
)

И опять же, это работает нормально. Но как только я попытаюсь масштабировать это еще на один слой (поэтому смотрю на Carpark вместо Booking)...

successful_bas = BarrierActivity.objects.order_by().filter(
    booking=OuterRef('pk'),
    activity_type=BarrierActivity.TYPE_ANPR_BOOKING,
    direction='entry'
).values('booking')

bookings = Booking.objects.order_by().filter(
    carpark=OuterRef('pk')
).values('carpark').annotate(
    entry_success=Exists(successful_bas)
).values('entry_success')

Carpark.objects.order_by().annotate(
    entry_hitrate=ExpressionWrapper(
        Avg(Cast(Subquery(bookings), IntegerField())) * 100,
        output_field=FloatField()
    )
)

... Я получаю подзапрос-ошибка-ошибки: more than one row returned by a subquery used as an expression. Подзапрос bookings явно возвращает слишком много, но как мне агрегировать это до того, как он попадет в самый внешний подзапрос?

Я пробовал много вещей, но вот реорганизация среднего значения внутри подзапроса. Та же ошибка:

successful_bas = "... as before ..."

bookings = Booking.objects_standard.order_by().filter(
    carpark=OuterRef('pk')
).values('site').annotate(
    entry_success=Exists(successful_bas)
).annotate(
    entry_avg=Avg(Cast('entry_success', IntegerField()))
).values('entry_avg')

Carpark.objects.order_by().annotate(
    entry_hitrate=ExpressionWrapper(
        Subquery(bookings, output_field=FloatField()) * 100,
        output_field=FloatField()
    )
)

django django-aggregation django-subquery
person Oli    schedule 07.06.2017    source источник
comment
Подзапрос возвращает несколько. Возьмите среднее значение перед вызовом приведения   -  person Brobin    schedule 07.06.2017
comment
Приведение необходимо для работы Avg (выполняет bool→int). Я испробовал несколько вариантов — безрезультатно — я отредактирую тот, который больше всего похож на то, что вы предлагаете.   -  person Oli    schedule 07.06.2017
comment
@Oli, ваш подход выглядит излишне сложным (преобразование bool в int, а затем получение среднего значения?), можете ли вы записать на простом SQL то, чего хотите достичь, и я думаю, что можно найти более простой подход.   -  person Todor    schedule 08.06.2017
comment
@ Тодор, я не думаю, что могу написать то, что хочу, на SQL. Английский и Python — лучшее, что у меня есть. Я пытаюсь выяснить, какая доля бронирований на каждой автостоянке имеет определенный вид барьерной активности. Я мог бы подсчитать активность барьеров на автостоянке, отличающуюся от бронирования, и усреднить это по отдельному подсчету бронирований парковки, но это менее точно. На самом деле я работаю над денормализацией входных и выходных BA, чтобы они были связаны обратно из каждого бронирования. Это значительно облегчит мой анализ, но я все же хотел бы знать — для моей пользы как разработчика Django — как выполнять вложенные подзапросы.   -  person Oli    schedule 08.06.2017
comment
@Oli, можете ли вы опубликовать полную структуру модели +, возможно, некоторые тестовые данные, чтобы я мог воспроизвести это (+ ожидаемый результат в качестве бонуса: D)? Я хотел бы поиграть с этим примером, но я не могу его воспроизвести.   -  person Todor    schedule 11.06.2017

Ответы (1)


arrow_upward
0
arrow_downward

Мне удалось воссоздать часть этого в одном из моих собственных проектов, и добавление distinct('<values_field_name>').order_by() во внешний подзапрос решило эту проблему.

Отдельный вызов необходим для уменьшения количества возвращаемых строк. Имя поля в отдельном вызове является обязательным, в противном случае он пытается выполнить отдельный вызов через другое поле. Затем вызов order_by() очищает любой порядок, чтобы запрос имел порядок отдельного выражения, соответствующий порядку исходного выражения.

Вот что я бы попробовал:

successful_bas = BarrierActivity.objects.order_by().filter(
    booking=OuterRef('pk'),
    activity_type=BarrierActivity.TYPE_ANPR_BOOKING,
    direction='entry'
).values('booking')

bookings = Booking.objects.order_by().filter(
    carpark=OuterRef('pk')
).annotate(
    entry_success=Exists(successful_bas)
).values('carpark').distinct('carpark').order_by()

Carpark.objects.order_by().annotate(
    entry_hitrate=ExpressionWrapper(
        Avg(Cast(Subquery(bookings), IntegerField())) * 100,
        output_field=FloatField()
    )
)

Обратите внимание, что я удалил один из вызовов values во внешнем подзапросе.

person schillingt    schedule 07.06.2017
comment
Пара проблем. Как есть, он возвращает идентификаторы автостоянок. Если я изменю этот последний вызов values() на entry_success, он просто выберет первый или что-то в этом роде. Он возвращает ноль. Я добавил несколько новых записей BarrierActivity только для того, чтобы убедиться, что он тоже не должен возвращать ноль. - person Oli; 08.06.2017
comment
Я боялся, что ваш конкретный случай будет слишком отличаться от моего тестового случая. Извини. Попробуйте распечатать запрос и перепроектировать решение ORM. - person schillingt; 08.06.2017