PostgreSQL specific aggregation functions

فهرست فصل‌ها دانلود آرشیوناشر

Django Docs · نسخهٔ 33d2e5948a0b · BSD-3-Clause

=========================================

PostgreSQL specific aggregation functions

:synopsis: 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 <https://www.postgresql.org/docs/current/functions-aggregate.html>`_.

All functions come without default aliases, so you must explicitly provide one. For example:

pycon
>>> SomeModel.objects.aggregate(arr=ArrayAgg("somefield"))
{'arr': [0, 1, 2]}

All aggregates have the filter <aggregate-filter> keyword argument and most also have the default <aggregate-default> keyword argument.

General-purpose aggregation functions

`ArrayAgg`

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

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

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::

from django.db.models import F

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

The `ordering keyword argument is deprecated. Use ArrayAgg.order_by` instead.

`BitAnd`

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

`BitOr`

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

`BitXor`

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`

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

Usage example::

class Comment(models.Model): body = models.TextField() published = models.BooleanField() rank = models.IntegerField()

pycon
>>> 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`

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::

class Comment(models.Model): body = models.TextField() published = models.BooleanField() rank = models.IntegerField()

pycon
>>> 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`

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

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

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::

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)

pycon
    >>> 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}
    ]}]>

.. deprecated:: 5.2

    The ``ordering`` keyword argument is deprecated. Use

`StringAgg`

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

Required argument. Needs to be a string.

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

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::

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

class Article(models.Model): headline = models.CharField(max_length=100) publications = models.ManyToManyField(Publication)

pycon
    >>> 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'
    }]>

.. deprecated:: 5.2

    The ``ordering`` keyword argument is deprecated. Use

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`

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

`CovarPop`

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

Optional. By default `CovarPop returns the general population covariance. However, if sample=True`, the return value will be the sample population covariance.

`RegrAvgX`

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

`RegrAvgY`

Returns the average of the dependent variable (`sum(y)/N) as a float, or default` if there aren't any matching rows.

`RegrCount`

Returns an `int` of the number of input rows in which both expressions are not null.

The `default` argument is not supported.

`RegrIntercept`

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`

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

`RegrSlope`

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`

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`

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`

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:

pycon
>>> 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 <https://en.wikipedia.org/wiki/Regression_analysis>_):

pycon
>>> 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}