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:
>>> SomeModel.objects.aggregate(arr=ArrayAgg('somefield')) {'arr': [0, 1, 2]}
ArrayAgg
class ArrayAgg(expression, distinct=False, filter=None, ordering=(), **extra)
[source]
Returns a list of values, including nulls, concatenated into an array.
distinct
An optional boolean argument that determines if array values will be distinct. Defaults to False
.
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, **extra)
[source]
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)
[source]
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)
[source]
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)
[source]
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)
[source]
Returns the input values as a JSON
array. Requires PostgreSQL ≥ 9.5.
StringAgg
class StringAgg(expression, delimiter, distinct=False, filter=None, ordering=())
[source]
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
.
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 ArrayAgg.ordering
.
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)
[source]
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)
[source]
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)
[source]
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)
[source]
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)
[source]
Returns an int
of the number of input rows in which both expressions are not null.
RegrIntercept
class RegrIntercept(y, x, filter=None)
[source]
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)
[source]
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)
[source]
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)
[source]
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)
[source]
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)
[source]
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.
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}
© Django Software Foundation and individual contributors
Licensed under the BSD License.
https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/aggregates/