TEXT 27
Aggregation.txt Guest on 29th April 2021 06:46:10 AM
  1. ===========
  2. Aggregation
  3. ===========
  4.  
  5. .. currentmodule:: django.db.models
  6.  
  7. The topic guide on :doc:`Django's database-abstraction API </topics/db/queries>`
  8. described the way that you can use Django queries that create,
  9. retrieve, update and delete individual objects. However, sometimes you will
  10. need to retrieve values that are derived by summarizing or *aggregating* a
  11. collection of objects. This topic guide describes the ways that aggregate values
  12. can be generated and returned using Django queries.
  13.  
  14. Throughout this guide, we'll refer to the following models. These models are
  15. used to track the inventory for a series of online bookstores:
  16.  
  17. .. _queryset-model-example:
  18.  
  19. .. code-block:: python
  20.  
  21.     from django.db import models
  22.  
  23.     class Author(models.Model):
  24.         name = models.CharField(max_length=100)
  25.         age = models.IntegerField()
  26.  
  27.     class Publisher(models.Model):
  28.         name = models.CharField(max_length=300)
  29.         num_awards = models.IntegerField()
  30.  
  31.     class Book(models.Model):
  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.         registered_users = models.PositiveIntegerField()
  44.  
  45. Cheat sheet
  46. ===========
  47.  
  48. In a hurry? Here's how to do common aggregate queries, assuming the models above:
  49.  
  50. .. code-block:: python
  51.  
  52.     # Total number of books.
  53.     >>> Book.objects.count()
  54.     2452
  55.  
  56.     # Total number of books with publisher=BaloneyPress
  57.     >>> Book.objects.filter(publisher__name='BaloneyPress').count()
  58.     73
  59.  
  60.     # Average price across all books.
  61.     >>> from django.db.models import Avg
  62.     >>> Book.objects.all().aggregate(Avg('price'))
  63.     {'price__avg': 34.35}
  64.  
  65.     # Max price across all books.
  66.     >>> from django.db.models import Max
  67.     >>> Book.objects.all().aggregate(Max('price'))
  68.     {'price__max': Decimal('81.20')}
  69.  
  70.     # Cost per page
  71.     >>> Book.objects.all().aggregate(
  72.     ...    price_per_page=Sum(F('price')/F('pages'), output_field=FloatField()))
  73.     {'price_per_page': 0.4470664529184653}
  74.  
  75.     # All the following queries involve traversing the Book<->Publisher
  76.     # many-to-many relationship backward
  77.  
  78.     # Each publisher, each with a count of books as a "num_books" attribute.
  79.     >>> from django.db.models import Count
  80.     >>> pubs = Publisher.objects.annotate(num_books=Count('book'))
  81.     >>> pubs
  82.     [<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
  83.     >>> pubs[0].num_books
  84.     73
  85.  
  86.     # The top 5 publishers, in order by number of books.
  87.     >>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
  88.     >>> pubs[0].num_books
  89.     1323
  90.  
  91. Generating aggregates over a QuerySet
  92. =====================================
  93.  
  94. Django provides two ways to generate aggregates. The first way is to generate
  95. summary values over an entire ``QuerySet``. For example, say you wanted to
  96. calculate the average price of all books available for sale. Django's query
  97. syntax provides a means for describing the set of all books::
  98.  
  99.     >>> Book.objects.all()
  100.  
  101. What we need is a way to calculate summary values over the objects that
  102. belong to this ``QuerySet``. This is done by appending an ``aggregate()``
  103. clause onto the ``QuerySet``::
  104.  
  105.     >>> from django.db.models import Avg
  106.     >>> Book.objects.all().aggregate(Avg('price'))
  107.     {'price__avg': 34.35}
  108.  
  109. The ``all()`` is redundant in this example, so this could be simplified to::
  110.  
  111.     >>> Book.objects.aggregate(Avg('price'))
  112.     {'price__avg': 34.35}
  113.  
  114. The argument to the ``aggregate()`` clause describes the aggregate value that
  115. we want to compute - in this case, the average of the ``price`` field on the
  116. ``Book`` model. A list of the aggregate functions that are available can be
  117. found in the :ref:`QuerySet reference <aggregation-functions>`.
  118.  
  119. ``aggregate()`` is a terminal clause for a ``QuerySet`` that, when invoked,
  120. returns a dictionary of name-value pairs. The name is an identifier for the
  121. aggregate value; the value is the computed aggregate. The name is
  122. automatically generated from the name of the field and the aggregate function.
  123. If you want to manually specify a name for the aggregate value, you can do so
  124. by providing that name when you specify the aggregate clause::
  125.  
  126.     >>> Book.objects.aggregate(average_price=Avg('price'))
  127.     {'average_price': 34.35}
  128.  
  129. If you want to generate more than one aggregate, you just add another
  130. argument to the ``aggregate()`` clause. So, if we also wanted to know
  131. the maximum and minimum price of all books, we would issue the query::
  132.  
  133.     >>> from django.db.models import Avg, Max, Min
  134.     >>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
  135.     {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}
  136.  
  137. Generating aggregates for each item in a QuerySet
  138. =================================================
  139.  
  140. The second way to generate summary values is to generate an independent
  141. summary for each object in a ``QuerySet``. For example, if you are retrieving
  142. a list of books, you may want to know how many authors contributed to
  143. each book. Each Book has a many-to-many relationship with the Author; we
  144. want to summarize this relationship for each book in the ``QuerySet``.
  145.  
  146. Per-object summaries can be generated using the ``annotate()`` clause.
  147. When an ``annotate()`` clause is specified, each object in the ``QuerySet``
  148. will be annotated with the specified values.
  149.  
  150. The syntax for these annotations is identical to that used for the
  151. ``aggregate()`` clause. Each argument to ``annotate()`` describes an
  152. aggregate that is to be calculated. For example, to annotate books with
  153. the number of authors:
  154.  
  155. .. code-block:: python
  156.  
  157.     # Build an annotated queryset
  158.     >>> from django.db.models import Count
  159.     >>> q = Book.objects.annotate(Count('authors'))
  160.     # Interrogate the first object in the queryset
  161.     >>> q[0]
  162.     <Book: The Definitive Guide to Django>
  163.     >>> q[0].authors__count
  164.     2
  165.     # Interrogate the second object in the queryset
  166.     >>> q[1]
  167.     <Book: Practical Django Projects>
  168.     >>> q[1].authors__count
  169.     1
  170.  
  171. As with ``aggregate()``, the name for the annotation is automatically derived
  172. from the name of the aggregate function and the name of the field being
  173. aggregated. You can override this default name by providing an alias when you
  174. specify the annotation::
  175.  
  176.     >>> q = Book.objects.annotate(num_authors=Count('authors'))
  177.     >>> q[0].num_authors
  178.     2
  179.     >>> q[1].num_authors
  180.     1
  181.  
  182. Unlike ``aggregate()``, ``annotate()`` is *not* a terminal clause. The output
  183. of the ``annotate()`` clause is a ``QuerySet``; this ``QuerySet`` can be
  184. modified using any other ``QuerySet`` operation, including ``filter()``,
  185. ``order_by()``, or even additional calls to ``annotate()``.
  186.  
  187. .. admonition:: If in doubt, inspect the SQL query!
  188.  
  189.     In order to understand what happens in your query, consider inspecting the
  190.     ``query`` property of your ``QuerySet``.
  191.  
  192.     For instance, combining multiple aggregations with ``annotate()`` will
  193.     yield the wrong results, as `multiple tables are cross joined`_,
  194.     resulting in duplicate row aggregations.
  195.  
  196. .. _multiple tables are cross joined: https://code.djangoproject.com/ticket/10060
  197.  
  198. Joins and aggregates
  199. ====================
  200.  
  201. So far, we have dealt with aggregates over fields that belong to the
  202. model being queried. However, sometimes the value you want to aggregate
  203. will belong to a model that is related to the model you are querying.
  204.  
  205. When specifying the field to be aggregated in an aggregate function, Django
  206. will allow you to use the same :ref:`double underscore notation
  207. <field-lookups-intro>` that is used when referring to related fields in
  208. filters. Django will then handle any table joins that are required to retrieve
  209. and aggregate the related value.
  210.  
  211. For example, to find the price range of books offered in each store,
  212. you could use the annotation::
  213.  
  214.     >>> from django.db.models import Max, Min
  215.     >>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))
  216.  
  217. This tells Django to retrieve the ``Store`` model, join (through the
  218. many-to-many relationship) with the ``Book`` model, and aggregate on the
  219. price field of the book model to produce a minimum and maximum value.
  220.  
  221. The same rules apply to the ``aggregate()`` clause. If you wanted to
  222. know the lowest and highest price of any book that is available for sale
  223. in a store, you could use the aggregate::
  224.  
  225.     >>> Store.objects.aggregate(min_price=Min('books__price'), max_price=Max('books__price'))
  226.  
  227. Join chains can be as deep as you require. For example, to extract the
  228. age of the youngest author of any book available for sale, you could
  229. issue the query::
  230.  
  231.     >>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))
  232.  
  233. Following relationships backwards
  234. ---------------------------------
  235.  
  236. In a way similar to :ref:`lookups-that-span-relationships`, aggregations and
  237. annotations on fields of models or models that are related to the one you are
  238. querying can include traversing "reverse" relationships. The lowercase name
  239. of related models and double-underscores are used here too.
  240.  
  241. For example, we can ask for all publishers, annotated with their respective
  242. total book stock counters (note how we use ``'book'`` to specify the
  243. ``Publisher`` -> ``Book`` reverse foreign key hop)::
  244.  
  245.     >>> from django.db.models import Count, Min, Sum, Avg
  246.     >>> Publisher.objects.annotate(Count('book'))
  247.  
  248. (Every ``Publisher`` in the resulting ``QuerySet`` will have an extra attribute
  249. called ``book__count``.)
  250.  
  251. We can also ask for the oldest book of any of those managed by every publisher::
  252.  
  253.     >>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))
  254.  
  255. (The resulting dictionary will have a key called ``'oldest_pubdate'``. If no
  256. such alias were specified, it would be the rather long ``'book__pubdate__min'``.)
  257.  
  258. This doesn't apply just to foreign keys. It also works with many-to-many
  259. relations. For example, we can ask for every author, annotated with the total
  260. number of pages considering all the books the author has (co-)authored (note how we
  261. use ``'book'`` to specify the ``Author`` -> ``Book`` reverse many-to-many hop)::
  262.  
  263.     >>> Author.objects.annotate(total_pages=Sum('book__pages'))
  264.  
  265. (Every ``Author`` in the resulting ``QuerySet`` will have an extra attribute
  266. called ``total_pages``. If no such alias were specified, it would be the rather
  267. long ``book__pages__sum``.)
  268.  
  269. Or ask for the average rating of all the books written by author(s) we have on
  270. file::
  271.  
  272.     >>> Author.objects.aggregate(average_rating=Avg('book__rating'))
  273.  
  274. (The resulting dictionary will have a key called ``'average__rating'``. If no
  275. such alias were specified, it would be the rather long ``'book__rating__avg'``.)
  276.  
  277. Aggregations and other QuerySet clauses
  278. =======================================
  279.  
  280. ``filter()`` and ``exclude()``
  281. ------------------------------
  282.  
  283. Aggregates can also participate in filters. Any ``filter()`` (or
  284. ``exclude()``) applied to normal model fields will have the effect of
  285. constraining the objects that are considered for aggregation.
  286.  
  287. When used with an ``annotate()`` clause, a filter has the effect of
  288. constraining the objects for which an annotation is calculated. For example,
  289. you can generate an annotated list of all books that have a title starting
  290. with "Django" using the query::
  291.  
  292.     >>> from django.db.models import Count, Avg
  293.     >>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))
  294.  
  295. When used with an ``aggregate()`` clause, a filter has the effect of
  296. constraining the objects over which the aggregate is calculated.
  297. For example, you can generate the average price of all books with a
  298. title that starts with "Django" using the query::
  299.  
  300.     >>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))
  301.  
  302. Filtering on annotations
  303. ~~~~~~~~~~~~~~~~~~~~~~~~
  304.  
  305. Annotated values can also be filtered. The alias for the annotation can be
  306. used in ``filter()`` and ``exclude()`` clauses in the same way as any other
  307. model field.
  308.  
  309. For example, to generate a list of books that have more than one author,
  310. you can issue the query::
  311.  
  312.     >>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)
  313.  
  314. This query generates an annotated result set, and then generates a filter
  315. based upon that annotation.
  316.  
  317. Order of ``annotate()`` and ``filter()`` clauses
  318. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  319.  
  320. When developing a complex query that involves both ``annotate()`` and
  321. ``filter()`` clauses, particular attention should be paid to the order
  322. in which the clauses are applied to the ``QuerySet``.
  323.  
  324. When an ``annotate()`` clause is applied to a query, the annotation is
  325. computed over the state of the query up to the point where the annotation
  326. is requested. The practical implication of this is that ``filter()`` and
  327. ``annotate()`` are not commutative operations -- that is, there is a
  328. difference between the query::
  329.  
  330.     >>> Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0)
  331.  
  332. and the query::
  333.  
  334.     >>> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
  335.  
  336. Both queries will return a list of publishers that have at least one good
  337. book (i.e., a book with a rating exceeding 3.0). However, the annotation in
  338. the first query will provide the total number of all books published by the
  339. publisher; the second query will only include good books in the annotated
  340. count. In the first query, the annotation precedes the filter, so the
  341. filter has no effect on the annotation. In the second query, the filter
  342. precedes the annotation, and as a result, the filter constrains the objects
  343. considered when calculating the annotation.
  344.  
  345. ``order_by()``
  346. --------------
  347.  
  348. Annotations can be used as a basis for ordering. When you
  349. define an ``order_by()`` clause, the aggregates you provide can reference
  350. any alias defined as part of an ``annotate()`` clause in the query.
  351.  
  352. For example, to order a ``QuerySet`` of books by the number of authors
  353. that have contributed to the book, you could use the following query::
  354.  
  355.     >>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
  356.  
  357. ``values()``
  358. ------------
  359.  
  360. Ordinarily, annotations are generated on a per-object basis - an annotated
  361. ``QuerySet`` will return one result for each object in the original
  362. ``QuerySet``. However, when a ``values()`` clause is used to constrain the
  363. columns that are returned in the result set, the method for evaluating
  364. annotations is slightly different. Instead of returning an annotated result
  365. for each result in the original ``QuerySet``, the original results are
  366. grouped according to the unique combinations of the fields specified in the
  367. ``values()`` clause. An annotation is then provided for each unique group;
  368. the annotation is computed over all members of the group.
  369.  
  370. For example, consider an author query that attempts to find out the average
  371. rating of books written by each author:
  372.  
  373.     >>> Author.objects.annotate(average_rating=Avg('book__rating'))
  374.  
  375. This will return one result for each author in the database, annotated with
  376. their average book rating.
  377.  
  378. However, the result will be slightly different if you use a ``values()`` clause::
  379.  
  380.     >>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))
  381.  
  382. In this example, the authors will be grouped by name, so you will only get
  383. an annotated result for each *unique* author name. This means if you have
  384. two authors with the same name, their results will be merged into a single
  385. result in the output of the query; the average will be computed as the
  386. average over the books written by both authors.
  387.  
  388. Order of ``annotate()`` and ``values()`` clauses
  389. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  390.  
  391. As with the ``filter()`` clause, the order in which ``annotate()`` and
  392. ``values()`` clauses are applied to a query is significant. If the
  393. ``values()`` clause precedes the ``annotate()``, the annotation will be
  394. computed using the grouping described by the ``values()`` clause.
  395.  
  396. However, if the ``annotate()`` clause precedes the ``values()`` clause,
  397. the annotations will be generated over the entire query set. In this case,
  398. the ``values()`` clause only constrains the fields that are generated on
  399. output.
  400.  
  401. For example, if we reverse the order of the ``values()`` and ``annotate()``
  402. clause from our previous example::
  403.  
  404.     >>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')
  405.  
  406. This will now yield one unique result for each author; however, only
  407. the author's name and the ``average_rating`` annotation will be returned
  408. in the output data.
  409.  
  410. You should also note that ``average_rating`` has been explicitly included
  411. in the list of values to be returned. This is required because of the
  412. ordering of the ``values()`` and ``annotate()`` clause.
  413.  
  414. If the ``values()`` clause precedes the ``annotate()`` clause, any annotations
  415. will be automatically added to the result set. However, if the ``values()``
  416. clause is applied after the ``annotate()`` clause, you need to explicitly
  417. include the aggregate column.
  418.  
  419. Interaction with default ordering or ``order_by()``
  420. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  421.  
  422. Fields that are mentioned in the ``order_by()`` part of a queryset (or which
  423. are used in the default ordering on a model) are used when selecting the
  424. output data, even if they are not otherwise specified in the ``values()``
  425. call. These extra fields are used to group "like" results together and they
  426. can make otherwise identical result rows appear to be separate. This shows up,
  427. particularly, when counting things.
  428.  
  429. By way of example, suppose you have a model like this::
  430.  
  431.     from django.db import models
  432.  
  433.     class Item(models.Model):
  434.         name = models.CharField(max_length=10)
  435.         data = models.IntegerField()
  436.  
  437.         class Meta:
  438.             ordering = ["name"]
  439.  
  440. The important part here is the default ordering on the ``name`` field. If you
  441. want to count how many times each distinct ``data`` value appears, you might
  442. try this::
  443.  
  444.     # Warning: not quite correct!
  445.     Item.objects.values("data").annotate(Count("id"))
  446.  
  447. ...which will group the ``Item`` objects by their common ``data`` values and
  448. then count the number of ``id`` values in each group. Except that it won't
  449. quite work. The default ordering by ``name`` will also play a part in the
  450. grouping, so this query will group by distinct ``(data, name)`` pairs, which
  451. isn't what you want. Instead, you should construct this queryset::
  452.  
  453.     Item.objects.values("data").annotate(Count("id")).order_by()
  454.  
  455. ...clearing any ordering in the query. You could also order by, say, ``data``
  456. without any harmful effects, since that is already playing a role in the
  457. query.
  458.  
  459. This behavior is the same as that noted in the queryset documentation for
  460. :meth:`~django.db.models.query.QuerySet.distinct` and the general rule is the
  461. same: normally you won't want extra columns playing a part in the result, so
  462. clear out the ordering, or at least make sure it's restricted only to those
  463. fields you also select in a ``values()`` call.
  464.  
  465. .. note::
  466.     You might reasonably ask why Django doesn't remove the extraneous columns
  467.     for you. The main reason is consistency with ``distinct()`` and other
  468.     places: Django **never** removes ordering constraints that you have
  469.     specified (and we can't change those other methods' behavior, as that
  470.     would violate our :doc:`/misc/api-stability` policy).
  471.  
  472. Aggregating annotations
  473. -----------------------
  474.  
  475. You can also generate an aggregate on the result of an annotation. When you
  476. define an ``aggregate()`` clause, the aggregates you provide can reference
  477. any alias defined as part of an ``annotate()`` clause in the query.
  478.  
  479. For example, if you wanted to calculate the average number of authors per
  480. book you first annotate the set of books with the author count, then
  481. aggregate that author count, referencing the annotation field::
  482.  
  483.     >>> from django.db.models import Count, Avg
  484.     >>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
  485.     {'num_authors__avg': 1.66}

Paste-bin is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.