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.

Informacja

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

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

Common aggregate options

All aggregates have the filter keyword argument.

General-purpose aggregation functions

ArrayAgg

class ArrayAgg(expression, distinct=False, filter=None, **extra)[źródło]

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

distinct
New in Django 2.0:

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

BitAnd

class BitAnd(expression, filter=None, **extra)[źródło]

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)[źródło]

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)[źródło]

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)[źródło]

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)[źródło]

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

StringAgg

class StringAgg(expression, delimiter, distinct=False, filter=None)[źródło]

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

delimiter

Required argument. Needs to be a string.

distinct

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)[źródło]

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)[źródło]

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

Has one optional argument:

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)[źródło]

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)[źródło]

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)[źródło]

Returns an int of the number of input rows in which both expressions are not null.

RegrIntercept

class RegrIntercept(y, x, filter=None)[źródło]

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)[źródło]

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)[źródło]

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)[źródło]

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)[źródło]

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)[źródło]

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

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