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]}
ArrayAggArrayAgg(expression, distinct=False, filter=None, ordering=(), **extra)Returns a list of values, including nulls, concatenated into an array.
distinctAn optional boolean argument that determines if array values
will be distinct. Defaults to False.
orderingAn 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()
BitAndBitAnd(expression, filter=None, **extra)int of the bitwise AND of all non-null input values, or None if all values are null.
BitOrBitOr(expression, filter=None, **extra)int of the bitwise OR of all non-null input values, or None if all values are null.
BoolAndBoolAnd(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}
BoolOrBoolOr(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}
JSONBAggJSONBAgg(expressions, filter=None, **extra)JSON array.
StringAggStringAgg(expression, delimiter, distinct=False, filter=None, ordering=())Returns the input values concatenated into a string, separated by
the delimiter string.
delimiterRequired argument. Needs to be a string.
distinctAn optional boolean argument that determines if concatenated values
will be distinct. Defaults to False.
orderingAn 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 xThe 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.
CorrCorr(y, x, filter=None)float, or None if there aren't any matching rows.
CovarPopCovarPop(y, x, sample=False, filter=None)Returns the population covariance as a float, or None if there
aren't any matching rows.
包含一个可选参数：
sampleBy default CovarPop returns the general population covariance.
However, if sample=True, the return value will be the sample
population covariance.
RegrAvgXRegrAvgX(y, x, filter=None)sum(x)/N) as a float, or None if there aren't any matching rows.
RegrAvgYRegrAvgY(y, x, filter=None)sum(y)/N) as a float, or None if there aren't any matching rows.
RegrCountRegrCount(y, x, filter=None)int of the number of input rows in which both expressions are not null.
RegrInterceptRegrIntercept(y, x, filter=None)(x, y) pairs as a float, or None if there aren't any matching rows.
RegrR2RegrR2(y, x, filter=None)float, or None if there aren't any matching rows.
RegrSlopeRegrSlope(y, x, filter=None)(x, y) pairs as a float, or None if there aren't any matching rows.
RegrSXXRegrSXX(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.
RegrSXYRegrSXY(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.
RegrSYYRegrSYY(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}