Django和MySQL字段的累计(运行)总和

Question

class Tip(models.Model):
prediction = models.ForeignKey(Prediction, on_delete=models.CASCADE)
pl = models.DecimalField(decimal_places=3, max_digits=5, default=None, blank=True, null=True)
class Prediction(models.Model):
fixture = models.ForeignKey(to=Fixture, on_delete=models.CASCADE, related_name="predictions", null=True, blank=True)
class Fixture(models.Model):
date = models.DateTimeField()

我试图得到Tip-模型中字段pl的累积和，这是由模型Fixture中的date-字段分组的。我用Django ORM尝试了以下操作：

Tip.objects.values("prediction__fixture__date__date").annotate(
cum_pl=Window(Sum('pl'), order_by=F('prediction__fixture__date__date'))
).order_by("prediction__fixture__date__date")

但此查询返回的是每个Tip的累计总和，而不是每个日期的总和。我如何才能在每次约会时做到这一点？

Answer 1

编辑以获得正确答案：

Tip.objects.values(
"prediction__fixture__date__date"
).annotate(
cum_pl=Window(
expression=Sum('pl'),
order_by=ExtractDay('prediction__fixture__date__date').desc()
)
).distinct("prediction__fixture__date__date")

我给出查询

SELECT
DISTINCT ON (
("transactions_fixture"."date" AT TIME ZONE 'UTC') :: date
) ("transactions_fixture"."date" AT TIME ZONE 'UTC') :: date,
SUM("transactions_tip"."pl") OVER (
ORDER BY
EXTRACT(
'day'
FROM
"transactions_fixture"."date" AT TIME ZONE 'UTC'
) DESC
) AS "cum_pl"
FROM
"transactions_tip"
INNER JOIN "transactions_prediction" ON (
"transactions_tip"."prediction_id" = "transactions_prediction"."id"
)
LEFT OUTER JOIN "transactions_fixture" ON (
"transactions_prediction"."fixture_id" = "transactions_fixture"."id"
)

刚刚在小提琴上测试过，效果很好，如果没有，请告诉我。

Answer 2

试试这个。

Tip.objects.values(
"prediction__fixture__date__date"
).annotate(
cum_pl=Sum('pl', distinct=True)
).order_by("prediction__fixture__date__date")