Создаём запросы с Group By в Django ORM

| Python

Django ORM GROUP BY

В данном уроке будет рассказано как выполнять SQL-подобные запросы group by используя Django ORM. Это достаточно распространённая операция для знатоков SQL. Django ORM – уровень абстракции, который позволяет манипулировать базой данных используя объектно-ориентированный подход, но в итоге все операции транслируются в SQL-запросы реляционной базы данных.

Работа с сырыми данными, извлеченными из базы данных, может производиться на стороне Python’а, группируя данные в словарях, выполняя итерации по ним, выполнение суммирования, средних значений и т. д. Но база данных является куда более мощным инструментом, чем просто хранение данных и чаще всего может сделать эту работу гораздо быстрее.

Вообще говоря, когда выполняются запросы с group by, нам не интересны детали каждого экземпляра модели (или строки таблицы), но при этом, нам нужно извлечь новую информацию из набора данных, основанной на некоторых общих аспектах соседних сущностей модели.

Рассмотрим пример:

class Country(models.Model):
    name = models.CharField(max_length=30)

class City(models.Model):
    name = models.CharField(max_length=30)
    country = models.ForeignKey(Country)
    population = models.PositiveIntegerField()

И данных, хранящихся в базе данных:

countries

id  name
1   Brazil
2   Turkey
3   Italy
4   Bangladesh
5   Canada
6   France
7   Peru
8   Argentina
9   Nigeria
10  Australia
11  Iran
12  Singapore
13  China
14  Chile
15  Thailand
16  Germany
17  Spain
18  Philippines
19  Indonesia
20  United States
21  South Korea
22  Pakistan
23  Angola
24  Mexico
25  India
26  United Kingdom
27  Colombia
28  Japan
29  Taiwan

cities

id  name    country_id  population
1   Tokyo   28  36,923,000
2   Shanghai    13  34,000,000
3   Jakarta 19  30,000,000
4   Seoul   21  25,514,000
5   Guangzhou   13  25,000,000
6   Beijing 13  24,900,000
7   Karachi 22  24,300,000
8   Shenzhen    13  23,300,000
9   Delhi   25  21,753,486
10  Mexico City 24  21,339,781
11  Lagos   9   21,000,000
12  São Paulo   1   20,935,204
13  Mumbai  25  20,748,395
14  New York City   20  20,092,883
15  Osaka   28  19,342,000
16  Wuhan   13  19,000,000
17  Chengdu 13  18,100,000
18  Dhaka   4   17,151,925
19  Chongqing   13  17,000,000
20  Tianjin 13  15,400,000
21  Kolkata 25  14,617,882
22  Tehran  11  14,595,904
23  Istanbul    2   14,377,018
24  London  26  14,031,830
25  Hangzhou    13  13,400,000
26  Los Angeles 20  13,262,220
27  Buenos Aires    8   13,074,000
28  Xi'an   13  12,900,000
29  Paris   6   12,405,426
30  Changzhou   13  12,400,000
31  Shantou 13  12,000,000
32  Rio de Janeiro  1   11,973,505
33  Manila  18  11,855,975
34  Nanjing 13  11,700,000
35  Rhine-Ruhr  16  11,470,000
36  Jinan   13  11,000,000
37  Bangalore   25  10,576,167
38  Harbin  13  10,500,000
39  Lima    7   9,886,647
40  Zhengzhou   13  9,700,000
41  Qingdao 13  9,600,000
42  Chicago 20  9,554,598
43  Nagoya  28  9,107,000
44  Chennai 25  8,917,749
45  Bangkok 15  8,305,218
46  Bogotá  27  7,878,783
47  Hyderabad   25  7,749,334
48  Shenyang    13  7,700,000
49  Wenzhou 13  7,600,000
50  Nanchang    13  7,400,000
51  Hong Kong   13  7,298,600
52  Taipei  29  7,045,488
53  Dallas–Fort Worth   20  6,954,330
54  Santiago    14  6,683,852
55  Luanda  23  6,542,944
56  Houston 20  6,490,180
57  Madrid  17  6,378,297
58  Ahmedabad   25  6,352,254
59  Toronto 5   6,055,724
60  Philadelphia    20  6,051,170
61  Washington, D.C.    20  6,033,737
62  Miami   20  5,929,819
63  Belo Horizonte  1   5,767,414
64  Atlanta 20  5,614,323
65  Singapore   12  5,535,000
66  Barcelona   17  5,445,616
67  Munich  16  5,203,738
68  Stuttgart   16  5,200,000
69  Ankara  2   5,150,072
70  Hamburg 16  5,100,000
71  Pune    25  5,049,968
72  Berlin  16  5,005,216
73  Guadalajara 24  4,796,050
74  Boston  20  4,732,161
75  Sydney  10  5,000,500
76  San Francisco   20  4,594,060
77  Surat   25  4,585,367
78  Phoenix 20  4,489,109
79  Monterrey   24  4,477,614
80  Inland Empire   20  4,441,890
81  Rome    3   4,321,244
82  Detroit 20  4,296,611
83  Milan   3   4,267,946
84  Melbourne   10  4,650,000

Данные взяты из Википедии, и для примера их достоверность не играет роли.

Если нам нужно знать общее количество жителей в 84 городах, мы могли бы использовать запрос с aggregate

from django.db.models import Sum

City.objects.all().aggregate(Sum('population'))

{'population__sum': 970880224}  # 970,880,224

Среднее значение численности 84 городов

from django.db.models import Avg

City.objects.all().aggregate(Avg('population'))

