Debian Codegraph @

tests/expressions_window/tests.py

raw
1 import datetime
2 from decimal import Decimal
3 from unittest import mock
4
5 from django.core.exceptions import FieldError
6 from django.db import NotSupportedError, connection
7 from django.db.models import (
8 Avg,
9 Case,
10 Count,
11 F,
12 IntegerField,
13 Max,
14 Min,
15 OuterRef,
16 Q,
17 RowRange,
18 Subquery,
19 Sum,
20 Value,
21 ValueRange,
22 When,
23 Window,
24 WindowFrame,
25 WindowFrameExclusion,
26 )
27 from django.db.models.fields.json import KeyTextTransform, KeyTransform
28 from django.db.models.functions import (
29 Cast,
30 CumeDist,
31 DenseRank,
32 ExtractYear,
33 FirstValue,
34 Lag,
35 LastValue,
36 Lead,
37 NthValue,
38 Ntile,
39 PercentRank,
40 Rank,
41 RowNumber,
42 Upper,
43 )
44 from django.db.models.lookups import Exact
45 from django.test import SimpleTestCase, TestCase, skipIfDBFeature, skipUnlessDBFeature
46 from django.test.utils import CaptureQueriesContext
47
48 from .models import Classification, Detail, Employee, PastEmployeeDepartment
49
50
51 @skipUnlessDBFeature("supports_over_clause")
52 class WindowFunctionTests(TestCase):
53 @classmethod
54 def setUpTestData(cls):
55 classification = Classification.objects.create()
56 Employee.objects.bulk_create(
57 [
58 Employee(
59 name=e[0],
60 salary=e[1],
61 department=e[2],
62 hire_date=e[3],
63 age=e[4],
64 bonus=Decimal(e[1]) / 400,
65 classification=classification,
66 )
67 for e in [
68 ("Jones", 45000, "Accounting", datetime.datetime(2005, 11, 1), 20),
69 (
70 "Williams",
71 37000,
72 "Accounting",
73 datetime.datetime(2009, 6, 1),
74 20,
75 ),
76 ("Jenson", 45000, "Accounting", datetime.datetime(2008, 4, 1), 20),
77 ("Adams", 50000, "Accounting", datetime.datetime(2013, 7, 1), 50),
78 ("Smith", 55000, "Sales", datetime.datetime(2007, 6, 1), 30),
79 ("Brown", 53000, "Sales", datetime.datetime(2009, 9, 1), 30),
80 ("Johnson", 40000, "Marketing", datetime.datetime(2012, 3, 1), 30),
81 ("Smith", 38000, "Marketing", datetime.datetime(2009, 10, 1), 20),
82 ("Wilkinson", 60000, "IT", datetime.datetime(2011, 3, 1), 40),
83 ("Moore", 34000, "IT", datetime.datetime(2013, 8, 1), 40),
84 ("Miller", 100000, "Management", datetime.datetime(2005, 6, 1), 40),
85 ("Johnson", 80000, "Management", datetime.datetime(2005, 7, 1), 50),
86 ]
87 ]
88 )
89 employees = list(Employee.objects.order_by("pk"))
90 PastEmployeeDepartment.objects.bulk_create(
91 [
92 PastEmployeeDepartment(employee=employees[6], department="Sales"),
93 PastEmployeeDepartment(employee=employees[10], department="IT"),
94 ]
95 )
96
97 def test_dense_rank(self):
98 tests = [
99 ExtractYear(F("hire_date")).asc(),
100 F("hire_date__year").asc(),
101 "hire_date__year",
102 ]
103 for order_by in tests:
104 with self.subTest(order_by=order_by):
105 qs = Employee.objects.annotate(
106 rank=Window(expression=DenseRank(), order_by=order_by),
107 )
108 self.assertQuerySetEqual(
109 qs,
110 [
111 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 1),
112 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 1),
113 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 1),
114 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 2),
115 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 3),
116 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 4),
117 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 4),
118 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 4),
119 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 5),
120 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 6),
121 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 7),
122 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 7),
123 ],
124 lambda entry: (
125 entry.name,
126 entry.salary,
127 entry.department,
128 entry.hire_date,
129 entry.rank,
130 ),
131 ordered=False,
132 )
133
134 def test_department_salary(self):
135 qs = Employee.objects.annotate(
136 department_sum=Window(
137 expression=Sum("salary"),
138 partition_by=F("department"),
139 order_by=[F("hire_date").asc()],
140 )
141 ).order_by("department", "department_sum")
142 self.assertQuerySetEqual(
143 qs,
144 [
145 ("Jones", "Accounting", 45000, 45000),
146 ("Jenson", "Accounting", 45000, 90000),
147 ("Williams", "Accounting", 37000, 127000),
148 ("Adams", "Accounting", 50000, 177000),
149 ("Wilkinson", "IT", 60000, 60000),
150 ("Moore", "IT", 34000, 94000),
151 ("Miller", "Management", 100000, 100000),
152 ("Johnson", "Management", 80000, 180000),
153 ("Smith", "Marketing", 38000, 38000),
154 ("Johnson", "Marketing", 40000, 78000),
155 ("Smith", "Sales", 55000, 55000),
156 ("Brown", "Sales", 53000, 108000),
157 ],
158 lambda entry: (
159 entry.name,
160 entry.department,
161 entry.salary,
162 entry.department_sum,
163 ),
164 )
165
166 def test_rank(self):
167 """
168 Rank the employees based on the year they're were hired. Since there
169 are multiple employees hired in different years, this will contain
170 gaps.
171 """
172 qs = Employee.objects.annotate(
173 rank=Window(
174 expression=Rank(),
175 order_by=F("hire_date__year").asc(),
176 )
177 )
178 self.assertQuerySetEqual(
179 qs,
180 [
181 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 1),
182 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 1),
183 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 1),
184 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 4),
185 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 5),
186 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 6),
187 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 6),
188 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 6),
189 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 9),
190 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 10),
191 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 11),
192 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 11),
193 ],
194 lambda entry: (
195 entry.name,
196 entry.salary,
197 entry.department,
198 entry.hire_date,
199 entry.rank,
200 ),
201 ordered=False,
202 )
203
204 def test_row_number(self):
205 """
206 The row number window function computes the number based on the order
207 in which the tuples were inserted. Depending on the backend,
208
209 Oracle requires an ordering-clause in the Window expression.
210 """
211 qs = Employee.objects.annotate(
212 row_number=Window(
213 expression=RowNumber(),
214 order_by=F("pk").asc(),
215 )
216 ).order_by("pk")
217 self.assertQuerySetEqual(
218 qs,
219 [
220 ("Jones", "Accounting", 1),
221 ("Williams", "Accounting", 2),
222 ("Jenson", "Accounting", 3),
223 ("Adams", "Accounting", 4),
224 ("Smith", "Sales", 5),
225 ("Brown", "Sales", 6),
226 ("Johnson", "Marketing", 7),
227 ("Smith", "Marketing", 8),
228 ("Wilkinson", "IT", 9),
229 ("Moore", "IT", 10),
230 ("Miller", "Management", 11),
231 ("Johnson", "Management", 12),
232 ],
233 lambda entry: (entry.name, entry.department, entry.row_number),
234 )
235
236 def test_row_number_no_ordering(self):
237 """
238 The row number window function computes the number based on the order
239 in which the tuples were inserted.
240 """
241 # Add a default ordering for consistent results across databases.
242 qs = Employee.objects.annotate(
243 row_number=Window(
244 expression=RowNumber(),
245 )
246 ).order_by("pk")
247 self.assertQuerySetEqual(
248 qs,
249 [
250 ("Jones", "Accounting", 1),
251 ("Williams", "Accounting", 2),
252 ("Jenson", "Accounting", 3),
253 ("Adams", "Accounting", 4),
254 ("Smith", "Sales", 5),
255 ("Brown", "Sales", 6),
256 ("Johnson", "Marketing", 7),
257 ("Smith", "Marketing", 8),
258 ("Wilkinson", "IT", 9),
259 ("Moore", "IT", 10),
260 ("Miller", "Management", 11),
261 ("Johnson", "Management", 12),
262 ],
263 lambda entry: (entry.name, entry.department, entry.row_number),
264 )
265
266 def test_avg_salary_department(self):
267 qs = Employee.objects.annotate(
268 avg_salary=Window(
269 expression=Avg("salary"),
270 order_by=F("department").asc(),
271 partition_by="department",
272 )
273 ).order_by("department", "-salary", "name")
274 self.assertQuerySetEqual(
275 qs,
276 [
277 ("Adams", 50000, "Accounting", 44250.00),
278 ("Jenson", 45000, "Accounting", 44250.00),
279 ("Jones", 45000, "Accounting", 44250.00),
280 ("Williams", 37000, "Accounting", 44250.00),
281 ("Wilkinson", 60000, "IT", 47000.00),
282 ("Moore", 34000, "IT", 47000.00),
283 ("Miller", 100000, "Management", 90000.00),
284 ("Johnson", 80000, "Management", 90000.00),
285 ("Johnson", 40000, "Marketing", 39000.00),
286 ("Smith", 38000, "Marketing", 39000.00),
287 ("Smith", 55000, "Sales", 54000.00),
288 ("Brown", 53000, "Sales", 54000.00),
289 ],
290 transform=lambda row: (
291 row.name,
292 row.salary,
293 row.department,
294 row.avg_salary,
295 ),
296 )
297
298 def test_lag(self):
299 """
300 Compute the difference between an employee's salary and the next
301 highest salary in the employee's department. Return None if the
302 employee has the lowest salary.
303 """
304 qs = Employee.objects.annotate(
305 lag=Window(
306 expression=Lag(expression="salary", offset=1),
307 partition_by=F("department"),
308 order_by=[F("salary").asc(), F("name").asc()],
309 )
310 ).order_by("department", F("salary").asc(), F("name").asc())
311 self.assertQuerySetEqual(
312 qs,
313 [
314 ("Williams", 37000, "Accounting", None),
315 ("Jenson", 45000, "Accounting", 37000),
316 ("Jones", 45000, "Accounting", 45000),
317 ("Adams", 50000, "Accounting", 45000),
318 ("Moore", 34000, "IT", None),
319 ("Wilkinson", 60000, "IT", 34000),
320 ("Johnson", 80000, "Management", None),
321 ("Miller", 100000, "Management", 80000),
322 ("Smith", 38000, "Marketing", None),
323 ("Johnson", 40000, "Marketing", 38000),
324 ("Brown", 53000, "Sales", None),
325 ("Smith", 55000, "Sales", 53000),
326 ],
327 transform=lambda row: (row.name, row.salary, row.department, row.lag),
328 )
329
330 def test_lag_decimalfield(self):
331 qs = Employee.objects.annotate(
332 lag=Window(
333 expression=Lag(expression="bonus", offset=1),
334 partition_by=F("department"),
335 order_by=[F("bonus").asc(), F("name").asc()],
336 )
337 ).order_by("department", F("bonus").asc(), F("name").asc())
338 self.assertQuerySetEqual(
339 qs,
340 [
341 ("Williams", 92.5, "Accounting", None),
342 ("Jenson", 112.5, "Accounting", 92.5),
343 ("Jones", 112.5, "Accounting", 112.5),
344 ("Adams", 125, "Accounting", 112.5),
345 ("Moore", 85, "IT", None),
346 ("Wilkinson", 150, "IT", 85),
347 ("Johnson", 200, "Management", None),
348 ("Miller", 250, "Management", 200),
349 ("Smith", 95, "Marketing", None),
350 ("Johnson", 100, "Marketing", 95),
351 ("Brown", 132.5, "Sales", None),
352 ("Smith", 137.5, "Sales", 132.5),
353 ],
354 transform=lambda row: (row.name, row.bonus, row.department, row.lag),
355 )
356
357 def test_order_by_decimalfield(self):
358 qs = Employee.objects.annotate(
359 rank=Window(expression=Rank(), order_by="bonus")
360 ).order_by("-bonus", "id")
361 self.assertQuerySetEqual(
362 qs,
363 [
364 ("Miller", 250.0, 12),
365 ("Johnson", 200.0, 11),
366 ("Wilkinson", 150.0, 10),
367 ("Smith", 137.5, 9),
368 ("Brown", 132.5, 8),
369 ("Adams", 125.0, 7),
370 ("Jones", 112.5, 5),
371 ("Jenson", 112.5, 5),
372 ("Johnson", 100.0, 4),
373 ("Smith", 95.0, 3),
374 ("Williams", 92.5, 2),
375 ("Moore", 85.0, 1),
376 ],
377 transform=lambda row: (row.name, float(row.bonus), row.rank),
378 )
379
380 def test_first_value(self):
381 qs = Employee.objects.annotate(
382 first_value=Window(
383 expression=FirstValue("salary"),
384 partition_by=F("department"),
385 order_by=F("hire_date").asc(),
386 )
387 ).order_by("department", "hire_date")
388 self.assertQuerySetEqual(
389 qs,
390 [
391 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 45000),
392 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 45000),
393 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 45000),
394 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 45000),
395 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 60000),
396 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 60000),
397 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 100000),
398 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 100000),
399 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 38000),
400 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 38000),
401 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 55000),
402 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 55000),
403 ],
404 lambda row: (
405 row.name,
406 row.salary,
407 row.department,
408 row.hire_date,
409 row.first_value,
410 ),
411 )
412
413 def test_last_value(self):
414 qs = Employee.objects.annotate(
415 last_value=Window(
416 expression=LastValue("hire_date"),
417 partition_by=F("department"),
418 order_by=F("hire_date").asc(),
419 )
420 )
421 self.assertQuerySetEqual(
422 qs,
423 [
424 (
425 "Adams",
426 "Accounting",
427 datetime.date(2013, 7, 1),
428 50000,
429 datetime.date(2013, 7, 1),
430 ),
431 (
432 "Jenson",
433 "Accounting",
434 datetime.date(2008, 4, 1),
435 45000,
436 datetime.date(2008, 4, 1),
437 ),
438 (
439 "Jones",
440 "Accounting",
441 datetime.date(2005, 11, 1),
442 45000,
443 datetime.date(2005, 11, 1),
444 ),
445 (
446 "Williams",
447 "Accounting",
448 datetime.date(2009, 6, 1),
449 37000,
450 datetime.date(2009, 6, 1),
451 ),
452 (
453 "Moore",
454 "IT",
455 datetime.date(2013, 8, 1),
456 34000,
457 datetime.date(2013, 8, 1),
458 ),
459 (
460 "Wilkinson",
461 "IT",
462 datetime.date(2011, 3, 1),
463 60000,
464 datetime.date(2011, 3, 1),
465 ),
466 (
467 "Miller",
468 "Management",
469 datetime.date(2005, 6, 1),
470 100000,
471 datetime.date(2005, 6, 1),
472 ),
473 (
474 "Johnson",
475 "Management",
476 datetime.date(2005, 7, 1),
477 80000,
478 datetime.date(2005, 7, 1),
479 ),
480 (
481 "Johnson",
482 "Marketing",
483 datetime.date(2012, 3, 1),
484 40000,
485 datetime.date(2012, 3, 1),
486 ),
487 (
488 "Smith",
489 "Marketing",
490 datetime.date(2009, 10, 1),
491 38000,
492 datetime.date(2009, 10, 1),
493 ),
494 (
495 "Brown",
496 "Sales",
497 datetime.date(2009, 9, 1),
498 53000,
499 datetime.date(2009, 9, 1),
500 ),
501 (
502 "Smith",
503 "Sales",
504 datetime.date(2007, 6, 1),
505 55000,
506 datetime.date(2007, 6, 1),
507 ),
508 ],
509 transform=lambda row: (
510 row.name,
511 row.department,
512 row.hire_date,
513 row.salary,
514 row.last_value,
515 ),
516 ordered=False,
517 )
518
519 def test_function_list_of_values(self):
520 qs = (
521 Employee.objects.annotate(
522 lead=Window(
523 expression=Lead(expression="salary"),
524 order_by=[F("hire_date").asc(), F("name").desc()],
525 partition_by="department",
526 )
527 )
528 .values_list("name", "salary", "department", "hire_date", "lead")
529 .order_by("department", F("hire_date").asc(), F("name").desc())
530 )
531 self.assertNotIn("GROUP BY", str(qs.query))
532 self.assertSequenceEqual(
533 qs,
534 [
535 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 45000),
536 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 37000),
537 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 50000),
538 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), None),
539 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 34000),
540 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), None),
541 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 80000),
542 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), None),
543 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 40000),
544 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), None),
545 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 53000),
546 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), None),
547 ],
548 )
549
550 def test_min_department(self):
551 """An alternative way to specify a query for FirstValue."""
552 qs = Employee.objects.annotate(
553 min_salary=Window(
554 expression=Min("salary"),
555 partition_by=F("department"),
556 order_by=[F("salary").asc(), F("name").asc()],
557 )
558 ).order_by("department", "salary", "name")
559 self.assertQuerySetEqual(
560 qs,
561 [
562 ("Williams", "Accounting", 37000, 37000),
563 ("Jenson", "Accounting", 45000, 37000),
564 ("Jones", "Accounting", 45000, 37000),
565 ("Adams", "Accounting", 50000, 37000),
566 ("Moore", "IT", 34000, 34000),
567 ("Wilkinson", "IT", 60000, 34000),
568 ("Johnson", "Management", 80000, 80000),
569 ("Miller", "Management", 100000, 80000),
570 ("Smith", "Marketing", 38000, 38000),
571 ("Johnson", "Marketing", 40000, 38000),
572 ("Brown", "Sales", 53000, 53000),
573 ("Smith", "Sales", 55000, 53000),
574 ],
575 lambda row: (row.name, row.department, row.salary, row.min_salary),
576 )
577
578 def test_max_per_year(self):
579 """
580 Find the maximum salary awarded in the same year as the
581 employee was hired, regardless of the department.
582 """
583 qs = Employee.objects.annotate(
584 max_salary_year=Window(
585 expression=Max("salary"),
586 order_by=ExtractYear("hire_date").asc(),
587 partition_by=ExtractYear("hire_date"),
588 )
589 ).order_by(ExtractYear("hire_date"), "salary")
590 self.assertQuerySetEqual(
591 qs,
592 [
593 ("Jones", "Accounting", 45000, 2005, 100000),
594 ("Johnson", "Management", 80000, 2005, 100000),
595 ("Miller", "Management", 100000, 2005, 100000),
596 ("Smith", "Sales", 55000, 2007, 55000),
597 ("Jenson", "Accounting", 45000, 2008, 45000),
598 ("Williams", "Accounting", 37000, 2009, 53000),
599 ("Smith", "Marketing", 38000, 2009, 53000),
600 ("Brown", "Sales", 53000, 2009, 53000),
601 ("Wilkinson", "IT", 60000, 2011, 60000),
602 ("Johnson", "Marketing", 40000, 2012, 40000),
603 ("Moore", "IT", 34000, 2013, 50000),
604 ("Adams", "Accounting", 50000, 2013, 50000),
605 ],
606 lambda row: (
607 row.name,
608 row.department,
609 row.salary,
610 row.hire_date.year,
611 row.max_salary_year,
612 ),
613 )
614
615 def test_cume_dist(self):
616 """
617 Compute the cumulative distribution for the employees based on the
618 salary in increasing order. Equal to rank/total number of rows (12).
619 """
620 qs = Employee.objects.annotate(
621 cume_dist=Window(
622 expression=CumeDist(),
623 order_by=F("salary").asc(),
624 )
625 ).order_by("salary", "name")
626 # Round result of cume_dist because Oracle uses greater precision.
627 self.assertQuerySetEqual(
628 qs,
629 [
630 ("Moore", "IT", 34000, 0.0833333333),
631 ("Williams", "Accounting", 37000, 0.1666666667),
632 ("Smith", "Marketing", 38000, 0.25),
633 ("Johnson", "Marketing", 40000, 0.3333333333),
634 ("Jenson", "Accounting", 45000, 0.5),
635 ("Jones", "Accounting", 45000, 0.5),
636 ("Adams", "Accounting", 50000, 0.5833333333),
637 ("Brown", "Sales", 53000, 0.6666666667),
638 ("Smith", "Sales", 55000, 0.75),
639 ("Wilkinson", "IT", 60000, 0.8333333333),
640 ("Johnson", "Management", 80000, 0.9166666667),
641 ("Miller", "Management", 100000, 1),
642 ],
643 lambda row: (
644 row.name,
645 row.department,
646 row.salary,
647 round(row.cume_dist, 10),
648 ),
649 )
650
651 def test_nthvalue(self):
652 qs = Employee.objects.annotate(
653 nth_value=Window(
654 expression=NthValue(expression="salary", nth=2),
655 order_by=[F("hire_date").asc(), F("name").desc()],
656 partition_by=F("department"),
657 )
658 ).order_by("department", "hire_date", "name")
659 self.assertQuerySetEqual(
660 qs,
661 [
662 ("Jones", "Accounting", datetime.date(2005, 11, 1), 45000, None),
663 ("Jenson", "Accounting", datetime.date(2008, 4, 1), 45000, 45000),
664 ("Williams", "Accounting", datetime.date(2009, 6, 1), 37000, 45000),
665 ("Adams", "Accounting", datetime.date(2013, 7, 1), 50000, 45000),
666 ("Wilkinson", "IT", datetime.date(2011, 3, 1), 60000, None),
667 ("Moore", "IT", datetime.date(2013, 8, 1), 34000, 34000),
668 ("Miller", "Management", datetime.date(2005, 6, 1), 100000, None),
669 ("Johnson", "Management", datetime.date(2005, 7, 1), 80000, 80000),
670 ("Smith", "Marketing", datetime.date(2009, 10, 1), 38000, None),
671 ("Johnson", "Marketing", datetime.date(2012, 3, 1), 40000, 40000),
672 ("Smith", "Sales", datetime.date(2007, 6, 1), 55000, None),
673 ("Brown", "Sales", datetime.date(2009, 9, 1), 53000, 53000),
674 ],
675 lambda row: (
676 row.name,
677 row.department,
678 row.hire_date,
679 row.salary,
680 row.nth_value,
681 ),
682 )
683
684 def test_lead(self):
685 """
686 Determine what the next person hired in the same department makes.
687 Because the dataset is ambiguous, the name is also part of the
688 ordering clause. No default is provided, so None/NULL should be
689 returned.
690 """
691 qs = Employee.objects.annotate(
692 lead=Window(
693 expression=Lead(expression="salary"),
694 order_by=[F("hire_date").asc(), F("name").desc()],
695 partition_by="department",
696 )
697 ).order_by("department", F("hire_date").asc(), F("name").desc())
698 self.assertQuerySetEqual(
699 qs,
700 [
701 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 45000),
702 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 37000),
703 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 50000),
704 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), None),
705 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 34000),
706 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), None),
707 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 80000),
708 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), None),
709 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 40000),
710 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), None),
711 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 53000),
712 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), None),
713 ],
714 transform=lambda row: (
715 row.name,
716 row.salary,
717 row.department,
718 row.hire_date,
719 row.lead,
720 ),
721 )
722
723 def test_lead_offset(self):
724 """
725 Determine what the person hired after someone makes. Due to
726 ambiguity, the name is also included in the ordering.
727 """
728 qs = Employee.objects.annotate(
729 lead=Window(
730 expression=Lead("salary", offset=2),
731 partition_by="department",
732 order_by=F("hire_date").asc(),
733 )
734 )
735 self.assertQuerySetEqual(
736 qs,
737 [
738 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 37000),
739 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 50000),
740 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), None),
741 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), None),
742 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), None),
743 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), None),
744 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), None),
745 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), None),
746 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), None),
747 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), None),
748 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), None),
749 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), None),
750 ],
751 transform=lambda row: (
752 row.name,
753 row.salary,
754 row.department,
755 row.hire_date,
756 row.lead,
757 ),
758 ordered=False,
759 )
760
761 @skipUnlessDBFeature("supports_default_in_lead_lag")
762 def test_lead_default(self):
763 qs = Employee.objects.annotate(
764 lead_default=Window(
765 expression=Lead(expression="salary", offset=5, default=60000),
766 partition_by=F("department"),
767 order_by=F("department").asc(),
768 )
769 )
770 self.assertEqual(
771 list(qs.values_list("lead_default", flat=True).distinct()), [60000]
772 )
773
774 def test_ntile(self):
775 """
776 Compute the group for each of the employees across the entire company,
777 based on how high the salary is for them. There are twelve employees
778 so it divides evenly into four groups.
779 """
780 qs = Employee.objects.annotate(
781 ntile=Window(
782 expression=Ntile(num_buckets=4),
783 order_by="-salary",
784 )
785 ).order_by("ntile", "-salary", "name")
786 self.assertQuerySetEqual(
787 qs,
788 [
789 ("Miller", "Management", 100000, 1),
790 ("Johnson", "Management", 80000, 1),
791 ("Wilkinson", "IT", 60000, 1),
792 ("Smith", "Sales", 55000, 2),
793 ("Brown", "Sales", 53000, 2),
794 ("Adams", "Accounting", 50000, 2),
795 ("Jenson", "Accounting", 45000, 3),
796 ("Jones", "Accounting", 45000, 3),
797 ("Johnson", "Marketing", 40000, 3),
798 ("Smith", "Marketing", 38000, 4),
799 ("Williams", "Accounting", 37000, 4),
800 ("Moore", "IT", 34000, 4),
801 ],
802 lambda x: (x.name, x.department, x.salary, x.ntile),
803 )
804
805 def test_percent_rank(self):
806 """
807 Calculate the percentage rank of the employees across the entire
808 company based on salary and name (in case of ambiguity).
809 """
810 qs = Employee.objects.annotate(
811 percent_rank=Window(
812 expression=PercentRank(),
813 order_by=[F("salary").asc(), F("name").asc()],
814 )
815 ).order_by("percent_rank")
816 # Round to account for precision differences among databases.
817 self.assertQuerySetEqual(
818 qs,
819 [
820 ("Moore", "IT", 34000, 0.0),
821 ("Williams", "Accounting", 37000, 0.0909090909),
822 ("Smith", "Marketing", 38000, 0.1818181818),
823 ("Johnson", "Marketing", 40000, 0.2727272727),
824 ("Jenson", "Accounting", 45000, 0.3636363636),
825 ("Jones", "Accounting", 45000, 0.4545454545),
826 ("Adams", "Accounting", 50000, 0.5454545455),
827 ("Brown", "Sales", 53000, 0.6363636364),
828 ("Smith", "Sales", 55000, 0.7272727273),
829 ("Wilkinson", "IT", 60000, 0.8181818182),
830 ("Johnson", "Management", 80000, 0.9090909091),
831 ("Miller", "Management", 100000, 1.0),
832 ],
833 transform=lambda row: (
834 row.name,
835 row.department,
836 row.salary,
837 round(row.percent_rank, 10),
838 ),
839 )
840
841 def test_nth_returns_null(self):
842 """
843 Find the nth row of the data set. None is returned since there are
844 fewer than 20 rows in the test data.
845 """
846 qs = Employee.objects.annotate(
847 nth_value=Window(
848 expression=NthValue("salary", nth=20), order_by=F("salary").asc()
849 )
850 )
851 self.assertEqual(
852 list(qs.values_list("nth_value", flat=True).distinct()), [None]
853 )
854
855 def test_multiple_partitioning(self):
856 """
857 Find the maximum salary for each department for people hired in the
858 same year.
859 """
860 qs = Employee.objects.annotate(
861 max=Window(
862 expression=Max("salary"),
863 partition_by=[F("department"), F("hire_date__year")],
864 ),
865 past_department_count=Count("past_departments"),
866 ).order_by("department", "hire_date", "name")
867 self.assertQuerySetEqual(
868 qs,
869 [
870 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 45000, 0),
871 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 45000, 0),
872 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 37000, 0),
873 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 50000, 0),
874 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 60000, 0),
875 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 34000, 0),
876 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 100000, 1),
877 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 100000, 0),
878 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 38000, 0),
879 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 40000, 1),
880 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 55000, 0),
881 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 53000, 0),
882 ],
883 transform=lambda row: (
884 row.name,
885 row.salary,
886 row.department,
887 row.hire_date,
888 row.max,
889 row.past_department_count,
890 ),
891 )
892
893 def test_multiple_ordering(self):
894 """
895 Accumulate the salaries over the departments based on hire_date.
896 If two people were hired on the same date in the same department, the
897 ordering clause will render a different result for those people.
898 """
899 qs = Employee.objects.annotate(
900 sum=Window(
901 expression=Sum("salary"),
902 partition_by="department",
903 order_by=[F("hire_date").asc(), F("name").asc()],
904 )
905 ).order_by("department", "sum")
906 self.assertQuerySetEqual(
907 qs,
908 [
909 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 45000),
910 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 90000),
911 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 127000),
912 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 177000),
913 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 60000),
914 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 94000),
915 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 100000),
916 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 180000),
917 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 38000),
918 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 78000),
919 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 55000),
920 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 108000),
921 ],
922 transform=lambda row: (
923 row.name,
924 row.salary,
925 row.department,
926 row.hire_date,
927 row.sum,
928 ),
929 )
930
931 def test_empty_ordering(self):
932 """
933 Explicit empty ordering makes little sense but it is something that
934 was historically allowed.
935 """
936 qs = Employee.objects.annotate(
937 sum=Window(
938 expression=Sum("salary"),
939 partition_by="department",
940 order_by=[],
941 )
942 ).order_by("department", "sum")
943 self.assertEqual(len(qs), 12)
944
945 def test_related_ordering_with_count(self):
946 qs = Employee.objects.annotate(
947 department_sum=Window(
948 expression=Sum("salary"),
949 partition_by=F("department"),
950 order_by=["classification__code"],
951 )
952 )
953 self.assertEqual(qs.count(), 12)
954
955 def test_filter(self):
956 qs = Employee.objects.annotate(
957 department_salary_rank=Window(
958 Rank(), partition_by="department", order_by="-salary"
959 ),
960 department_avg_age_diff=(
961 Window(Avg("age"), partition_by="department") - F("age")
962 ),
963 ).order_by("department", "name")
964 # Direct window reference.
965 self.assertQuerySetEqual(
966 qs.filter(department_salary_rank=1),
967 ["Adams", "Wilkinson", "Miller", "Johnson", "Smith"],
968 lambda employee: employee.name,
969 )
970 # Through a combined expression containing a window.
971 self.assertQuerySetEqual(
972 qs.filter(department_avg_age_diff__gt=0),
973 ["Jenson", "Jones", "Williams", "Miller", "Smith"],
974 lambda employee: employee.name,
975 )
976 # Intersection of multiple windows.
977 self.assertQuerySetEqual(
978 qs.filter(department_salary_rank=1, department_avg_age_diff__gt=0),
979 ["Miller"],
980 lambda employee: employee.name,
981 )
982 # Union of multiple windows.
983 self.assertQuerySetEqual(
984 qs.filter(Q(department_salary_rank=1) | Q(department_avg_age_diff__gt=0)),
985 [
986 "Adams",
987 "Jenson",
988 "Jones",
989 "Williams",
990 "Wilkinson",
991 "Miller",
992 "Johnson",
993 "Smith",
994 "Smith",
995 ],
996 lambda employee: employee.name,
997 )
998
999 def test_filter_conditional_annotation(self):
1000 qs = (
1001 Employee.objects.annotate(
1002 rank=Window(Rank(), partition_by="department", order_by="-salary"),
1003 case_first_rank=Case(
1004 When(rank=1, then=True),
1005 default=False,
1006 ),
1007 q_first_rank=Q(rank=1),
1008 )
1009 .order_by("name")
1010 .values_list("name", flat=True)
1011 )
1012 for annotation in ["case_first_rank", "q_first_rank"]:
1013 with self.subTest(annotation=annotation):
1014 self.assertSequenceEqual(
1015 qs.filter(**{annotation: True}),
1016 ["Adams", "Johnson", "Miller", "Smith", "Wilkinson"],
1017 )
1018
1019 def test_filter_conditional_expression(self):
1020 qs = (
1021 Employee.objects.filter(
1022 Exact(Window(Rank(), partition_by="department", order_by="-salary"), 1)
1023 )
1024 .order_by("name")
1025 .values_list("name", flat=True)
1026 )
1027 self.assertSequenceEqual(
1028 qs, ["Adams", "Johnson", "Miller", "Smith", "Wilkinson"]
1029 )
1030
1031 def test_filter_column_ref_rhs(self):
1032 qs = (
1033 Employee.objects.annotate(
1034 max_dept_salary=Window(Max("salary"), partition_by="department")
1035 )
1036 .filter(max_dept_salary=F("salary"))
1037 .order_by("name")
1038 .values_list("name", flat=True)
1039 )
1040 self.assertSequenceEqual(
1041 qs, ["Adams", "Johnson", "Miller", "Smith", "Wilkinson"]
1042 )
1043
1044 def test_filter_values(self):
1045 qs = (
1046 Employee.objects.annotate(
1047 department_salary_rank=Window(
1048 Rank(), partition_by="department", order_by="-salary"
1049 ),
1050 )
1051 .order_by("department", "name")
1052 .values_list(Upper("name"), flat=True)
1053 )
1054 self.assertSequenceEqual(
1055 qs.filter(department_salary_rank=1),
1056 ["ADAMS", "WILKINSON", "MILLER", "JOHNSON", "SMITH"],
1057 )
1058
1059 def test_filter_alias(self):
1060 qs = Employee.objects.alias(
1061 department_avg_age_diff=(
1062 Window(Avg("age"), partition_by="department") - F("age")
1063 ),
1064 ).order_by("department", "name")
1065 self.assertQuerySetEqual(
1066 qs.filter(department_avg_age_diff__gt=0),
1067 ["Jenson", "Jones", "Williams", "Miller", "Smith"],
1068 lambda employee: employee.name,
1069 )
1070
1071 def test_filter_select_related(self):
1072 qs = (
1073 Employee.objects.alias(
1074 department_avg_age_diff=(
1075 Window(Avg("age"), partition_by="department") - F("age")
1076 ),
1077 )
1078 .select_related("classification")
1079 .filter(department_avg_age_diff__gt=0)
1080 .order_by("department", "name")
1081 )
1082 self.assertQuerySetEqual(
1083 qs,
1084 ["Jenson", "Jones", "Williams", "Miller", "Smith"],
1085 lambda employee: employee.name,
1086 )
1087 with self.assertNumQueries(0):
1088 qs[0].classification
1089
1090 def test_exclude(self):
1091 qs = Employee.objects.annotate(
1092 department_salary_rank=Window(
1093 Rank(), partition_by="department", order_by="-salary"
1094 ),
1095 department_avg_age_diff=(
1096 Window(Avg("age"), partition_by="department") - F("age")
1097 ),
1098 ).order_by("department", "name")
1099 # Direct window reference.
1100 self.assertQuerySetEqual(
1101 qs.exclude(department_salary_rank__gt=1),
1102 ["Adams", "Wilkinson", "Miller", "Johnson", "Smith"],
1103 lambda employee: employee.name,
1104 )
1105 # Through a combined expression containing a window.
1106 self.assertQuerySetEqual(
1107 qs.exclude(department_avg_age_diff__lte=0),
1108 ["Jenson", "Jones", "Williams", "Miller", "Smith"],
1109 lambda employee: employee.name,
1110 )
1111 # Union of multiple windows.
1112 self.assertQuerySetEqual(
1113 qs.exclude(
1114 Q(department_salary_rank__gt=1) | Q(department_avg_age_diff__lte=0)
1115 ),
1116 ["Miller"],
1117 lambda employee: employee.name,
1118 )
1119 # Intersection of multiple windows.
1120 self.assertQuerySetEqual(
1121 qs.exclude(department_salary_rank__gt=1, department_avg_age_diff__lte=0),
1122 [
1123 "Adams",
1124 "Jenson",
1125 "Jones",
1126 "Williams",
1127 "Wilkinson",
1128 "Miller",
1129 "Johnson",
1130 "Smith",
1131 "Smith",
1132 ],
1133 lambda employee: employee.name,
1134 )
1135
1136 def test_heterogeneous_filter(self):
1137 qs = (
1138 Employee.objects.annotate(
1139 department_salary_rank=Window(
1140 Rank(), partition_by="department", order_by="-salary"
1141 ),
1142 )
1143 .order_by("name")
1144 .values_list("name", flat=True)
1145 )
1146 # Heterogeneous filter between window function and aggregates pushes
1147 # the WHERE clause to the QUALIFY outer query.
1148 self.assertSequenceEqual(
1149 qs.filter(
1150 department_salary_rank=1, department__in=["Accounting", "Management"]
1151 ),
1152 ["Adams", "Miller"],
1153 )
1154 self.assertSequenceEqual(
1155 qs.filter(
1156 Q(department_salary_rank=1)
1157 | Q(department__in=["Accounting", "Management"])
1158 ),
1159 [
1160 "Adams",
1161 "Jenson",
1162 "Johnson",
1163 "Johnson",
1164 "Jones",
1165 "Miller",
1166 "Smith",
1167 "Wilkinson",
1168 "Williams",
1169 ],
1170 )
1171 # Heterogeneous filter between window function and aggregates pushes
1172 # the HAVING clause to the QUALIFY outer query.
1173 qs = qs.annotate(past_department_count=Count("past_departments"))
1174 self.assertSequenceEqual(
1175 qs.filter(department_salary_rank=1, past_department_count__gte=1),
1176 ["Johnson", "Miller"],
1177 )
1178 self.assertSequenceEqual(
1179 qs.filter(Q(department_salary_rank=1) | Q(past_department_count__gte=1)),
1180 ["Adams", "Johnson", "Miller", "Smith", "Wilkinson"],
1181 )
1182
1183 def test_limited_filter(self):
1184 """
1185 A query filtering against a window function have its limit applied
1186 after window filtering takes place.
1187 """
1188 self.assertQuerySetEqual(
1189 Employee.objects.annotate(
1190 department_salary_rank=Window(
1191 Rank(), partition_by="department", order_by="-salary"
1192 )
1193 )
1194 .filter(department_salary_rank=1)
1195 .order_by("department")[0:3],
1196 ["Adams", "Wilkinson", "Miller"],
1197 lambda employee: employee.name,
1198 )
1199
1200 def test_filter_count(self):
1201 with CaptureQueriesContext(connection) as ctx:
1202 self.assertEqual(
1203 Employee.objects.annotate(
1204 department_salary_rank=Window(
1205 Rank(), partition_by="department", order_by="-salary"
1206 )
1207 )
1208 .filter(department_salary_rank=1)
1209 .count(),
1210 5,
1211 )
1212 self.assertEqual(len(ctx.captured_queries), 1)
1213 sql = ctx.captured_queries[0]["sql"].lower()
1214 self.assertEqual(sql.count("select"), 3)
1215 self.assertNotIn("group by", sql)
1216
1217 @skipUnlessDBFeature("supports_frame_range_fixed_distance")
1218 def test_range_n_preceding_and_following(self):
1219 qs = Employee.objects.annotate(
1220 sum=Window(
1221 expression=Sum("salary"),
1222 order_by=F("salary").asc(),
1223 partition_by="department",
1224 frame=ValueRange(start=-2, end=2),
1225 )
1226 )
1227 self.assertIn("RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING", str(qs.query))
1228 self.assertQuerySetEqual(
1229 qs,
1230 [
1231 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 37000),
1232 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 90000),
1233 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 90000),
1234 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 50000),
1235 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 53000),
1236 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 55000),
1237 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 40000),
1238 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 38000),
1239 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 60000),
1240 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 34000),
1241 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 100000),
1242 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 80000),
1243 ],
1244 transform=lambda row: (
1245 row.name,
1246 row.salary,
1247 row.department,
1248 row.hire_date,
1249 row.sum,
1250 ),
1251 ordered=False,
1252 )
1253
1254 @skipUnlessDBFeature(
1255 "supports_frame_exclusion", "supports_frame_range_fixed_distance"
1256 )
1257 def test_range_exclude_current(self):
1258 qs = Employee.objects.annotate(
1259 sum=Window(
1260 expression=Sum("salary"),
1261 order_by=F("salary").asc(),
1262 partition_by="department",
1263 frame=ValueRange(end=2, exclusion=WindowFrameExclusion.CURRENT_ROW),
1264 )
1265 ).order_by("department", "salary")
1266 self.assertIn(
1267 "RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW",
1268 str(qs.query),
1269 )
1270 self.assertQuerySetEqual(
1271 qs,
1272 [
1273 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), None),
1274 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 82000),
1275 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 82000),
1276 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 127000),
1277 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), None),
1278 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 34000),
1279 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), None),
1280 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 80000),
1281 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), None),
1282 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 38000),
1283 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), None),
1284 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 53000),
1285 ],
1286 transform=lambda row: (
1287 row.name,
1288 row.salary,
1289 row.department,
1290 row.hire_date,
1291 row.sum,
1292 ),
1293 )
1294
1295 def test_range_unbound(self):
1296 """A query with RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING."""
1297 qs = Employee.objects.annotate(
1298 sum=Window(
1299 expression=Sum("salary"),
1300 partition_by="age",
1301 order_by=[F("age").asc()],
1302 frame=ValueRange(start=None, end=None),
1303 )
1304 ).order_by("department", "hire_date", "name")
1305 self.assertIn(
1306 "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING", str(qs.query)
1307 )
1308 self.assertQuerySetEqual(
1309 qs,
1310 [
1311 ("Jones", "Accounting", 45000, datetime.date(2005, 11, 1), 165000),
1312 ("Jenson", "Accounting", 45000, datetime.date(2008, 4, 1), 165000),
1313 ("Williams", "Accounting", 37000, datetime.date(2009, 6, 1), 165000),
1314 ("Adams", "Accounting", 50000, datetime.date(2013, 7, 1), 130000),
1315 ("Wilkinson", "IT", 60000, datetime.date(2011, 3, 1), 194000),
1316 ("Moore", "IT", 34000, datetime.date(2013, 8, 1), 194000),
1317 ("Miller", "Management", 100000, datetime.date(2005, 6, 1), 194000),
1318 ("Johnson", "Management", 80000, datetime.date(2005, 7, 1), 130000),
1319 ("Smith", "Marketing", 38000, datetime.date(2009, 10, 1), 165000),
1320 ("Johnson", "Marketing", 40000, datetime.date(2012, 3, 1), 148000),
1321 ("Smith", "Sales", 55000, datetime.date(2007, 6, 1), 148000),
1322 ("Brown", "Sales", 53000, datetime.date(2009, 9, 1), 148000),
1323 ],
1324 transform=lambda row: (
1325 row.name,
1326 row.department,
1327 row.salary,
1328 row.hire_date,
1329 row.sum,
1330 ),
1331 )
1332
1333 def test_subquery_row_range_rank(self):
1334 qs = Employee.objects.annotate(
1335 highest_avg_salary_date=Subquery(
1336 Employee.objects.filter(
1337 department=OuterRef("department"),
1338 )
1339 .annotate(
1340 avg_salary=Window(
1341 expression=Avg("salary"),
1342 order_by=[F("hire_date").asc()],
1343 frame=RowRange(start=-1, end=1),
1344 ),
1345 )
1346 .order_by("-avg_salary", "hire_date")
1347 .values("hire_date")[:1],
1348 ),
1349 ).order_by("department", "name")
1350 self.assertQuerySetEqual(
1351 qs,
1352 [
1353 ("Adams", "Accounting", datetime.date(2005, 11, 1)),
1354 ("Jenson", "Accounting", datetime.date(2005, 11, 1)),
1355 ("Jones", "Accounting", datetime.date(2005, 11, 1)),
1356 ("Williams", "Accounting", datetime.date(2005, 11, 1)),
1357 ("Moore", "IT", datetime.date(2011, 3, 1)),
1358 ("Wilkinson", "IT", datetime.date(2011, 3, 1)),
1359 ("Johnson", "Management", datetime.date(2005, 6, 1)),
1360 ("Miller", "Management", datetime.date(2005, 6, 1)),
1361 ("Johnson", "Marketing", datetime.date(2009, 10, 1)),
1362 ("Smith", "Marketing", datetime.date(2009, 10, 1)),
1363 ("Brown", "Sales", datetime.date(2007, 6, 1)),
1364 ("Smith", "Sales", datetime.date(2007, 6, 1)),
1365 ],
1366 transform=lambda row: (
1367 row.name,
1368 row.department,
1369 row.highest_avg_salary_date,
1370 ),
1371 )
1372
1373 @skipUnlessDBFeature("supports_frame_exclusion")
1374 def test_row_range_rank_exclude_current_row(self):
1375 qs = Employee.objects.annotate(
1376 avg_salary_cohort=Window(
1377 expression=Avg("salary"),
1378 order_by=[F("hire_date").asc(), F("name").desc()],
1379 frame=RowRange(
1380 start=-1, end=1, exclusion=WindowFrameExclusion.CURRENT_ROW
1381 ),
1382 )
1383 ).order_by("hire_date")
1384 self.assertIn(
1385 "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW",
1386 str(qs.query),
1387 )
1388 self.assertQuerySetEqual(
1389 qs,
1390 [
1391 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 80000),
1392 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 72500),
1393 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 67500),
1394 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 45000),
1395 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 46000),
1396 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 49000),
1397 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 37500),
1398 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 56500),
1399 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 39000),
1400 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 55000),
1401 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 37000),
1402 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 50000),
1403 ],
1404 transform=lambda row: (
1405 row.name,
1406 row.salary,
1407 row.department,
1408 row.hire_date,
1409 row.avg_salary_cohort,
1410 ),
1411 )
1412
1413 @skipUnlessDBFeature("supports_frame_exclusion")
1414 def test_row_range_rank_exclude_group(self):
1415 qs = Employee.objects.annotate(
1416 avg_salary_cohort=Window(
1417 expression=Avg("salary"),
1418 order_by=[F("hire_date").asc(), F("name").desc()],
1419 frame=RowRange(start=-1, end=1, exclusion=WindowFrameExclusion.GROUP),
1420 )
1421 ).order_by("hire_date")
1422 self.assertIn(
1423 "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP",
1424 str(qs.query),
1425 )
1426 self.assertQuerySetEqual(
1427 qs,
1428 [
1429 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 80000),
1430 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 72500),
1431 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 67500),
1432 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 45000),
1433 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 46000),
1434 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 49000),
1435 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 37500),
1436 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 56500),
1437 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 39000),
1438 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 55000),
1439 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 37000),
1440 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 50000),
1441 ],
1442 transform=lambda row: (
1443 row.name,
1444 row.salary,
1445 row.department,
1446 row.hire_date,
1447 row.avg_salary_cohort,
1448 ),
1449 )
1450
1451 @skipUnlessDBFeature("supports_frame_exclusion")
1452 def test_row_range_rank_exclude_ties(self):
1453 qs = Employee.objects.annotate(
1454 sum_salary_cohort=Window(
1455 expression=Sum("salary"),
1456 order_by=[F("hire_date").asc(), F("name").desc()],
1457 frame=RowRange(start=-1, end=1, exclusion=WindowFrameExclusion.TIES),
1458 )
1459 ).order_by("hire_date")
1460 self.assertIn(
1461 "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES",
1462 str(qs.query),
1463 )
1464 self.assertQuerySetEqual(
1465 qs,
1466 [
1467 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 180000),
1468 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 225000),
1469 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 180000),
1470 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 145000),
1471 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 137000),
1472 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 135000),
1473 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 128000),
1474 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 151000),
1475 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 138000),
1476 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 150000),
1477 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 124000),
1478 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 84000),
1479 ],
1480 transform=lambda row: (
1481 row.name,
1482 row.salary,
1483 row.department,
1484 row.hire_date,
1485 row.sum_salary_cohort,
1486 ),
1487 )
1488
1489 @skipUnlessDBFeature("supports_frame_exclusion")
1490 def test_row_range_rank_exclude_no_others(self):
1491 qs = Employee.objects.annotate(
1492 sum_salary_cohort=Window(
1493 expression=Sum("salary"),
1494 order_by=[F("hire_date").asc(), F("name").desc()],
1495 frame=RowRange(
1496 start=-1, end=1, exclusion=WindowFrameExclusion.NO_OTHERS
1497 ),
1498 )
1499 ).order_by("hire_date")
1500 self.assertIn(
1501 "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS",
1502 str(qs.query),
1503 )
1504 self.assertQuerySetEqual(
1505 qs,
1506 [
1507 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 180000),
1508 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 225000),
1509 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 180000),
1510 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 145000),
1511 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 137000),
1512 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 135000),
1513 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 128000),
1514 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 151000),
1515 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 138000),
1516 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 150000),
1517 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 124000),
1518 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 84000),
1519 ],
1520 transform=lambda row: (
1521 row.name,
1522 row.salary,
1523 row.department,
1524 row.hire_date,
1525 row.sum_salary_cohort,
1526 ),
1527 )
1528
1529 @skipIfDBFeature("supports_frame_exclusion")
1530 def test_unsupported_frame_exclusion_raises_error(self):
1531 msg = "This backend does not support window frame exclusions."
1532 with self.assertRaisesMessage(NotSupportedError, msg):
1533 list(
1534 Employee.objects.annotate(
1535 avg_salary_cohort=Window(
1536 expression=Avg("salary"),
1537 order_by=[F("hire_date").asc(), F("name").desc()],
1538 frame=RowRange(
1539 start=-1, end=1, exclusion=WindowFrameExclusion.CURRENT_ROW
1540 ),
1541 )
1542 )
1543 )
1544
1545 @skipUnlessDBFeature("supports_frame_exclusion")
1546 def test_invalid_frame_exclusion_value_raises_error(self):
1547 msg = "RowRange.exclusion must be a WindowFrameExclusion instance."
1548 with self.assertRaisesMessage(TypeError, msg):
1549 Employee.objects.annotate(
1550 avg_salary_cohort=Window(
1551 expression=Avg("salary"),
1552 order_by=[F("hire_date").asc(), F("name").desc()],
1553 frame=RowRange(start=-1, end=1, exclusion="RUBBISH"),
1554 )
1555 )
1556
1557 def test_row_range_rank(self):
1558 """
1559 A query with ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING.
1560 The resulting sum is the sum of the three next (if they exist) and all
1561 previous rows according to the ordering clause.
1562 """
1563 qs = Employee.objects.annotate(
1564 sum=Window(
1565 expression=Sum("salary"),
1566 order_by=[F("hire_date").asc(), F("name").desc()],
1567 frame=RowRange(start=None, end=3),
1568 )
1569 ).order_by("sum", "hire_date")
1570 self.assertIn("ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING", str(qs.query))
1571 self.assertQuerySetEqual(
1572 qs,
1573 [
1574 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 280000),
1575 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 325000),
1576 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 362000),
1577 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 415000),
1578 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 453000),
1579 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 513000),
1580 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 553000),
1581 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 603000),
1582 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 637000),
1583 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 637000),
1584 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 637000),
1585 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 637000),
1586 ],
1587 transform=lambda row: (
1588 row.name,
1589 row.salary,
1590 row.department,
1591 row.hire_date,
1592 row.sum,
1593 ),
1594 )
1595
1596 def test_row_range_both_preceding(self):
1597 """
1598 A query with ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING.
1599 The resulting sum is the sum of the previous two (if they exist) rows
1600 according to the ordering clause.
1601 """
1602 qs = Employee.objects.annotate(
1603 sum=Window(
1604 expression=Sum("salary"),
1605 order_by=[F("hire_date").asc(), F("name").desc()],
1606 frame=RowRange(start=-2, end=-1),
1607 )
1608 ).order_by("hire_date")
1609 self.assertIn("ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING", str(qs.query))
1610 self.assertQuerySetEqual(
1611 qs,
1612 [
1613 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), None),
1614 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 100000),
1615 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 180000),
1616 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 125000),
1617 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 100000),
1618 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 100000),
1619 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 82000),
1620 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 90000),
1621 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 91000),
1622 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 98000),
1623 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 100000),
1624 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), 90000),
1625 ],
1626 transform=lambda row: (
1627 row.name,
1628 row.salary,
1629 row.department,
1630 row.hire_date,
1631 row.sum,
1632 ),
1633 )
1634
1635 def test_row_range_both_following(self):
1636 """
1637 A query with ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING.
1638 The resulting sum is the sum of the following two (if they exist) rows
1639 according to the ordering clause.
1640 """
1641 qs = Employee.objects.annotate(
1642 sum=Window(
1643 expression=Sum("salary"),
1644 order_by=[F("hire_date").asc(), F("name").desc()],
1645 frame=RowRange(start=1, end=2),
1646 )
1647 ).order_by("hire_date")
1648 self.assertIn("ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING", str(qs.query))
1649 self.assertQuerySetEqual(
1650 qs,
1651 [
1652 ("Miller", 100000, "Management", datetime.date(2005, 6, 1), 125000),
1653 ("Johnson", 80000, "Management", datetime.date(2005, 7, 1), 100000),
1654 ("Jones", 45000, "Accounting", datetime.date(2005, 11, 1), 100000),
1655 ("Smith", 55000, "Sales", datetime.date(2007, 6, 1), 82000),
1656 ("Jenson", 45000, "Accounting", datetime.date(2008, 4, 1), 90000),
1657 ("Williams", 37000, "Accounting", datetime.date(2009, 6, 1), 91000),
1658 ("Brown", 53000, "Sales", datetime.date(2009, 9, 1), 98000),
1659 ("Smith", 38000, "Marketing", datetime.date(2009, 10, 1), 100000),
1660 ("Wilkinson", 60000, "IT", datetime.date(2011, 3, 1), 90000),
1661 ("Johnson", 40000, "Marketing", datetime.date(2012, 3, 1), 84000),
1662 ("Adams", 50000, "Accounting", datetime.date(2013, 7, 1), 34000),
1663 ("Moore", 34000, "IT", datetime.date(2013, 8, 1), None),
1664 ],
1665 transform=lambda row: (
1666 row.name,
1667 row.salary,
1668 row.department,
1669 row.hire_date,
1670 row.sum,
1671 ),
1672 )
1673
1674 @skipUnlessDBFeature("can_distinct_on_fields")
1675 def test_distinct_window_function(self):
1676 """
1677 Window functions are not aggregates, and hence a query to filter out
1678 duplicates may be useful.
1679 """
1680 qs = (
1681 Employee.objects.annotate(
1682 sum=Window(
1683 expression=Sum("salary"),
1684 partition_by=ExtractYear("hire_date"),
1685 order_by=ExtractYear("hire_date"),
1686 ),
1687 year=ExtractYear("hire_date"),
1688 )
1689 .filter(sum__gte=45000)
1690 .values("year", "sum")
1691 .distinct("year")
1692 .order_by("year")
1693 )
1694 results = [
1695 {"year": 2005, "sum": 225000},
1696 {"year": 2007, "sum": 55000},
1697 {"year": 2008, "sum": 45000},
1698 {"year": 2009, "sum": 128000},
1699 {"year": 2011, "sum": 60000},
1700 {"year": 2013, "sum": 84000},
1701 ]
1702 for idx, val in zip(range(len(results)), results):
1703 with self.subTest(result=val):
1704 self.assertEqual(qs[idx], val)
1705
1706 def test_fail_update(self):
1707 """Window expressions can't be used in an UPDATE statement."""
1708 msg = (
1709 "Window expressions are not allowed in this query (salary=<Window: "
1710 "Max(Col(expressions_window_employee, expressions_window.Employee.salary)) "
1711 "OVER (PARTITION BY Col(expressions_window_employee, "
1712 "expressions_window.Employee.department))>)."
1713 )
1714 with self.assertRaisesMessage(FieldError, msg):
1715 Employee.objects.filter(department="Management").update(
1716 salary=Window(expression=Max("salary"), partition_by="department"),
1717 )
1718
1719 def test_fail_insert(self):
1720 """Window expressions can't be used in an INSERT statement."""
1721 msg = (
1722 "Window expressions are not allowed in this query (salary=<Window: "
1723 "Sum(Value(10000), order_by=OrderBy(F(pk), descending=False)) OVER ()"
1724 )
1725 with self.assertRaisesMessage(FieldError, msg):
1726 Employee.objects.create(
1727 name="Jameson",
1728 department="Management",
1729 hire_date=datetime.date(2007, 7, 1),
1730 salary=Window(expression=Sum(Value(10000), order_by=F("pk").asc())),
1731 )
1732
1733 def test_window_expression_within_subquery(self):
1734 subquery_qs = Employee.objects.annotate(
1735 highest=Window(
1736 FirstValue("id"),
1737 partition_by=F("department"),
1738 order_by=F("salary").desc(),
1739 )
1740 ).values("highest")
1741 highest_salary = Employee.objects.filter(pk__in=subquery_qs)
1742 self.assertCountEqual(
1743 highest_salary.values("department", "salary"),
1744 [
1745 {"department": "Accounting", "salary": 50000},
1746 {"department": "Sales", "salary": 55000},
1747 {"department": "Marketing", "salary": 40000},
1748 {"department": "IT", "salary": 60000},
1749 {"department": "Management", "salary": 100000},
1750 ],
1751 )
1752
1753 @skipUnlessDBFeature("supports_json_field")
1754 def test_key_transform(self):
1755 Detail.objects.bulk_create(
1756 [
1757 Detail(value={"department": "IT", "name": "Smith", "salary": 37000}),
1758 Detail(value={"department": "IT", "name": "Nowak", "salary": 32000}),
1759 Detail(value={"department": "HR", "name": "Brown", "salary": 50000}),
1760 Detail(value={"department": "HR", "name": "Smith", "salary": 55000}),
1761 Detail(value={"department": "PR", "name": "Moore", "salary": 90000}),
1762 ]
1763 )
1764 tests = [
1765 (KeyTransform("department", "value"), KeyTransform("name", "value")),
1766 (F("value__department"), F("value__name")),
1767 ]
1768 for partition_by, order_by in tests:
1769 with self.subTest(partition_by=partition_by, order_by=order_by):
1770 qs = Detail.objects.annotate(
1771 department_sum=Window(
1772 expression=Sum(
1773 Cast(
1774 KeyTextTransform("salary", "value"),
1775 output_field=IntegerField(),
1776 )
1777 ),
1778 partition_by=[partition_by],
1779 order_by=[order_by],
1780 )
1781 ).order_by("value__department", "department_sum")
1782 self.assertQuerySetEqual(
1783 qs,
1784 [
1785 ("Brown", "HR", 50000, 50000),
1786 ("Smith", "HR", 55000, 105000),
1787 ("Nowak", "IT", 32000, 32000),
1788 ("Smith", "IT", 37000, 69000),
1789 ("Moore", "PR", 90000, 90000),
1790 ],
1791 lambda entry: (
1792 entry.value["name"],
1793 entry.value["department"],
1794 entry.value["salary"],
1795 entry.department_sum,
1796 ),
1797 )
1798
1799 def test_invalid_start_value_range(self):
1800 msg = "start argument must be a negative integer, zero, or None, but got '3'."
1801 with self.assertRaisesMessage(ValueError, msg):
1802 list(
1803 Employee.objects.annotate(
1804 test=Window(
1805 expression=Sum("salary"),
1806 order_by=F("hire_date").asc(),
1807 frame=ValueRange(start=3),
1808 )
1809 )
1810 )
1811
1812 def test_invalid_end_value_range(self):
1813 msg = "end argument must be a positive integer, zero, or None, but got '-3'."
1814 with self.assertRaisesMessage(ValueError, msg):
1815 list(
1816 Employee.objects.annotate(
1817 test=Window(
1818 expression=Sum("salary"),
1819 order_by=F("hire_date").asc(),
1820 frame=ValueRange(end=-3),
1821 )
1822 )
1823 )
1824
1825 def test_invalid_start_end_value_for_row_range(self):
1826 msg = "start cannot be greater than end."
1827 with self.assertRaisesMessage(ValueError, msg):
1828 list(
1829 Employee.objects.annotate(
1830 test=Window(
1831 expression=Sum("salary"),
1832 order_by=F("hire_date").asc(),
1833 frame=RowRange(start=4, end=-3),
1834 )
1835 )
1836 )
1837
1838 def test_invalid_type_end_value_range(self):
1839 msg = "end argument must be a positive integer, zero, or None, but got 'a'."
1840 with self.assertRaisesMessage(ValueError, msg):
1841 list(
1842 Employee.objects.annotate(
1843 test=Window(
1844 expression=Sum("salary"),
1845 order_by=F("hire_date").asc(),
1846 frame=ValueRange(end="a"),
1847 )
1848 )
1849 )
1850
1851 def test_invalid_type_start_value_range(self):
1852 msg = "start argument must be a negative integer, zero, or None, but got 'a'."
1853 with self.assertRaisesMessage(ValueError, msg):
1854 list(
1855 Employee.objects.annotate(
1856 test=Window(
1857 expression=Sum("salary"),
1858 frame=ValueRange(start="a"),
1859 )
1860 )
1861 )
1862
1863 def test_invalid_type_end_row_range(self):
1864 msg = "end argument must be an integer, zero, or None, but got 'a'."
1865 with self.assertRaisesMessage(ValueError, msg):
1866 list(
1867 Employee.objects.annotate(
1868 test=Window(
1869 expression=Sum("salary"),
1870 frame=RowRange(end="a"),
1871 )
1872 )
1873 )
1874
1875 @skipUnlessDBFeature("only_supports_unbounded_with_preceding_and_following")
1876 def test_unsupported_range_frame_start(self):
1877 msg = (
1878 "%s only supports UNBOUNDED together with PRECEDING and FOLLOWING."
1879 % connection.display_name
1880 )
1881 with self.assertRaisesMessage(NotSupportedError, msg):
1882 list(
1883 Employee.objects.annotate(
1884 test=Window(
1885 expression=Sum("salary"),
1886 order_by=F("hire_date").asc(),
1887 frame=ValueRange(start=-1),
1888 )
1889 )
1890 )
1891
1892 @skipUnlessDBFeature("only_supports_unbounded_with_preceding_and_following")
1893 def test_unsupported_range_frame_end(self):
1894 msg = (
1895 "%s only supports UNBOUNDED together with PRECEDING and FOLLOWING."
1896 % connection.display_name
1897 )
1898 with self.assertRaisesMessage(NotSupportedError, msg):
1899 list(
1900 Employee.objects.annotate(
1901 test=Window(
1902 expression=Sum("salary"),
1903 order_by=F("hire_date").asc(),
1904 frame=ValueRange(end=1),
1905 )
1906 )
1907 )
1908
1909 def test_invalid_type_start_row_range(self):
1910 msg = "start argument must be an integer, zero, or None, but got 'a'."
1911 with self.assertRaisesMessage(ValueError, msg):
1912 list(
1913 Employee.objects.annotate(
1914 test=Window(
1915 expression=Sum("salary"),
1916 order_by=F("hire_date").asc(),
1917 frame=RowRange(start="a"),
1918 )
1919 )
1920 )
1921
1922 def test_invalid_filter(self):
1923 msg = (
1924 "Heterogeneous disjunctive predicates against window functions are not "
1925 "implemented when performing conditional aggregation."
1926 )
1927 qs = Employee.objects.annotate(
1928 window=Window(Rank()),
1929 past_dept_cnt=Count("past_departments"),
1930 )
1931 with self.assertRaisesMessage(NotImplementedError, msg):
1932 list(qs.filter(Q(window=1) | Q(department="Accounting")))
1933 with self.assertRaisesMessage(NotImplementedError, msg):
1934 list(qs.exclude(window=1, department="Accounting"))
1935
1936
1937 class WindowUnsupportedTests(TestCase):
1938 def test_unsupported_backend(self):
1939 msg = "This backend does not support window expressions."
1940 with mock.patch.object(connection.features, "supports_over_clause", False):
1941 with self.assertRaisesMessage(NotSupportedError, msg):
1942 Employee.objects.annotate(
1943 dense_rank=Window(expression=DenseRank())
1944 ).get()
1945
1946 def test_filter_subquery(self):
1947 qs = Employee.objects.annotate(
1948 department_salary_rank=Window(
1949 Rank(), partition_by="department", order_by="-salary"
1950 )
1951 )
1952 msg = (
1953 "Referencing outer query window expression is not supported: "
1954 "department_salary_rank."
1955 )
1956 with self.assertRaisesMessage(NotSupportedError, msg):
1957 qs.annotate(
1958 employee_name=Subquery(
1959 Employee.objects.filter(
1960 age=OuterRef("department_salary_rank")
1961 ).values("name")[:1]
1962 )
1963 )
1964
1965
1966 class NonQueryWindowTests(SimpleTestCase):
1967 def test_window_repr(self):
1968 self.assertEqual(
1969 repr(Window(expression=Sum("salary"), partition_by="department")),
1970 "<Window: Sum(F(salary)) OVER (PARTITION BY F(department))>",
1971 )
1972 self.assertEqual(
1973 repr(Window(expression=Avg("salary"), order_by=F("department").asc())),
1974 "<Window: Avg(F(salary)) OVER (OrderBy(F(department), descending=False))>",
1975 )
1976
1977 def test_window_frame_repr(self):
1978 self.assertEqual(
1979 repr(RowRange(start=-1)),
1980 "<RowRange: ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING>",
1981 )
1982 self.assertEqual(
1983 repr(ValueRange(start=None, end=1)),
1984 "<ValueRange: RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING>",
1985 )
1986 self.assertEqual(
1987 repr(ValueRange(start=0, end=0)),
1988 "<ValueRange: RANGE BETWEEN CURRENT ROW AND CURRENT ROW>",
1989 )
1990 self.assertEqual(
1991 repr(RowRange(start=0, end=0)),
1992 "<RowRange: ROWS BETWEEN CURRENT ROW AND CURRENT ROW>",
1993 )
1994 self.assertEqual(
1995 repr(RowRange(start=-2, end=-1)),
1996 "<RowRange: ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING>",
1997 )
1998 self.assertEqual(
1999 repr(RowRange(start=1, end=2)),
2000 "<RowRange: ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING>",
2001 )
2002 self.assertEqual(
2003 repr(RowRange(start=1, end=2, exclusion=WindowFrameExclusion.CURRENT_ROW)),
2004 "<RowRange: ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING EXCLUDE CURRENT ROW>",
2005 )
2006
2007 def test_window_frame_exclusion_repr(self):
2008 self.assertEqual(repr(WindowFrameExclusion.TIES), "WindowFrameExclusion.TIES")
2009
2010 def test_empty_group_by_cols(self):
2011 window = Window(expression=Sum("pk"))
2012 self.assertEqual(window.get_group_by_cols(), [])
2013 self.assertFalse(window.contains_aggregate)
2014
2015 def test_frame_empty_group_by_cols(self):
2016 frame = WindowFrame()
2017 self.assertEqual(frame.get_group_by_cols(), [])
2018
2019 def test_frame_window_frame_notimplemented(self):
2020 frame = WindowFrame()
2021 msg = "Subclasses must implement window_frame_start_end()."
2022 with self.assertRaisesMessage(NotImplementedError, msg):
2023 frame.window_frame_start_end(None, None, None)
2024
2025 def test_invalid_order_by(self):
2026 msg = (
2027 "Window.order_by must be either a string reference to a field, an "
2028 "expression, or a list or tuple of them."
2029 )
2030 with self.assertRaisesMessage(ValueError, msg):
2031 Window(expression=Sum("power"), order_by={"-horse"})
2032
2033 def test_invalid_source_expression(self):
2034 msg = "Expression 'Upper' isn't compatible with OVER clauses."
2035 with self.assertRaisesMessage(ValueError, msg):
2036 Window(expression=Upper("name"))