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.
注解
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)[源代码]
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)[源代码]
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)[源代码]
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.
-
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)[源代码]
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:
-
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):
>>> 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}