时间戳

首页 > 解决方案 > 在一组 Max'd 值上运行 Window 函数

django - 在一组 Max'd 值上运行 Window 函数

问题描述

所以，我有一个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...
                                                             ^

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

谢谢！

标签： djangopostgresqldjango-modelsdjango-postgresql

解决方案

问题是函数 PercentRank 不接受任何参数。Django 文档不是很清楚。

推荐阅读