Agrégation

Le guide thématique sur l’API d’abstraction de base de données de Django décrit la façon dont les requêtes Django peuvent être utilisées pour créer, récupérer, mettre à jour et supprimer des objets individuels. Il est cependant parfois nécessaire de récupérer des valeurs dérivées du résumé ou de l’agrégation d’une collection d’objets. Ce guide thématique décrit la manière dont des valeurs agrégées peuvent être générées et renvoyées en utilisant des requêtes Django.

Tout au long de ce guide, nous nous référerons aux modèles suivants. Ces modèles sont utilisés pour gérer l’inventaire d’une série de bibliothèques en ligne :

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)
    num_awards = models.IntegerField()

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)
    registered_users = models.PositiveIntegerField()

Antisèche

Vous êtes pressé ? Voici comment effectuer des requêtes avec agrégation, sur la base des modèles ci-dessus :

# Total number of books.
>>> Book.objects.count()
2452

# Total number of books with publisher=BaloneyPress
>>> Book.objects.filter(publisher__name='BaloneyPress').count()
73

# Average price across all books.
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

# Max price across all books.
>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('81.20')}

# Cost per page
>>> Book.objects.all().aggregate(
...    price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()))
{'price_per_page': 0.4470664529184653}

# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.

# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73

# The top 5 publishers, in order by number of books.
>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323

Génération de requêtes d’agrégation sur un objet QuerySet

Django propose deux manières de générer des agrégations. La première est de générer un résumé des valeurs d’un QuerySet entier. Par exemple, admettons que vous vouliez calculer le prix moyen de tous les livres disponibles à la vente. La syntaxe de requêtes de Django fournit une façon de décrire l’ensemble de tous les livres :

>>> Book.objects.all()

Ce dont nous avons besoin, c’est d’une manière de calculer un résumé des valeurs de tous les objets appartenant à ce QuerySet. Cela se fait en ajoutant une clause aggregate() à la suite de l’objet QuerySet:

>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

Dans cet exemple, all() est redondant, cela pourrait donc être simplifié en écrivant :

>>> Book.objects.aggregate(Avg('price'))
{'price__avg': 34.35}

Le paramètre de la clause aggregate() décrit la valeur d’agrégation que vous souhaitez calculer, dans ce cas la moyenne du champ price du modèle Book. Une liste des fonctions d’agrégation disponibles se trouve dans la référence de QuerySet.

aggregate() est la clause terminale d’un objet QuerySet qui, lorsqu’il est évalué, renvoie un dictionnaire de paires nom-valeur. Le nom est un identifiant de la valeur agrégée ; la valeur est le résultat du calcul d’agrégation. Le nom est généré automatiquement à partir du nom du champ et de la fonction d’agrégation. Si vous voulez indiquer manuellement le nom de la valeur agrégée, vous pouvez le faire en précisant ce nom dans la clause d’agrégation :

>>> Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 34.35}

Si vous voulez générer plus d’une valeur agrégée, il suffit d’ajouter un autre paramètre dans la clause aggregate(). Ainsi, si nous voulions aussi connaître les prix maximaux et minimaux de tous les livres, nous écririons la requête suivante :

>>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

Génération de valeurs agrégées pour chaque élément d’un QuerySet

La deuxième manière de générer des valeurs récapitulatives est de générer un récapitulatif individuel pour chaque objet d’un QuerySet. Par exemple, si vous récupérez une liste de livres, il peut être utile de connaître le nombre d’auteurs ayant contribué à chaque livre. Chaque objet Book possède une relation plusieurs-à-plusieurs vers le modèle Author; nous voulons récapituler cette relation pour chaque livre de la requête.

Les récapitulatifs par objet peuvent être générés par la clause annotate(). Lorsque celle-ci est mentionnée, chaque objet de la requête est annoté avec les valeurs indiquées.

La syntaxe de ces annotations est identique à celle utilisée pour les clauses aggregate(). Chaque paramètre d’annotate() décrit une valeur agrégée à calculer. Par exemple, pour annoter les livres avec le nombre de leurs auteurs :

# Build an annotated queryset
>>> from django.db.models import Count
>>> q = Book.objects.annotate(Count('authors'))
# Interrogate the first object in the queryset
>>> q[0]
<Book: The Definitive Guide to Django>
>>> q[0].authors__count
2
# Interrogate the second object in the queryset
>>> q[1]
<Book: Practical Django Projects>
>>> q[1].authors__count
1

Comme pour aggregate(), le nom de l’annotation est automatiquement dérivé du nom de la fonction d’agrégation et du nom du champ sur lequel se fait le calcul. Vous pouvez surcharger ce nom par défaut en fournissant un alias lors de la définition de l’annotation :

>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1

