/docs/topics/db/aggregation.txt

  1===========
  2Aggregation
  3===========
  4
  5.. currentmodule:: django.db.models
  6
  7The topic guide on :doc:`Django's database-abstraction API </topics/db/queries>`
  8described the way that you can use Django queries that create,
  9retrieve, update and delete individual objects. However, sometimes you will
 10need to retrieve values that are derived by summarizing or *aggregating* a
 11collection of objects. This topic guide describes the ways that aggregate values
 12can be generated and returned using Django queries.
 13
 14Throughout this guide, we'll refer to the following models. These models are
 15used to track the inventory for a series of online bookstores:
 16
 17.. _queryset-model-example:
 18
 19.. code-block:: python
 20
 21    class Author(models.Model):
 22       name = models.CharField(max_length=100)
 23       age = models.IntegerField()
 24       friends = models.ManyToManyField('self', blank=True)
 25
 26    class Publisher(models.Model):
 27       name = models.CharField(max_length=300)
 28       num_awards = models.IntegerField()
 29
 30    class Book(models.Model):
 31       isbn = models.CharField(max_length=9)
 32       name = models.CharField(max_length=300)
 33       pages = models.IntegerField()
 34       price = models.DecimalField(max_digits=10, decimal_places=2)
 35       rating = models.FloatField()
 36       authors = models.ManyToManyField(Author)
 37       publisher = models.ForeignKey(Publisher)
 38       pubdate = models.DateField()
 39
 40    class Store(models.Model):
 41       name = models.CharField(max_length=300)
 42       books = models.ManyToManyField(Book)
 43
 44
 45Generating aggregates over a QuerySet
 46=====================================
 47
 48Django provides two ways to generate aggregates. The first way is to generate
 49summary values over an entire ``QuerySet``. For example, say you wanted to
 50calculate the average price of all books available for sale. Django's query
 51syntax provides a means for describing the set of all books::
 52
 53    >>> Book.objects.all()
 54
 55What we need is a way to calculate summary values over the objects that
 56belong to this ``QuerySet``. This is done by appending an ``aggregate()``
 57clause onto the ``QuerySet``::
 58
 59    >>> from django.db.models import Avg
 60    >>> Book.objects.all().aggregate(Avg('price'))
 61    {'price__avg': 34.35}
 62
 63The ``all()`` is redundant in this example, so this could be simplified to::
 64
 65    >>> Book.objects.aggregate(Avg('price'))
 66    {'price__avg': 34.35}
 67
 68The argument to the ``aggregate()`` clause describes the aggregate value that
 69we want to compute - in this case, the average of the ``price`` field on the
 70``Book`` model. A list of the aggregate functions that are available can be
 71found in the :ref:`QuerySet reference <aggregation-functions>`.
 72
 73``aggregate()`` is a terminal clause for a ``QuerySet`` that, when invoked,
 74returns a dictionary of name-value pairs. The name is an identifier for the
 75aggregate value; the value is the computed aggregate. The name is
 76automatically generated from the name of the field and the aggregate function.
 77If you want to manually specify a name for the aggregate value, you can do so
 78by providing that name when you specify the aggregate clause::
 79
 80    >>> Book.objects.aggregate(average_price=Avg('price'))
 81    {'average_price': 34.35}
 82
 83If you want to generate more than one aggregate, you just add another
 84argument to the ``aggregate()`` clause. So, if we also wanted to know
 85the maximum and minimum price of all books, we would issue the query::
 86
 87    >>> from django.db.models import Avg, Max, Min, Count
 88    >>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
 89    {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}
 90
 91Generating aggregates for each item in a QuerySet
 92=================================================
 93
 94The second way to generate summary values is to generate an independent
 95summary for each object in a ``QuerySet``. For example, if you are retrieving
 96a list of books, you may want to know how many authors contributed to
 97each book. Each Book has a many-to-many relationship with the Author; we
 98want to summarize this relationship for each book in the ``QuerySet``.
 99
