Cumulative (running) sum of field Django and MySQL

user_8850721 0
Viewed 258
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()

I'm trying to get the cumulative sum of the field pl in the Tip-model, this grouped by the date-field in the model Fixture. I tried the following with the 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")

But this query returns the cumulative sum per Tip instead of per date. How can I achieve this per date instead?

mysql django-models django
2 Answers

Edit for the correct answer:

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")

I gives the query

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"
  )

Just tested on a fiddle and worked fine, let me know if it doesn't.

user_29329460

try this.

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