def test_aggregate_over_complex_annotation(self):
qs = Author.objects.annotate(
combined_ages=Sum(F('age') + F('friends__age')))
age = qs.aggregate(max_combined_age=Max('combined_ages'))
self.assertEqual(age['max_combined_age'], 176)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') * 2)
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') +
Max('combined_ages'))
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') +
Max('combined_ages'),
sum_combined_age=Sum('combined_ages'))
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
self.assertEqual(age['sum_combined_age'], 954)
age = qs.aggregate(max_combined_age_doubled=Max('combined_ages') +
Max('combined_ages'),
sum_combined_age_doubled=Sum('combined_ages') +
Sum('combined_ages'))
self.assertEqual(age['max_combined_age_doubled'], 176 * 2)
self.assertEqual(age['sum_combined_age_doubled'], 954 * 2)
def test_annotation_in_f_grouped_by_annotation(self):
qs = (
Publisher.objects.annotate(multiplier=Value(3))
# group by option => sum of value * multiplier
.values('name').annotate(
multiplied_value_sum=Sum(F('multiplier') *
F('num_awards'))).order_by())
self.assertCountEqual(qs, [
{
'multiplied_value_sum': 9,
'name': 'Apress'
},
{
'multiplied_value_sum': 0,
'name': "Jonno's House of Books"
},
{
'multiplied_value_sum': 27,
'name': 'Morgan Kaufmann'
},
{
'multiplied_value_sum': 21,
'name': 'Prentice Hall'
},
{
'multiplied_value_sum': 3,
'name': 'Sams'
},
])
def test_function_list_of_values(self):
qs = Employee.objects.annotate(lead=Window(
expression=Lead(expression='salary'),
order_by=[F('hire_date').asc(),
F('name').desc()],
partition_by='department',
)).values_list('name', 'salary', 'department', 'hire_date', 'lead')
qs = fix_ordering_for_mariadb(
qs, ('department', F('hire_date').asc(), F('name').desc()))
self.assertNotIn('GROUP BY', str(qs.query))
self.assertSequenceEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 45000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 37000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6,
1), 50000),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), None),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 34000),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), None),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 80000),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), None),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 40000),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), None),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 53000),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), None),
])
def test_distance_lookups_with_expression_rhs(self):
stx_pnt = self.stx_pnt.transform(
SouthTexasCity._meta.get_field('point').srid, clone=True)
qs = SouthTexasCity.objects.filter(
point__distance_lte=(stx_pnt, F('radius')), ).order_by('name')
self.assertEqual(self.get_names(qs), [
'Bellaire', 'Downtown Houston', 'Southside Place',
'West University Place'
])
# With a combined expression
qs = SouthTexasCity.objects.filter(
point__distance_lte=(stx_pnt, F('radius') * 2), ).order_by('name')
self.assertEqual(len(qs), 5)
self.assertIn('Pearland', self.get_names(qs))
# With spheroid param
if connection.features.supports_distance_geodetic:
hobart = AustraliaCity.objects.get(name='Hobart')
qs = AustraliaCity.objects.filter(
point__distance_lte=(hobart.point, F('radius') * 70,
'spheroid'), ).order_by('name')
self.assertEqual(self.get_names(qs),
['Canberra', 'Hobart', 'Melbourne'])
# With a complex geometry expression
self.assertFalse(
SouthTexasCity.objects.filter(
point__distance_gt=(Union('point', 'point'), 0)))
self.assertEqual(
SouthTexasCity.objects.filter(
point__distance_lte=(Union('point', 'point'), 0)).count(),
SouthTexasCity.objects.count(),
)
def test_order_by_nulls_last(self):
Article.objects.filter(headline="Article 3").update(
author=self.author_1)
Article.objects.filter(headline="Article 4").update(
author=self.author_2)
# asc and desc are chainable with nulls_last.
self.assertQuerysetEqualReversible(
Article.objects.order_by(
F("author").desc(nulls_last=True), 'headline'),
[self.a4, self.a3, self.a1, self.a2],
)
self.assertQuerysetEqualReversible(
Article.objects.order_by(
F("author").asc(nulls_last=True), 'headline'),
[self.a3, self.a4, self.a1, self.a2],
)
self.assertQuerysetEqualReversible(
Article.objects.order_by(
Upper("author__name").desc(nulls_last=True), 'headline'),
[self.a4, self.a3, self.a1, self.a2],
)
self.assertQuerysetEqualReversible(
Article.objects.order_by(
Upper("author__name").asc(nulls_last=True), 'headline'),
[self.a3, self.a4, self.a1, self.a2],
)
def test_nthvalue(self):
qs = Employee.objects.annotate(nth_value=Window(
expression=NthValue(expression='salary', nth=2),
order_by=[F('hire_date').asc(),
F('name').desc()],
partition_by=F('department'),
)).order_by('department', 'hire_date', 'name')
self.assertQuerysetEqual(
qs, [
('Jones', 'Accounting', datetime.date(2005, 11,
1), 45000, None),
('Jenson', 'Accounting', datetime.date(2008, 4,
1), 45000, 45000),
('Williams', 'Accounting', datetime.date(2009, 6,
1), 37000, 45000),
('Adams', 'Accounting', datetime.date(2013, 7,
1), 50000, 45000),
('Wilkinson', 'IT', datetime.date(2011, 3, 1), 60000, None),
('Moore', 'IT', datetime.date(2013, 8, 1), 34000, 34000),
('Miller', 'Management', datetime.date(2005, 6,
1), 100000, None),
('Johnson', 'Management', datetime.date(2005, 7,
1), 80000, 80000),
('Smith', 'Marketing', datetime.date(2009, 10,
1), 38000, None),
('Johnson', 'Marketing', datetime.date(2012, 3,
1), 40000, 40000),
('Smith', 'Sales', datetime.date(2007, 6, 1), 55000, None),
('Brown', 'Sales', datetime.date(2009, 9, 1), 53000, 53000),
], lambda row: (row.name, row.department, row.hire_date, row.
salary, row.nth_value))
def test_custom_functions(self):
Company(name='Apple',
motto=None,
ticker_name='APPL',
description='Beautiful Devices').save()
Company(name='Django Software Foundation',
motto=None,
ticker_name=None,
description=None).save()
Company(name='Google',
motto='Do No Evil',
ticker_name='GOOG',
description='Internet Company').save()
Company(name='Yahoo',
motto=None,
ticker_name=None,
description='Internet Company').save()
qs = Company.objects.annotate(
tagline=Func(F('motto'),
F('ticker_name'),
F('description'),
Value('No Tag'),
function='COALESCE')).order_by('name')
self.assertQuerysetEqual(qs, [('Apple', 'APPL'),
('Django Software Foundation', 'No Tag'),
('Google', 'Do No Evil'),
('Yahoo', 'Internet Company')], lambda c:
(c.name, c.tagline))
def test_order_by_f_expression_duplicates(self):
"""
A column may only be included once (the first occurrence) so we check
to ensure there are no duplicates by inspecting the SQL.
"""
qs = Article.objects.order_by(
F('headline').asc(),
F('headline').desc())
sql = str(qs.query).upper()
fragment = sql[sql.find('ORDER BY'):]
self.assertEqual(fragment.count('HEADLINE'), 1)
self.assertQuerysetEqual(qs, [
"Article 1",
"Article 2",
"Article 3",
"Article 4",
], attrgetter("headline"))
qs = Article.objects.order_by(
F('headline').desc(),
F('headline').asc())
sql = str(qs.query).upper()
fragment = sql[sql.find('ORDER BY'):]
self.assertEqual(fragment.count('HEADLINE'), 1)
self.assertQuerysetEqual(qs, [
"Article 4",
"Article 3",
"Article 2",
"Article 1",
], attrgetter("headline"))
def test_lag(self):
"""
Compute the difference between an employee's salary and the next
highest salary in the employee's department. Return None if the
employee has the lowest salary.
"""
qs = Employee.objects.annotate(lag=Window(
expression=Lag(expression='salary', offset=1),
partition_by=F('department'),
order_by=[F('salary').asc(), F('name').asc()],
)).order_by('department')
qs = fix_ordering_for_mariadb(
qs, ('department', F('salary').asc(), F('name').asc()))
self.assertQuerysetEqual(
qs, [
('Williams', 37000, 'Accounting', None),
('Jenson', 45000, 'Accounting', 37000),
('Jones', 45000, 'Accounting', 45000),
('Adams', 50000, 'Accounting', 45000),
('Moore', 34000, 'IT', None),
('Wilkinson', 60000, 'IT', 34000),
('Johnson', 80000, 'Management', None),
('Miller', 100000, 'Management', 80000),
('Smith', 38000, 'Marketing', None),
('Johnson', 40000, 'Marketing', 38000),
('Brown', 53000, 'Sales', None),
('Smith', 55000, 'Sales', 53000),
],
transform=lambda row:
(row.name, row.salary, row.department, row.lag))
def test_mti_annotations(self):
"""
Fields on an inherited model can be referenced by an
annotated field.
"""
d = DepartmentStore.objects.create(
name='Angus & Robinson',
original_opening=datetime.date(2014, 3, 8),
friday_night_closing=datetime.time(21, 00, 00),
chain='Westfield')
books = Book.objects.filter(rating__gt=4)
for b in books:
d.books.add(b)
qs = DepartmentStore.objects.annotate(
other_name=F('name'),
other_chain=F('chain'),
is_open=Value(True, BooleanField()),
book_isbn=F('books__isbn')).order_by('book_isbn').filter(
chain='Westfield')
self.assertQuerysetEqual(
qs, [('Angus & Robinson', 'Westfield', True, '155860191'),
('Angus & Robinson', 'Westfield', True, '159059725')],
lambda d: (d.other_name, d.other_chain, d.is_open, d.book_isbn))
def test_percent_rank(self):
"""
Calculate the percentage rank of the employees across the entire
company based on salary and name (in case of ambiguity).
"""
qs = Employee.objects.annotate(percent_rank=Window(
expression=PercentRank(),
order_by=[F('salary').asc(), F('name').asc()],
)).order_by('percent_rank')
# Round to account for precision differences among databases.
self.assertQuerysetEqual(qs, [
('Moore', 'IT', 34000, 0.0),
('Williams', 'Accounting', 37000, 0.0909090909),
('Smith', 'Marketing', 38000, 0.1818181818),
('Johnson', 'Marketing', 40000, 0.2727272727),
('Jenson', 'Accounting', 45000, 0.3636363636),
('Jones', 'Accounting', 45000, 0.4545454545),
('Adams', 'Accounting', 50000, 0.5454545455),
('Brown', 'Sales', 53000, 0.6363636364),
('Smith', 'Sales', 55000, 0.7272727273),
('Wilkinson', 'IT', 60000, 0.8181818182),
('Johnson', 'Management', 80000, 0.9090909091),
('Miller', 'Management', 100000, 1.0),
],
transform=lambda row:
(row.name, row.department, row.salary,
round(row.percent_rank, 10)))
def test_multiple_partitioning(self):
"""
Find the maximum salary for each department for people hired in the
same year.
"""
qs = Employee.objects.annotate(max=Window(
expression=Max('salary'),
partition_by=[F('department'),
ExtractYear(F('hire_date'))],
)).order_by('department', 'hire_date', 'name')
self.assertQuerysetEqual(
qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11,
1), 45000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4,
1), 45000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6,
1), 37000),
('Adams', 50000, 'Accounting', datetime.date(2013, 7,
1), 50000),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 60000),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 34000),
('Miller', 100000, 'Management', datetime.date(2005, 6,
1), 100000),
('Johnson', 80000, 'Management', datetime.date(2005, 7,
1), 100000),
('Smith', 38000, 'Marketing', datetime.date(2009,
10, 1), 38000),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3,
1), 40000),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 55000),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 53000),
],
transform=lambda row:
(row.name, row.salary, row.department, row.hire_date, row.max))
def test_mixed_type_annotation_numbers(self):
test = self.b1
b = Book.objects.annotate(combined=ExpressionWrapper(
F('pages') +
F('rating'), output_field=IntegerField())).get(isbn=test.isbn)
combined = int(test.pages + test.rating)
self.assertEqual(b.combined, combined)
def test_deconstruct_multiple_kwargs(self):
q = Q(price__gt=F('discounted_price'), price=F('discounted_price'))
path, args, kwargs = q.deconstruct()
self.assertEqual(args, (
('price', F('discounted_price')),
('price__gt', F('discounted_price')),
))
self.assertEqual(kwargs, {})
def test_deconstruct_negated(self):
q = ~Q(price__gt=F('discounted_price'))
path, args, kwargs = q.deconstruct()
self.assertEqual(args, ())
self.assertEqual(kwargs, {
'price__gt': F('discounted_price'),
'_negated': True,
})
def test_lead_default(self):
qs = Employee.objects.annotate(lead_default=Window(
expression=Lead(expression='salary', offset=5, default=60000),
partition_by=F('department'),
order_by=F('department').asc(),
))
self.assertEqual(
list(qs.values_list('lead_default', flat=True).distinct()),
[60000])
def test_mixed_type_annotation_date_interval(self):
active = datetime.datetime(2015, 3, 20, 14, 0, 0)
duration = datetime.timedelta(hours=1)
expires = datetime.datetime(2015, 3, 20, 14, 0, 0) + duration
Ticket.objects.create(active_at=active, duration=duration)
t = Ticket.objects.annotate(expires=ExpressionWrapper(
F('active_at') +
F('duration'), output_field=DateTimeField())).first()
self.assertEqual(t.expires, expires)
def test_complex_aggregations_require_kwarg(self):
with self.assertRaisesMessage(TypeError,
'Complex annotations require an alias'):
Author.objects.annotate(Sum(F('age') + F('friends__age')))
with self.assertRaisesMessage(TypeError,
'Complex aggregates require an alias'):
Author.objects.aggregate(Sum('age') / Count('age'))
with self.assertRaisesMessage(TypeError,
'Complex aggregates require an alias'):
Author.objects.aggregate(Sum(1))
def test_deconstruct_or(self):
q1 = Q(price__gt=F('discounted_price'))
q2 = Q(price=F('discounted_price'))
q = q1 | q2
path, args, kwargs = q.deconstruct()
self.assertEqual(args, (
('price__gt', F('discounted_price')),
('price', F('discounted_price')),
))
self.assertEqual(kwargs, {'_connector': 'OR'})
def test_deconstruct_and(self):
q1 = Q(price__gt=F('discounted_price'))
q2 = Q(price=F('discounted_price'))
q = q1 & q2
path, args, kwargs = q.deconstruct()
self.assertEqual(args, (
('price__gt', F('discounted_price')),
('price', F('discounted_price')),
))
self.assertEqual(kwargs, {})
def test_combined_annotation_commutative(self):
book1 = Book.objects.annotate(adjusted_rating=F('rating') +
2).get(pk=self.b1.pk)
book2 = Book.objects.annotate(adjusted_rating=2 +
F('rating')).get(pk=self.b1.pk)
self.assertEqual(book1.adjusted_rating, book2.adjusted_rating)
book1 = Book.objects.annotate(adjusted_rating=F('rating') +
None).get(pk=self.b1.pk)
book2 = Book.objects.annotate(adjusted_rating=None +
F('rating')).get(pk=self.b1.pk)
self.assertEqual(book1.adjusted_rating, book2.adjusted_rating)
def test_chaining_annotation_filter_with_m2m(self):
qs = Author.objects.filter(
name='Adrian Holovaty',
friends__age=35,
).annotate(jacob_name=F('friends__name'), ).filter(
friends__age=29, ).annotate(
james_name=F('friends__name'), ).values(
'jacob_name', 'james_name')
self.assertCountEqual(
qs,
[{
'jacob_name': 'Jacob Kaplan-Moss',
'james_name': 'James Bennett'
}],
)
def test_range_unbound(self):
"""A query with RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING."""
qs = Employee.objects.annotate(sum=Window(
expression=Sum('salary'),
partition_by='age',
order_by=[F('age').asc()],
frame=ValueRange(start=None, end=None),
)).order_by('department', 'hire_date', 'name')
self.assertIn(
'RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING',
str(qs.query))
self.assertQuerysetEqual(
qs,
[('Jones', 'Accounting', 45000, datetime.date(2005, 11,
1), 165000),
('Jenson', 'Accounting', 45000, datetime.date(2008, 4,
1), 165000),
('Williams', 'Accounting', 37000, datetime.date(2009, 6,
1), 165000),
('Adams', 'Accounting', 50000, datetime.date(2013, 7, 1), 130000),
('Wilkinson', 'IT', 60000, datetime.date(2011, 3, 1), 194000),
('Moore', 'IT', 34000, datetime.date(2013, 8, 1), 194000),
('Miller', 'Management', 100000, datetime.date(2005, 6,
1), 194000),
('Johnson', 'Management', 80000, datetime.date(2005, 7,
1), 130000),
('Smith', 'Marketing', 38000, datetime.date(2009, 10, 1), 165000),
('Johnson', 'Marketing', 40000, datetime.date(2012, 3,
1), 148000),
('Smith', 'Sales', 55000, datetime.date(2007, 6, 1), 148000),
('Brown', 'Sales', 53000, datetime.date(2009, 9, 1), 148000)],
transform=lambda row:
(row.name, row.department, row.salary, row.hire_date, row.sum))
def test_range_n_preceding_and_following(self):
qs = Employee.objects.annotate(sum=Window(
expression=Sum('salary'),
order_by=F('salary').asc(),
partition_by='department',
frame=ValueRange(start=-2, end=2),
))
self.assertIn('RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING',
str(qs.query))
self.assertQuerysetEqual(qs, [
('Williams', 37000, 'Accounting', datetime.date(2009, 6,
1), 37000),
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 90000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 90000),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 50000),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 53000),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 55000),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 40000),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 38000),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 60000),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 34000),
('Miller', 100000, 'Management', datetime.date(2005, 6,
1), 100000),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 80000),
],
transform=lambda row:
(row.name, row.salary, row.department, row.
hire_date, row.sum),
ordered=False)
def test_annotation_reverse_m2m(self):
books = Book.objects.annotate(store_name=F('store__name'), ).filter(
name='Practical Django Projects', ).order_by('store_name')
self.assertQuerysetEqual(
books, ['Amazon.com', 'Books.com', 'Mamma and Pappa\'s Books'],
lambda b: b.store_name)
def test_ntile(self):
"""
Compute the group for each of the employees across the entire company,
based on how high the salary is for them. There are twelve employees
so it divides evenly into four groups.
"""
qs = Employee.objects.annotate(ntile=Window(
expression=Ntile(num_buckets=4),
order_by=F('salary').desc(),
)).order_by('ntile', '-salary', 'name')
self.assertQuerysetEqual(
qs, [
('Miller', 'Management', 100000, 1),
('Johnson', 'Management', 80000, 1),
('Wilkinson', 'IT', 60000, 1),
('Smith', 'Sales', 55000, 2),
('Brown', 'Sales', 53000, 2),
('Adams', 'Accounting', 50000, 2),
('Jenson', 'Accounting', 45000, 3),
('Jones', 'Accounting', 45000, 3),
('Johnson', 'Marketing', 40000, 3),
('Smith', 'Marketing', 38000, 4),
('Williams', 'Accounting', 37000, 4),
('Moore', 'IT', 34000, 4),
], lambda x: (x.name, x.department, x.salary, x.ntile))
def test_lead_offset(self):
"""
Determine what the person hired after someone makes. Due to
ambiguity, the name is also included in the ordering.
"""
qs = Employee.objects.annotate(lead=Window(
expression=Lead('salary', offset=2),
partition_by='department',
order_by=F('hire_date').asc(),
))
self.assertQuerysetEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 37000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 50000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), None),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), None),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), None),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), None),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), None),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), None),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), None),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), None),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), None),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), None),
],
transform=lambda row:
(row.name, row.salary, row.department, row.
hire_date, row.lead),
ordered=False)
def test_rank(self):
"""
Rank the employees based on the year they're were hired. Since there
are multiple employees hired in different years, this will contain
gaps.
"""
qs = Employee.objects.annotate(rank=Window(
expression=Rank(),
order_by=ExtractYear(F('hire_date')).asc(),
))
self.assertQuerysetEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 1),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 1),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 1),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 4),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 5),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 6),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 6),
('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 6),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 9),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 10),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 11),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 11),
],
lambda entry:
(entry.name, entry.salary, entry.department,
entry.hire_date, entry.rank),
ordered=False)
def test_cume_dist(self):
"""
Compute the cumulative distribution for the employees based on the
salary in increasing order. Equal to rank/total number of rows (12).
"""
qs = Employee.objects.annotate(cume_dist=Window(
expression=CumeDist(),
order_by=F('salary').asc(),
)).order_by('salary', 'name')
# Round result of cume_dist because Oracle uses greater precision.
self.assertQuerysetEqual(
qs, [
('Moore', 'IT', 34000, 0.0833333333),
('Williams', 'Accounting', 37000, 0.1666666667),
('Smith', 'Marketing', 38000, 0.25),
('Johnson', 'Marketing', 40000, 0.3333333333),
('Jenson', 'Accounting', 45000, 0.5),
('Jones', 'Accounting', 45000, 0.5),
('Adams', 'Accounting', 50000, 0.5833333333),
('Brown', 'Sales', 53000, 0.6666666667),
('Smith', 'Sales', 55000, 0.75),
('Wilkinson', 'IT', 60000, 0.8333333333),
('Johnson', 'Management', 80000, 0.9166666667),
('Miller', 'Management', 100000, 1),
], lambda row:
(row.name, row.department, row.salary, round(row.cume_dist, 10)))
def test_row_number(self):
"""
The row number window function computes the number based on the order
in which the tuples were inserted. Depending on the backend,
Oracle requires an ordering-clause in the Window expression.
"""
qs = Employee.objects.annotate(row_number=Window(
expression=RowNumber(),
order_by=F('pk').asc(),
)).order_by('pk')
self.assertQuerysetEqual(
qs, [
('Jones', 'Accounting', 1),
('Williams', 'Accounting', 2),
('Jenson', 'Accounting', 3),
('Adams', 'Accounting', 4),
('Smith', 'Sales', 5),
('Brown', 'Sales', 6),
('Johnson', 'Marketing', 7),
('Smith', 'Marketing', 8),
('Wilkinson', 'IT', 9),
('Moore', 'IT', 10),
('Miller', 'Management', 11),
('Johnson', 'Management', 12),
], lambda entry: (entry.name, entry.department, entry.row_number))