100Per-object summaries can be generated using the ``annotate()`` clause.
101When an ``annotate()`` clause is specified, each object in the ``QuerySet``
102will be annotated with the specified values.
103
104The syntax for these annotations is identical to that used for the
105``aggregate()`` clause. Each argument to ``annotate()`` describes an
106aggregate that is to be calculated. For example, to annotate Books with
107the number of authors::
108
109    # Build an annotated queryset
110    >>> q = Book.objects.annotate(Count('authors'))
111    # Interrogate the first object in the queryset
112    >>> q[0]
113    <Book: The Definitive Guide to Django>
114    >>> q[0].authors__count
115    2
116    # Interrogate the second object in the queryset
117    >>> q[1]
118    <Book: Practical Django Projects>
119    >>> q[1].authors__count
120    1
121
122As with ``aggregate()``, the name for the annotation is automatically derived
123from the name of the aggregate function and the name of the field being
124aggregated. You can override this default name by providing an alias when you
125specify the annotation::
126
127    >>> q = Book.objects.annotate(num_authors=Count('authors'))
128    >>> q[0].num_authors
129    2
130    >>> q[1].num_authors
131    1
132
133Unlike ``aggregate()``, ``annotate()`` is *not* a terminal clause. The output
134of the ``annotate()`` clause is a ``QuerySet``; this ``QuerySet`` can be
135modified using any other ``QuerySet`` operation, including ``filter()``,
136``order_by``, or even additional calls to ``annotate()``.
137
138Joins and aggregates
139====================
140
141So far, we have dealt with aggregates over fields that belong to the
142model being queried. However, sometimes the value you want to aggregate
143will belong to a model that is related to the model you are querying.
144
145When specifying the field to be aggregated in an aggregate function, Django
146will allow you to use the same :ref:`double underscore notation
147<field-lookups-intro>` that is used when referring to related fields in
148filters. Django will then handle any table joins that are required to retrieve
149and aggregate the related value.
150
151For example, to find the price range of books offered in each store,
152you could use the annotation::
153
154    >>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))
155
156This tells Django to retrieve the Store model, join (through the
157many-to-many relationship) with the Book model, and aggregate on the
158price field of the book model to produce a minimum and maximum value.
159
160The same rules apply to the ``aggregate()`` clause. If you wanted to
161know the lowest and highest price of any book that is available for sale
162in a store, you could use the aggregate::
163
164    >>> Store.objects.aggregate(min_price=Min('books__price'), max_price=Max('books__price'))
165
166Join chains can be as deep as you require. For example, to extract the
167age of the youngest author of any book available for sale, you could
168issue the query::
169
170    >>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))
171
172Aggregations and other QuerySet clauses
173=======================================
174
175``filter()`` and ``exclude()``
176------------------------------
177
178Aggregates can also participate in filters. Any ``filter()`` (or
179``exclude()``) applied to normal model fields will have the effect of
180constraining the objects that are considered for aggregation.
181
182When used with an ``annotate()`` clause, a filter has the effect of
183constraining the objects for which an annotation is calculated. For example,
184you can generate an annotated list of all books that have a title starting
185with "Django" using the query::
186
187    >>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))
188
189When used with an ``aggregate()`` clause, a filter has the effect of
190constraining the objects over which the aggregate is calculated.
191For example, you can generate the average price of all books with a
192title that starts with "Django" using the query::
193
194    >>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))
195
196Filtering on annotations
197~~~~~~~~~~~~~~~~~~~~~~~~
198
199Annotated values can also be filtered. The alias for the annotation can be
200used in ``filter()`` and ``exclude()`` clauses in the same way as any other
201model field.
202
203For example, to generate a list of books that have more than one author,
204you can issue the query::
205
206    >>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)
207
208This query generates an annotated result set, and then generates a filter
209based upon that annotation.
210
211Order of ``annotate()`` and ``filter()`` clauses
212~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
213
214When developing a complex query that involves both ``annotate()`` and
215``filter()`` clauses, particular attention should be paid to the order
216in which the clauses are applied to the ``QuerySet``.
217
218When an ``annotate()`` clause is applied to a query, the annotation is
219computed over the state of the query up to the point where the annotation
220is requested. The practical implication of this is that ``filter()`` and
221``annotate()`` are not commutative operations -- that is, there is a
222difference between the query::
223
224    >>> Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0)
225
226and the query::
227
228    >>> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
229
230Both queries will return a list of Publishers that have at least one good
231book (i.e., a book with a rating exceeding 3.0). However, the annotation in
232the first query will provide the total number of all books published by the
233publisher; the second query will only include good books in the annotated
234count. In the first query, the annotation precedes the filter, so the
235filter has no effect on the annotation. In the second query, the filter
236precedes the annotation, and as a result, the filter constrains the objects
237considered when calculating the annotation.
238
239``order_by()``
240--------------
241
242Annotations can be used as a basis for ordering. When you
243define an ``order_by()`` clause, the aggregates you provide can reference
244any alias defined as part of an ``annotate()`` clause in the query.
245
246For example, to order a ``QuerySet`` of books by the number of authors
247that have contributed to the book, you could use the following query::
248
249    >>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
250
251``values()``
252------------
253
254Ordinarily, annotations are generated on a per-object basis - an annotated
255``QuerySet`` will return one result for each object in the original
256``QuerySet``. However, when a ``values()`` clause is used to constrain the
257columns that are returned in the result set, the method for evaluating
258annotations is slightly different. Instead of returning an annotated result
259for each result in the original ``QuerySet``, the original results are
260grouped according to the unique combinations of the fields specified in the
261``values()`` clause. An annotation is then provided for each unique group;
262the annotation is computed over all members of the group.
263
264For example, consider an author query that attempts to find out the average
265rating of books written by each author:
266
267    >>> Author.objects.annotate(average_rating=Avg('book__rating'))
268
269This will return one result for each author in the database, annotated with
270their average book rating.
271
272However, the result will be slightly different if you use a ``values()`` clause::
273
274    >>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))
275
276In this example, the authors will be grouped by name, so you will only get
277an annotated result for each *unique* author name. This means if you have
278two authors with the same name, their results will be merged into a single
279result in the output of the query; the average will be computed as the
280average over the books written by both authors.
281
282Order of ``annotate()`` and ``values()`` clauses
283~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
284
285As with the ``filter()`` clause, the order in which ``annotate()`` and
286``values()`` clauses are applied to a query is significant. If the
287``values()`` clause precedes the ``annotate()``, the annotation will be
288computed using the grouping described by the ``values()`` clause.
289
290However, if the ``annotate()`` clause precedes the ``values()`` clause,
291the annotations will be generated over the entire query set. In this case,
292the ``values()`` clause only constrains the fields that are generated on
293output.
294
295For example, if we reverse the order of the ``values()`` and ``annotate()``
296clause from our previous example::
297
298    >>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')
299
300This will now yield one unique result for each author; however, only
301the author's name and the ``average_rating`` annotation will be returned
302in the output data.
303
304You should also note that ``average_rating`` has been explicitly included
305in the list of values to be returned. This is required because of the
306ordering of the ``values()`` and ``annotate()`` clause.
307
308If the ``values()`` clause precedes the ``annotate()`` clause, any annotations
309will be automatically added to the result set. However, if the ``values()``
310clause is applied after the ``annotate()`` clause, you need to explicitly
311include the aggregate column.
312
313Interaction with default ordering or ``order_by()``
314~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
315
316Fields that are mentioned in the ``order_by()`` part of a queryset (or which
317are used in the default ordering on a model) are used when selecting the
318output data, even if they are not otherwise specified in the ``values()``
319call. These extra fields are used to group "like" results together and they
320can make otherwise identical result rows appear to be separate. This shows up,
321particularly, when counting things.
322
323By way of example, suppose you have a model like this::
324
325    class Item(models.Model):
326        name = models.CharField(max_length=10)
327        data = models.IntegerField()
328
329        class Meta:
330            ordering = ["name"]
331
332The important part here is the default ordering on the ``name`` field. If you
333want to count how many times each distinct ``data`` value appears, you might
334try this::
335
336    # Warning: not quite correct!
337    Item.objects.values("data").annotate(Count("id"))
338
339...which will group the ``Item`` objects by their common ``data`` values and
340then count the number of ``id`` values in each group. Except that it won't
341quite work. The default ordering by ``name`` will also play a part in the
342grouping, so this query will group by distinct ``(data, name)`` pairs, which
343isn't what you want. Instead, you should construct this queryset::
344
345    Item.objects.values("data").annotate(Count("id")).order_by()
346
347...clearing any ordering in the query. You could also order by, say, ``data``
348without any harmful effects, since that is already playing a role in the
349query.
350
351This behavior is the same as that noted in the queryset documentation for
352:meth:`~django.db.models.QuerySet.distinct` and the general rule is the same:
353normally you won't want extra columns playing a part in the result, so clear
354out the ordering, or at least make sure it's restricted only to those fields
355you also select in a ``values()`` call.
356
357.. note::
358    You might reasonably ask why Django doesn't remove the extraneous columns
359    for you. The main reason is consistency with ``distinct()`` and other
360    places: Django **never** removes ordering constraints that you have
361    specified (and we can't change those other methods' behavior, as that
362    would violate our :doc:`/misc/api-stability` policy).
363
364Aggregating annotations
365-----------------------
366
367You can also generate an aggregate on the result of an annotation. When you
368define an ``aggregate()`` clause, the aggregates you provide can reference
369any alias defined as part of an ``annotate()`` clause in the query.
370
371For example, if you wanted to calculate the average number of authors per
372book you first annotate the set of books with the author count, then
373aggregate that author count, referencing the annotation field::
374
375    >>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
376    {'num_authors__avg': 1.66}