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_nth_returns_null(self): """ Find the nth row of the data set. None is returned since there are fewer than 20 rows in the test data. """ qs = Employee.objects.annotate(nth_value=Window( expression=NthValue('salary', nth=20), order_by=F('salary').asc())) self.assertEqual( list(qs.values_list('nth_value', flat=True).distinct()), [None])
def test_nth_negative_nth_value(self): msg = "NthValue requires a positive integer as for nth" with self.assertRaisesMessage(ValueError, msg): NthValue(expression="salary", nth=-1)
def test_nth_null_expression(self): msg = "NthValue requires a non-null source expression" with self.assertRaisesMessage(ValueError, msg): NthValue(expression=None)