Django 4.0.1.dev20211213062056 文档

Home | Table of contents | Index | Modules

« previous | up | next »

PostgreSQL 特有聚合函数

这些功能可以从 django.contrib.postgres.aggregates 模块中获得。在 PostgreSQL docs 中对这些功能有更详细的描述。

注解

所有的函数都没有默认的别名，所以你必须明确提供一个别名。例如：

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

通用聚合选项

All aggregates have the filter keyword argument and most also have the default keyword argument.

通用聚合函数

ArrayAgg

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

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

distinct

一个可选的布尔参数，用于确定数组值是否会被去重。默认值为 False。

ordering

可选的字段名字符串（可选的 "-" 前缀表示降序）或表达式（或字符串和／或表达式的元组或列表），指定结果列表中元素的顺序。

举例：

'some_field'
'-some_field'
from django.db.models import F
F('some_field').desc()

4.0 版后已移除: If there are no rows and default is not provided, ArrayAgg returns an empty list instead of None. This behavior is deprecated and will be removed in Django 5.0. If you need it, explicitly set default to Value([]).

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.

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.

使用实例：

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

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

使用实例：

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

>>> 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, ordering=(), **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

New in Django 3.2.

一个可选的布尔参数，用于确定数组值是否会被去重。默认值为 False。

ordering

New in Django 3.2.

可选的字段名字符串（可选的 "-" 前缀表示降序）或表达式（或字符串和／或表达式的元组或列表），指定结果列表中元素的顺序。

例子与 ArrayAgg.ordering 相同。

使用实例：

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

class HotelReservation(model.Model):
    room = models.ForeignKey('Room', on_delete=models.CASCADE)
    start = models.DateTimeField()
    end = models.DateTimeField()
    requirements = models.JSONField(blank=True, null=True)

>>> from django.contrib.postgres.aggregates import JSONBAgg
>>> Room.objects.annotate(
...     requirements=JSONBAgg(
...         'hotelreservation__requirements',
...         ordering='-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}
]}]>

4.0 版后已移除: If there are no rows and default is not provided, JSONBAgg returns an empty list instead of None. This behavior is deprecated and will be removed in Django 5.0. If you need it, explicitly set default to Value('[]').

StringAgg

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

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

delimiter

必要参数。需要是一个字符串。

distinct

一个可选的布尔参数，用于确定连接的值是否是不同的。默认值为 False。

ordering

可选的字段名字符串（可选的 "-" 前缀表示降序）或表达式（或字符串和／或表达式的元组或列表），指定结果字符串中元素的顺序。

例子与 ArrayAgg.ordering 相同。

4.0 版后已移除: If there are no rows and default is not provided, StringAgg returns an empty string instead of None. This behavior is deprecated and will be removed in Django 5.0. If you need it, explicitly set default to Value('').

统计的聚合功能

y 和 x

所有这些函数的参数 y 和 x 可以是字段名或返回数值数据的表达式。这两个参数都是必须的。

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

默认情况下，CovarPop 返回一般总体协方差。但是，如果 sample=True，返回值将是样本总体协方差。

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)

返回两个表达式都不为空的输入行数的 int。

注解

The default argument is not supported.

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.

使用实例：

我们将用这个例表：

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

下面是一些通用聚合函数的例子：

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

下一个例子显示了统计聚合函数的使用。基本的数学知识将不作说明（例如，你可以在 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}

Last update:

12月 13, 2021

« previous | up | next »