Au contraire de aggregate(), annotate() n’est pas une clause terminale. Le résultat de la clause annotate() est un objet QuerySet. Cet objet peut très bien être modifié par une autre opération de type QuerySet, comme par exemple filter(), order_by() ou même d’autres appels à annotate().

Combinaison de plusieurs agrégations

La combinaison de plusieurs agrégations avec annotate() produit un mauvais résultat <https://code.djangoproject.com/ticket/10060>`_, dans la mesure ou plusieurs tables sont jointes. En raison de l’utilisation de LEFT OUTER JOIN, des lignes dupliquées seront produites si certaines des tables jointes contiennent plus d’enregistrements que d’autres :

>>> Book.objects.first().authors.count()
2
>>> Book.objects.first().chapters.count()
3
>>> q = Book.objects.annotate(Count('authors'), Count('chapters'))
>>> q[0].authors__count
6
>>> q[0].chapters__count
6

Pour la plupart des agrégations, il n’existe pas de solution à ce problème. Cependant, l’agrégation Count possède un paramètre distinct qui peut être utile :

>>> q = Book.objects.annotate(Count('authors', distinct=True), Count('chapters', distinct=True))
>>> q[0].authors__count
2
>>> q[0].chapters__count
3

En cas de doute, inspectez la requête SQL !

Afin de comprendre ce qui se produit dans la requête, il est possible d’examiner la propriété query de l’objet QuerySet.

Jointures et agrégations

Jusqu’ici, nous avons eu affaire à des agrégations sur des champs appartenant au modèle sur lequel portait la requête. Cependant, la valeur à agréger peut parfois appartenir à un modèle qui est lié au modèle sur lequel porte la requête.

Lors de la définition du champ à agréger dans une fonction d’agrégation, Django permet d’utiliser la même notation de double soulignement qui est utilisée pour se référer aux champs liés dans les filtres. Django se charge ensuite des jointures de tables nécessaires pour récupérer et agréger la valeur liée.

Par exemple, pour trouver l’intervalle des prix pratiqués dans chaque magasin, vous pourriez utiliser l’annotation :

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))

Ceci indique à Django de récupérer le modèle Store, de faire la jointure avec le modèle Book (par la relation plusieurs-à-plusieurs) et d’agréger sur le champ du prix du modèle Book pour produire les valeurs du minimum et du maximum.

Les mêmes règles s’appliquent à la clause aggregate(). Si vous vouliez connaître le prix le plus bas et le prix le plus haut de tous les livres mis en vente dans n’importe quel magasin, vous pourriez utiliser la fonction d’agrégation :

>>> Store.objects.aggregate(min_price=Min('books__price'), max_price=Max('books__price'))

Les chaînes de jointure peuvent être aussi profondes que nécessaire. Par exemple, pour extraire l’âge du plus jeune auteur de tous les livres mis en vente, vous pourriez effectuer la requête :

>>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))

Suivi des relations inverses

D’une manière similaire aux Recherches traversant les relations, les agrégations et les annotations sur les champs de modèles ou sur les modèles liés à celui sur lequel porte la requête peuvent contenir des relations « inverses » traversantes. Ici également, il est possible d’utiliser la syntaxe des noms en minuscules des modèles liés et des doubles soulignements.

Par exemple, il est possible d’extraire tous les éditeurs annotés avec leur décompte respectif de tous leurs livres publiés (notez la manière d’utiliser 'book' pour définir la relation de clé étrangère inversée) :

>>> from django.db.models import Count, Min, Sum, Avg
>>> Publisher.objects.annotate(Count('book'))

(chaque Publisher du jeu de requête résultant possédera un attribut supplémentaire nommé book__count)

Une autre requête pourrait porter sur le plus ancien livre publié par chaque éditeur :

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

Le dictionnaire résultant possédera une clé nommée 'oldest_pubdate'. Si l’alias n’avait pas été précisé, cette clé se serait appelée 'book__pubdate__min', ce qui est plutôt long.

Cela ne s’applique pas seulement aux clés étrangères, mais fonctionne aussi avec les relations plusieurs-à-plusieurs. Par exemple, nous pouvons rechercher tous les auteurs annotés avec le nombre total de pages de tous les livres pour lesquels ils ont été (co-)auteurs (notez la manière d’utiliser 'book' pour définir la relation plusieurs-à-plusieurs inversée de Author vers Book)

>>> Author.objects.annotate(total_pages=Sum('book__pages'))

Chaque Author du jeu de requête résultant possédera un attribut supplémentaire nommé total_pages. Si l’alias n’avait pas été précisé, cette clé se serait appelée 'book__pages__sum', ce qui est plutôt long.

Nous pourrions encore extraire la moyenne des notes de tous les livres écrits par les auteurs enregistrés :

>>> Author.objects.aggregate(average_rating=Avg('book__rating'))

