djangodocs

Django 6.0
Português (Brasil)
Documentation contents

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.

General-purpose aggregation functions

ArrayAgg

class ArrayAgg(expression, distinct=False, filter=None, default=None, order_by=(), **extra)

Returns a list of values, including nulls, concatenated into an array, or default if there are no values.

distinct

An optional boolean argument that determines if array values will be distinct. Defaults to False.

order_by

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.

Exemplos:

Code
from django.db.models import F

ArrayAgg("a_field", order_by="-some_field")
ArrayAgg("a_field", order_by=F("some_field").desc())

BitAnd

class BitAnd(expression, filter=None, default=None, **extra)

Returns an int of the bitwise AND of all non-null input values, or default if all values are null.

BitOr

class BitOr(expression, filter=None, default=None, **extra)

Returns an int of the bitwise OR of all non-null input values, or default if all values are null.

BitXor

class BitXor(expression, filter=None, default=None, **extra)

Returns an int of the bitwise XOR of all non-null input values, or default if all values are null. It requires PostgreSQL 14+.

BoolAnd

class BoolAnd(expression, filter=None, default=None, **extra)

Returns True, if all input values are true, default if all values are null or if there are no values, otherwise False.

Usage example:

Code
class Comment(models.Model):
    body = models.TextField()
    published = models.BooleanField()
    rank = models.IntegerField()
Python console
>>> from django.db.models import 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)))
{'booland': True}

BoolOr

class BoolOr(expression, filter=None, default=None, **extra)

Returns True if at least one input value is true, default if all values are null or if there are no values, otherwise False.

Usage example:

Code
class Comment(models.Model):
    body = models.TextField()
    published = models.BooleanField()
    rank = models.IntegerField()
Python console
>>> from django.db.models import 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)))
{'boolor': False}

JSONBAgg

class JSONBAgg(expressions, distinct=False, filter=None, default=None, order_by=(), **extra)

Returns the input values as a JSON array, or default if there are no values. You can query the result using key and index lookups.

distinct

An optional boolean argument that determines if array values will be distinct. Defaults to False.

order_by

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.

Examples are the same as for ArrayAgg.order_by.

Usage example:

Code
class Room(models.Model):
    number = models.IntegerField(unique=True)


class HotelReservation(models.Model):
    room = models.ForeignKey("Room", on_delete=models.CASCADE)
    start = models.DateTimeField()
    end = models.DateTimeField()
    requirements = models.JSONField(blank=True, null=True)
Python console
>>> from django.contrib.postgres.aggregates import JSONBAgg
>>> Room.objects.annotate(
...     requirements=JSONBAgg(
...         "hotelreservation__requirements",
...         order_by="-hotelreservation__start",
...     )
... ).filter(requirements__0__sea_view=True).values("number", "requirements")
<QuerySet [{'number': 102, 'requirements': [
    {'parking': False, 'sea_view': True, 'double_bed': False},
    {'parking': True, 'double_bed': True}
]}]>

StringAgg

class StringAgg(expression, delimiter, distinct=False, filter=None, default=None, order_by=())

Returns the input values concatenated into a string, separated by the delimiter string, or default if there are no values.

delimiter

Required argument. A string, Value, or expression representing the string for separating values. For example, Value(",").

distinct

An optional boolean argument that determines if concatenated values will be distinct. Defaults to False.

order_by

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.order_by.

Usage example:

Code
class Publication(models.Model):
    title = models.CharField(max_length=30)


class Article(models.Model):
    headline = models.CharField(max_length=100)
    publications = models.ManyToManyField(Publication)
Python console
>>> article = Article.objects.create(headline="NASA uses Python")
>>> article.publications.create(title="The Python Journal")
<Publication: Publication object (1)>
>>> article.publications.create(title="Science News")
<Publication: Publication object (2)>
>>> from django.contrib.postgres.aggregates import StringAgg
>>> Article.objects.annotate(
...     publication_names=StringAgg(
...         "publications__title",
...         delimiter=", ",
...         order_by="publications__title",
...     )
... ).values("headline", "publication_names")
<QuerySet [{
    'headline': 'NASA uses Python', 'publication_names': 'Science News, The Python Journal'
}]>

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, default=None)

Returns the correlation coefficient as a float, or default if there aren’t any matching rows.

CovarPop

class CovarPop(y, x, sample=False, filter=None, default=None)

Returns the population covariance as a float, or default if there aren’t any matching rows.

sample

Optional. 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, default=None)

Returns the average of the independent variable (sum(x)/N) as a float, or default if there aren’t any matching rows.

RegrAvgY

class RegrAvgY(y, x, filter=None, default=None)

Returns the average of the dependent variable (sum(y)/N) as a float, or default 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, default=None)

Returns the y-intercept of the least-squares-fit linear equation determined by the (x, y) pairs as a float, or default if there aren’t any matching rows.

RegrR2

class RegrR2(y, x, filter=None, default=None)

Returns the square of the correlation coefficient as a float, or default if there aren’t any matching rows.

RegrSlope

class RegrSlope(y, x, filter=None, default=None)

Returns the slope of the least-squares-fit linear equation determined by the (x, y) pairs as a float, or default if there aren’t any matching rows.

RegrSXX

class RegrSXX(y, x, filter=None, default=None)

Returns sum(x^2) - sum(x)^2/N (“sum of squares” of the independent variable) as a float, or default if there aren’t any matching rows.

RegrSXY

class RegrSXY(y, x, filter=None, default=None)

Returns sum(x*y) - sum(x) * sum(y)/N (“sum of products” of independent times dependent variable) as a float, or default if there aren’t any matching rows.

RegrSYY

class RegrSYY(y, x, filter=None, default=None)

Returns sum(y^2) - sum(y)^2/N (“sum of squares” of the dependent variable) as a float, or default if there aren’t any matching rows.

Usage examples

We will use this example table:

Text
| FIELD1 | FIELD2 | FIELD3 |
|--------|--------|--------|
|    foo |      1 |     13 |
|    bar |      2 | (null) |
|   test |      3 |     13 |

Here’s some examples of some of the general-purpose aggregation functions:

Python console
>>> 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):

Python console
>>> 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}
View as Markdown Edit this page on GitHub Official version