Среднее значение из 5 лучших значений в модели

У меня есть модель django с большим количеством полей. Я пытаюсь в одном запросе получить среднее значение данного поля и среднее значение 5 лучших значений этого же поля (из моего другого вопроса о чистом SQL: Среднее значение 5 лучших значений в таблице для заданной группы by). Не то чтобы это имело значение, но: моя база данных - redshift.

Я нашел два разных способа добиться этого на SQL, но у меня возникают проблемы с реализацией этих запросов с помощью django ORM

Вот пример того, что я хочу сделать с помощью Cars:

class Cars(models.Model):
    manufacturer = models.CharField()
    model = models.CharField()
    price = models.FloatField()

Данные:

manufacturer | model | price
Citroen        C1      1
Citroen        C2      2
Citroen        C3      3
Citroen        C4      4
Citroen        C5      5
Citroen        C6      6
Ford           F1      7
Ford           F2      8
Ford           F3      9
Ford           F4      10
Ford           F5      11
Ford           F6      12 
Ford           F6      19 
GenMotor       G1      20
GenMotor       G3      25
GenMotor       G4      22

Ожидаемый выход: 

manufacturer | average_price | average_top_5_price
Citroen        3.5             4.0
Ford           10.85           12.2
GenMotor       22.33           22.33

Вот два чистых SQL-запроса, достигающих желаемого эффекта:

SELECT
    main.manufacturer,
    AVG(main.price) AS average_price,
    AVG(CASE WHEN rank <= 5 THEN main.price END) AS average_top_5_price
FROM (
    SELECT
        manufacturer,
        price,
        ROW_NUMBER() OVER (PARTITION BY manufacturer ORDER BY price DESC) AS rank
    FROM
        cars
) main
GROUP BY
    main.manufacturer;

А второй способ: 

SELECT A.manufacturer, A.avg_price, B.top5_price
FROM (
    SELECT manufacturer, AVG(price) as avg_price
    FROM cars
    GROUP BY manufacturer
) A
JOIN (
    SELECT manufacturer, AVG(psv_99) as top5_price
    FROM (
        SELECT manufacturer, price, RANK()
        OVER (PARTITION BY manufacturer ORDER BY price DESC, id)
        FROM cars
    )
    WHERE rank <= 5
    GROUP BY manufacturer
) B
ON A.manufacturer = B.manufacturer
ORDER BY manufacturer

Пока что мне не удалось реализовать ни один из этих запросов с помощью django ORM, для первого я не могу найти способ заставить django сделать "select from subquery", для второго я не могу найти хороший способ заставить django "join two subquery"

Вы можете использовать комбинацию values и annotate для group by производителя, а затем вычислить среднее по группе с помощью Avg.

Вычислить average_price довольно просто: 

from django.db.models import Avg
from django.db.models.functions import Round

averages =
Car.objects.values("manufacturer").annotate(average_price=Round(Avg("price"), precision=2))

Но чтобы вычислить пятерку лучших в каждой группе, все становится немного сложнее (как мне кажется). Для этого вам понадобится Subquery. Итак, полный код будет выглядеть так:


from django.db.models import Subquery, OuterRef, Avg, Q
from django.db.models.functions import Round

group_top_5 = Car.objects.filter(manufacturer=OuterRef("manufacturer")).order_by("-price")[:5].values("price")

query_filter = Q(price__in=group_top_5)
averages = (
Car.objects.values("manufacturer")
.annotate(
average_price=Round(Avg("price"), precision=2), 
average_top_5_price=Round(Avg("price", filter=query_filter), precision=2))

)

Это должно дать вам:

{'manufacturer': 'Citroen', 'average_price': 3.5, 'average_top_5_price': 4.0}
{'manufacturer': 'Ford', 'average_price': 10.86, 'average_top_5_price': 12.2}
{'manufacturer': 'GenMotor', 'average_price': 22.33, 'average_top_5_price': 22.33}

Предполагая, что ваша СУБД поддерживает функцию AVG со спецификацией оконной рамки, вы можете использовать следующее:

from carsdemo.models import Cars
from django.db.models import OuterRef, Subquery
from django.db.models import Window, RowRange, F, Avg

windowFrameSpec = {
  "order_by": F("price").desc(),
  "frame": RowRange(start=0, end=4)
}

averageSubquery = Cars.objects.filter(manufacturer=OuterRef("manufacturer")).annotate(
  the_value = Window(expression=Avg("price"), **windowFrameSpec)
).order_by("-price")

result = Cars.objects.values("manufacturer").annotate(
  average_price=Avg("price"),
  average_top_5_price=Subquery(averageSubquery.values("the_value")[:1])
)

На SQL Server он выдает следующий запрос:

SELECT carsdemo_cars.manufacturer, AVG(carsdemo_cars.price) AS average_price, (
  SELECT TOP 1 AVG(U0.price) OVER (
    ORDER BY U0.price DESC 
    ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING
  ) AS the_value
  FROM carsdemo_cars U0
  WHERE U0.manufacturer = (carsdemo_cars.manufacturer)
  ORDER BY U0.price DESC
) AS average_top_5_price
FROM carsdemo_cars
GROUP BY carsdemo_cars.manufacturer

И результат: 

<QuerySet [
{'manufacturer': 'Citroen', 'average_price': 3.5, 'average_top_5_price': 4.0},
{'manufacturer': 'Ford', 'average_price': 10.857142857142858, 'average_top_5_price': 12.2},
{'manufacturer': 'GenMotor', 'average_price': 22.333333333333332, 'average_top_5_price': 22.333333333333332}
]>

Обратите внимание, что, возможно, есть лучшие способы написать приведенный выше запрос, но я нахожу этот способ более простым в реализации.

Вернуться на верх