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 tilt_rank(self):
"""return percentage rank in score compared to all summoners"""
qset = Summoner.objects.annotate(rank=Window(
expression=PercentRank(), order_by=F("score").asc())).values(
"pk", "rank")
raw_query = "SELECT * FROM ({subquery}) as x WHERE id={id}".format(
subquery=qset.query,
id=self.id,
)
return Summoner.objects.raw(raw_query)[0].rank * 100
def filter_query_with_percentile(percentile, queryset):
"""
Calculate players percentile and filter to given value
:param percentile:
:param queryset:
:return:
"""
# filter on percentile when user requested
if percentile:
queryset = queryset.annotate(Avg('player__score')).annotate(
percentile=Window(expression=PercentRank(), order_by=F('player__score__avg').asc(), ))
# queryset does not support filtering after window operation
final_data = []
for elm in queryset:
if elm.percentile >= float(percentile):
final_data.append(elm)
queryset = final_data
return queryset
def get_queryset(self):
def make_boon_rank(boon):
return Window(expression=PercentRank(),
partition_by=[F('name')],
order_by=F(boon).asc())
dps_rank = Window(expression=PercentRank(),
partition_by=[F('archetype'),
F('name')],
order_by=F('DPS').asc())
queryset = Encounter.objects.annotate(
percent_rank=dps_rank, percentrankdps=F('percent_rank')).annotate(
percent_rank=make_boon_rank('might'),
percentrankmight=F('percent_rank')).annotate(
percent_rank=make_boon_rank('quickness'),
percentrankquickness=F('percent_rank')).annotate(
percent_rank=make_boon_rank('alacrity'),
percentrankalacrity=F('percent_rank')).annotate(
percent_rank=make_boon_rank('fury'),
percentrankfury=F('percent_rank'))
return queryset
def make_boon_rank(boon):
return Window(expression=PercentRank(),
partition_by=[F('name')],
order_by=F(boon).asc())