Django: Вычисление и группировка в Django ORM с несколькими столбцами

Добрый день,

Прямо сейчас я пытаюсь улучшить свои знания о Django ORM, но борюсь с задачей ниже:

Сначала база данных выглядит следующим образом:

class DeathsWorldwide(models.Model):
    causes_name = models.CharField(max_length=50, null=False, blank=False)
    death_numbers = models.PositiveIntegerField(default=0)
    country = models.CharField(max_length=50, null=True, blank=True)
    year = models.PositiveIntegerField(null=False, blank=False, validators=[MinValueValidator(1990), MaxValueValidator(2019)])


causes_name    |    death_numbers    |    country    |    year
Alcohol dis.   |    25430            |    Germany    |    1998
Poisoning      |    4038             |    Germany    |    1998
...
Maternal dis.  |    9452             |    Germany    |    1998
Alcohol dis.   |    21980            |    Germany    |    1999
Poisoning      |    5117             |    Germany    |    1999
...
Maternal dis.  |    8339             |    Germany    |    1999

Всегда блок всех заболеваний для каждого года, каждой страны и так далее... Диапазон лет идет от 1990 до 2019.

Что я - или, скорее, скажем так, задача - хочу получить, это список всех стран с рассчитанным количеством смертей, примерно так...

country    |    death_numbers
France     |    78012
Germany    |    70510
Austria    |    38025

...но с одной дополнительной особенностью: количество смертей для каждой страны между 1990-1999 годами должно быть вычтено из количества смертей 2000-2019 годов. Таким образом, полный список будет выглядеть примерно так:

country    |    death_numbers    |    19xx    |    2xxx
France     |    78012            |    36913   |    114925
Germany    |    70510            |    ...     |    ...
Austria    |    38025            |    ...     |    ...

Можно ли добиться такого результата с помощью только одного запроса?

Спасибо за помощь и хорошего дня!

Что-то вроде следующего должно помочь.

from django.db.models import Sum, Q

t19 = Sum('death_numbers', filter=Q(year__lt=2000))
t20 = Sum('death_numbers', filter=Q(year__gte=2000))
DeathsWorldwide.objects.values('country').annotate(t19=t19, t20=t20, total=t20 - t19)

В результате получается следующий SQL:

SELECT 
    "base_deathsworldwide"."country", 
    SUM("base_deathsworldwide"."death_numbers") 
        FILTER (WHERE "base_deathsworldwide"."year" < 2000)
        AS "t19", 
    SUM("base_deathsworldwide"."death_numbers") 
        FILTER (WHERE "base_deathsworldwide"."year" >= 2000)
        AS "t20", 
    (
        SUM("base_deathsworldwide"."death_numbers") 
            FILTER (WHERE "base_deathsworldwide"."year" >= 2000) 
        - SUM("base_deathsworldwide"."death_numbers") 
            FILTER (WHERE "base_deathsworldwide"."year" < 2000)
    ) AS "total" 
FROM "base_deathsworldwide" 
GROUP BY "base_deathsworldwide"."country"

Это один запрос, но повторяющий вычисления. Похоже, что ORM не поддерживает его, но мы можем попробовать построить сырой SQL с минимальными усилиями:

from django.db import connection  # replace it with connections if using multiple databases
from django.db.models import Sum, Q

t19 = Sum('death_numbers', filter=Q(year__lt=2000))
t20 = Sum('death_numbers', filter=Q(year__gte=2000))
base_query = DeathsWorldwide.objects.values('country').annotate(t19=t19, t20=t20).query
sql, params = base_query.sql_with_params()
template = 'SELECT 1 as id, country, t19, t20, (t20 - t19) AS total FROM ({}) "temp"'

with connection.cursor() as cursor:
    data = cursor.execute(template.format(sql), params).fetchall()

print(data)

Я предлагаю измерить производительность обоих вариантов и сравнить. Если разница невелика (или вторая меньше, что также возможно, поскольку оптимизация плана может занять больше времени) или первая достаточно быстра для вашего случая использования, то лучше придерживаться ORM-решения.

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