Fungsi pengumpulan khusus PostgreSQL¶

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¶

Sebuah argumen boolean pilihan yang menentukan jika nilai senarai akan berbeda. Awalan pada False.

ordering¶

Sebuah string pilihan dari sebuah nama bidang (dengan sebuah awalan "-" pilihan yang menunjukkan urutan menurun) atau sebuah pernyataan (atau sebuah tuple atau list dari string dan/atau pernyataan) yang menentukan urutan dari unsur dalam daftar hasil.

Contoh:

from django.db.models import F

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

Changed in Django 5.0:

In older versions, if there are no rows and default is not provided, ArrayAgg returned an empty list instead of None. 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.

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.

Contoh penggunaan:

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.

Contoh penggunaan:

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¶

Sebuah argumen boolean pilihan yang menentukan jika nilai senarai akan berbeda. Awalan pada False.

ordering¶

Sebuah string pilihan dari sebuah nama bidang (dengan sebuah awalan "-" pilihan yang menunjukkan urutan menurun) atau sebuah pernyataan (atau sebuah tuple atau list dari string dan/atau pernyataan) yang menentukan urutan dari unsur dalam daftar hasil.

Contoh-contoh adalah sama seperti untuk ArrayAgg.ordering.

Contoh penggunaan:

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)

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

Changed in Django 5.0:

In older versions, if there are no rows and default is not provided, JSONBAgg returned an empty list instead of None. 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¶

Diwajibkan argumen. Butuh berupa sebuah string.

distinct¶

Sebuah argumen boolean pilihan yang menentukan jika nilai yang digabungkan akan berbeda. Awalan menjadi False.

ordering¶

Sebuah string pilihan dari sebuah bidang nama (dengan awalan "-" pilihan yang menunjukkan urutan menurun) atau sebuah pernyataan (atau sebuah tuple atau list dari string dan/atau pernyataan) yang menentukan urutan dari unsur dalam hasil string.

Contoh-contoh adalah sama seperti untuk ArrayAgg.ordering.

Contoh penggunaan:

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


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

>>> 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=", ",
...         ordering="publications__title",
...     )
... ).values("headline", "publication_names")
<QuerySet [{
    'headline': 'NASA uses Python', 'publication_names': 'Science News, The Python Journal'
}]>

Changed in Django 5.0:

In older versions, if there are no rows and default is not provided, StringAgg returned an empty string instead of None. If you need it, explicitly set default to Value("").

y dan x¶

Argumen-argumen y dan x untuk semua fungsi-fungsi ini dapat dinamai dari bidang atau sebuah pernyataan mengembalikan data numerik. Kedua diwajibkan.

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)¶

Mengembalikan sebuah int dari angka dari baris masukan dimana kedua pernyataan tidak null.

Catatan

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.