PageRenderTime 31ms CodeModel.GetById 0ms RepoModel.GetById 0ms app.codeStats 0ms

/docs/topics/db/aggregation.txt

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