Debian Codegraph @

tests/postgres_tests/test_aggregates.py

raw
1 from django.db import transaction
2 from django.db.models import (
3 CharField,
4 F,
5 Func,
6 IntegerField,
7 JSONField,
8 OuterRef,
9 Q,
10 Subquery,
11 Value,
12 Window,
13 )
14 from django.db.models.fields.json import KeyTextTransform, KeyTransform
15 from django.db.models.functions import Cast, Concat, LPad, Substr
16 from django.test.utils import Approximate
17 from django.utils import timezone
18 from django.utils.deprecation import RemovedInDjango61Warning
19
20 from . import PostgreSQLTestCase
21 from .models import AggregateTestModel, HotelReservation, Room, StatTestModel
22
23 try:
24 from django.contrib.postgres.aggregates import (
25 ArrayAgg,
26 BitAnd,
27 BitOr,
28 BitXor,
29 BoolAnd,
30 BoolOr,
31 Corr,
32 CovarPop,
33 JSONBAgg,
34 RegrAvgX,
35 RegrAvgY,
36 RegrCount,
37 RegrIntercept,
38 RegrR2,
39 RegrSlope,
40 RegrSXX,
41 RegrSXY,
42 RegrSYY,
43 StatAggregate,
44 StringAgg,
45 )
46 from django.contrib.postgres.fields import ArrayField
47 except ImportError:
48 pass # psycopg2 is not installed
49
50
51 class TestGeneralAggregate(PostgreSQLTestCase):
52 @classmethod
53 def setUpTestData(cls):
54 cls.aggs = AggregateTestModel.objects.bulk_create(
55 [
56 AggregateTestModel(
57 boolean_field=True,
58 char_field="Foo1",
59 text_field="Text1",
60 integer_field=0,
61 ),
62 AggregateTestModel(
63 boolean_field=False,
64 char_field="Foo2",
65 text_field="Text2",
66 integer_field=1,
67 json_field={"lang": "pl"},
68 ),
69 AggregateTestModel(
70 boolean_field=False,
71 char_field="Foo4",
72 text_field="Text4",
73 integer_field=2,
74 json_field={"lang": "en"},
75 ),
76 AggregateTestModel(
77 boolean_field=True,
78 char_field="Foo3",
79 text_field="Text3",
80 integer_field=0,
81 json_field={"breed": "collie"},
82 ),
83 ]
84 )
85
86 def test_empty_result_set(self):
87 AggregateTestModel.objects.all().delete()
88 tests = [
89 ArrayAgg("char_field"),
90 ArrayAgg("integer_field"),
91 ArrayAgg("boolean_field"),
92 BitAnd("integer_field"),
93 BitOr("integer_field"),
94 BoolAnd("boolean_field"),
95 BoolOr("boolean_field"),
96 JSONBAgg("integer_field"),
97 StringAgg("char_field", delimiter=";"),
98 BitXor("integer_field"),
99 ]
100 for aggregation in tests:
101 with self.subTest(aggregation=aggregation):
102 # Empty result with non-execution optimization.
103 with self.assertNumQueries(0):
104 values = AggregateTestModel.objects.none().aggregate(
105 aggregation=aggregation,
106 )
107 self.assertEqual(values, {"aggregation": None})
108 # Empty result when query must be executed.
109 with self.assertNumQueries(1):
110 values = AggregateTestModel.objects.aggregate(
111 aggregation=aggregation,
112 )
113 self.assertEqual(values, {"aggregation": None})
114
115 def test_default_argument(self):
116 AggregateTestModel.objects.all().delete()
117 tests = [
118 (ArrayAgg("char_field", default=["<empty>"]), ["<empty>"]),
119 (ArrayAgg("integer_field", default=[0]), [0]),
120 (ArrayAgg("boolean_field", default=[False]), [False]),
121 (BitAnd("integer_field", default=0), 0),
122 (BitOr("integer_field", default=0), 0),
123 (BoolAnd("boolean_field", default=False), False),
124 (BoolOr("boolean_field", default=False), False),
125 (JSONBAgg("integer_field", default=["<empty>"]), ["<empty>"]),
126 (
127 JSONBAgg("integer_field", default=Value(["<empty>"], JSONField())),
128 ["<empty>"],
129 ),
130 (StringAgg("char_field", delimiter=";", default="<empty>"), "<empty>"),
131 (
132 StringAgg("char_field", delimiter=";", default=Value("<empty>")),
133 "<empty>",
134 ),
135 (BitXor("integer_field", default=0), 0),
136 ]
137 for aggregation, expected_result in tests:
138 with self.subTest(aggregation=aggregation):
139 # Empty result with non-execution optimization.
140 with self.assertNumQueries(0):
141 values = AggregateTestModel.objects.none().aggregate(
142 aggregation=aggregation,
143 )
144 self.assertEqual(values, {"aggregation": expected_result})
145 # Empty result when query must be executed.
146 with transaction.atomic(), self.assertNumQueries(1):
147 values = AggregateTestModel.objects.aggregate(
148 aggregation=aggregation,
149 )
150 self.assertEqual(values, {"aggregation": expected_result})
151
152 def test_ordering_warns_of_deprecation(self):
153 msg = "The ordering argument is deprecated. Use order_by instead."
154 with self.assertWarnsMessage(RemovedInDjango61Warning, msg) as ctx:
155 values = AggregateTestModel.objects.aggregate(
156 arrayagg=ArrayAgg("integer_field", ordering=F("integer_field").desc())
157 )
158 self.assertEqual(values, {"arrayagg": [2, 1, 0, 0]})
159 self.assertEqual(ctx.filename, __file__)
160
161 def test_ordering_and_order_by_causes_error(self):
162 with self.assertWarns(RemovedInDjango61Warning):
163 with self.assertRaisesMessage(
164 TypeError,
165 "Cannot specify both order_by and ordering.",
166 ):
167 AggregateTestModel.objects.aggregate(
168 stringagg=StringAgg(
169 "char_field",
170 delimiter=Value("'"),
171 order_by="char_field",
172 ordering="char_field",
173 )
174 )
175
176 def test_array_agg_charfield(self):
177 values = AggregateTestModel.objects.aggregate(arrayagg=ArrayAgg("char_field"))
178 self.assertEqual(values, {"arrayagg": ["Foo1", "Foo2", "Foo4", "Foo3"]})
179
180 def test_array_agg_charfield_order_by(self):
181 order_by_test_cases = (
182 (F("char_field").desc(), ["Foo4", "Foo3", "Foo2", "Foo1"]),
183 (F("char_field").asc(), ["Foo1", "Foo2", "Foo3", "Foo4"]),
184 (F("char_field"), ["Foo1", "Foo2", "Foo3", "Foo4"]),
185 (
186 [F("boolean_field"), F("char_field").desc()],
187 ["Foo4", "Foo2", "Foo3", "Foo1"],
188 ),
189 (
190 (F("boolean_field"), F("char_field").desc()),
191 ["Foo4", "Foo2", "Foo3", "Foo1"],
192 ),
193 ("char_field", ["Foo1", "Foo2", "Foo3", "Foo4"]),
194 ("-char_field", ["Foo4", "Foo3", "Foo2", "Foo1"]),
195 (Concat("char_field", Value("@")), ["Foo1", "Foo2", "Foo3", "Foo4"]),
196 (Concat("char_field", Value("@")).desc(), ["Foo4", "Foo3", "Foo2", "Foo1"]),
197 (
198 (
199 Substr("char_field", 1, 1),
200 F("integer_field"),
201 Substr("char_field", 4, 1).desc(),
202 ),
203 ["Foo3", "Foo1", "Foo2", "Foo4"],
204 ),
205 )
206 for order_by, expected_output in order_by_test_cases:
207 with self.subTest(order_by=order_by, expected_output=expected_output):
208 values = AggregateTestModel.objects.aggregate(
209 arrayagg=ArrayAgg("char_field", order_by=order_by)
210 )
211 self.assertEqual(values, {"arrayagg": expected_output})
212
213 def test_array_agg_integerfield(self):
214 values = AggregateTestModel.objects.aggregate(
215 arrayagg=ArrayAgg("integer_field")
216 )
217 self.assertEqual(values, {"arrayagg": [0, 1, 2, 0]})
218
219 def test_array_agg_integerfield_order_by(self):
220 values = AggregateTestModel.objects.aggregate(
221 arrayagg=ArrayAgg("integer_field", order_by=F("integer_field").desc())
222 )
223 self.assertEqual(values, {"arrayagg": [2, 1, 0, 0]})
224
225 def test_array_agg_booleanfield(self):
226 values = AggregateTestModel.objects.aggregate(
227 arrayagg=ArrayAgg("boolean_field")
228 )
229 self.assertEqual(values, {"arrayagg": [True, False, False, True]})
230
231 def test_array_agg_booleanfield_order_by(self):
232 order_by_test_cases = (
233 (F("boolean_field").asc(), [False, False, True, True]),
234 (F("boolean_field").desc(), [True, True, False, False]),
235 (F("boolean_field"), [False, False, True, True]),
236 )
237 for order_by, expected_output in order_by_test_cases:
238 with self.subTest(order_by=order_by, expected_output=expected_output):
239 values = AggregateTestModel.objects.aggregate(
240 arrayagg=ArrayAgg("boolean_field", order_by=order_by)
241 )
242 self.assertEqual(values, {"arrayagg": expected_output})
243
244 def test_array_agg_jsonfield(self):
245 values = AggregateTestModel.objects.aggregate(
246 arrayagg=ArrayAgg(
247 KeyTransform("lang", "json_field"),
248 filter=Q(json_field__lang__isnull=False),
249 ),
250 )
251 self.assertEqual(values, {"arrayagg": ["pl", "en"]})
252
253 def test_array_agg_jsonfield_order_by(self):
254 values = AggregateTestModel.objects.aggregate(
255 arrayagg=ArrayAgg(
256 KeyTransform("lang", "json_field"),
257 filter=Q(json_field__lang__isnull=False),
258 order_by=KeyTransform("lang", "json_field"),
259 ),
260 )
261 self.assertEqual(values, {"arrayagg": ["en", "pl"]})
262
263 def test_array_agg_filter_and_order_by_params(self):
264 values = AggregateTestModel.objects.aggregate(
265 arrayagg=ArrayAgg(
266 "char_field",
267 filter=Q(json_field__has_key="lang"),
268 order_by=LPad(Cast("integer_field", CharField()), 2, Value("0")),
269 )
270 )
271 self.assertEqual(values, {"arrayagg": ["Foo2", "Foo4"]})
272
273 def test_array_agg_filter(self):
274 values = AggregateTestModel.objects.aggregate(
275 arrayagg=ArrayAgg("integer_field", filter=Q(integer_field__gt=0)),
276 )
277 self.assertEqual(values, {"arrayagg": [1, 2]})
278
279 def test_array_agg_lookups(self):
280 aggr1 = AggregateTestModel.objects.create()
281 aggr2 = AggregateTestModel.objects.create()
282 StatTestModel.objects.create(related_field=aggr1, int1=1, int2=0)
283 StatTestModel.objects.create(related_field=aggr1, int1=2, int2=0)
284 StatTestModel.objects.create(related_field=aggr2, int1=3, int2=0)
285 StatTestModel.objects.create(related_field=aggr2, int1=4, int2=0)
286 qs = (
287 StatTestModel.objects.values("related_field")
288 .annotate(array=ArrayAgg("int1"))
289 .filter(array__overlap=[2])
290 .values_list("array", flat=True)
291 )
292 self.assertCountEqual(qs.get(), [1, 2])
293
294 def test_array_agg_filter_index(self):
295 aggr1 = AggregateTestModel.objects.create(integer_field=1)
296 aggr2 = AggregateTestModel.objects.create(integer_field=2)
297 StatTestModel.objects.bulk_create(
298 [
299 StatTestModel(related_field=aggr1, int1=1, int2=0),
300 StatTestModel(related_field=aggr1, int1=2, int2=1),
301 StatTestModel(related_field=aggr2, int1=3, int2=0),
302 StatTestModel(related_field=aggr2, int1=4, int2=1),
303 ]
304 )
305 qs = (
306 AggregateTestModel.objects.filter(pk__in=[aggr1.pk, aggr2.pk])
307 .annotate(
308 array=ArrayAgg("stattestmodel__int1", filter=Q(stattestmodel__int2=0))
309 )
310 .annotate(array_value=F("array__0"))
311 .values_list("array_value", flat=True)
312 )
313 self.assertCountEqual(qs, [1, 3])
314
315 def test_array_agg_filter_slice(self):
316 aggr1 = AggregateTestModel.objects.create(integer_field=1)
317 aggr2 = AggregateTestModel.objects.create(integer_field=2)
318 StatTestModel.objects.bulk_create(
319 [
320 StatTestModel(related_field=aggr1, int1=1, int2=0),
321 StatTestModel(related_field=aggr1, int1=2, int2=1),
322 StatTestModel(related_field=aggr2, int1=3, int2=0),
323 StatTestModel(related_field=aggr2, int1=4, int2=1),
324 StatTestModel(related_field=aggr2, int1=5, int2=0),
325 ]
326 )
327 qs = (
328 AggregateTestModel.objects.filter(pk__in=[aggr1.pk, aggr2.pk])
329 .annotate(
330 array=ArrayAgg("stattestmodel__int1", filter=Q(stattestmodel__int2=0))
331 )
332 .annotate(array_value=F("array__1_2"))
333 .values_list("array_value", flat=True)
334 )
335 self.assertCountEqual(qs, [[], [5]])
336
337 def test_array_agg_with_empty_filter_and_default_values(self):
338 for filter_value in ([-1], []):
339 for default_value in ([], Value([])):
340 with self.subTest(filter=filter_value, default=default_value):
341 queryset = AggregateTestModel.objects.annotate(
342 test_array_agg=ArrayAgg(
343 "stattestmodel__int1",
344 filter=Q(pk__in=filter_value),
345 default=default_value,
346 )
347 )
348 self.assertSequenceEqual(
349 queryset.values_list("test_array_agg", flat=True),
350 [[], [], [], []],
351 )
352
353 def test_array_agg_with_order_by_outer_ref(self):
354 StatTestModel.objects.annotate(
355 atm_ids=Subquery(
356 AggregateTestModel.objects.annotate(
357 ids=ArrayAgg(
358 "id",
359 order_by=[OuterRef("int1")],
360 )
361 ).values("ids")[:1]
362 )
363 )
364
365 def test_bit_and_general(self):
366 values = AggregateTestModel.objects.filter(integer_field__in=[0, 1]).aggregate(
367 bitand=BitAnd("integer_field")
368 )
369 self.assertEqual(values, {"bitand": 0})
370
371 def test_bit_and_on_only_true_values(self):
372 values = AggregateTestModel.objects.filter(integer_field=1).aggregate(
373 bitand=BitAnd("integer_field")
374 )
375 self.assertEqual(values, {"bitand": 1})
376
377 def test_bit_and_on_only_false_values(self):
378 values = AggregateTestModel.objects.filter(integer_field=0).aggregate(
379 bitand=BitAnd("integer_field")
380 )
381 self.assertEqual(values, {"bitand": 0})
382
383 def test_bit_or_general(self):
384 values = AggregateTestModel.objects.filter(integer_field__in=[0, 1]).aggregate(
385 bitor=BitOr("integer_field")
386 )
387 self.assertEqual(values, {"bitor": 1})
388
389 def test_bit_or_on_only_true_values(self):
390 values = AggregateTestModel.objects.filter(integer_field=1).aggregate(
391 bitor=BitOr("integer_field")
392 )
393 self.assertEqual(values, {"bitor": 1})
394
395 def test_bit_or_on_only_false_values(self):
396 values = AggregateTestModel.objects.filter(integer_field=0).aggregate(
397 bitor=BitOr("integer_field")
398 )
399 self.assertEqual(values, {"bitor": 0})
400
401 def test_bit_xor_general(self):
402 AggregateTestModel.objects.create(integer_field=3)
403 values = AggregateTestModel.objects.filter(
404 integer_field__in=[1, 3],
405 ).aggregate(bitxor=BitXor("integer_field"))
406 self.assertEqual(values, {"bitxor": 2})
407
408 def test_bit_xor_on_only_true_values(self):
409 values = AggregateTestModel.objects.filter(
410 integer_field=1,
411 ).aggregate(bitxor=BitXor("integer_field"))
412 self.assertEqual(values, {"bitxor": 1})
413
414 def test_bit_xor_on_only_false_values(self):
415 values = AggregateTestModel.objects.filter(
416 integer_field=0,
417 ).aggregate(bitxor=BitXor("integer_field"))
418 self.assertEqual(values, {"bitxor": 0})
419
420 def test_bool_and_general(self):
421 values = AggregateTestModel.objects.aggregate(booland=BoolAnd("boolean_field"))
422 self.assertEqual(values, {"booland": False})
423
424 def test_bool_and_q_object(self):
425 values = AggregateTestModel.objects.aggregate(
426 booland=BoolAnd(Q(integer_field__gt=2)),
427 )
428 self.assertEqual(values, {"booland": False})
429
430 def test_bool_or_general(self):
431 values = AggregateTestModel.objects.aggregate(boolor=BoolOr("boolean_field"))
432 self.assertEqual(values, {"boolor": True})
433
434 def test_bool_or_q_object(self):
435 values = AggregateTestModel.objects.aggregate(
436 boolor=BoolOr(Q(integer_field__gt=2)),
437 )
438 self.assertEqual(values, {"boolor": False})
439
440 def test_string_agg_requires_delimiter(self):
441 with self.assertRaises(TypeError):
442 AggregateTestModel.objects.aggregate(stringagg=StringAgg("char_field"))
443
444 def test_string_agg_delimiter_escaping(self):
445 values = AggregateTestModel.objects.aggregate(
446 stringagg=StringAgg("char_field", delimiter="'")
447 )
448 self.assertEqual(values, {"stringagg": "Foo1'Foo2'Foo4'Foo3"})
449
450 def test_string_agg_charfield(self):
451 values = AggregateTestModel.objects.aggregate(
452 stringagg=StringAgg("char_field", delimiter=";")
453 )
454 self.assertEqual(values, {"stringagg": "Foo1;Foo2;Foo4;Foo3"})
455
456 def test_string_agg_default_output_field(self):
457 values = AggregateTestModel.objects.aggregate(
458 stringagg=StringAgg("text_field", delimiter=";"),
459 )
460 self.assertEqual(values, {"stringagg": "Text1;Text2;Text4;Text3"})
461
462 def test_string_agg_charfield_order_by(self):
463 order_by_test_cases = (
464 (F("char_field").desc(), "Foo4;Foo3;Foo2;Foo1"),
465 (F("char_field").asc(), "Foo1;Foo2;Foo3;Foo4"),
466 (F("char_field"), "Foo1;Foo2;Foo3;Foo4"),
467 ("char_field", "Foo1;Foo2;Foo3;Foo4"),
468 ("-char_field", "Foo4;Foo3;Foo2;Foo1"),
469 (Concat("char_field", Value("@")), "Foo1;Foo2;Foo3;Foo4"),
470 (Concat("char_field", Value("@")).desc(), "Foo4;Foo3;Foo2;Foo1"),
471 )
472 for order_by, expected_output in order_by_test_cases:
473 with self.subTest(order_by=order_by, expected_output=expected_output):
474 values = AggregateTestModel.objects.aggregate(
475 stringagg=StringAgg("char_field", delimiter=";", order_by=order_by)
476 )
477 self.assertEqual(values, {"stringagg": expected_output})
478
479 def test_string_agg_jsonfield_order_by(self):
480 values = AggregateTestModel.objects.aggregate(
481 stringagg=StringAgg(
482 KeyTextTransform("lang", "json_field"),
483 delimiter=";",
484 order_by=KeyTextTransform("lang", "json_field"),
485 output_field=CharField(),
486 ),
487 )
488 self.assertEqual(values, {"stringagg": "en;pl"})
489
490 def test_string_agg_filter(self):
491 values = AggregateTestModel.objects.aggregate(
492 stringagg=StringAgg(
493 "char_field",
494 delimiter=";",
495 filter=Q(char_field__endswith="3") | Q(char_field__endswith="1"),
496 )
497 )
498 self.assertEqual(values, {"stringagg": "Foo1;Foo3"})
499
500 def test_orderable_agg_alternative_fields(self):
501 values = AggregateTestModel.objects.aggregate(
502 arrayagg=ArrayAgg("integer_field", order_by=F("char_field").asc())
503 )
504 self.assertEqual(values, {"arrayagg": [0, 1, 0, 2]})
505
506 def test_jsonb_agg(self):
507 values = AggregateTestModel.objects.aggregate(jsonbagg=JSONBAgg("char_field"))
508 self.assertEqual(values, {"jsonbagg": ["Foo1", "Foo2", "Foo4", "Foo3"]})
509
510 def test_jsonb_agg_charfield_order_by(self):
511 order_by_test_cases = (
512 (F("char_field").desc(), ["Foo4", "Foo3", "Foo2", "Foo1"]),
513 (F("char_field").asc(), ["Foo1", "Foo2", "Foo3", "Foo4"]),
514 (F("char_field"), ["Foo1", "Foo2", "Foo3", "Foo4"]),
515 ("char_field", ["Foo1", "Foo2", "Foo3", "Foo4"]),
516 ("-char_field", ["Foo4", "Foo3", "Foo2", "Foo1"]),
517 (Concat("char_field", Value("@")), ["Foo1", "Foo2", "Foo3", "Foo4"]),
518 (Concat("char_field", Value("@")).desc(), ["Foo4", "Foo3", "Foo2", "Foo1"]),
519 )
520 for order_by, expected_output in order_by_test_cases:
521 with self.subTest(order_by=order_by, expected_output=expected_output):
522 values = AggregateTestModel.objects.aggregate(
523 jsonbagg=JSONBAgg("char_field", order_by=order_by),
524 )
525 self.assertEqual(values, {"jsonbagg": expected_output})
526
527 def test_jsonb_agg_integerfield_order_by(self):
528 values = AggregateTestModel.objects.aggregate(
529 jsonbagg=JSONBAgg("integer_field", order_by=F("integer_field").desc()),
530 )
531 self.assertEqual(values, {"jsonbagg": [2, 1, 0, 0]})
532
533 def test_jsonb_agg_booleanfield_order_by(self):
534 order_by_test_cases = (
535 (F("boolean_field").asc(), [False, False, True, True]),
536 (F("boolean_field").desc(), [True, True, False, False]),
537 (F("boolean_field"), [False, False, True, True]),
538 )
539 for order_by, expected_output in order_by_test_cases:
540 with self.subTest(order_by=order_by, expected_output=expected_output):
541 values = AggregateTestModel.objects.aggregate(
542 jsonbagg=JSONBAgg("boolean_field", order_by=order_by),
543 )
544 self.assertEqual(values, {"jsonbagg": expected_output})
545
546 def test_jsonb_agg_jsonfield_order_by(self):
547 values = AggregateTestModel.objects.aggregate(
548 jsonbagg=JSONBAgg(
549 KeyTransform("lang", "json_field"),
550 filter=Q(json_field__lang__isnull=False),
551 order_by=KeyTransform("lang", "json_field"),
552 ),
553 )
554 self.assertEqual(values, {"jsonbagg": ["en", "pl"]})
555
556 def test_jsonb_agg_key_index_transforms(self):
557 room101 = Room.objects.create(number=101)
558 room102 = Room.objects.create(number=102)
559 datetimes = [
560 timezone.datetime(2018, 6, 20),
561 timezone.datetime(2018, 6, 24),
562 timezone.datetime(2018, 6, 28),
563 ]
564 HotelReservation.objects.create(
565 datespan=(datetimes[0].date(), datetimes[1].date()),
566 start=datetimes[0],
567 end=datetimes[1],
568 room=room102,
569 requirements={"double_bed": True, "parking": True},
570 )
571 HotelReservation.objects.create(
572 datespan=(datetimes[1].date(), datetimes[2].date()),
573 start=datetimes[1],
574 end=datetimes[2],
575 room=room102,
576 requirements={"double_bed": False, "sea_view": True, "parking": False},
577 )
578 HotelReservation.objects.create(
579 datespan=(datetimes[0].date(), datetimes[2].date()),
580 start=datetimes[0],
581 end=datetimes[2],
582 room=room101,
583 requirements={"sea_view": False},
584 )
585 values = (
586 Room.objects.annotate(
587 requirements=JSONBAgg(
588 "hotelreservation__requirements",
589 order_by="-hotelreservation__start",
590 )
591 )
592 .filter(requirements__0__sea_view=True)
593 .values("number", "requirements")
594 )
595 self.assertSequenceEqual(
596 values,
597 [
598 {
599 "number": 102,
600 "requirements": [
601 {"double_bed": False, "sea_view": True, "parking": False},
602 {"double_bed": True, "parking": True},
603 ],
604 },
605 ],
606 )
607
608 def test_string_agg_array_agg_order_by_in_subquery(self):
609 stats = []
610 for i, agg in enumerate(AggregateTestModel.objects.order_by("char_field")):
611 stats.append(StatTestModel(related_field=agg, int1=i, int2=i + 1))
612 stats.append(StatTestModel(related_field=agg, int1=i + 1, int2=i))
613 StatTestModel.objects.bulk_create(stats)
614
615 for aggregate, expected_result in (
616 (
617 ArrayAgg("stattestmodel__int1", order_by="-stattestmodel__int2"),
618 [
619 ("Foo1", [0, 1]),
620 ("Foo2", [1, 2]),
621 ("Foo3", [2, 3]),
622 ("Foo4", [3, 4]),
623 ],
624 ),
625 (
626 StringAgg(
627 Cast("stattestmodel__int1", CharField()),
628 delimiter=";",
629 order_by="-stattestmodel__int2",
630 ),
631 [("Foo1", "0;1"), ("Foo2", "1;2"), ("Foo3", "2;3"), ("Foo4", "3;4")],
632 ),
633 ):
634 with self.subTest(aggregate=aggregate.__class__.__name__):
635 subquery = (
636 AggregateTestModel.objects.filter(
637 pk=OuterRef("pk"),
638 )
639 .annotate(agg=aggregate)
640 .values("agg")
641 )
642 values = (
643 AggregateTestModel.objects.annotate(
644 agg=Subquery(subquery),
645 )
646 .order_by("char_field")
647 .values_list("char_field", "agg")
648 )
649 self.assertEqual(list(values), expected_result)
650
651 def test_string_agg_array_agg_filter_in_subquery(self):
652 StatTestModel.objects.bulk_create(
653 [
654 StatTestModel(related_field=self.aggs[0], int1=0, int2=5),
655 StatTestModel(related_field=self.aggs[0], int1=1, int2=4),
656 StatTestModel(related_field=self.aggs[0], int1=2, int2=3),
657 ]
658 )
659 for aggregate, expected_result in (
660 (
661 ArrayAgg("stattestmodel__int1", filter=Q(stattestmodel__int2__gt=3)),
662 [("Foo1", [0, 1]), ("Foo2", None)],
663 ),
664 (
665 StringAgg(
666 Cast("stattestmodel__int2", CharField()),
667 delimiter=";",
668 filter=Q(stattestmodel__int1__lt=2),
669 ),
670 [("Foo1", "5;4"), ("Foo2", None)],
671 ),
672 ):
673 with self.subTest(aggregate=aggregate.__class__.__name__):
674 subquery = (
675 AggregateTestModel.objects.filter(
676 pk=OuterRef("pk"),
677 )
678 .annotate(agg=aggregate)
679 .values("agg")
680 )
681 values = (
682 AggregateTestModel.objects.annotate(
683 agg=Subquery(subquery),
684 )
685 .filter(
686 char_field__in=["Foo1", "Foo2"],
687 )
688 .order_by("char_field")
689 .values_list("char_field", "agg")
690 )
691 self.assertEqual(list(values), expected_result)
692
693 def test_string_agg_filter_in_subquery_with_exclude(self):
694 subquery = (
695 AggregateTestModel.objects.annotate(
696 stringagg=StringAgg(
697 "char_field",
698 delimiter=";",
699 filter=Q(char_field__endswith="1"),
700 )
701 )
702 .exclude(stringagg="")
703 .values("id")
704 )
705 self.assertSequenceEqual(
706 AggregateTestModel.objects.filter(id__in=Subquery(subquery)),
707 [self.aggs[0]],
708 )
709
710 def test_ordering_isnt_cleared_for_array_subquery(self):
711 inner_qs = AggregateTestModel.objects.order_by("-integer_field")
712 qs = AggregateTestModel.objects.annotate(
713 integers=Func(
714 Subquery(inner_qs.values("integer_field")),
715 function="ARRAY",
716 output_field=ArrayField(base_field=IntegerField()),
717 ),
718 )
719 self.assertSequenceEqual(
720 qs.first().integers,
721 inner_qs.values_list("integer_field", flat=True),
722 )
723
724 def test_window(self):
725 self.assertCountEqual(
726 AggregateTestModel.objects.annotate(
727 integers=Window(
728 expression=ArrayAgg("char_field"),
729 partition_by=F("integer_field"),
730 )
731 ).values("integers", "char_field"),
732 [
733 {"integers": ["Foo1", "Foo3"], "char_field": "Foo1"},
734 {"integers": ["Foo1", "Foo3"], "char_field": "Foo3"},
735 {"integers": ["Foo2"], "char_field": "Foo2"},
736 {"integers": ["Foo4"], "char_field": "Foo4"},
737 ],
738 )
739
740 def test_values_list(self):
741 tests = [ArrayAgg("integer_field"), JSONBAgg("integer_field")]
742 for aggregation in tests:
743 with self.subTest(aggregation=aggregation):
744 self.assertCountEqual(
745 AggregateTestModel.objects.values_list(aggregation),
746 [([0],), ([1],), ([2],), ([0],)],
747 )
748
749
750 class TestAggregateDistinct(PostgreSQLTestCase):
751 @classmethod
752 def setUpTestData(cls):
753 AggregateTestModel.objects.create(char_field="Foo")
754 AggregateTestModel.objects.create(char_field="Foo")
755 AggregateTestModel.objects.create(char_field="Bar")
756
757 def test_string_agg_distinct_false(self):
758 values = AggregateTestModel.objects.aggregate(
759 stringagg=StringAgg("char_field", delimiter=" ", distinct=False)
760 )
761 self.assertEqual(values["stringagg"].count("Foo"), 2)
762 self.assertEqual(values["stringagg"].count("Bar"), 1)
763
764 def test_string_agg_distinct_true(self):
765 values = AggregateTestModel.objects.aggregate(
766 stringagg=StringAgg("char_field", delimiter=" ", distinct=True)
767 )
768 self.assertEqual(values["stringagg"].count("Foo"), 1)
769 self.assertEqual(values["stringagg"].count("Bar"), 1)
770
771 def test_array_agg_distinct_false(self):
772 values = AggregateTestModel.objects.aggregate(
773 arrayagg=ArrayAgg("char_field", distinct=False)
774 )
775 self.assertEqual(sorted(values["arrayagg"]), ["Bar", "Foo", "Foo"])
776
777 def test_array_agg_distinct_true(self):
778 values = AggregateTestModel.objects.aggregate(
779 arrayagg=ArrayAgg("char_field", distinct=True)
780 )
781 self.assertEqual(sorted(values["arrayagg"]), ["Bar", "Foo"])
782
783 def test_jsonb_agg_distinct_false(self):
784 values = AggregateTestModel.objects.aggregate(
785 jsonbagg=JSONBAgg("char_field", distinct=False),
786 )
787 self.assertEqual(sorted(values["jsonbagg"]), ["Bar", "Foo", "Foo"])
788
789 def test_jsonb_agg_distinct_true(self):
790 values = AggregateTestModel.objects.aggregate(
791 jsonbagg=JSONBAgg("char_field", distinct=True),
792 )
793 self.assertEqual(sorted(values["jsonbagg"]), ["Bar", "Foo"])
794
795
796 class TestStatisticsAggregate(PostgreSQLTestCase):
797 @classmethod
798 def setUpTestData(cls):
799 StatTestModel.objects.create(
800 int1=1,
801 int2=3,
802 related_field=AggregateTestModel.objects.create(integer_field=0),
803 )
804 StatTestModel.objects.create(
805 int1=2,
806 int2=2,
807 related_field=AggregateTestModel.objects.create(integer_field=1),
808 )
809 StatTestModel.objects.create(
810 int1=3,
811 int2=1,
812 related_field=AggregateTestModel.objects.create(integer_field=2),
813 )
814
815 # Tests for base class (StatAggregate)
816
817 def test_missing_arguments_raises_exception(self):
818 with self.assertRaisesMessage(ValueError, "Both y and x must be provided."):
819 StatAggregate(x=None, y=None)
820
821 def test_correct_source_expressions(self):
822 func = StatAggregate(x="test", y=13)
823 self.assertIsInstance(func.source_expressions[0], Value)
824 self.assertIsInstance(func.source_expressions[1], F)
825
826 def test_alias_is_required(self):
827 class SomeFunc(StatAggregate):
828 function = "TEST"
829
830 with self.assertRaisesMessage(TypeError, "Complex aggregates require an alias"):
831 StatTestModel.objects.aggregate(SomeFunc(y="int2", x="int1"))
832
833 # Test aggregates
834
835 def test_empty_result_set(self):
836 StatTestModel.objects.all().delete()
837 tests = [
838 (Corr(y="int2", x="int1"), None),
839 (CovarPop(y="int2", x="int1"), None),
840 (CovarPop(y="int2", x="int1", sample=True), None),
841 (RegrAvgX(y="int2", x="int1"), None),
842 (RegrAvgY(y="int2", x="int1"), None),
843 (RegrCount(y="int2", x="int1"), 0),
844 (RegrIntercept(y="int2", x="int1"), None),
845 (RegrR2(y="int2", x="int1"), None),
846 (RegrSlope(y="int2", x="int1"), None),
847 (RegrSXX(y="int2", x="int1"), None),
848 (RegrSXY(y="int2", x="int1"), None),
849 (RegrSYY(y="int2", x="int1"), None),
850 ]
851 for aggregation, expected_result in tests:
852 with self.subTest(aggregation=aggregation):
853 # Empty result with non-execution optimization.
854 with self.assertNumQueries(0):
855 values = StatTestModel.objects.none().aggregate(
856 aggregation=aggregation,
857 )
858 self.assertEqual(values, {"aggregation": expected_result})
859 # Empty result when query must be executed.
860 with self.assertNumQueries(1):
861 values = StatTestModel.objects.aggregate(
862 aggregation=aggregation,
863 )
864 self.assertEqual(values, {"aggregation": expected_result})
865
866 def test_default_argument(self):
867 StatTestModel.objects.all().delete()
868 tests = [
869 (Corr(y="int2", x="int1", default=0), 0),
870 (CovarPop(y="int2", x="int1", default=0), 0),
871 (CovarPop(y="int2", x="int1", sample=True, default=0), 0),
872 (RegrAvgX(y="int2", x="int1", default=0), 0),
873 (RegrAvgY(y="int2", x="int1", default=0), 0),
874 # RegrCount() doesn't support the default argument.
875 (RegrIntercept(y="int2", x="int1", default=0), 0),
876 (RegrR2(y="int2", x="int1", default=0), 0),
877 (RegrSlope(y="int2", x="int1", default=0), 0),
878 (RegrSXX(y="int2", x="int1", default=0), 0),
879 (RegrSXY(y="int2", x="int1", default=0), 0),
880 (RegrSYY(y="int2", x="int1", default=0), 0),
881 ]
882 for aggregation, expected_result in tests:
883 with self.subTest(aggregation=aggregation):
884 # Empty result with non-execution optimization.
885 with self.assertNumQueries(0):
886 values = StatTestModel.objects.none().aggregate(
887 aggregation=aggregation,
888 )
889 self.assertEqual(values, {"aggregation": expected_result})
890 # Empty result when query must be executed.
891 with self.assertNumQueries(1):
892 values = StatTestModel.objects.aggregate(
893 aggregation=aggregation,
894 )
895 self.assertEqual(values, {"aggregation": expected_result})
896
897 def test_corr_general(self):
898 values = StatTestModel.objects.aggregate(corr=Corr(y="int2", x="int1"))
899 self.assertEqual(values, {"corr": -1.0})
900
901 def test_covar_pop_general(self):
902 values = StatTestModel.objects.aggregate(covarpop=CovarPop(y="int2", x="int1"))
903 self.assertEqual(values, {"covarpop": Approximate(-0.66, places=1)})
904
905 def test_covar_pop_sample(self):
906 values = StatTestModel.objects.aggregate(
907 covarpop=CovarPop(y="int2", x="int1", sample=True)
908 )
909 self.assertEqual(values, {"covarpop": -1.0})
910
911 def test_regr_avgx_general(self):
912 values = StatTestModel.objects.aggregate(regravgx=RegrAvgX(y="int2", x="int1"))
913 self.assertEqual(values, {"regravgx": 2.0})
914
915 def test_regr_avgy_general(self):
916 values = StatTestModel.objects.aggregate(regravgy=RegrAvgY(y="int2", x="int1"))
917 self.assertEqual(values, {"regravgy": 2.0})
918
919 def test_regr_count_general(self):
920 values = StatTestModel.objects.aggregate(
921 regrcount=RegrCount(y="int2", x="int1")
922 )
923 self.assertEqual(values, {"regrcount": 3})
924
925 def test_regr_count_default(self):
926 msg = "RegrCount does not allow default."
927 with self.assertRaisesMessage(TypeError, msg):
928 RegrCount(y="int2", x="int1", default=0)
929
930 def test_regr_intercept_general(self):
931 values = StatTestModel.objects.aggregate(
932 regrintercept=RegrIntercept(y="int2", x="int1")
933 )
934 self.assertEqual(values, {"regrintercept": 4})
935
936 def test_regr_r2_general(self):
937 values = StatTestModel.objects.aggregate(regrr2=RegrR2(y="int2", x="int1"))
938 self.assertEqual(values, {"regrr2": 1})
939
940 def test_regr_slope_general(self):
941 values = StatTestModel.objects.aggregate(
942 regrslope=RegrSlope(y="int2", x="int1")
943 )
944 self.assertEqual(values, {"regrslope": -1})
945
946 def test_regr_sxx_general(self):
947 values = StatTestModel.objects.aggregate(regrsxx=RegrSXX(y="int2", x="int1"))
948 self.assertEqual(values, {"regrsxx": 2.0})
949
950 def test_regr_sxy_general(self):
951 values = StatTestModel.objects.aggregate(regrsxy=RegrSXY(y="int2", x="int1"))
952 self.assertEqual(values, {"regrsxy": -2.0})
953
954 def test_regr_syy_general(self):
955 values = StatTestModel.objects.aggregate(regrsyy=RegrSYY(y="int2", x="int1"))
956 self.assertEqual(values, {"regrsyy": 2.0})
957
958 def test_regr_avgx_with_related_obj_and_number_as_argument(self):
959 """
960 This is more complex test to check if JOIN on field and
961 number as argument works as expected.
962 """
963 values = StatTestModel.objects.aggregate(
964 complex_regravgx=RegrAvgX(y=5, x="related_field__integer_field")
965 )
966 self.assertEqual(values, {"complex_regravgx": 1.0})