Создаём запросы с Group By в Django ORM
В данном уроке будет рассказано как выполнять 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
Автор надеется, что этот маленький туторил был полезен.