在一组最大值上运行窗口函数

Question

所以，我有一个对象Trainer，它与许多Survey对象有相反的关系。每个Survey对象都有大量的整数字段，并表示某个时间点的统计数据。并不是每个Survey对象都会填写每个字段，所以我使用django.db.models.Max()来获取最新值(值永远不会下降)。

然后，我尝试将这些值与数据库中所有其他Trainer对象中的其他人和django.db.models.functions.windows.PercentRank()进行比较，以获得它们的百分位数。

这就是我所拥有的，它运行良好，直到Window Expression - Calculate Percentile，然后我得到一个错误！

from django.db.models import Max, Window, F
from django.db.models.functions.window import PercentRank

from survey.models import Survey, Trainer

fp_default_fields = ['badge_travel_km', 'badge_capture_total', 'badge_evolved_total', 'badge_hatched_total', 'badge_pokestops_visited', 'badge_big_magikarp', 'badge_battle_attack_won', 'badge_small_rattata', 'badge_pikachu', 'badge_legendary_battle_won', 'badge_berries_fed', 'badge_hours_defended', 'badge_raid_battle_won', 'gymbadges_gold', 'badge_challenge_quests', 'badge_max_level_friends', 'badge_trading', 'badge_trading_distance']

def calculate_foo_points(survey: Survey, fields: str=fp_default_fields, top_x: int=10):
    '''
    Calculates a Trainer's Foo Points at the time of Surveying
    '''

# Base Query - All Trainers valid BEFORE the date of calculation
query = Trainer.objects.filter(survey__update_time__lte=survey.update_time)
# Modify Query - Exclude Spoofers
query = query.exclude(account_falsify_location_spawns=True,account_falsify_location_gyms=True,account_falsify_location_raids=True,account_falsify_location_level_up=True)
# Extend Query - Get Max'd Values
query = query.annotate(**{x:Max(f'survey__{x}') for x in fields})
# Window Expression - Calculate Percentile
query = query.annotate(**{f'{x}_percentile':Window(expression=PercentRank(x), order_by=F(x).asc()) for x in fields})
# Delay the fields we don't need
query = query.only('user__id')
# Get Trainer
trainer = [x for x in query if x.pk == survey.trainer.pk]
# Get 10* most highest ranked fields
top_x_result = sorted([getattr(trainer, x) for x in fields])[:top_x]
# Average together fields
result = sum(top_x_result, top_x)
return result

错误：

Traceback (most recent call last):
  File "/mnt/sshd/Gits/tl40/env/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: WITHIN GROUP is required for ordered-set aggregate percent_rank
LINE 1: ...e_trading_distance") AS "badge_trading_distance", PERCENT_RA...
                                                             ^

如果有人能够解释这意味着什么或如何绕过它，那就太棒了:)

谢谢!

Answer 1

问题是函数PercentRank不带任何参数。Django文档不太清楚。