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:
>>> SomeModel.objects.aggregate(arr=ArrayAgg('somefield'))
{'arr': [0, 1, 2]}
General-purpose aggregation functions¶
ArrayAgg
¶
BitAnd
¶
BitOr
¶
BoolAnd
¶
BoolOr
¶
JSONBAgg
¶
StringAgg
¶
-
class
StringAgg
(expression, delimiter, distinct=False, filter=None)[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
.
-
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
¶
CovarPop
¶
-
class
CovarPop
(y, x, sample=False, filter=None)[source]¶ Returns the population covariance as a
float
, orNone
if there aren’t any matching rows.Has one optional argument:
-
sample
¶ By default
CovarPop
returns the general population covariance. However, ifsample=True
, the return value will be the sample population covariance.
-
RegrAvgX
¶
RegrAvgY
¶
RegrCount
¶
RegrIntercept
¶
RegrR2
¶
RegrSlope
¶
RegrSXX
¶
RegrSXY
¶
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}