PostgreSQL specific aggregation functions¶
These functions 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]}
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)[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}