在Django中聚合窗口查询集

vaqhlq81 于 2023-04-07 发布在 Go

关注(0)|答案(2)|浏览(92)

后台

假设我们有一组 * 问题 ，以及一组 * 学生 * 回答了这些问题。答案已经过审查， 分数 * 已经在某个未知的范围内分配。
现在，我们需要将 scores 相对于每个 question 中的极值进行归一化。例如，如果 question 1 的最小 score 为4，最大 score 为12，则这些分数将分别归一化为0和1。之间的分数将线性插值（如Normalization to bring in the range of [0,1]中所述）。
然后，对于每个 * 学生 *，我们想知道所有 * 问题 * 组合的 * 标准化分数 * 的平均值。

最小示例

下面是一个 * 非常幼稚 * 的最小实现，只是为了说明我们想要实现的：

class Question(models.Model):
    pass

class Student(models.Model):
    def mean_normalized_score(self):
        normalized_scores = []
        for score in self.score_set.all():
            normalized_scores.append(score.normalized_value())
        return mean(normalized_scores) if normalized_scores else None

class Score(models.Model):
    student = models.ForeignKey(to=Student, on_delete=models.CASCADE)
    question = models.ForeignKey(to=Question, on_delete=models.CASCADE)
    value = models.FloatField()

    def normalized_value(self):
        limits = Score.objects.filter(question=self.question).aggregate(
            min=models.Min('value'), max=models.Max('value'))
        return (self.value - limits['min']) / (limits['max'] - limits['min'])

这工作得很好，但是在数据库查询等方面效率很低。

目标

与上面的实现不同，我更愿意将数字处理工作转移到数据库上。

我所尝试的

例如，考虑以下两个用例：
1.列出所有Score对象的normalized_value
1.列出所有Student对象的mean_normalized_score
第一个用例可以在查询中使用window functions来覆盖，如下所示：

w_min = Window(expression=Min('value'), partition_by=[F('question')])
w_max = Window(expression=Max('value'), partition_by=[F('question')])
annotated_scores = Score.objects.annotate(
    normalized_value=(F('value') - w_min) / (w_max - w_min))

这很好地工作了，因此不再需要示例中的Score.normalized_value()方法。
现在，我想为第二个用例做一些类似的事情，用单个数据库查询替换Student.mean_normalized_score()方法。
原始SQL可能看起来像这样（对于sqlite）：

SELECT id, student_id, AVG(normalized_value) AS mean_normalized_score
FROM (
    SELECT
        myapp_score.*,
        ((myapp_score.value - MIN(myapp_score.value) OVER (PARTITION BY myapp_score.question_id)) / (MAX(myapp_score.value) OVER (PARTITION BY myapp_score.question_id) - MIN(myapp_score.value) OVER (PARTITION BY myapp_score.question_id)))
        AS normalized_value
    FROM myapp_score
    ) 
GROUP BY student_id

我可以把它作为一个原始的Django查询，但是我还不能用Django的ORM来重现这个查询。
我尝试过在上面描述的annotated_scores查询集上构建，使用Django的Subquery，annotate()，aggregate()，Prefetch以及它们的组合，但我一定在某个地方犯了错误。
可能我得到的最接近的是这个：

subquery = Subquery(annotated_scores.values('normalized_value'))
Score.objects.values('student_id').annotate(mean=Avg(subquery))

但这是不正确的。
有人能给我指出正确的方向，而不诉诸原始查询吗？

django

来源：https://stackoverflow.com/questions/68292557/aggregating-a-windowed-queryset-in-django

2条答案

按热度按时间

46qrfjad1#

我可能已经找到了一种使用子查询的方法。主要的是，至少从django来说，我们不能在聚合上使用窗口函数，所以这就是阻止计算归一化值的平均值的原因。我在行上添加了注解来解释我试图做的事情：

# Get the minimum score per question
min_subquery = Score.objects.filter(question=OuterRef('question')).values('question').annotate(min=Min('value'))

# Get the maximum score per question
max_subquery = Score.objects.filter(question=OuterRef('question')).values('question').annotate(max=Max('value'))

# Calculate the normalized value per score, then get the average by grouping by students
mean_subquery = Score.objects.filter(student=OuterRef('pk')).annotate(
    min=Subquery(min_subquery.values('min')[:1]), 
    max=Subquery(max_subquery.values('max')[:1]), 
    normalized=ExpressionWrapper((F('value') - F('min'))/(F('max') - F('min')), output_field=FloatField())
).values('student').annotate(mean=Avg('normalized'))

# Get the calculated mean per student
Student.objects.annotate(mean=Subquery(mean_subquery.values('mean')[:1]))

生成的SQL为：

SELECT 
  "student"."id", 
  "student"."name", 
  (
    SELECT 
      AVG(
        (
          (
            V0."value" - (
              SELECT 
                MIN(U0."value") AS "min" 
              FROM 
                "score" U0 
              WHERE 
                U0."question_id" = (V0."question_id") 
              GROUP BY 
                U0."question_id" 
              LIMIT 
                1
            )
          ) / (
            (
              SELECT 
                MAX(U0."value") AS "max" 
              FROM 
                "score" U0 
              WHERE 
                U0."question_id" = (V0."question_id") 
              GROUP BY 
                U0."question_id" 
              LIMIT 
                1
            ) - (
              SELECT 
                MIN(U0."value") AS "min" 
              FROM 
                "score" U0 
              WHERE 
                U0."question_id" = (V0."question_id") 
              GROUP BY 
                U0."question_id" 
              LIMIT 
                1
            )
          )
        )
      ) AS "mean" 
    FROM 
      "score" V0 
    WHERE 
      V0."student_id" = ("student"."id") 
    GROUP BY 
      V0."student_id" 
    LIMIT 
      1
  ) AS "mean" 
FROM 
  "student"

赞(0）回复(0）举报 2023-04-07

uqcuzwp82#

正如@bdbd所提到的，从this Django issue判断，似乎还不可能注解窗口化的查询集（使用Django 3.2）。
作为一个临时的解决方案，我重构了@bdbd的优秀Subquery解决方案，如下所示。

class ScoreQuerySet(models.QuerySet):
    def annotate_normalized(self):
        w_min = Subquery(self.filter(
            question=OuterRef('question')).values('question').annotate(
            min=Min('value')).values('min')[:1])
        w_max = Subquery(self.filter(
            question=OuterRef('question')).values('question').annotate(
            max=Max('value')).values('max')[:1])
        return self.annotate(normalized=(F('value') - w_min) / (w_max - w_min))

    def aggregate_student_mean(self):
        return self.annotate_normalized().values('student_id').annotate(
            mean=Avg('normalized'))

class Score(models.Model):
    objects = ScoreQuerySet.as_manager()
    ...

注意：如果需要，我们可以在aggregate_student_mean()中的values()上添加更多的Student查找，例如student__name。只要我们注意不要弄乱分组。
现在，如果有可能注解窗口查询集，我们可以简单地用更简单的Window实现替换Subquery行：

w_min = Window(expression=Min('value'), partition_by=[F('question')])
w_max = Window(expression=Max('value'), partition_by=[F('question')])

编辑：Django 4.2现在支持过滤窗口查询集，但仍然不允许注解。

赞(0）回复(0）举报 2023-04-07

我来回答

在Django中聚合窗口查询集

后台

最小示例

目标

我所尝试的

2条答案

相关问题

热门标签

最新问答