Debian Codegraph @

docs/ref/contrib/postgres/aggregates.txt

raw
1 =========================================
2 PostgreSQL specific aggregation functions
3 =========================================
4
5 .. module:: django.contrib.postgres.aggregates
6 :synopsis: PostgreSQL specific aggregation functions
7
8 These functions are available from the ``django.contrib.postgres.aggregates``
9 module. They are described in more detail in the `PostgreSQL docs
10 <https://www.postgresql.org/docs/current/functions-aggregate.html>`_.
11
12 .. note::
13
14 All functions come without default aliases, so you must explicitly provide
15 one. For example:
16
17 .. code-block:: pycon
18
19 >>> SomeModel.objects.aggregate(arr=ArrayAgg("somefield"))
20 {'arr': [0, 1, 2]}
21
22 .. admonition:: Common aggregate options
23
24 All aggregates have the :ref:`filter <aggregate-filter>` keyword argument
25 and most also have the :ref:`default <aggregate-default>` keyword argument.
26
27 General-purpose aggregation functions
28 =====================================
29
30 ``ArrayAgg``
31 ------------
32
33 .. class:: ArrayAgg(expression, distinct=False, filter=None, default=None, order_by=(), **extra)
34
35 Returns a list of values, including nulls, concatenated into an array, or
36 ``default`` if there are no values.
37
38 .. attribute:: distinct
39
40 An optional boolean argument that determines if array values
41 will be distinct. Defaults to ``False``.
42
43 .. attribute:: order_by
44
45 .. versionadded:: 5.2
46
47 An optional string of a field name (with an optional ``"-"`` prefix
48 which indicates descending order) or an expression (or a tuple or list
49 of strings and/or expressions) that specifies the ordering of the
50 elements in the result list.
51
52 Examples::
53
54 from django.db.models import F
55
56 ArrayAgg("a_field", order_by="-some_field")
57 ArrayAgg("a_field", order_by=F("some_field").desc())
58
59 .. deprecated:: 5.2
60
61 The ``ordering`` keyword argument is deprecated. Use
62 :attr:`ArrayAgg.order_by` instead.
63
64 ``BitAnd``
65 ----------
66
67 .. class:: BitAnd(expression, filter=None, default=None, **extra)
68
69 Returns an ``int`` of the bitwise ``AND`` of all non-null input values, or
70 ``default`` if all values are null.
71
72 ``BitOr``
73 ---------
74
75 .. class:: BitOr(expression, filter=None, default=None, **extra)
76
77 Returns an ``int`` of the bitwise ``OR`` of all non-null input values, or
78 ``default`` if all values are null.
79
80 ``BitXor``
81 ----------
82
83 .. class:: BitXor(expression, filter=None, default=None, **extra)
84
85 Returns an ``int`` of the bitwise ``XOR`` of all non-null input values, or
86 ``default`` if all values are null. It requires PostgreSQL 14+.
87
88 ``BoolAnd``
89 -----------
90
91 .. class:: BoolAnd(expression, filter=None, default=None, **extra)
92
93 Returns ``True``, if all input values are true, ``default`` if all values
94 are null or if there are no values, otherwise ``False``.
95
96 Usage example::
97
98 class Comment(models.Model):
99 body = models.TextField()
100 published = models.BooleanField()
101 rank = models.IntegerField()
102
103 .. code-block:: pycon
104
105 >>> from django.db.models import Q
106 >>> from django.contrib.postgres.aggregates import BoolAnd
107 >>> Comment.objects.aggregate(booland=BoolAnd("published"))
108 {'booland': False}
109 >>> Comment.objects.aggregate(booland=BoolAnd(Q(rank__lt=100)))
110 {'booland': True}
111
112 ``BoolOr``
113 ----------
114
115 .. class:: BoolOr(expression, filter=None, default=None, **extra)
116
117 Returns ``True`` if at least one input value is true, ``default`` if all
118 values are null or if there are no values, otherwise ``False``.
119
120 Usage example::
121
122 class Comment(models.Model):
123 body = models.TextField()
124 published = models.BooleanField()
125 rank = models.IntegerField()
126
127 .. code-block:: pycon
128
129 >>> from django.db.models import Q
130 >>> from django.contrib.postgres.aggregates import BoolOr
131 >>> Comment.objects.aggregate(boolor=BoolOr("published"))
132 {'boolor': True}
133 >>> Comment.objects.aggregate(boolor=BoolOr(Q(rank__gt=2)))
134 {'boolor': False}
135
136 ``JSONBAgg``
137 ------------
138
139 .. class:: JSONBAgg(expressions, distinct=False, filter=None, default=None, order_by=(), **extra)
140
141 Returns the input values as a ``JSON`` array, or ``default`` if there are
142 no values. You can query the result using :lookup:`key and index lookups
143 <jsonfield.key>`.
144
145 .. attribute:: distinct
146
147 An optional boolean argument that determines if array values will be
148 distinct. Defaults to ``False``.
149
150 .. attribute:: order_by
151
152 .. versionadded:: 5.2
153
154 An optional string of a field name (with an optional ``"-"`` prefix
155 which indicates descending order) or an expression (or a tuple or list
156 of strings and/or expressions) that specifies the ordering of the
157 elements in the result list.
158
159 Examples are the same as for :attr:`ArrayAgg.order_by`.
160
161 Usage example::
162
163 class Room(models.Model):
164 number = models.IntegerField(unique=True)
165
166
167 class HotelReservation(models.Model):
168 room = models.ForeignKey("Room", on_delete=models.CASCADE)
169 start = models.DateTimeField()
170 end = models.DateTimeField()
171 requirements = models.JSONField(blank=True, null=True)
172
173 .. code-block:: pycon
174
175 >>> from django.contrib.postgres.aggregates import JSONBAgg
176 >>> Room.objects.annotate(
177 ... requirements=JSONBAgg(
178 ... "hotelreservation__requirements",
179 ... order_by="-hotelreservation__start",
180 ... )
181 ... ).filter(requirements__0__sea_view=True).values("number", "requirements")
182 <QuerySet [{'number': 102, 'requirements': [
183 {'parking': False, 'sea_view': True, 'double_bed': False},
184 {'parking': True, 'double_bed': True}
185 ]}]>
186
187 .. deprecated:: 5.2
188
189 The ``ordering`` keyword argument is deprecated. Use
190 :attr:`JSONBAgg.order_by` instead.
191
192 ``StringAgg``
193 -------------
194
195 .. class:: StringAgg(expression, delimiter, distinct=False, filter=None, default=None, order_by=())
196
197 Returns the input values concatenated into a string, separated by
198 the ``delimiter`` string, or ``default`` if there are no values.
199
200 .. attribute:: delimiter
201
202 Required argument. Needs to be a string.
203
204 .. attribute:: distinct
205
206 An optional boolean argument that determines if concatenated values
207 will be distinct. Defaults to ``False``.
208
209 .. attribute:: order_by
210
211 .. versionadded:: 5.2
212
213 An optional string of a field name (with an optional ``"-"`` prefix
214 which indicates descending order) or an expression (or a tuple or list
215 of strings and/or expressions) that specifies the ordering of the
216 elements in the result string.
217
218 Examples are the same as for :attr:`ArrayAgg.order_by`.
219
220 Usage example::
221
222 class Publication(models.Model):
223 title = models.CharField(max_length=30)
224
225
226 class Article(models.Model):
227 headline = models.CharField(max_length=100)
228 publications = models.ManyToManyField(Publication)
229
230 .. code-block:: pycon
231
232 >>> article = Article.objects.create(headline="NASA uses Python")
233 >>> article.publications.create(title="The Python Journal")
234 <Publication: Publication object (1)>
235 >>> article.publications.create(title="Science News")
236 <Publication: Publication object (2)>
237 >>> from django.contrib.postgres.aggregates import StringAgg
238 >>> Article.objects.annotate(
239 ... publication_names=StringAgg(
240 ... "publications__title",
241 ... delimiter=", ",
242 ... order_by="publications__title",
243 ... )
244 ... ).values("headline", "publication_names")
245 <QuerySet [{
246 'headline': 'NASA uses Python', 'publication_names': 'Science News, The Python Journal'
247 }]>
248
249 .. deprecated:: 5.2
250
251 The ``ordering`` keyword argument is deprecated. Use
252 :attr:`StringAgg.order_by` instead.
253
254 Aggregate functions for statistics
255 ==================================
256
257 ``y`` and ``x``
258 ---------------
259
260 The arguments ``y`` and ``x`` for all these functions can be the name of a
261 field or an expression returning a numeric data. Both are required.
262
263 ``Corr``
264 --------
265
266 .. class:: Corr(y, x, filter=None, default=None)
267
268 Returns the correlation coefficient as a ``float``, or ``default`` if there
269 aren't any matching rows.
270
271 ``CovarPop``
272 ------------
273
274 .. class:: CovarPop(y, x, sample=False, filter=None, default=None)
275
276 Returns the population covariance as a ``float``, or ``default`` if there
277 aren't any matching rows.
278
279 .. attribute:: sample
280
281 Optional. By default ``CovarPop`` returns the general population
282 covariance. However, if ``sample=True``, the return value will be the
283 sample population covariance.
284
285 ``RegrAvgX``
286 ------------
287
288 .. class:: RegrAvgX(y, x, filter=None, default=None)
289
290 Returns the average of the independent variable (``sum(x)/N``) as a
291 ``float``, or ``default`` if there aren't any matching rows.
292
293 ``RegrAvgY``
294 ------------
295
296 .. class:: RegrAvgY(y, x, filter=None, default=None)
297
298 Returns the average of the dependent variable (``sum(y)/N``) as a
299 ``float``, or ``default`` if there aren't any matching rows.
300
301 ``RegrCount``
302 -------------
303
304 .. class:: RegrCount(y, x, filter=None)
305
306 Returns an ``int`` of the number of input rows in which both expressions
307 are not null.
308
309 .. note::
310
311 The ``default`` argument is not supported.
312
313 ``RegrIntercept``
314 -----------------
315
316 .. class:: RegrIntercept(y, x, filter=None, default=None)
317
318 Returns the y-intercept of the least-squares-fit linear equation determined
319 by the ``(x, y)`` pairs as a ``float``, or ``default`` if there aren't any
320 matching rows.
321
322 ``RegrR2``
323 ----------
324
325 .. class:: RegrR2(y, x, filter=None, default=None)
326
327 Returns the square of the correlation coefficient as a ``float``, or
328 ``default`` if there aren't any matching rows.
329
330 ``RegrSlope``
331 -------------
332
333 .. class:: RegrSlope(y, x, filter=None, default=None)
334
335 Returns the slope of the least-squares-fit linear equation determined
336 by the ``(x, y)`` pairs as a ``float``, or ``default`` if there aren't any
337 matching rows.
338
339 ``RegrSXX``
340 -----------
341
342 .. class:: RegrSXX(y, x, filter=None, default=None)
343
344 Returns ``sum(x^2) - sum(x)^2/N`` ("sum of squares" of the independent
345 variable) as a ``float``, or ``default`` if there aren't any matching rows.
346
347 ``RegrSXY``
348 -----------
349
350 .. class:: RegrSXY(y, x, filter=None, default=None)
351
352 Returns ``sum(x*y) - sum(x) * sum(y)/N`` ("sum of products" of independent
353 times dependent variable) as a ``float``, or ``default`` if there aren't
354 any matching rows.
355
356 ``RegrSYY``
357 -----------
358
359 .. class:: RegrSYY(y, x, filter=None, default=None)
360
361 Returns ``sum(y^2) - sum(y)^2/N`` ("sum of squares" of the dependent
362 variable) as a ``float``, or ``default`` if there aren't any matching rows.
363
364 Usage examples
365 ==============
366
367 We will use this example table:
368
369 .. code-block:: text
370
371 | FIELD1 | FIELD2 | FIELD3 |
372 |--------|--------|--------|
373 | foo | 1 | 13 |
374 | bar | 2 | (null) |
375 | test | 3 | 13 |
376
377 Here's some examples of some of the general-purpose aggregation functions:
378
379 .. code-block:: pycon
380
381 >>> TestModel.objects.aggregate(result=StringAgg("field1", delimiter=";"))
382 {'result': 'foo;bar;test'}
383 >>> TestModel.objects.aggregate(result=ArrayAgg("field2"))
384 {'result': [1, 2, 3]}
385 >>> TestModel.objects.aggregate(result=ArrayAgg("field1"))
386 {'result': ['foo', 'bar', 'test']}
387
388 The next example shows the usage of statistical aggregate functions. The
389 underlying math will be not described (you can read about this, for example, at
390 `wikipedia <https://en.wikipedia.org/wiki/Regression_analysis>`_):
391
392 .. code-block:: pycon
393
394 >>> TestModel.objects.aggregate(count=RegrCount(y="field3", x="field2"))
395 {'count': 2}
396 >>> TestModel.objects.aggregate(
397 ... avgx=RegrAvgX(y="field3", x="field2"), avgy=RegrAvgY(y="field3", x="field2")
398 ... )
399 {'avgx': 2, 'avgy': 13}