========================================= 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 `_. .. 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 ` keyword argument and most also have the :ref:`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() ``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 `. .. 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") ``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") >>> article.publications.create(title="Science News") >>> from django.contrib.postgres.aggregates import StringAgg >>> Article.objects.annotate( ... publication_names=StringAgg( ... "publications__title", ... delimiter=", ", ... ordering="publications__title", ... ) ... ).values("headline", "publication_names") 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 `_): .. 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}