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]}
ArrayAgg
ArrayAgg
(expression, distinct=False, filter=None, ordering=(), **extra)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.
举例:
'some_field'
'-some_field'
from django.db.models import F
F('some_field').desc()
BitAnd
BitAnd
(expression, filter=None, **extra)int
of the bitwise AND
of all non-null input values, or None
if all values are null.
BitOr
BitOr
(expression, filter=None, **extra)int
of the bitwise OR
of all non-null input values, or None
if all values are null.
BoolAnd
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
.
使用实例:
class Comment(models.Model):
body = models.TextField()
published = models.BooleanField()
rank = models.IntegerField()
>>> from django.db.models import BooleanField, Q
>>> from django.contrib.postgres.aggregates import BoolAnd
>>> Comment.objects.aggregate(booland=BoolAnd('published'))
{'booland': False}
>>> Comment.objects.aggregate(booland=BoolAnd(Q(rank__lt=100), output_field=BooleanField()))
{'booland': True}
BoolOr
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
.
使用实例:
class Comment(models.Model):
body = models.TextField()
published = models.BooleanField()
rank = models.IntegerField()
>>> from django.db.models import BooleanField, Q
>>> from django.contrib.postgres.aggregates import BoolOr
>>> Comment.objects.aggregate(boolor=BoolOr('published'))
{'boolor': True}
>>> Comment.objects.aggregate(boolor=BoolOr(Q(rank__gt=2), output_field=BooleanField()))
{'boolor': False}
JSONBAgg
JSONBAgg
(expressions, filter=None, **extra)JSON
array.
StringAgg
StringAgg
(expression, delimiter, distinct=False, filter=None, ordering=())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
Corr
(y, x, filter=None)float
, or None
if there aren't any matching rows.
CovarPop
CovarPop
(y, x, sample=False, filter=None)Returns the population covariance as a float
, or None
if there
aren't any matching rows.
包含一个可选参数:
sample
By default CovarPop
returns the general population covariance.
However, if sample=True
, the return value will be the sample
population covariance.
RegrAvgX
RegrAvgX
(y, x, filter=None)sum(x)/N
) as a float
, or None
if there aren't any matching rows.
RegrAvgY
RegrAvgY
(y, x, filter=None)sum(y)/N
) as a float
, or None
if there aren't any matching rows.
RegrCount
RegrCount
(y, x, filter=None)int
of the number of input rows in which both expressions are not null.
RegrIntercept
RegrIntercept
(y, x, filter=None)(x, y)
pairs as a float
, or None
if there aren't any matching rows.
RegrR2
RegrR2
(y, x, filter=None)float
, or None
if there aren't any matching rows.
RegrSlope
RegrSlope
(y, x, filter=None)(x, y)
pairs as a float
, or None
if there aren't any matching rows.
RegrSXX
RegrSXX
(y, x, filter=None)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
RegrSXY
(y, x, filter=None)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
RegrSYY
(y, x, filter=None)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}