django/docs/ref/contrib/postgres/aggregates.txt

=========================================
PostgreSQL specific aggregation functions
=========================================

.. module:: django.contrib.postgres.aggregates
   :synopsis: PostgreSQL specific aggregation functions

These functions are described in more detail in the `PostgreSQL docs
<https://www.postgresql.org/docs/current/static/functions-aggregate.html>`_.

.. note::

    All functions come without default aliases, so you must explicitly provide
    one. For example::

        >>> SomeModel.objects.aggregate(arr=ArrayAgg('somefield'))
        {'arr': [0, 1, 2]}

General-purpose aggregation functions
=====================================

``ArrayAgg``
------------

.. class:: ArrayAgg(expression, distinct=False, filter=None, **extra)

    Returns a list of values, including nulls, concatenated into an array.

    .. attribute:: distinct

        .. versionadded:: 2.0

        An optional boolean argument that determines if array values
        will be distinct. Defaults to ``False``.

``BitAnd``
----------

.. class:: BitAnd(expression, filter=None, **extra)

    Returns an ``int`` of the bitwise ``AND`` of all non-null input values, or
    ``None`` if all values are null.

``BitOr``
---------

.. class:: BitOr(expression, filter=None, **extra)

    Returns an ``int`` of the bitwise ``OR`` of all non-null input values, or
    ``None`` if all values are null.

``BoolAnd``
-----------

.. class:: BoolAnd(expression, filter=None, **extra)

    Returns ``True``, if all input values are true, ``None`` if all values are
    null or if there are no values, otherwise ``False`` .

``BoolOr``
----------

.. class:: BoolOr(expression, filter=None, **extra)

    Returns ``True`` if at least one input value is true, ``None`` if all
    values are null or if there are no values, otherwise ``False``.

``JSONBAgg``
------------

.. class:: JSONBAgg(expressions, filter=None, **extra)

    .. versionadded:: 1.11

    Returns the input values as a ``JSON`` array. Requires PostgreSQL ≥ 9.5.

``StringAgg``
-------------

.. class:: StringAgg(expression, delimiter, distinct=False, filter=None)

    Returns the input values concatenated into a string, separated by
    the ``delimiter`` string.

    .. attribute:: delimiter

        Required argument. Needs to be a string.

    .. attribute:: distinct

        .. versionadded:: 1.11

        An optional boolean argument that determines if concatenated values
        will be distinct. Defaults to ``False``.

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)

    Returns the correlation coefficient as a ``float``, or ``None`` if there
    aren't any matching rows.

``CovarPop``
------------

.. class:: CovarPop(y, x, sample=False, filter=None)

    Returns the population covariance as a ``float``, or ``None`` if there
    aren't any matching rows.

    Has one optional argument:

    .. attribute:: sample

        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)

    Returns the average of the independent variable (``sum(x)/N``) as a
    ``float``, or ``None`` if there aren't any matching rows.

``RegrAvgY``
------------

.. class:: RegrAvgY(y, x, filter=None)

    Returns the average of the dependent variable (``sum(y)/N``) as a
    ``float``, or ``None`` 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.

``RegrIntercept``
-----------------

.. class:: RegrIntercept(y, x, filter=None)

    Returns the y-intercept of the least-squares-fit linear equation determined
    by the ``(x, y)`` pairs as a ``float``, or ``None`` if there aren't any
    matching rows.

``RegrR2``
----------

.. class:: RegrR2(y, x, filter=None)

    Returns the square of the correlation coefficient as a ``float``, or
    ``None`` if there aren't any matching rows.

``RegrSlope``
-------------

.. class:: RegrSlope(y, x, filter=None)

    Returns the slope of the least-squares-fit linear equation determined
    by the ``(x, y)`` pairs as a ``float``, or ``None`` if there aren't any
    matching rows.

``RegrSXX``
-----------

.. class:: RegrSXX(y, x, filter=None)

    Returns ``sum(x^2) - sum(x)^2/N`` ("sum of squares" of the independent
    variable) as a ``float``, or ``None`` if there aren't any matching rows.

``RegrSXY``
-----------

.. class:: RegrSXY(y, x, filter=None)

    Returns ``sum(x*y) - sum(x) * sum(y)/N`` ("sum of products" of independent
    times dependent variable) as a ``float``, or ``None`` if there aren't any
    matching rows.

``RegrSYY``
-----------

.. class:: RegrSYY(y, x, filter=None)

    Returns ``sum(y^2) - sum(y)^2/N`` ("sum of squares" of the dependent
    variable)  as a ``float``, or ``None`` if there aren't any matching rows.

Usage examples
==============

We will use this example table::

    | FIELD1 | FIELD2 | FIELD3 |
    |--------|--------|--------|
    |    foo |      1 |     13 |
    |    bar |      2 | (null) |
    |   test |      3 |     13 |


Here's some examples of some of the general-purpose aggregation functions::

    >>> 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>`_)::

    >>> 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}