Агрегатные функции и их группировка

В этом шаге вы узнаете, как использовать Django Group By с агрегатными функциями для вычисления агрегации для групп.

Знакомство с группировкой в Django

Предложение SQL GROUP BY группирует строки, возвращаемые запросом, в группы. Как правило, вы используете агрегатные функции , такие как Count, Min, Max, Avg и Sum, с предложением, GROUP BY чтобы вернуть агрегированное значение для каждой группы.

Вот основное использование предложения GROUP BY в SELECT операторе:

SELECT column_1, AGGREGATE(column_2)
FROM table_name
GROUP BY column1;

В Django вы можете использовать annotate() метод для values() применения агрегации к группам следующим образом:

(Entity.objects.values('column_2').annotate(value=AGGREGATE('column_1')))

В этом синтаксисе;

values('column_2')– передать в метод столбец, который вы хотите сгруппировать values().
annotate(value=AGGREGATE('column_1'))– указать, что агрегировать в annotate()методе.

Обратите внимание, что порядок вызова values() и annotates() имеет значение. Если вы не вызовете values() метод первым и annotate() вторым, выражение не будет давать агрегированные результаты.

Django Group в примерах

Мы будем использовать модели Employee и Department из HR приложения для демонстрации. Модели Emloyee и Department сопоставляются с таблицами hr_employee и hr_department в базе данных:

1) Django Group By с примером Count

В следующем примере метод values() and используется annotate() для получения количества сотрудников по отделам:

Employee.objects.values('department').annotate(head_count=Count('department')).order_by('department')

Как это работает.

Сначала сгруппируем сотрудников по отделам с помощью метода values():

values('department')

Далее, применяется функция Count() к каждой группе:

annotate(head_count=Count('department'))

И в конце отсортируйте объекты в наборе QuerySet по отделам:

order_by('department')

За кулисами Django выполняет SELECT оператор с предложением GROUP BY:

SELECT "hr_employee"."department_id",
       COUNT("hr_employee"."department_id") AS "head_count"
  FROM "hr_employee"
 GROUP BY "hr_employee"."department_id"
 ORDER BY "hr_employee"."department_id" ASC
 LIMIT 21

2) Django Group By с примером Sum

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

Employee.objects.values('department').annotate(head_count=Sum('department')).order_by('department')

3) Django Group By с примером Min, Max и Avg

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

Employee.objects.values('department').annotate(min_age=Min('age'), max_age=Max('age'), avg_age=Avg('age')).order_by('department')

4) Группировка Django с примером соединения связанных таблиц

В следующем примере используются методы values() и annotate() для получения количества сотрудников в каждом отделе:

Department.objects.values('name').annotate(head_count=Count('employee'))

Как это работает.

values('name') – группирует отделы по названию.
annotate(headcount=Count('employee')) – подсчитывает сотрудников в каждом отделе.

За кулисами Django использует LEFT JOIN для соединения hr_department таблицы с hr_employee таблицей и применения COUNT() функции к каждой группе.

5) Группировка Django и filter()

Чтобы применить условие к группам, вы используете filter() метод. Например, следующий метод использует filter() метод для получения отдела с количеством сотрудников более 1:

Department.objects.values('name').annotate(head_count=Count('employee')).filter(head_count__gt=1)

За кулисами Django использует HAVING предложение для фильтрации группы на основе условия, которое мы передаем методу filter():

SELECT "hr_department"."name",
       COUNT("hr_employee"."id") AS "head_count"
  FROM "hr_department"
  LEFT OUTER JOIN "hr_employee"
    ON ("hr_department"."id" = "hr_employee"."department_id")
 GROUP BY "hr_department"."name"
HAVING COUNT("hr_employee"."id") > 1