docs/ref/contrib/postgres/aggregates.txt
=========================================
PostgreSQL specific aggregation functions
=========================================
.. module:: django.contrib.postgres.aggregates
:synopsis: PostgreSQL specific aggregation functions
These functions are available from the ``django.contrib.postgres.aggregates``
module. They are described in more detail in the `PostgreSQL docs
<https://www.postgresql.org/docs/current/functions-aggregate.html>`_.
.. note::
All functions come without default aliases, so you must explicitly provide
one. For example:
.. code-block:: pycon
>>> SomeModel.objects.aggregate(arr=ArrayAgg("somefield"))
{'arr': [0, 1, 2]}
.. admonition:: Common aggregate options
All aggregates have the :ref:`filter <aggregate-filter>` keyword argument
and most also have the :ref:`default <aggregate-default>` keyword argument.
General-purpose aggregation functions
=====================================
``ArrayAgg``
------------
.. class:: ArrayAgg(expression, distinct=False, filter=None, default=None, ordering=(), **extra)
Returns a list of values, including nulls, concatenated into an array, or
``default`` if there are no values.
.. attribute:: distinct
An optional boolean argument that determines if array values
will be distinct. Defaults to ``False``.
.. attribute:: ordering
An optional string of a field name (with an optional ``"-"`` prefix
which indicates descending order) or an expression (or a tuple or list
of strings and/or expressions) that specifies the ordering of the
elements in the result list.
Examples::
"some_field"
"-some_field"
from django.db.models import F
F("some_field").desc()
.. versionchanged:: 5.0
In older versions, if there are no rows and ``default`` is not
provided, ``ArrayAgg`` returned an empty list instead of ``None``. If
you need it, explicitly set ``default`` to ``Value([])``.
``BitAnd``
----------
.. class:: BitAnd(expression, filter=None, default=None, **extra)
Returns an ``int`` of the bitwise ``AND`` of all non-null input values, or
``default`` if all values are null.
``BitOr``
---------
.. class:: BitOr(expression, filter=None, default=None, **extra)
Returns an ``int`` of the bitwise ``OR`` of all non-null input values, or
``default`` if all values are null.
``BitXor``
----------
.. class:: BitXor(expression, filter=None, default=None, **extra)
Returns an ``int`` of the bitwise ``XOR`` of all non-null input values, or
``default`` if all values are null. It requires PostgreSQL 14+.
``BoolAnd``
-----------
.. class:: BoolAnd(expression, filter=None, default=None, **extra)
Returns ``True``, if all input values are true, ``default`` if all values
are null or if there are no values, otherwise ``False``.
Usage example::
class Comment(models.Model):
body = models.TextField()
published = models.BooleanField()
rank = models.IntegerField()
.. code-block:: pycon
>>> from django.db.models import Q
>>> from django.contrib.postgres.aggregates import BoolAnd
>>> Comment.objects.aggregate(booland=BoolAnd("published"))
{'booland': False}
>>> Comment.objects.aggregate(booland=BoolAnd(Q(rank__lt=100)))
{'booland': True}
``BoolOr``
----------
.. class:: BoolOr(expression, filter=None, default=None, **extra)
Returns ``True`` if at least one input value is true, ``default`` if all
values are null or if there are no values, otherwise ``False``.
Usage example::
class Comment(models.Model):
body = models.TextField()
published = models.BooleanField()
rank = models.IntegerField()
.. code-block:: pycon
>>> from django.db.models import Q
>>> from django.contrib.postgres.aggregates import BoolOr
>>> Comment.objects.aggregate(boolor=BoolOr("published"))
{'boolor': True}
>>> Comment.objects.aggregate(boolor=BoolOr(Q(rank__gt=2)))
{'boolor': False}
``JSONBAgg``
------------
.. class:: JSONBAgg(expressions, distinct=False, filter=None, default=None, ordering=(), **extra)
Returns the input values as a ``JSON`` array, or ``default`` if there are
no values. You can query the result using :lookup:`key and index lookups
<jsonfield.key>`.
.. attribute:: distinct
An optional boolean argument that determines if array values will be
distinct. Defaults to ``False``.
.. attribute:: ordering
An optional string of a field name (with an optional ``"-"`` prefix
which indicates descending order) or an expression (or a tuple or list
of strings and/or expressions) that specifies the ordering of the
elements in the result list.
Examples are the same as for :attr:`ArrayAgg.ordering`.
Usage example::
class Room(models.Model):
number = models.IntegerField(unique=True)
class HotelReservation(models.Model):
room = models.ForeignKey("Room", on_delete=models.CASCADE)
start = models.DateTimeField()
end = models.DateTimeField()
requirements = models.JSONField(blank=True, null=True)
.. code-block:: pycon
>>> from django.contrib.postgres.aggregates import JSONBAgg
>>> Room.objects.annotate(
... requirements=JSONBAgg(
... "hotelreservation__requirements",
... ordering="-hotelreservation__start",
... )
... ).filter(requirements__0__sea_view=True).values("number", "requirements")
<QuerySet [{'number': 102, 'requirements': [
{'parking': False, 'sea_view': True, 'double_bed': False},
{'parking': True, 'double_bed': True}
]}]>
.. versionchanged:: 5.0
In older versions, if there are no rows and ``default`` is not
provided, ``JSONBAgg`` returned an empty list instead of ``None``. If
you need it, explicitly set ``default`` to ``Value([])``.
``StringAgg``
-------------
.. class:: StringAgg(expression, delimiter, distinct=False, filter=None, default=None, ordering=())
Returns the input values concatenated into a string, separated by
the ``delimiter`` string, or ``default`` if there are no values.
.. attribute:: delimiter
Required argument. Needs to be a string.
.. attribute:: distinct
An optional boolean argument that determines if concatenated values
will be distinct. Defaults to ``False``.
.. attribute:: ordering
An optional string of a field name (with an optional ``"-"`` prefix
which indicates descending order) or an expression (or a tuple or list
of strings and/or expressions) that specifies the ordering of the
elements in the result string.
Examples are the same as for :attr:`ArrayAgg.ordering`.
Usage example::
class Publication(models.Model):
title = models.CharField(max_length=30)
class Article(models.Model):
headline = models.CharField(max_length=100)
publications = models.ManyToManyField(Publication)
.. code-block:: pycon
>>> article = Article.objects.create(headline="NASA uses Python")
>>> article.publications.create(title="The Python Journal")
<Publication: Publication object (1)>
>>> article.publications.create(title="Science News")
<Publication: Publication object (2)>
>>> from django.contrib.postgres.aggregates import StringAgg
>>> Article.objects.annotate(
... publication_names=StringAgg(
... "publications__title",
... delimiter=", ",
... ordering="publications__title",
... )
... ).values("headline", "publication_names")
<QuerySet [{
'headline': 'NASA uses Python', 'publication_names': 'Science News, The Python Journal'
}]>
.. versionchanged:: 5.0
In older versions, if there are no rows and ``default`` is not
provided, ``StringAgg`` returned an empty string instead of ``None``.
If you need it, explicitly set ``default`` to ``Value("")``.
Aggregate functions for statistics
==================================
``y`` and ``x``
---------------
The arguments ``y`` and ``x`` for all these functions can be the name of a
field or an expression returning a numeric data. Both are required.
``Corr``
--------
.. class:: Corr(y, x, filter=None, default=None)
Returns the correlation coefficient as a ``float``, or ``default`` if there
aren't any matching rows.
``CovarPop``
------------
.. class:: CovarPop(y, x, sample=False, filter=None, default=None)
Returns the population covariance as a ``float``, or ``default`` if there
aren't any matching rows.
.. attribute:: sample
Optional. By default ``CovarPop`` returns the general population
covariance. However, if ``sample=True``, the return value will be the
sample population covariance.
``RegrAvgX``
------------
.. class:: RegrAvgX(y, x, filter=None, default=None)
Returns the average of the independent variable (``sum(x)/N``) as a
``float``, or ``default`` if there aren't any matching rows.
``RegrAvgY``
------------
.. class:: RegrAvgY(y, x, filter=None, default=None)
Returns the average of the dependent variable (``sum(y)/N``) as a
``float``, or ``default`` if there aren't any matching rows.
``RegrCount``
-------------
.. class:: RegrCount(y, x, filter=None)
Returns an ``int`` of the number of input rows in which both expressions
are not null.
.. note::
The ``default`` argument is not supported.
``RegrIntercept``
-----------------
.. class:: RegrIntercept(y, x, filter=None, default=None)
Returns the y-intercept of the least-squares-fit linear equation determined
by the ``(x, y)`` pairs as a ``float``, or ``default`` if there aren't any
matching rows.
``RegrR2``
----------
.. class:: RegrR2(y, x, filter=None, default=None)
Returns the square of the correlation coefficient as a ``float``, or
``default`` if there aren't any matching rows.
``RegrSlope``
-------------
.. class:: RegrSlope(y, x, filter=None, default=None)
Returns the slope of the least-squares-fit linear equation determined
by the ``(x, y)`` pairs as a ``float``, or ``default`` if there aren't any
matching rows.
``RegrSXX``
-----------
.. class:: RegrSXX(y, x, filter=None, default=None)
Returns ``sum(x^2) - sum(x)^2/N`` ("sum of squares" of the independent
variable) as a ``float``, or ``default`` if there aren't any matching rows.
``RegrSXY``
-----------
.. class:: RegrSXY(y, x, filter=None, default=None)
Returns ``sum(x*y) - sum(x) * sum(y)/N`` ("sum of products" of independent
times dependent variable) as a ``float``, or ``default`` if there aren't
any matching rows.
``RegrSYY``
-----------
.. class:: RegrSYY(y, x, filter=None, default=None)
Returns ``sum(y^2) - sum(y)^2/N`` ("sum of squares" of the dependent
variable) as a ``float``, or ``default`` if there aren't any matching rows.
Usage examples
==============
We will use this example table:
.. code-block:: text
| FIELD1 | FIELD2 | FIELD3 |
|--------|--------|--------|
| foo | 1 | 13 |
| bar | 2 | (null) |
| test | 3 | 13 |
Here's some examples of some of the general-purpose aggregation functions:
.. code-block:: pycon
>>> TestModel.objects.aggregate(result=StringAgg("field1", delimiter=";"))
{'result': 'foo;bar;test'}
>>> TestModel.objects.aggregate(result=ArrayAgg("field2"))
{'result': [1, 2, 3]}
>>> TestModel.objects.aggregate(result=ArrayAgg("field1"))
{'result': ['foo', 'bar', 'test']}
The next example shows the usage of statistical aggregate functions. The
underlying math will be not described (you can read about this, for example, at
`wikipedia <https://en.wikipedia.org/wiki/Regression_analysis>`_):
.. code-block:: pycon
>>> TestModel.objects.aggregate(count=RegrCount(y="field3", x="field2"))
{'count': 2}
>>> TestModel.objects.aggregate(
... avgx=RegrAvgX(y="field3", x="field2"), avgy=RegrAvgY(y="field3", x="field2")
... )
{'avgx': 2, 'avgy': 13}