Django: Медиана результата подзапроса (возможно, она же Joining с подзапросом)
Уже некоторое время ломаю над этим голову, увы (так всегда бывает, прежде чем прибегнуть к хорошо составленному вопросу). Но он заслуживает тщательного введения и изложения проблемы.
Я очень осторожно взял довольно сильно нагруженную контекстом проблему и извлек из нее классический пример в стиле Django tutorial. В немалой степени, как часть моего собственного исследования проблемы (то есть, я переделал их в проект Django Tutorial с урезанными моделями в стиле автора книги)
Пространство проблем
Представьте себе ученых, публикующих статьи в журналах. Они сотрудничают с другими учеными и публикуют много работ в группах с другими учеными, и они публикуются в различных журналах в различных группах.
Теперь я хочу построить ленивый набор запросов, который возвращает таблицу статистики обо всех публикациях ученых.
Пространство образца
Вот мой пересказ в книжном/авторском стиле моей собственной глубоко контекстуально нагруженной проблемы. К слову сказать, нет необходимости размышлять о разумности моделей, поскольку они фактически уже являются лишь аналогами для другого проблемного пространства, в котором существуют подобные отношения. И я приложил некоторые усилия, чтобы переформулировать ее в пространство учебника специально для проверки моих запросов и их понимания.
Зависимости
Мы будем использовать:
from django.db.models import Model, CharField, DateField, ForeignKey
from django.db.models.functions import Extract, Greatest
from django.db.models import Count, Min, Max, Case, When
from tailslide import Median
Пока что игнорируем Median (хотя это центральный вопрос, и мы до него доберемся), но tailslide - это просто прекрасное обеспечение агрегатора Median для Django.
Модели
Мы будем использовать эти модели:
class Scientist(Model):
name = CharField()
class Journal(Model):
name = CharField()
class Paper(Model):
date = DateField()
title = CharField()
journal = ForeignKey(Journal, related_name="papers")
class Publication(Model):
paper = ForeignKey(Paper, related_name="publications")
author = ForeignKey(Scientist, related_name="publications")
Главное, что следует отметить, это то, что модель Publication связывает автора со статьей, и используется потому, что у статьи может быть много авторов (в некотором смысле она играет роль соединителя "многие ко многим", но может нести в себе и множество других областей). А журнал, конечно, может иметь много статей.
Статистика, которая работает
У меня довольно много статистики, которая хорошо работает.
Начнем с базового набора запросов всех авторов:
authors = Scientist.objects.all()
Затем добавьте некоторые основные статистические данные о публикациях:
authors = authors.annotate(
papers=Count('publications'),
first_paper_date=Min('publications__paper__date'),
latest_paper_date=Max('publications__paper__date')
)
Затем мы становимся немного хитрее и собираем название первого и последнего журнала, в котором была опубликована статья.
publications = Publication.objects.filter(author=OuterRef('pk'))
first_publication = Publication.order_by('paper__date')[:1]
latest_publication = Publication.order_by('-paper__date')[:1]
first_journal = Subquery(first_publication.values('paper__journal__name'))
latest_journal = Subquery(latest_publication.values('paper__journal__name'))
authors = authors.annotate(
first_paper_journal=first_journal,
latest_paper_journal=latest_journal
)
Я использую Subquery и он работает хорошо, но я открыт для изучения лучшего. Проблема, с которой я столкнулся, заключается в том, что я хочу найти свойство статьи, которое я идентифицирую по ее дате. Я не нашел более элегантного способа добиться этого. Агрегаторы Min и Max могут возвращать даты самых ранних и самых поздних публикаций, но не самых ранних публикаций, и поэтому OuterRef и Subquery подходят для решения этой проблемы.
Технически я хотел бы определить статью с самой ранней (или самой поздней) датой и вернуть название журнала, в котором она была опубликована. Интересно, возможно ли это без подзапроса.
journals = Journal.objects.filter(papers__publications__author=OuterRef('pk'))
journals = journals.annotate(journal_count=Count('pk'), last_journal=Max('papers__date'))
favourite_journal = journals.order_by('-journal_count', '-last_journal')[:1]
favourite_journal_name = Subquery(favourite_journal.values('name')
authors = authors.annotate(favourite_journal=favourite_journal_name))
И снова, чтобы получить любимый журнал, я опираюсь на Subquery, прежде всего потому, что мне нужно OuterRef ссылаться на журналы для данного автора, и мы можем использовать агрегатор Count, чтобы получить подсчет каждого Journal pk (первичный ключ) и выбрать тот, который имеет наибольший подсчет.
Это прекрасно работает и генерирует функциональный SQL с подзапросом, имеющим LIMIT 1, из которого выбирается один name. Ключ к тому, что это работает в данном контексте, заключается в том, что подзапрос возвращает единственный результат.
tenure = Greatest(Extract((Max('publications__paper__date') - Min('publications__paper__date')), 'days') , 1)
authors = authors.annotate(publication_career_length=tenure)
Продолжительность публикационной карьеры автора (tenure) относительно легко извлечь. Единственная хитрость здесь заключается в том, что некоторые авторы имеют только одну публикацию, поэтому этот показатель становится равным 0, что нереально и нежелательно (так как я хочу разделить на него, чтобы получить коэффициент публикаций), поэтому я извлекаю его с минимальным значением 1, используя Greatest. Все по-прежнему восхитительно лениво.
ppm = ExpressionWrapper(
Count('publications') / tenure * Case(When(tenure__gt=30, then=30), default=1),
output_field=FloatField()
)
authors = authors.annotate(publication_rate_ppm=ppm)
Коэффициент публикаций (Papers per Month или ppm) аналогично прост с помощью простого выражения и Case, используемого для предотвращения смехотворно высоких показателей публикаций для тех авторов, которые имеют только одну публикацию. Мы также приводим его к float, потому что в противном случае он остается int, а низкие показатели публикаций становятся нулевыми. Это работает очень хорошо (хотя удивительно, что SQL при проверке выдает float, но Django приводит его обратно к int, и нам приходится просить Django не делать этого). Если уж на то пошло, я проверяю SQL и запускаю его в pgadmin, чтобы посмотреть, что он выдает.
paper_pks = Paper.objects.filter(publications__author=OuterRef(OuterRef('pk'))).values('pk')
papers = Paper.objects.filter(pk__in=paper_pks).annotate(num_authors=Count('publications'))
smallest_team = papers.order_by('num_authors')[:1]
largest_team = papers.order_by('-num_athors')[:1]
authors = authors.annotate(
smallest_team=smallest_team,
largest_team=largest_team
)
Хотим посмотреть, как автор пишет, часто в одиночку или в больших коллективах, или что, некоторые статистические данные о размере коллектива, то есть о количестве авторов в данной работе.
Это оказывается немного сложнее, поскольку мне нужно количество авторов статьи, поэтому для аннотирования статей я хочу отфильтровать только те, которые принадлежат данному автору. И здесь кроется глубокая загвоздка. Первая загвоздка в том, что мне нужно два OuterRefs ... оказывается, первый относится ко второму запросу Paper, а второй нужен для того, чтобы он относился к ученому (автору) в этой строке
Это кажется мне неуклюжим, но работает замечательно и остается ленивым (так как я могу проверять SQL и тестировать в pgadmin).
Невероятно, но явный Subquery необходим из-за OutRef. Похоже, что нам нужно OuterRef для ссылки на этого автора. Более простой синтаксис, который я пробовал, не работает:
papers = Paper.objects.filter(publications__author__pk=OuterRef('pk')).annotate(num_authors=Count('publications'))
Чтобы получить наименьшее и наибольшее количество авторов одной опубликованной работы, мне нужно количество авторов каждой работы, но только тех, которые принадлежат данному автору.
Было бы неплохо иметь возможность делать это с помощью join, а не подзапроса, но не могу придумать, как этого добиться.
Статистика, которую я не могу получить (Median Team Size)
Все вышеперечисленное работает. И я могу получить красивый ленивый запрос, который, будучи оценен, представляет собой таблицу ученых (авторов) и все эти прекрасные статистические данные. Я могу и фильтровать ее, конечно, в представлениях, но ее подготовка мила, и база данных запрашивается только один раз, чтобы получить конечную таблицу (поэтому вся работа делается на стороне базы данных).
Но мне нужны не только наименьшая и наибольшая команды, в которых публиковался ученый, мне нужен и медианный размер команды, в которой они публиковались. Я серьезно говорю о среднем, хотя это не имеет особого значения, поскольку я не могу получить ни того, ни другого. (для знатоков статистики, среднее, конечно, бессмысленно как мера размера команды, а медиана и режим представляют интерес, но медиана удобно доступна через tailwind, и я использую медианы по всему сайту в других местах таким образом - медиана и режим тоже должны быть в ядре Django IMHO)
Проблема, с которой я сразу же столкнулся, заключается в том, что Median (или Avg) - это агрегация, и поэтому она работает на столбце/поле, которое не входит в предложение GROUP BY, агрегируя это поле каким-то образом во время группировки. Проблема в том, чтобы построить такой Queryset, в котором могли бы работать операторы агрегации.
Здесь можно было бы пересмотреть smallest_team и largest_team выше в терминах агрегаторов Min и Max, если это возможно. Вместо этого я использовал подзапрос, упорядочивание и ограничение результата (до одной строки). Использование явных агрегаторов было бы более элегантным.
Но что это влечет за собой, так это наличие набора Queryset, состоящего из статей, аннотированных по количеству авторов:
papers = Paper.objects.annotate(num_authors=Count('publications'))
И некоторый способ присоединить это к Scientist. То есть присоединение аннотированной модели к существующей модели.
Если бы это было возможно, то авторский Queryset (который находится на сайте Scientist) можно было бы аннотировать просто:
authors = authors.annotate(median_team_size=Median('publications__paper__num_authors'))
По сути, нам нужна медиана того же подзапроса, из которого мы получили min и max. Вспомнив, что наименьшее и наибольшее значения можно получить, используя неявный подзапрос с упорядочиванием и ограничением:
paper_pks = Paper.objects.filter(publications__author=OuterRef(OuterRef('pk'))).values('pk')
papers = Paper.objects.filter(pk__in=paper_pks).annotate(num_authors=Count('publications'))
smallest_team = papers.order_by('num_authors')[:1]
largest_team = papers.order_by('-num_athors')[:1]
Но если это подзапрос:
paper_pks = Paper.objects.filter(publications__author=OuterRef(OuterRef('pk'))).values('pk')
papers = Paper.objects.filter(pk__in=paper_pks).annotate(num_authors=Count('publications'))
(он возвращает список статей, аннотированных с num_authors для данного автора - OuterRefed), то нам нужна медиана этого списка num_authors. Увы, Median является агрегатором и не работает с массивами, иначе мог бы сработать такой трюк:
authors = authors.annotate(
smallest_team=Min(Array(papers)),
largest_team=Max(Array(papers)),
median_team_size=Median(Array(papers)),
)
Но это не так, поскольку агрегаторы не работают с массивами (и даже не могут быть дико эффективными, если бы работали, поскольку это могут быть большие массивы), они работают с полями таблицы/модели в запросе с группированными кортежами.
Я перепробовал столько вариантов и проверил столько SQL и застрял. Я подозреваю, что я так и останусь в тупике, и единственным выходом может быть RAW SQL, которого я хочу избежать, если это вообще возможно.
Все сводится к объединению кверисетов, а не только моделей. Один из способов, который может быть использован, это определение кверисета как Common Table Expression (CTE) в SQL, и django-cte может быть полезен, но я до сих пор не смог найти способ применить его здесь, а документация по нему несколько бедна.