{'population__avg': 11558097.904761905}  # 11,558,097.90

Как быть если нужно увидеть общую численность населения с упорядочиванием по стране, а не всего набора данных? В данном случае мы не можем использовать метод aggregate, вместо него задействуем annotate.

Предикат aggregate является финишным, т. к. он возвращает Python словарь, значит больше нельзя вызывать специфические методы QuerySet. Вместе с тем, он всегда будет возвращать единственный результат. Если нужно посчитать количество жителей страны используя aggregate, то нужно сделать что-то подобное:

# НЕ ДАЛАЙТЕ ТАК!!!
from django.db.models import Sum

for country in Country.objects.all():
    result = City.objects.filter(country=country).aggregate(Sum('population'))
    print '{}: {}'.format(country.name, result['population__sum'])

# Output:
# -------
# Brazil: 38676123
# Turkey: 19527090
# Italy: 8589190
# Bangladesh: 17151925
# Canada: 6055724
# France: 12405426
# Peru: 9886647
# Argentina: 13074000
# Nigeria: 21000000
# Australia: 9650500
# Iran: 14595904
# ...

Сформирован правильный результат, но нужно выполнить 30 различных запросов к базе данных. Также потеряны некоторые возможности ORM, например, сортировка результирующего набора. Возможно, данные были бы более интересны, если бы была возможность сортировки в порядке убывания населения по стране.

Правильный способ сделать это – использовать annotate, которые будут транслировать запрос с group by к базе данных.

# ПРАВИЛЬНЫЙ ВАРИАНТ
City.objects.all().values('country__name').annotate(Sum('population'))

[
  {'country__name': u'Angola', 'population__sum': 6542944},
  {'country__name': u'Argentina', 'population__sum': 13074000},
  {'country__name': u'Australia', 'population__sum': 9650500},
  {'country__name': u'Bangladesh', 'population__sum': 17151925},
  {'country__name': u'Brazil', 'population__sum': 38676123},
  '...(remaining elements truncated)...'
]

Теперь, если нам нужно отсортировать в порядке убывания численность населения страны, то мы можем воспользоваться псевдонимом, чтобы запрос выглядел чище и используя предикат order_by()

City.objects.all() \
  .values('country__name') \
  .annotate(country_population=Sum('population')) \
  .order_by('-country_population')

[
  {'country__name': u'China', 'country_population': 309898600},
  {'country__name': u'United States', 'country_population': 102537091},
  {'country__name': u'India', 'country_population': 100350602},
  {'country__name': u'Japan', 'country_population': 65372000},
  {'country__name': u'Brazil', 'country_population': 38676123},
  '...(remaining elements truncated)...'
]

Таким образом Django ORM сформирует SQL запрос:

SELECT "core_country"."name", SUM("core_city"."population") AS "country_population"
    FROM "core_city" INNER JOIN "core_country" ON ("core_city"."country_id" = "core_country"."id")
GROUP BY "core_country"."name"
ORDER BY "country_population" DESC

Стоит упомянуть про важную вещь: все манипуляции имеют смысл только при добавлении предиката values(), для данных, которые должны быть сгруппированы. К каждому полю к которому будет добавлен предикат values(), будет использоваться запрос с group by.

Взгляните на QuerySet объект:

City.objects.all().values('name', 'country__name').annotate(Sum('population'))

Результирующий запрос будет следующим:

  SELECT "core_city"."name", "core_country"."name", SUM("core_city"."population") AS "population__sum"
    FROM "core_city" INNER JOIN "core_country" ON ("core_city"."country_id" = "core_country"."id")
GROUP BY "core_city"."name", "core_country"."name"

Данный пример неэффективен, потому что все названия городов уникальны, и они не могут быть сгруппированы (база данных будет стараться сгруппировать их, но каждая группа будет иметь только одну строку/экземпляр). Добиться нужного результата, можно просто применив count к каждому объекту QuerySet.

City.objects.all().values('name', 'country__name').annotate(Sum('population')).count()
84

City.objects.all().values('country__name').annotate(Sum('population')).count()
29

Когда выполняется group by по стране, для подсчёта количества населения, мы потеряли детали о городах (по крайней мере в результате выполнения запроса).

Иногда полезно иметь более чем одно значение в предикате values(). Например, если БД состояла бы из таблиц City / State / Country, тогда можно использовать group_by используя .values('state__name', 'country__name') получив в результате количество всего населения страны. И мы избежали бы State из разных стран (с тем же именем) для группировки.

Значения, которые будут получены из БД, используя предикат annotate, могут также отфильтровываться. Обычно в БД для этого используется функция HAVING. Мы можем читать запрос подобно обычному английскому языку. Теперь в возможностях Django ORM есть простой filter.

Например, допустим нужно посчитать общую численность населения страны, но только для тех стран, где численность больше 50 000 000 человек.

City.objects.all() \
  .values('country__name') \
  .annotate(country_population=Sum('population')) \
  .filter(country_population__gt=50000000) \
  .order_by('-country_population')

[
  {'country__name': u'China', 'country_population': 309898600},
  {'country__name': u'United States', 'country_population': 102537091},
  {'country__name': u'India', 'country_population': 100350602},
  {'country__name': u'Japan', 'country_population': 65372000}
]

Результирующий SQL запрос:

SELECT "core_country"."name", SUM("core_city"."population") AS "country_population"
    FROM "core_city" INNER JOIN "core_country" ON ("core_city"."country_id" = "core_country"."id")
GROUP BY "core_country"."name" HAVING SUM("core_city"."population") > 50000000
ORDER BY "country_population" DESC

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