Le dictionnaire résultant possédera une clé nommée 'average__rating'. Si l’alias n’avait pas été précisé, cette clé se serait appelée 'book__rating__avg', ce qui est plutôt long.

Agrégations et autres clauses de requêtes

filter() et exclude()

Les agrégations peuvent aussi intervenir dans les filtres. Toute méthode filter() (ou exclude()) appliquée à des champs de modèle normaux fera l’effet de restreindre les objets concernés par l’agrégation.

Lorsqu’il est combiné à une clause annotate(), un filtre fait l’effet de restreindre les objets sur lesquels l’annotation est calculée. Par exemple, vous pouvez générer une liste annotée de tous les livres ayant leur titre commençant par « Django » à l’aide de la requête :

>>> from django.db.models import Count, Avg
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

Lorsqu’il est combiné à une clause aggregate(), un filtre fait l’effet de restreindre les objets sur lesquels l’agrégation est calculée. Par exemple, vous pouvez générer le prix moyen de tous les livres ayant leur titre commençant par « Django » à l’aide de la requête :

>>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))

Filtrage sur les annotations

Les valeurs annotées peuvent également être filtrées. L’alias de l’annotation peut être utilisé dans les clauses filter() et exclude() de la même manière que pour n’importe quel autre champ de modèle.

Par exemple, pour générer une liste de livres ayant plus d’un auteur, vous pouvez écrire la requête :

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

Cette requête génère un jeu de requête annoté, puis filtre le résultat sur la base de l’annotation.

Ordre des clauses annotate() et filter()

Lors de la rédaction de requête complexe impliquant à la fois des clauses annotate() et filter(), une attention particulière doit être donnée à l’ordre dans lequel les clauses sont appliquées à l’objet QuerySet.

Lorsqu’une clause annotate() est appliquée à une requête, l’annotation est calculée sur l’état de la requête au point exact où l’annotation est demandée. L’implication pratique de ceci est que filter() et annotate() ne sont pas des opérations commutatives.

Étant donné :

• Un « Publisher » A possède deux livres avec notations 4 et 5.

• Un « Publisher » B possède deux livres avec notations 1 et 4.

• Un « Publisher » A possède un livre avec notation 1.

Voici un exemple avec l’agrégation Count:

>>> a, b = Publisher.objects.annotate(num_books=Count('book', distinct=True)).filter(book__rating__gt=3.0)
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 2)

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 1)

Les deux requêtes renvoient une liste d’objets « Publisher » ayant au moins un livre avec une note dépassant 3.0, ce qui fait que Publisher C est exclu.

Dans la première requête, l’annotation précède le filtre, ce qui fait que le filtre n’a aucun effet sur l’annotation. distinct=True est obligatoire pour éviter une anomalie de jointure croisée.

La seconde requête compte le nombre de livres ayant une note dépassant 3.0 pour chaque Publisher. Le filtre précède l’annotation, ce qui fait que le filtre limite les objets pris en compte lors du calcul de l’annotation.

Voici un autre exemple avec l’agrégation Avg:

>>> a, b = Publisher.objects.annotate(avg_rating=Avg('book__rating')).filter(book__rating__gt=3.0)
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 2.5)  # (1+4)/2

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(avg_rating=Avg('book__rating'))
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 4.0)  # 4/1 (book with rating 1 excluded)

La première requête calcule la note moyenne de tous les livres de chaque éditeur pour les éditeurs ayant au moins un livre avec une note dépassant 3.0. La seconde requête calcule la moyenne des notes des livres d’un éditeur pour tous les livres dont la note dépasse 3.0.

Il est difficile de deviner comment l’ORM va traduire des jeux de requêtes complexes en instructions SQL ; en cas de doute, inspectez le code SQL produit avec str(queryset.query) et écrivez de nombreux tests.

order_by()

Les annotations peuvent être utilisées comme base de tri. Lorsque vous définissez une clause order_by(), les agrégations que vous indiquez peuvent référencer n’importe quel alias défini dans le cadre d’une clause annotate() de la requête.

Par exemple, pour trier un jeu de requête de livres par le nombre d’auteurs ayant contribué au livre, vous pourriez écrire la requête suivante :

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

values()

En principe, les annotations sont générées pour chaque objet ; un jeu de requête annoté renvoie un résultat par objet du jeu de requête original. Cependant, lorsqu’une clause values() est utilisée pour restreindre les colonnes renvoyées dans le jeu de requête, la méthode d’évaluation des annotations est légèrement différente. Au lieu de renvoyer un résultat annoté pour chaque résultat du jeu de requête original, les résultats d’origine sont groupés selon les combinaisons uniques des champs indiqués dans la clause values(). Puis l’annotation est fournie pour chaque groupe unique ; l’annotation est donc calculée sur tous les membres du groupe.

