File: aggregates.txt

package info (click to toggle)
python-django 3%3A3.2.19-1%2Bdeb12u2
  • links: PTS, VCS
  • area: main
  • in suites: bookworm-proposed-updates
  • size: 56,696 kB
  • sloc: python: 264,418; javascript: 18,362; xml: 193; makefile: 178; sh: 43
file content (302 lines) | stat: -rw-r--r-- 8,838 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
=========================================
PostgreSQL specific aggregation functions
=========================================

.. module:: django.contrib.postgres.aggregates
   :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>`_.

.. note::

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

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

.. admonition:: Common aggregate options

    All aggregates have the :ref:`filter <aggregate-filter>` keyword
    argument.

General-purpose aggregation functions
=====================================

``ArrayAgg``
------------

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

    Returns a list of values, including nulls, concatenated into an array.

    .. attribute:: distinct

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

    .. attribute:: ordering

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

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

``BitAnd``
----------

.. class:: BitAnd(expression, filter=None, **extra)

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

``BitOr``
---------

.. class:: BitOr(expression, filter=None, **extra)

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

``BoolAnd``
-----------

.. class:: BoolAnd(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`` .

    Usage example::

        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, **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``.

    Usage example::

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

    Returns the input values as a ``JSON`` array.

    .. attribute:: distinct

        .. versionadded:: 3.2

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

    .. attribute:: ordering

        .. versionadded:: 3.2

        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 :attr:`ArrayAgg.ordering`.

``StringAgg``
-------------

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

    Returns the input values concatenated into a string, separated by
    the ``delimiter`` string.

    .. attribute:: delimiter

        Required argument. Needs to be a string.

    .. attribute:: distinct

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

    .. attribute:: ordering

        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 :attr:`ArrayAgg.ordering`.

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)

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

``CovarPop``
------------

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

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

    Has one optional argument:

    .. attribute:: sample

        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)

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

``RegrAvgY``
------------

.. class:: RegrAvgY(y, x, filter=None)

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

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

``RegrR2``
----------

.. class:: RegrR2(y, x, filter=None)

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

``RegrSlope``
-------------

.. class:: RegrSlope(y, x, filter=None)

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

``RegrSXX``
-----------

.. class:: RegrSXX(y, x, filter=None)

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

``RegrSXY``
-----------

.. class:: RegrSXY(y, x, filter=None)

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

``RegrSYY``
-----------

.. class:: RegrSYY(y, x, filter=None)

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

Usage examples
==============

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

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