Par exemple, considérons une requête sur les auteurs cherchant à trouver la moyenne des notes des livres écrits par chaque auteur :

>>> Author.objects.annotate(average_rating=Avg('book__rating'))

Ceci renvoie un résultat pour chaque auteur de la base de données, annoté par la note moyenne de leurs livres.

Cependant, le résultat sera légèrement différent si vous utilisez une clause values():

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

Dans cet exemple, les auteurs sont groupés par nom, vous allez donc obtenir un résultat annoté pour chaque nom d’auteur unique. Cela signifie que si vous avez deux auteurs de même nom, leurs résultats seront fusionnés en un seul dans la requête résultante ; la moyenne sera calculée sur tous les livres écrits par les deux auteurs.

Ordre des clauses annotate() et values()

Comme pour la clause filter(), l’ordre d’apparition des clauses annotate() et values() dans une requête est important. Si la clause values() précède annotate(), l’annotation est calculée par rapport aux groupements définis par la clause values().

Cependant, si la clause annotate() précède la clause values(), les annotations sont générées sur la totalité du jeu de requête. Dans ce cas, la clause values() ne restreint que les champs dans le résultat final.

Par exemple, si nous inversons l’ordre des clauses values() et annotate() dans notre exemple précédent :

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

Cela produit maintenant un résultat unique pour chaque auteur ; cependant, seuls le nom de l’auteur et l’annotation average_rating sont renvoyés dans les données de résultat.

Remarquez également que average_rating a été explicitement inclus dans la liste des valeurs à renvoyer. C’est obligatoire à cause de l’ordre des clauses values() et annotate().

Si la clause values() précède la clause annotate(), toute annotation sera automatiquement ajoutée au résultat de la requête. Toutefois, si la clause values() est appliquée après la clause annotate(), vous devez inclure explicitement la colonne agrégée.

Interaction avec le tri par défaut ou order_by()

Les champs mentionnés dans la partie order_by() d’une requête (ou ceux faisant partie du tri par défaut d’un modèle) sont utilisés lors de la sélection des données du résultat, même s’ils ne sont pas spécifiés explicitement dans l’appel à values(). Ces champs supplémentaires sont aussi pris en compte pour regrouper les résultats similaires et peuvent faire apparaître de manière distincte des lignes qui seraient regroupées sans la présence de ces champs. C’est particulièrement flagrant lorsqu’il s’agit de compter des objets.

Pour fournir un exemple, supposons que vous ayez un modèle comme celui-ci :

from django.db import models

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

L’élément important est ici le tri par défaut sur le champ name. Si vous souhaitez compter le nombre d’apparitions distinctes de la valeur data, vous pourriez écrire ceci :

# Warning: not quite correct!
Item.objects.values("data").annotate(Count("id"))

…ce qui regroupe les objets Item par leurs valeurs data communes, puis compte le nombre de valeurs id dans chaque groupe. Sauf que ça ne marchera pas comme prévu. Le tri par défaut selon name intervient aussi dans le regroupement, ce qui fait que la requête regroupe sur les paires distinctes (data, name), ce qui ne correspond pas à l’intention de départ. Voici en réalité ce qu’il faut écrire pour obtenir la requête espérée :

Item.objects.values("data").annotate(Count("id")).order_by()

…effaçant ainsi tout ordre de tri dans la requête. Il serait aussi possible de trier sur le champ data sans conséquence néfaste, dans la mesure où ce champ est déjà impliqué dans la requête.

Ce comportement est le même que ce qui est noté dans la documentation des requêtes concernant distinct(), la règle générale étant identique : il n’est normalement pas souhaité que des colonnes supplémentaires soient inclues dans le résultat, il faut donc effacer l’ordre de tri ou en tout cas s’assurer que les champs de tri sont déjà impliqués dans l’appel à values().

Note

Vous pourriez raisonnablement vous demander pourquoi Django ne supprime pas ces colonnes parasites à notre place. La raison principale est de conserver la cohérence avec distinct() et d’autres endroits : Django ne supprime jamais les contraintes de tri que vous avez indiquées (et nous ne pouvons pas modifier le comportement de ces autres méthodes car cela trahirait notre politique de Stabilité de l’API).

Agrégation des annotations

Vous pouvez aussi générer une agrégation des résultats d’une annotation. Lorsque vous définissez une clause aggregate(), cette agrégation peut se référer à n’importe quel alias défini dans une clause annotate() de la même requête.

Par exemple, si vous souhaitiez calculer le nombre moyen d’auteurs par livre, vous annoteriez premièrement les livres avec le nombre d’auteurs, puis vous agrégeriez ce nombre en vous référant au champ annoté :

>>> from django.db.models import Count, Avg
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}